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: 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.