Although MySQL has rename database syntax, but only in 5.1.7 to 5.1.23 provided, the other version does not, want to do rename operation how to do? Percona provides a shell
#!/Bin/bash# Copyright -Percona LLCand/orIts affiliatesSet -eif [- Z " $ $"]; Thenecho "rename_db<Server> <Database> <New_database>" Exit 1fidb_exists=' MySQL-H $1 -E "Show databases like ' $'"-SSS 'if [- N "$db _exists"]; Thenecho "Error:newDatabaseAlreadyexists$3" Exit 1fiTIMESTAMP=' Date+%s ' Character_set=' MySQL-H $1 -E "ShowCreate Database$2G "-Sss|Grep^Create |Awk-F'CHARACTER SET' '{print $}' |Awk'{print $}'' TABLES=' MySQL-H $1 -ESelecttable_name fromInformation_schema.tableswhereTable_schema=' $' andTable_type='BASE TABLE'"-SSS ' STATUS=$?if ["$STATUS"! = 0] || [- Z "$TABLES"]; Thenecho "Error Retrieving tables from$2" Exit 1Fiecho "Create Database$3 DEFAULT CHARACTER SET$character _set "MySQL-H $1 -ECreate Database$3 DEFAULT CHARACTER SET$character _set "TRIGGERS=' MySQL-H $1$2 -E "Show TRIGGERSG"|GrepTrigger:|Awk'{print $}'' views=' MySQL-H $1 -ESelecttable_name fromInformation_schema.tableswhereTable_schema=' $' andTable_type='VIEW'"-SSS 'if [- N "$VIEWS"]; Thenmysqldump-H $1$2$VIEWS> /Tmp/${2}_views${TIMESTAMP}.DumpFimysqldump-H $1$2 -D-T-R-E> /Tmp/${2}_triggers${TIMESTAMP}.Dump for TRIGGER inch$TRIGGERS, do echo "Drop Trigger$TRIGGER"MySQL-H $1$2 -EDrop Trigger$TRIGGER" Done for TABLE inch$TABLES, do echo "renameTable$2.$TABLE to$3.$TABLE"MySQL-H $1$2 -ESETForeign_key_checks=0; RenameTable$2.$TABLE to$3.$TABLE" Doneif [- N "$VIEWS"]; Thenecho "Loading views" MySQL-H $1$3 < /Tmp/${2}_views${TIMESTAMP}.DumpFiecho "Loading triggers, routines andevents "MySQL-H $1$3 < /Tmp/${2}_triggers${TIMESTAMP}.DumpTABLES=' MySQL-H $1 -ESelecttable_name fromInformation_schema.tableswhereTable_schema=' $' andTable_type='BASE TABLE'"-SSS 'if [- Z "$TABLES"]; Thenecho "DroppingDatabase$2"MySQL-H $1$2 -EDrop Database$2"Fiif [' mysql-h $1-e ' select COUNT (*) from Mysql.columns_priv where db= ' $ $ ' "-sss '-gt 0]; ThenColumns_priv="UPDATEMysql.columns_privSetDb=' $' WHEREDb=' $';" Fiif [' mysql-h $1-e ' select COUNT (*) from Mysql.procs_priv where db= ' $ $ ' "-sss '-gt 0]; ThenProcs_priv="UPDATEMysql.procs_privSetDb=' $' WHEREDb=' $';" Fiif [' mysql-h $1-e ' select COUNT (*) from Mysql.tables_priv where db= ' $ $ ' "-sss '-gt 0]; ThenTables_priv="UPDATEMysql.tables_privSetDb=' $' WHEREDb=' $';" Fiif [' mysql-h $1-e ' select COUNT (*) from mysql.db where db= ' $ $ ' "-sss '-gt 0]; ThenDb_priv="UPDATEMysql.dbSetDb=' $' WHEREDb=' $';" Fiif [- N "$COLUMNS _priv"] || [- N "$PROCS _priv"] || [- N "$TABLES _priv"] || [- N "$DB _priv"]; Thenecho "IFYou want toRENAME the GRANTS you need toRUN AllOUTPUT BELOW: "if [- N "$COLUMNS _priv"]; Thenecho "$COLUMNS _priv"; Fiif [- N "$PROCS _priv"]; Thenecho "$PROCS _priv"; Fiif [- N "$TABLES _priv"]; Thenecho "$TABLES _priv"; Fiif [- N "$DB _priv"]; Thenecho "$DB _priv"; fi echo "FlushPrivileges;" Fi
Source:
https://www.percona.com/blog/2013/12/24/renaming-database-schema-mysql/
What does MySQL rename database do?