Computersnyou

How to move MySQL database to new server on Ubuntu

Posted on  5/1/2016

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  --single-transaction

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 :

screenshot of mysql table schema information
screenshot of mysql table schema information

Restore database dumps

Install mysql server  package  in new server then , copy your database dump file from old server  to new server using scp  or 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

run upgrades

sudo mysql_upgrade -u root -p

Sample Output :

Screenshot from 2016-05-01 21-15-31
Screenshot from 2016-05-01 21-15-31

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 :

mysql wp post query output
mysql wp post query output

  • Home
  • About