Xfer DB Srv to Srv 2
From: https://medium.com/@richb_/the-easy-and-secure-way-to-migrate-mysql-mariadb-databases-d72b9108bb38
The easy (and secure) way to migrate MySQL / MariaDB databases
tl;dr: pipe the output of mysqldump through an ssh tunnel directly into the mysql binary on the destination server.
Migrating databases between servers is not a fun task, but it doesn’t have to be difficult. I’ve broken this down below in some detail for those unacquainted, but the principles here are straightforward.
Things worth checking
max_allowed_packet
Make sure the max_allowed_packet is the same on both servers (in my.cnf). This ensures the output from mysqldump is within the required size constraints for processing by mysql on the destination server.
[mysqld]
max_allowed_packet=16M[mysqldump]
max_allowed_packet=16M
SQL_MODE
Something that can trip you up is the new more strict mode that is enabled by default in newer versions of MySQL / MariaDB. This can cause your application to fail because:
“Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range.”
To fix this, on your new server ensure SQL_MODE is set as follows in your my.cnf file:
sql_mode = NO_ENGINE_SUBSTITUTION
Setting up a secure connection
Your existing server is going to need secure access to the new one. You can use SSH keys to achieve this.
On your source server:
cat ~/.ssh/id_rsa.pub
Copy the contents of this public key, then use nano to paste it onto a new line in the authorized_keys file on your destination server.
nano ~/.ssh/authorized_keys
You’re now able to securely open an SSH connection from the source server to the destination server.
Transferring your data
You’re all set to go ahead and transfer the data from the source to destination server. This is the command you will use:
mysqldump -u srcdbuser -p'srcdbpass' srcdatabase | ssh
dstsshuser@xxx.xxx.xxx.xxx mysql -u dstdbuser -p'dstdbpass'
dstdatabase
You’ll replace these values:
- srcdbuser: the database user on the source server.
- srcdbpass: the database passwordon the source server.
- srcdatabase: the name of the database on the source server.
- dstsshuser: SSH user on the destination server.
- xxx.xxx.xxx.xxx: IP Address of the destination server.
- dstdbuser: the database user on the destination server.
- dstdbpass: the (new, empty) database user on the destination server.
- dstdatabase: the name of the database on the destination server.
Let’s break this down:
This part outputs SQL statements required to rebuild ‘srcdatabase’ database on the source sever:
mysqldump -u srcdbuser -p'srcdbpass' srcdatabase
The ‘pipe’ pushes the output of mysqldump into the SSH tunnel:
| ssh dstsshuser@xxx.xxx.xxx
MySQL on the destination server receives the data streamed through the SSH tunnel and inserts it directly the new database ‘destdatabase’:
mysql -u dstdbuser -p'dstdbpass' dstdatabase
Summary
Moving a database like this streamlines the normal process of dumping to a file, moving the file across, then reimporting. It can save you a lot of time and as I always profess, less moving parts = less to go wrong.