Wie kann ich eine MySQL-Datenbank schnell umbenennen (Schemanamen ändern)?

Question

Das MySQL-Handbuch unter MySQL deckt dies ab.

Normalerweise lösche ich die Datenbank und importiere sie unter einem neuen Namen erneut. Bei sehr großen Datenbanken ist das keine Option. Anscheinend RENAME {DATABASE | SCHEMA} db_name TO new_db_name; macht schlechte Dinge, existiert nur in einer Handvoll Versionen und ist insgesamt eine schlechte Idee .

Dies muss funktionieren mit InnoDB die Dinge ganz anders speichert als MyISAM .

Answer 1

Schritte :

1. Hit http://localhost/phpmyadmin/
2. Wählen Sie Ihre DB
3. Klicken Sie auf die Registerkarte Operationen
4. Es wird eine Registerkarte "Datenbank umbenennen in" geben. Fügen Sie einen neuen Namen hinzu und überprüfen Sie die Berechtigungen anpassen.
5. Klicken Sie auf Los.

Answer 2

Die meisten Antworten hier sind aus einem von zwei Gründen falsch:

1. Sie können nicht einfach RENAME TABLE verwenden, da es möglicherweise Views und Trigger gibt. Wenn es Trigger gibt, schlägt RENAME TABLE fehl
2. Sie können mysqldump nicht verwenden, wenn Sie "schnell" (wie in der Frage gefordert) eine große Datenbank umbenennen wollen

Percona hat einen Blog-Beitrag darüber geschrieben, wie man das am besten macht: https://www.percona.com/blog/2013/12/24/renaming-database-schema-mysql/

und ein Skript, das von Simon R. Jones gepostet (erstellt?) wurde und das die in diesem Beitrag vorgeschlagene Funktion erfüllt. Ich habe einen Fehler behoben, den ich in dem Skript gefunden habe. Sie können es hier sehen:

https://gist.github.com/ryantm/76944318b0473ff25993ef2a7186213d

Hier ist eine Kopie des Dokuments:

#!/bin/bash
# Copyright 2013 Percona LLC and/or its affiliates
# @see https://www.percona.com/blog/2013/12/24/renaming-database-schema-mysql/
set -e
if [ -z "$3" ]; then
    echo "rename_db <server> <database> <new_database>"
    exit 1
fi
db_exists=`mysql -h $1 -e "show databases like '$3'" -sss`
if [ -n "$db_exists" ]; then
    echo "ERROR: New database already exists $3"
    exit 1
fi
TIMESTAMP=`date +%s`
character_set=`mysql -h $1 -e "SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = '$2'" -sss`
TABLES=`mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$2' and TABLE_TYPE='BASE TABLE'" -sss`
STATUS=$?
if [ "$STATUS" != 0 ] || [ -z "$TABLES" ]; then
    echo "Error retrieving tables from $2"
    exit 1
fi
echo "create database $3 DEFAULT CHARACTER SET $character_set"
mysql -h $1 -e "create database $3 DEFAULT CHARACTER SET $character_set"
TRIGGERS=`mysql -h $1 $2 -e "show triggers\G" | grep Trigger: | awk '{print $2}'`
VIEWS=`mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$2' and TABLE_TYPE='VIEW'" -sss`
if [ -n "$VIEWS" ]; then
    mysqldump -h $1 $2 $VIEWS > /tmp/${2}_views${TIMESTAMP}.dump
fi
mysqldump -h $1 $2 -d -t -R -E > /tmp/${2}_triggers${TIMESTAMP}.dump
for TRIGGER in $TRIGGERS; do
    echo "drop trigger $TRIGGER"
    mysql -h $1 $2 -e "drop trigger $TRIGGER"
done
for TABLE in $TABLES; do
    echo "rename table $2.$TABLE to $3.$TABLE"
    mysql -h $1 $2 -e "SET FOREIGN_KEY_CHECKS=0; rename table $2.$TABLE to $3.$TABLE"
done
if [ -n "$VIEWS" ]; then
    echo "loading views"
    mysql -h $1 $3 < /tmp/${2}_views${TIMESTAMP}.dump
fi
echo "loading triggers, routines and events"
mysql -h $1 $3 < /tmp/${2}_triggers${TIMESTAMP}.dump
TABLES=`mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$2' and TABLE_TYPE='BASE TABLE'" -sss`
if [ -z "$TABLES" ]; then
    echo "Dropping database $2"
    mysql -h $1 $2 -e "drop database $2"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.columns_priv where db='$2'" -sss` -gt 0 ]; then
    COLUMNS_PRIV="    UPDATE mysql.columns_priv set db='$3' WHERE db='$2';"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.procs_priv where db='$2'" -sss` -gt 0 ]; then
    PROCS_PRIV="    UPDATE mysql.procs_priv set db='$3' WHERE db='$2';"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.tables_priv where db='$2'" -sss` -gt 0 ]; then
    TABLES_PRIV="    UPDATE mysql.tables_priv set db='$3' WHERE db='$2';"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.db where db='$2'" -sss` -gt 0 ]; then
    DB_PRIV="    UPDATE mysql.db set db='$3' WHERE db='$2';"
fi
if [ -n "$COLUMNS_PRIV" ] || [ -n "$PROCS_PRIV" ] || [ -n "$TABLES_PRIV" ] || [ -n "$DB_PRIV" ]; then
    echo "IF YOU WANT TO RENAME the GRANTS YOU NEED TO RUN ALL OUTPUT BELOW:"
    if [ -n "$COLUMNS_PRIV" ]; then echo "$COLUMNS_PRIV"; fi
    if [ -n "$PROCS_PRIV" ]; then echo "$PROCS_PRIV"; fi
    if [ -n "$TABLES_PRIV" ]; then echo "$TABLES_PRIV"; fi
    if [ -n "$DB_PRIV" ]; then echo "$DB_PRIV"; fi
    echo "    flush privileges;"
fi

Speichern Sie sie in einer Datei namens rename_db und machen Sie das Skript ausführbar mit chmod +x rename_db dann verwenden Sie es wie ./rename_db localhost old_db new_db

Answer 3

Das verwende ich:

$ mysqldump -u root -p olddb >~/olddb.sql
$ mysql -u root -p
mysql> create database newdb;
mysql> use newdb
mysql> source ~/olddb.sql
mysql> drop database olddb;

Answer 4

Nun, es gibt 2 Methoden:

Methode 1: Eine bekannte Methode zur Umbenennung von Datenbankschemata besteht darin, das Schema mit Mysqldump zu dumpen und es in einem anderen Schema wiederherzustellen und dann das alte Schema zu löschen (falls erforderlich).

Von Shell

 mysqldump emp > emp.out
 mysql -e "CREATE DATABASE employees;"
 mysql employees < emp.out 
 mysql -e "DROP DATABASE emp;"

Die obige Methode ist zwar einfach, aber zeit- und platzaufwendig. Was ist, wenn das Schema mehr als eine 100 GB? Es gibt Methoden, mit denen Sie die oben genannten Befehle zusammenfassen können, um Platz zu sparen, allerdings wird dadurch keine Zeit gespart.

Um solche Situationen zu beheben, gibt es eine weitere schnelle Methode zur Umbenennung von Schemata, bei der jedoch einige Vorsicht geboten ist.

Methode 2: MySQL hat eine sehr gute Funktion zum Umbenennen von Tabellen, die sogar über verschiedene Schemata hinweg funktioniert. Diese Umbenennungsoperation ist atomar und niemand sonst kann auf die Tabelle zugreifen, während sie umbenannt wird. Der Vorgang ist in kurzer Zeit abgeschlossen, da die Änderung des Tabellennamens oder des Schemas nur eine Änderung der Metadaten ist. Hier ist ein verfahrenstechnischer Ansatz für die Umbenennung:

Erstellen Sie das neue Datenbankschema mit dem gewünschten Namen. Benennen Sie die Tabellen des alten Schemas in das neue Schema um, indem Sie den MySQL-Befehl "RENAME TABLE" verwenden. Löschen Sie das alte Datenbankschema. If there are views, triggers, functions, stored procedures in the schema, those will need to be recreated too . MySQL's "RENAME TABLE" schlägt fehl, wenn es Trigger für die Tabellen gibt. Um dies zu beheben, können wir Folgendes tun :

1) Dump the triggers, events and stored routines in a separate file. Dies geschieht mit den Flags -E, -R (zusätzlich zu -t -d, das die Trigger ausgibt) für den Befehl mysqldump. Sobald die Trigger gedumpt sind, müssen wir sie aus dem Schema entfernen, damit der Befehl RENAME TABLE funktioniert.

 $ mysqldump <old_schema_name> -d -t -R -E > stored_routines_triggers_events.out

2) Erzeugen Sie eine Liste von nur "BASE"-Tabellen. Diese können mit einer Abfrage auf information_schema.TABLES mesa.

 mysql> select TABLE_NAME from information_schema.tables where 
    table_schema='<old_schema_name>' and TABLE_TYPE='BASE TABLE';

3) Speichern Sie die Ansichten in einer Ausgabedatei. Die Ansichten können mit einer Abfrage über dieselbe information_schema.TABLES mesa.

mysql> select TABLE_NAME from information_schema.tables where 
   table_schema='<old_schema_name>' and TABLE_TYPE='VIEW';
 $ mysqldump <database> <view1> <view2> … > views.out

4) Löschen Sie die Trigger für die aktuellen Tabellen im alten Schema.

mysql> DROP TRIGGER <trigger_name>;
...

5) Stellen Sie die oben genannten Dump-Dateien wieder her, sobald alle in Schritt 2 gefundenen "Base"-Tabellen umbenannt sind.

mysql> RENAME TABLE <old_schema>.table_name TO <new_schema>.table_name;
...
$ mysql <new_schema> < views.out
$ mysql <new_schema> < stored_routines_triggers_events.out

Komplikationen mit den oben genannten Methoden: Möglicherweise müssen wir die GRANTS für Benutzer aktualisieren, damit sie mit dem richtigen schema_name übereinstimmen. Dies könnte mit einem einfachen UPDATE auf mysql.columns_priv, mysql.procs_priv, mysql.tables_priv, mysql.db tables behoben werden, wobei der Name des alten Schemas auf den des neuen Schemas aktualisiert und "Flush privileges;" aufgerufen wird. Obwohl "Methode 2 ein wenig komplizierter erscheint als "Methode 1, ist dies vollständig skriptfähig. Ein einfaches Bash-Skript, das die oben genannten Schritte in der richtigen Reihenfolge ausführt, kann Ihnen helfen, beim nächsten Mal beim Umbenennen von Datenbankschemas Platz und Zeit zu sparen.

Das Percona Remote DBA-Team hat ein Skript namens "rename_db" geschrieben, das folgendermaßen funktioniert:

[root@dba~]# /tmp/rename_db
rename_db <server> <database> <new_database>

Um die Verwendung dieses Skripts zu demonstrieren, wurde ein Beispielschema "emp" verwendet, Testauslöser erstellt und Routinen für dieses Schema gespeichert. Wir werden versuchen, das Datenbankschema mit dem Skript umzubenennen, was im Gegensatz zur zeitaufwändigen Dump/Restore-Methode einige Sekunden dauert.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| emp                |
| mysql              |
| performance_schema |
| test               |
+--------------------+

[root@dba ~]# time /tmp/rename_db localhost emp emp_test
create database emp_test DEFAULT CHARACTER SET latin1
drop trigger salary_trigger
rename table emp.__emp_new to emp_test.__emp_new
rename table emp._emp_new to emp_test._emp_new
rename table emp.departments to emp_test.departments
rename table emp.dept to emp_test.dept
rename table emp.dept_emp to emp_test.dept_emp
rename table emp.dept_manager to emp_test.dept_manager
rename table emp.emp to emp_test.emp
rename table emp.employees to emp_test.employees
rename table emp.salaries_temp to emp_test.salaries_temp
rename table emp.titles to emp_test.titles
loading views
loading triggers, routines and events
Dropping database emp

real    0m0.643s
user    0m0.053s
sys     0m0.131s

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| emp_test           |
| mysql              |
| performance_schema |
| test               |
+--------------------+

Wie Sie in der obigen Ausgabe sehen können, wurde das Datenbankschema "emp" in weniger als einer Sekunde in "emp_test" umbenannt. Schließlich ist dies das Skript von Percona, das oben für "Methode 2" verwendet wird.

#!/bin/bash
# Copyright 2013 Percona LLC and/or its affiliates
set -e
if [ -z "$3" ]; then
    echo "rename_db <server> <database> <new_database>"
    exit 1
fi
db_exists=`mysql -h $1 -e "show databases like '$3'" -sss`
if [ -n "$db_exists" ]; then
    echo "ERROR: New database already exists $3"
    exit 1
fi
TIMESTAMP=`date +%s`
character_set=`mysql -h $1 -e "show create database $2\G" -sss | grep ^Create | awk -F'CHARACTER SET ' '{print $2}' | awk '{print $1}'`
TABLES=`mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$2' and TABLE_TYPE='BASE TABLE'" -sss`
STATUS=$?
if [ "$STATUS" != 0 ] || [ -z "$TABLES" ]; then
    echo "Error retrieving tables from $2"
    exit 1
fi
echo "create database $3 DEFAULT CHARACTER SET $character_set"
mysql -h $1 -e "create database $3 DEFAULT CHARACTER SET $character_set"
TRIGGERS=`mysql -h $1 $2 -e "show triggers\G" | grep Trigger: | awk '{print $2}'`
VIEWS=`mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$2' and TABLE_TYPE='VIEW'" -sss`
if [ -n "$VIEWS" ]; then
    mysqldump -h $1 $2 $VIEWS > /tmp/${2}_views${TIMESTAMP}.dump
fi
mysqldump -h $1 $2 -d -t -R -E > /tmp/${2}_triggers${TIMESTAMP}.dump
for TRIGGER in $TRIGGERS; do
    echo "drop trigger $TRIGGER"
    mysql -h $1 $2 -e "drop trigger $TRIGGER"
done
for TABLE in $TABLES; do
    echo "rename table $2.$TABLE to $3.$TABLE"
    mysql -h $1 $2 -e "SET FOREIGN_KEY_CHECKS=0; rename table $2.$TABLE to $3.$TABLE"
done
if [ -n "$VIEWS" ]; then
    echo "loading views"
    mysql -h $1 $3 < /tmp/${2}_views${TIMESTAMP}.dump
fi
echo "loading triggers, routines and events"
mysql -h $1 $3 < /tmp/${2}_triggers${TIMESTAMP}.dump
TABLES=`mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$2' and TABLE_TYPE='BASE TABLE'" -sss`
if [ -z "$TABLES" ]; then
    echo "Dropping database $2"
    mysql -h $1 $2 -e "drop database $2"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.columns_priv where db='$2'" -sss` -gt 0 ]; then
    COLUMNS_PRIV="    UPDATE mysql.columns_priv set db='$3' WHERE db='$2';"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.procs_priv where db='$2'" -sss` -gt 0 ]; then
    PROCS_PRIV="    UPDATE mysql.procs_priv set db='$3' WHERE db='$2';"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.tables_priv where db='$2'" -sss` -gt 0 ]; then
    TABLES_PRIV="   UPDATE mysql.tables_priv set db='$3' WHERE db='$2';"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.db where db='$2'" -sss` -gt 0 ]; then
    DB_PRIV="    UPDATE mysql.db set db='$3' WHERE db='$2';"
fi
if [ -n "$COLUMNS_PRIV" ] || [ -n "$PROCS_PRIV" ] || [ -n "$TABLES_PRIV" ] || [ -n "$DB_PRIV" ]; then
    echo "IF YOU WANT TO RENAME the GRANTS YOU NEED TO RUN ALL OUTPUT BELOW:"
    if [ -n "$COLUMNS_PRIV" ]; then echo "$COLUMNS_PRIV"; fi
    if [ -n "$PROCS_PRIV" ]; then echo "$PROCS_PRIV"; fi
    if [ -n "$TABLES_PRIV" ]; then echo "$TABLES_PRIV"; fi
    if [ -n "$DB_PRIV" ]; then echo "$DB_PRIV"; fi
    echo "    flush privileges;"
fi

Answer 5

MySQL unterstützt das Umbenennen einer Datenbank über seine Befehlsschnittstelle derzeit nicht, aber Sie können die Datenbank umbenennen, wenn Sie Zugriff auf das Verzeichnis haben, in dem MySQL seine Datenbanken speichert. Bei Standard-MySQL-Installationen befindet sich dieses Verzeichnis normalerweise im Verzeichnis Data unter dem Verzeichnis, in dem MySQL installiert wurde. Suchen Sie den Namen der Datenbank, die Sie umbenennen möchten, im Data-Verzeichnis und benennen Sie sie um. Das Umbenennen des Verzeichnisses kann allerdings zu Problemen mit den Berechtigungen führen. Seien Sie sich dessen bewusst.

Anmerkung: Sie müssen MySQL stoppen, bevor Sie die Datenbank umbenennen können

Ich würde empfehlen, eine neue Datenbank zu erstellen (unter dem von Ihnen gewünschten Namen) und die benötigten Daten aus der alten in die neue Datenbank zu exportieren/importieren. Ziemlich einfach.