How to Transfer All MySQL Databases From Old to New Server
Transferring or Migrating a MySQL/MariaDB database between servers usually takes only few easy steps, but data transfer can take some time depending on the volume of data you would like to transfer.
STEP 1 : Export a MySQL Databases to Dump File :-
1. First start by login into your old server and stop the mysql/mariadb service using the systemctl command.
# systemctl stop mariadb
OR
# systemctl stop mysql
2. Then dump all your MySQL databases to a single file.
# mysqldump -u [user] -p --all-databases > all_databases.sql
Once the dump is completed, you are ready to transfer the databases.
3. If you want to dump a single database, you can use:
# mysqldump -u root -p --opt [database name] > database_name.sql
STEP 2 : Transfer MySQL Databases Dump File to New Server :-
1. Now use scp command to transfer your databases dump file to the new server under home directory
# scp all_databases.sql user@example.com:~/ [All Databases]
# scp database_name.sql user@example.com:~/ [Singe Database
Once you connect, the database will be transferred to the new server.
STEP 3 : Import MySQL Databases Dump File to New Server :-
1. Once the MySQL dump file has been traferred to the new server, you can use the following command to import all your databases into MySQL.
# mysql -u [user] -p --all-databases < all_databases.sql [All Databases]
# mysql -u [user] -p newdatabase < database_name.sql [Singe Database]
Once the import is completed, you can verify the databases on both servers using the following command on the mysql shell.
# mysql -u user -p
# show databases;