One of the routine tasks for a DBA is MySQL renaming database schemas, and as such MySQL added a command to carry out that purpose called “RENAME DATABASE <database_name>”. However, this command just made it through a few minor releases before being discontinued (from MySQL 5.1.7 to 5.1.23). Here’s a link to the reference manual regarding the command http://dev.mysql.com/doc/refman/5.1/en/rename-database.html. Vadim wrote a MySQL Performance Blog post about this a few years ago where he mentions the dangerous nature of this command – that post was appropriately headlined, “Dangerous Command.” Today we will see what are the ways in which a database schema can be renamed and which of them is the quickest.
How to Rename MySQL Databases
Method 1: A well-known method for renaming database schema is by dumping the schema using Mysqldump and restoring it in another schema, and then dropping the old schema (if needed).
1 2 3 4 | [root@percona ~]# mysqldump emp > emp.out [root@percona ~]# mysql -e "CREATE DATABASE employees;" [root@percona ~]# mysql employees < emp.out [root@percona ~]# mysql -e "DROP DATABASE emp;" |
Although the above method is easy, it is time and space consuming. What if the schema is more than a 100GB? There are methods where you can pipe the above commands together to save on space, however, it will not save time.
To remedy such situations, there is another quick method to rename schemas, however, some care must be taken while doing it.
Method 2: MySQL has a very good feature for renaming tables that even works across different schemas. This rename operation is atomic and no one else can access the table while it is being renamed. This takes a short time to complete since changing a table’s name or its schema is only a metadata change. Here is the procedural approach at doing the rename:
- a) Create the new database schema with the desired name.
- b) Rename the tables from old schema to the new schema, using MySQL’s “RENAME TABLE” command.
- c) Drop the old database schema.
If there are views, triggers, functions, stored procedures in the schema, those will need to be recreated too. MySQL’s “RENAME TABLE” fails if there are triggers exists on the tables. To remedy this we can do the following things :
1) Dump the triggers, events and stored routines in a separate file. This is done using -E, -R flags (in addition to -t -d which dumps the triggers) to the mysqldump command. Once triggers are dumped, we will need to drop them from the schema, for RENAME TABLE command to work.
1 | $ mysqldump <old_schema_name> -d -t -R -E > stored_routines_triggers_events.out |
2) Generate a list of only “BASE” tables. These can be found using a query on information_schema.TABLES table.
1 | mysql> select TABLE_NAME from information_schema.tables where table_schema='<old_schema_name>' and TABLE_TYPE='BASE TABLE'; |
3) Dump the views in an out file. Views can be found using a query on the same information_schema.TABLES table.
1 2 | 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) Drop the triggers on the current tables in the old_schema.
1 2 | mysql> DROP TRIGGER <trigger_name>; ... |
5) Restore the above dump files once all the “Base” tables found in step #2 are renamed.
1 2 3 4 | 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 |
Intricacies with above methods :
- We may need to update the GRANTS for users such that they match the correct schema_name. These could be fixed with a simple UPDATE on mysql.columns_priv, mysql.procs_priv, mysql.tables_priv, mysql.db tables updating the old_schema name to new_schema and calling “Flush privileges;”.
Although “method 2” seems a bit more complicated than the “method 1”, this is totally scriptable. A simple bash script to carry out the above steps in proper sequence can help you save space and time while renaming database schemas next time.
We on the Percona Remote DBA team have written a script called “rename_db” that works in the following way :
1 2 | [root@percona ~]# /tmp/rename_db rename_db <server> <database> <new_database> |
To demonstrate the use of this script, we used a sample schema “emp”, created test triggers, stored routines on that schema. We will try to rename the database schema using the script, which takes some seconds to complete as opposed to time-consuming dump/restore method.
1 2 3 4 5 6 7 8 9 10 | mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | emp | | mysql | | performance_schema | | test | +--------------------+ |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | [root@percona ~]# 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 |
1 2 3 4 5 6 7 8 9 10 | mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | emp_test | | mysql | | performance_schema | | test | +--------------------+ |
As you can see in the above output the database schema “emp” was renamed to “emp_test” in less than a second.
Lastly, we are happy to share the script we used above for “method 2”.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 | #!/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 $2G" -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 triggersG" | 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 |
Very helpful.
script means is it “rename_db.bat” or “rename_db.sql” file..
Good trick for method 2.
Method 1 is for very small schemas.
Its really the best way. Also it saves lot of time in doing.
Very useful script. And that toolbar above the script that allows easy copy – paste is useful as well 🙂
if some user’s privileges related to this database,i can use the follow bash to flush his privileges
#!/bin/bash
DATE=
date +%Y%m%d
if [ -f “/tmp/grants$DATE.sql” ];then
rm -fr /tmp/grants$DATE.sql;
fi
for i in
mysql -N -s -e "select concat(\"show grants for '\",user,\"'@'\",host,\"';\") from mysql.user"|grep -w 'show grants'| mysql -N |grep -w 'GRANT'| sed 's/$/;/g' | grep $1 | awk -F'TO' '{print $2}'
;do mysql -N -s -e “show grants for $i”|grep -w ‘GRANT’| sed ‘s/$/;/g’ >> /tmp/grants$DATE.sql;doneif [ -f “/tmp/grants$DATE.sql” ];then
sed -i “s/$1/$2/g” /tmp/grants$DATE.sql;
fi
for i in
mysql -N -s -e "select concat(\"show grants for '\",user,\"'@'\",host,\"';\") from mysql.user"|grep -w 'show grants'| mysql -N |grep -w 'GRANT'| sed 's/$/;/g' | grep $1 | awk -F'TO' '{print $2}'
;do mysql -N -s -e “drop user $i” ;donemysql < /tmp/grants$DATE.sql
Does this work both on shared tablespace and innodb_file_per_table ?
Steven, this should work on both types of tablespaces in InnoDB. Since renaming table is just a metadata change I dont think it could be a problem.
Steven, I was curious about your question, so I tested the rename to a new database procedure above with both a shared tablespace and innodb_file_per_table. It works the same with both. I’m using 5.6.15.
What would be the fastest way to copy a huge database with innodb tables to a new database?
I would like to add that if you are using Percona Cluster, changing the mysql.db table directly will not replicate to other cluster nodes. So the commands for modifying permissions needs to be run on all cluster nodes.
Also do not forget to put user/password for both [mysql] and [mysqldump] in a file first – otherwise the script cannot connect to database.
Thank you for this great solution, it saves me a lot of time!
The only problem was the trigger dump cannot restored because it writes also the (source) database name into the dump:
….. TRIGGER
accounts_before_insert
BEFORE INSERT ONtest_crm_at
.accounts
FOR EACH ROW BEGIN ….And at restore this fails at the database with the new name.
I found no way to prevent the output of the database name in the mysqldump at line 28 ….
So I fix this with add following after line 28:
sed -i “s/
$2
\./$3
\./g” /tmp/${2}_triggers${TIMESTAMP}.dumpIt’s a little strange but I think with inclusion of the point in
databasename
. in the regex string, it ensures that it not renames other elements as the database name.This script is almost perfect for me when I need to rename schemas from production snapshots to different names for non-production environments. However, there is one table in all of my schemas that consistently refuses to be renamed (I’ve run your script line by line to isolate it). This is what happens in the MySQL interactive client, and I don’t understand:
mysql> rename table promo_max.promotions to promo_xxx.promotions ;
ERROR 1050 (42S01): Table ‘./promo_xxx/promotions’ already exists
Actually renaming that one table to ANYTHING — even a different table name in the same schema fails:
mysql> rename table promo_max.promotions to promo_max.s ;
ERROR 1050 (42S01): Table ‘./promo_max/s’ already exists
mysql> rename table promo_max.promotions to promo_max.1 ;
ERROR 1050 (42S01): Table ‘./promo_max/1’ already exists
mysql> rename table promo_max.promotions to promo_max.HUBBLE_TELESCOPE ;
ERROR 1050 (42S01): Table ‘./promo_max/HUBBLE_TELESCOPE’ already exists
(None of these tables, it goes without saying, actually exist.)
The target database is stock MySQL 5.5.33 on Amazon RDS, using innodb_file_per_table=ON. So I don’t have access to the base filesystem to see if the claimed files already exist (but given the refusal of arbitrary, never-used names, I’m pretty sure they don’t).
If you wanted to live life on the edge, you can also take the DB offline, rename the folder for the database (careful of MySQL file naming conventions), and then restart the DB.
Because of the 1-to-1 nature between folder name and database name, MySQL will generally re-establish the database in the schema list … but make no effort to fix permissions etc.
In line 14:
“show create database $2G”
needs to be:
“show create database $2\G”
similarly in line 23:
“show triggersG”
needs to be:
“show triggers\G”
Here’s a script that can be run to get the command needed to rename all of the tables.
SET @oldSchemaName = ‘oldSchema’;
SET @newSchemaName = ‘newSchema’;
SET SESSION group_concat_max_len = 4294967295;
SELECT CONCAT(‘RENAME TABLE ‘, GROUP_CONCAT(mySchema.table_schema,’.
',table_name, '
TO ‘,@newSchemaName,’.',table_name,'
\n’),’;’) AS StatementFROM information_schema.TABLES AS mySchema
WHERE mySchema.table_schema LIKE @oldSchemaName
GROUP BY mySchema.table_schema
;
It’s very simple in WAMP Server. It provide option to rename an existing database. However you can also rename database if you don’have server like WAMP, XAMP. First export you database data with structure and create new database. Import your data into new database and drop previous one.
Had to point this out.
Incorrect – If there are views, triggers, functions, stored procedures in the schema, those will need to be recreated too. MySQL’s “RENAME TABLE” fails if there are triggers exists on the tables.
Correct – If there are views, triggers, functions, stored procedures in the schema, those will need to be recreated too. MySQL’s “RENAME TABLE” fails if triggers exist on the tables.
Here is a version of the script with the fix for \G mentioned before applied, some corrections to schema names escaping characters, and a couple improvements for the messages shown (in my opinion). It works on my 5.5.47-0ubuntu0.12.04.1 version.
#!/bin/bash
# Copyright 2013 Percona LLC and/or its affiliates
set -e
if [ -z “$3” ]; then
echo “rename_db ”
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
# Reason for all the forward slashes: http://stackoverflow.com/a/2310284/3779073
character_set=
mysql -h $1 -e "show create database \\
$2\\`\G” -sss | grep ^Create | awk -F’CHARACTER SET ‘ ‘{print $2}’ | awk ‘{print $1}’
mysql -h $1 -e “select TABLE_NAME from information_schema.tables where table_schema=’$2′ and TABLE_TYPE=’BASE TABLE'” -sssTABLES=
$3STATUS=$?
if [ "$STATUS" != 0 ] || [ -z "$TABLES" ]; then
echo "Error retrieving tables from '$2'"
exit 1
fi
echo "create database
DEFAULT CHARACTER SET $character_set"
$3mysql -h $1 -e "create database
DEFAULT CHARACTER SET $character_set"
mysql -h $1 $2 -e “show triggers\G” | grep Trigger: | awk ‘{print $2}’TRIGGERS=
mysql -h $1 -e “select TABLE_NAME from information_schema.tables where table_schema=’$2′ and TABLE_TYPE=’VIEW'” -sssVIEWS=
$2if [ -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
.$TABLE to
$3.$TABLE"
$2mysql -h $1 $2 -e "SET FOREIGN_KEY_CHECKS=0; rename table
.$TABLE to
$3.$TABLE"
mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$2' and TABLE_TYPE='BASE TABLE'" -sssdone
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=
$2if [ -z "$TABLES" ]; then
echo "Dropping database
"
$2mysql -h $1 $2 -e "drop database
"
mysql -h $1 -e "select count(*) from mysql.columns_priv where db='$2'" -sssfi
if [
-gt 0 ]; then
mysql -h $1 -e "select count(*) from mysql.procs_priv where db='$2'" -sssCOLUMNS_PRIV=" UPDATE mysql.columns_priv set db='$3' WHERE db='$2';"
fi
if [
-gt 0 ]; then
mysql -h $1 -e "select count(*) from mysql.tables_priv where db='$2'" -sssPROCS_PRIV=" UPDATE mysql.procs_priv set db='$3' WHERE db='$2';"
fi
if [
-gt 0 ]; then
mysql -h $1 -e "select count(*) from mysql.db where db='$2'" -sss` -gt 0 ]; thenTABLES_PRIV=" UPDATE mysql.tables_priv set db='$3' WHERE db='$2';"
fi
if [
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
The script you pasted is not properly formated probably due to workpress, would it be possible to paste is as plain text ?
I don’t know if this will work; if not, simply indent once the ifs and for structures, there is just one level.
# https://www.percona.com/blog/2013/12/24/renaming-database-schema-mysql/#comment-10966856
#!/bin/bash
# Copyright 2013 Percona LLC and/or its affiliates
set -e
if [ -z "$5" ]; then
echo "rename_db "
exit 1
fi
db_exists=
mysql --user=$2 --password=$3 -h $1 -e "show databases like '$5'" -sss
if [ -n "$db_exists" ]; then
echo "ERROR: New database already exists '$5'"
exit 1
fi
TIMESTAMP=
date +%s
# Reason for all the forward slashes: http://stackoverflow.com/a/2310284/3779073
character_set=
mysql --user=$2 --password=$3 -h $1 -e "show create database \\
$4\\`\G" -sss | grep ^Create | awk -F'CHARACTER SET ' '{print $2}' | awk '{print $1}'
mysql --user=$2 --password=$3 -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$4' and TABLE_TYPE='BASE TABLE'" -sssTABLES=
$5STATUS=$?
if [ "$STATUS" != 0 ] || [ -z "$TABLES" ]; then
echo "Error retrieving tables from '$4'"
exit 1
fi
echo "create database
DEFAULT CHARACTER SET $character_set"
$5mysql --user=$2 --password=$3 -h $1 -e "create database
DEFAULT CHARACTER SET $character_set"
mysql --user=$2 --password=$3 -h $1 $4 -e "show triggers\G" | grep Trigger: | awk '{print $4}'TRIGGERS=
mysql --user=$2 --password=$3 -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$4' and TABLE_TYPE='VIEW'" -sssVIEWS=
$4if [ -n "$VIEWS" ]; then
mysqldump --user=$2 --password=$3 -h $1 $4 $VIEWS > /tmp/${2}_views${TIMESTAMP}.dump
fi
mysqldump --user=$2 --password=$3 -h $1 $4 -d -t -R -E > /tmp/${2}_triggers${TIMESTAMP}.dump
for TRIGGER in $TRIGGERS; do
echo "drop trigger $TRIGGER"
mysql --user=$2 --password=$3 -h $1 $4 -e "drop trigger $TRIGGER"
done
for TABLE in $TABLES; do
echo "rename table
.$TABLE to
$5.$TABLE"
$4mysql --user=$2 --password=$3 -h $1 $4 -e "SET FOREIGN_KEY_CHECKS=0; rename table
.$TABLE to
$5.$TABLE"
mysql --user=$2 --password=$3 -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$4' and TABLE_TYPE='BASE TABLE'" -sssdone
if [ -n "$VIEWS" ]; then
echo "loading views"
mysql --user=$2 --password=$3 -h $1 $5 < /tmp/${2}_views${TIMESTAMP}.dump
fi
echo "loading triggers, routines and events"
mysql --user=$2 --password=$3 -h $1 $5 < /tmp/${2}_triggers${TIMESTAMP}.dump
TABLES=
$4if [ -z "$TABLES" ]; then
echo "Dropping database
"
$4mysql --user=$2 --password=$3 -h $1 $4 -e "drop database
"
mysql --user=$2 --password=$3 -h $1 -e "select count(*) from mysql.columns_priv where db='$4'" -sssfi
if [
-gt 0 ]; then
mysql --user=$2 --password=$3 -h $1 -e "select count(*) from mysql.procs_priv where db='$4'" -sssCOLUMNS_PRIV=" UPDATE mysql.columns_priv set db='$5' WHERE db='$4';"
fi
if [
-gt 0 ]; then
mysql --user=$2 --password=$3 -h $1 -e "select count(*) from mysql.tables_priv where db='$4'" -sssPROCS_PRIV=" UPDATE mysql.procs_priv set db='$5' WHERE db='$4';"
fi
if [
-gt 0 ]; then
mysql --user=$2 --password=$3 -h $1 -e "select count(*) from mysql.db where db='$4'" -sss` -gt 0 ]; thenTABLES_PRIV=" UPDATE mysql.tables_priv set db='$5' WHERE db='$4';"
fi
if [
DB_PRIV=" UPDATE mysql.db set db='$5' WHERE db='$4';"
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
Also, does anybody know of a command for MySQL to not ask for credentials after a successful identification? Somehow like when you use sudo, that it doesn’t ask you for credentials for several minutes after the first time.
I got a really big database to change name. I admit this script help me, but it require many improvement, like change schema name inside VIEW, PROCEDURE etc…
And need say to take care about galera cluster to change priv table table directly.
Thanks for that feedback. I am currently going thru comments to see which ones might make for a good follow up or ‘new view’ posts, and this may well be one of them. If you might be interested in writing it up by the way (cheeky I know) I’m very happy to get content for our https://www.percona.com/community-blog – and then you’ll get the recognition for your experience.
Just a thought 🙂
what is script file format and how to execute this in Windows.
@kbpmowrya The script is in bash, for execution on Linux. You would need something like Cygwin, or Bash for Windows in order to execute it.