Is it possible to backup MySQL by stupidly copying a directory from the database?

Question:

The situation is as follows: there is a MySQL master server (well, or MariaDB, to be precise), there is a replica. Every night, mysqld with the replica is shut down for a while, and the daddy from the replica database (conditional /var/lib/mysql ) is shoved into the archive with the backup, after which mysqld is turned back on.

I've had such things since 2014 (it was done according to the principle "I'll figure it out later", and this "later" should come sometime :). I periodically try to roll out old backups – it works. While working. Since such copying is not a "legal" form of creating a backup (after all, it is not mysqldump or any xtrabackup), I am not completely sure of its reliability.

If anyone knows how MySQL / MariaDB works well enough, tell us if there are any pitfalls that such backups might have in the future? Or, if you remember to run mysql_upgrade , everything will be fine? Or maybe there are some dependencies on the OS, system libraries or configuration parameters that can accidentally break such a backup?

Answer:

You cannot just copy the database directory. But you are not just copying it, but by stopping the base.

Since such copying is not a "legal" form of creating a backup

Is TimesCold Backups . There are two.Making Backups Using a File System Snapshot .

Copying the directory and database configuration files when the database is stopped is a valid option. But, as is the case with backups – in any case, it is useful to regularly check that you can recover from the backup.

For the backup of innodb tables, there is a recommendation to stop the DBMS as a clean shutdown , i.e. indicating

mysql -u root -p --execute="SET GLOBAL innodb_fast_shutdown=0"
# выключить базу
# убедиться, что она действительно выключилась
# скопировать datadir и конфиги
# запустить базу вновь

Of course, you need to restore from a physical backup to the same major version that was used before removing the backup and not lower than the same minor one. Since there are separate instructions for the major update of the base version. Although the instructions are very similar in meaning, it is not necessary to combine system upgrade and disaster recovery.

Scroll to Top