MySQL database migration from one server to another server.

Migrating a MySQL database from one server to another is a common DBA task, whether for hardware upgrades, disaster recovery, or server consolidation. In this guide, we will walk through the process of migrating a database from Testmysql01 to Testmysql02 and updating the web application accordingly.

MySQL database migration from one server to another server.

Connect to Testmysql01 using ssh

Take a dump of Test_database database

[root@Testmysql01 ~]# mysqldump -u root -p Test_database  > /opt/Test_database.sql

Copy the Dump file to Testmysql02

[root@Testmysql01 ~]# scp /opt/Test_database.sql root@Testmysql02.domain.com:/opt

[root@Testmysql01 ~]# Exit

Connect to Testmysql02 using ssh

connect to MySQL

[root@Testmysql02~]# MySQL -u root -p

Create Database and grant permission for the user

MySQL> create database Test_database;

MySQL> GRANT ALL ON Test_database.* TO testuser@'%' IDENTIFIED BY 'password';

MySQL> quit

Import Dump to new database

[root@Testmysql02~]# MySQL -u root -p Test_DBA < /opt/Test_DBA.sql

[root@Testmysql02~]# Exit

Connect to webserver using RDP.

Navigate to C:\inetpub\Test_database\Test_dat
abase\config

Edit file config.php and change 'dbhost' => in the file to Testmysql02.domain.com.

verify 'dbuser' => and 'dbpassword' => is defined correctly.

restart the site from IIS manager

connect to Test_database  site and verify everything is working fine.