Migrate Your Databases¶

Prerequisites¶

• Ensure the same version of MySQL is installed on both servers.

• Ensure there is enough free space for the size of the database on both servers.

  • To find out the size of each database use the following query:

SELECT table_schema AS "Database",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
GROUP BY table_schema;

Create a database dump¶

On the current databases server, run the relative commands via SSH. Depending on how large the database(s) is, it may take a short while to complete.

For a single database :

mysqldump --events --routines --triggers --single-transaction dbname > dbname

For all / multiple databases, the below commands will split the individual databases into separate files in the /root/mysqldumps directory:

mkdir /root/mysqldumps

for i in $(mysql -NBe 'show databases' | grep -v 'performance_schema'), do
  mysqldump --force --events --routines --single-transaction --triggers $i > /root/mysqldumps/$i
done

Transfer the dump¶

When the database dump is complete, its time to transfer the file(s) to the alternate server. This example uses SCP to securely copy the files and directories between remote hosts without using an FTP client. For servers behind the same dedicated firewall, use the internal IP address of the remote server.

For a single database :

scp -v -P <ssh-port> dbname [email protected]:~/dbname

For multiple databases :

scp -vr  -P <ssh-port> /root/mysqldumps [email protected]:mysqldumps

Check that the files transferred as expected on the remote host:

ll /root/mysqldumps/

total 2068
-rw-r--r-- 1 root root    2107 Jul 10 17:25 example1
-rw-r--r-- 1 root root  533999 Jul 10 17:25 example2
-rw-r--r-- 1 root root    2372 Jul 10 17:25 example3
-rw-r--r-- 1 root root 1033415 Jul 10 17:25 information_schema
-rw-r--r-- 1 root root  515484 Jul 10 17:25 mysql
-rw-r--r-- 1 root root    2247 Jul 10 17:25 example4

Before the Import¶

Before the import, first ensure the files do not contain USE or CREATE DATABASE statements as this can cause unexpected outcomes.

Use the following grep command to check, if blank, move on to import your database:

egrep "^USE|^CREATE DATABASE" example1

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `example1` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `example1`;

Theses lines can be deleted manually, or by using the following sed commands:

sed -i '/^USE/d' example1
sed -i '/^CREATE DATABASE/d' example1

Import your database¶

Now the files are transferred, it’s time to to perform the import. If there is an existing database of that name there will be conflicts and you may wish to rename the conflicting database.

For a single database :

MariaDB [(none)]> CREATE DATABASE example1;

To import the database, run the following command:

mysql -u root -p example1 < example1

For multiple databases :

for i in $(ls /root/mysqldumps); do
  mysql -e "CREATE DATABASE $i" && mysql -u root -p $i < /root/mysqldumps/$i;
done