Steps to Move Copy Rename MySQL Database

Steps to Move Copy Rename MySQL Database

Moving, copying or renaming database is a very basic activity. I have just noted a few commands for reference to quickly follow the required operation.

1. Rename database on Linux Machine:
A. Use RENAME DATABASE Command [MySQL 5.1].

RENAME DATABASE db_name TO new_db_name;

B.

mysql -uroot -pPASSWORD -e “drop database if exists NEW-DB-NAME; create database NEW-DB-NAME” && mysqldump -uroot -pPASSWORD SOURCE-DB-NAME | mysql -uroot -pPASSWORD NEW-DB-NAME && mysql -uroot -pPASSWORD -e “drop database SOURCE-DB-NAME”

2. Create Duplicate of a database:

mysql -uroot -ppassword -e “drop database if exists DB_TO_BE_CREATED;create database DB_TO_BE_CREATED;” | mysqldump -uroot -ppassword DB_TO_BE_COPIED | mysql -uroot -ppassword DB_TO_BE_CREATED

3. Copy MySQL Database to Remote MySQL Server:

3A. By using -h (hostname) option.

Create MySQL Dump:

mysqldump -uroot -ppassword –databases DB_TO_BE_COPIED > DB_TO_BE_COPIED.sql

Load MySQL Dump:

mysql -uroot -ppassword -hHOSTNAME < DB_TO_BE_COPIED.sql OR Using Single Command: mysql -uroot -ppassword -e “drop database if exists DB_TO_BE_CREATED;create database DB_TO_BE_CREATED;” | mysqldump -uroot -ppassword DB_TO_BE_COPIED | mysql -uroot -ppassword -hHOSTNAME DB_TO_BE_CREATED 3B. By moving dump file to remote server. Create MySQL Dump: mysqldump -uroot -ppassword –databases DB_TO_BE_COPIED > DB_TO_BE_COPIED.sql

Copy SQL file to remote MySQL Server:

scp DB_TO_BE_COPIED.sql username@remote-machin:/path/to/copy

Login to remote-machin.

Load MySQL Dump:

mysql -uroot -ppassword DB_TO_BE_CREATED < /path/to/copy/DB_TO_BE_COPIED.sql

Here, I haven’t considered copying and moving data-files for MyISAM databases as these are just the commands-way.