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.
