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
  • Home
  • Home
About
lorem lasda asda adasd adsad lorem lasda asda adasd adsad lorem lasda asda adasd adsad lorem lasda asda adasd adsad lorem lasda asda adasd adsad lorem lasda asda adasd adsad