If you are moving your database server from old server to another server then this guide is for you . In this case I will demonstrate how to migrate your existing MySQL databases one to another server step by step . no more huge story likes guide just exact steps for exact thing .
Scenario : let’s assume that you have working LEMP installation with small WordPress site and you want to upgrade to new server with more memory and processing powers , and you are planning to move everything from one server to another . this is tutorial for moving MySQL database to new server .
Dump All databases
mysqldump -u root -p --single-transaction --routines --triggers --all-databases > all_db_dump.sql
Above command will dump all databases with prevention of any writes while dumping .
NOTE : for
myisam database engine use
--lock-tables instead of
If you dont know what kind of database engine you are using execute following query to know :
mysql > SELECT TABLE_NAME , ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_db_name' AND ENGINE IS NOT NULL;
Sample output :
Restore database dumps
mysql server package in new server then , copy your database dump file from old server to new server using
rsync then run following command to restore all database .
mysql -u root -p < backup_db.sql
If everything will go well, nothing will be printed on screen . Then run
mysql_upgrade to upgrade any system related settings which will will correct any incompatibilities with your current database with current version supported database . read more about mysql upgrade here : http://dev.mysql.com/doc/refman/5.7/en/mysql-upgrade.html
sudo mysql_upgrade -u root -p
Sample Output :
test new server
Now , lets test everything , login in your mysql server using mysql server and try to run some queries on your wordpress databse form example
mysql > select ID , post_name from wp.wp_posts ;
Sample Output :