New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
What could go wrong when migrating a large MariaDB database using RSYNC?
How safe is it to use rsync to sync the folder /var/lib/mysql/database_name/ from the old server to the new server when doing live site migration?
- Both the old and new server's /etc/my.cnf have the same configuration
- Before running rsync, both MariaDB servers were stopped
- Both MariaDB are on the same version and OS
Or should I sync /var/lib/mysql/?
I haven't done this thing and am just using mysqldump for a database size of 80GB+.
Comments
Since both servers are stopped, you won't face any issue.
Every time I've done it, I did full /var/lib/mysql, never sent only one database though.
Do the full /var/lib/mysql/* as you might not have much fun with innodb tables if you try single databases. It should work perfectly if you do /var/lib/mysql/*.
With both mariadb instances offline there is no risk. Several months ago we did transfer 190+GB size DB from old server to new one.
Copying full /var/lib/mysql would also save you some time because all users would be copied, you wouldn't have to create and configure anything from scratch
Thank you so much for all your responses.
Does it matter, or is it really important that both the old and new server's /etc/my.cnf have the same configuration?
Nope, but in some cases if user has specific need, then you might want to make some changes laters depending upon issue you face.
When migrating a PG database to a new machine, the first thing I remember doing is changing the listen address to the new ip on the configs. This is not necessary if you use * or 0.0.0.0 (which is bad security practice).
If you also migrate the replica/slave machine, make sure to point to that too.
I think the rest can remain identical.
I take my full SQL backup daily for my backup server with the following code.
There's no need to shut down; the backup is taken seamlessly while it's running.
mariabackup --backup --target-dir=/root/mysqlbackup --user=root --password=password
mariabackup --prepare --target-dir=/root/mysqlbackup
chown -R mysql:mysql /root/mysqlbackup
Obviously you don’t have write intensive database. And when you dump db while data is in transit - you get corruption. Of course locking takes place, bit it slows down backup by a lot for db 500+ GB.
MariaBackup fork of Percona XtraBackup is different it handles databases properly to prevent corruption https://mariadb.com/kb/en/mariabackup-overview/. Table locking though will depend on database engines of your database. Myisam will involve table locks while InnoDB won't have table level lock due to nature of InnoDB. I use MariaBackup and Percona XtraBackup fine with databases terabyte sizes. Definitely much safer than rsyncing database individually and probably faster too if you have many cpu threads.
I can easily and safely backup 65GB worth of databases in 9 mins and restore it in 11 mins using MariaBackup as its multi-threaded.
Mysqldump default backups/restores are single threaded but you can speed up and make use of multiple threads with mysqldump's tab delimited backup methods to individual table backup files which you can restore in multi-threaded manner which allows mysqldump to be 2-20x times faster for restoration compared to default mysqldump method.
@Roldan mysqldump via tab delimited backup/restore, mydumper, MariaBackup and Percona XtraBackup are the right tools to safely backup MySQL data and all can properly leverage more CPU threads for better multi-threaded process.
Depending on size of data set and number of CPU threads available fastest to slowest will be