Rename Database in MySQL

Today I am writing about how to rename MySQL Database.

So If you want to rename a database in MySQL dont forget taking backup of your Views,Procedures and Functions of current database. Because while you renaming changes wont apply for Views,Procedures and functions.

After you have taken the backup , We can go for renaming with the command below.

RENAME TABLE old_db.table TO new_db.table;

Here old_db is your old database name and new_db is your new database name. You have to execute this command for each table. And this is the way.

But what if we have a big database with 1000 tables ? executing the same above command for 1000 times is a stupid idea.

If you are using Linux you can use following BASH script to do that for you

for table in $(mysql -p -s -N -e "show tables from db1"); do mysql -s -N -e "rename table db1.$table to db2.$table"; done;

db1 is your old database name and db2 is your new database name and remaining everything will tool care by script only except giving password.:P

After executing the command successfully without any errors , you must have to restore that backup of Views,Proc's and Functions.

Errors: I have got one error with triggering. Generally some tables have triggers. So you copy the code of triggers and then delete those triggers. Then try to rename the table again.

After it got successful , just recreate the triggers for the same tables in new database.

I have done this trigger creation in MYSQL Workbench.

If you want to backup only Functions and not data of database then you can use this command

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt  <database> > outputfile.sql

Here : 

--routines                 =       for including routines ( views,functions,procedures)

--no-create-info       =       Do not write CREATE TABLE statements that re-create each dumped

                              table.

--no-data                  =        Do not write any row information for the table. This is very useful

                        if you want to get a dump of only the structure for a table.

 --no-create-db         =        This option suppresses the CREATE DATABASE /*!32312 IF NOT EXISTS*/

                        db_name statements that are otherwise included in the output if the
                        --databases or --all-databases option is given.

--skip-out        = --opt

                    This option is shorthand; it is the same as specifying
                    --add-drop-table --add-locks --create-options --disable-keys
                    --extended-insert --lock-tables --quick --set-charset. It should
                    give you a fast dump operation and produce a dump file that can be
                    reloaded into a MySQL server quickly.  As of MySQL 4.1, --opt is on
                    by default, but can be disabled with --skip-opt. To disable only
                    certain of the options enabled by --opt, use their --skip forms; for
                    example, --skip-add-drop-table or --skip-quick.

Basic MySQL Administration

1. How to create a user and along with his privileges automatically ?

GRANT ALL PRIVILEGES ON database.* To 'username'@'hosr or IP ' IDENTIFIED BY 'password';

flush privileges;

2. How to give only specific privileges to users ?

GRANT select,lock tables ON *.* To 'user'@'host or IP ' IDENTIFIED BY 'password';

flush privileges;

3. How to see permissions of a user ?

show grants for 'username'@'hostanme';

4. How to revoke all given privileges ?

revoke all privileges on *.* from 'user'@'host';

5. How to take backup of database ?

mysqldump --user <username> --password=<password> databasename > databasebackup.sql

6. How to take backup of two databases ?

mysqldump --user <username> --password=<password> databasename1 databasename2 > databasebackup.sql

7. How to take backup of all databases in MySQL ?

mysqldump --user <username> --password=<password> -all-databases > alldbbackup.sql

8. How to take backup of table in database ?

mysqldump --user <username> --password=<password> database_name database_table \  >  databasebackup.sql 

9. How to restore one database ?

mysql --user <username> --password=<password> databasename < databasebackup.sql
mysql_upgrade -p -u root --force