Question:
The situation is as follows: there is a master MySQL server (well, or MariaDB, to be precise), there is a replica. Every night, mysqld with the replica is turned off for a while, and the daddy from the replica database (conditional /var/lib/mysql ) is shoved into the backup archive, after which mysqld is turned back on.
I have had such cases since 2014 (it was done according to the principle “I’ll figure it out later”, and this “later” should someday already come :). I periodically try to roll out old backups – it works. While it works. Since such copying is not a "legal" form of creating a backup (it's not mysqldump or xtrabackup after all), I'm not completely sure of its reliability.
If anyone knows how MySQL/MariaDB works well enough, tell me if there are any pitfalls that such backups may get out 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 settings that can accidentally break such a backup?
Answer:
You can't just copy the database directory. But you copy it not just, but by stopping the base.
Since such copying is not a "legal" form of backup
Is times – Cold Backups . Is two. – Making Backups Using a File System Snapshot .
Copying the base directory and configuration files while the base 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 restore from a backup.
For backup of innodb tables, the recommendation is to stop the DBMS as clean shutdown , i.e. indicating
mysql -u root -p --execute="SET GLOBAL innodb_fast_shutdown=0"
# выключить базу
# убедиться, что она действительно выключилась
# скопировать datadir и конфиги
# запустить базу вновь
Of course, it is necessary 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 version. Since there are separate instructions for updating the database version major . Although the instructions are very close in meaning, it is not necessary to combine a system upgrade and recovery after an accident.