0

Purpose 1 - The running application should not be stopped and run 24*7 while backup or copy MySQL data directory (/var/lib/mysql).

Purpose 2 - To backup MySQL files directly and copy on another server as backup restoration so as to access same database on another server successfully.

Activity - I am trying to compress and copy MySQL Data Directory from source path (/var/lib/mysql) on a Linux server using command line/shell script and copy to another server with same version of MySQL (5.7.44) as a remedial measure of backup restoration.

Issue - After copying the files on another server, sometimes the MySQL service starts successfully whereas sometimes it fails. If failed, I need to delete all copied files and repeat the process from scratch (take fresh copy of MySQL Data Directory) before services could start successfully.

Observation 1 - When mysql services are stopped before copying the files, the copied files are successfully restored and services are up when restarted.

Observation 2 - When services are running and transactions are happening in the database, if I copy the files and restore it on another server, sometimes services failed to start.

Observation 3 - I found that "ibdata1" file under /var/lib/mysql directly continuously gets updated and if copied during running services, sometimes it gets corrupted which results in failure of service on destination server, when copied.

Command used on source server -

$> cd /var/lib
$> tar -cpzvf $bkp_dir/mysql.gz mysql 2>/dev/null | pv

Command used on destination server -

$> rm -rf /var/lib/mysql
$> tar -xzvf /var/tmp/mysql.gz -C /var/lib
$> chown -R mysql /var/lib/mysql
$> chgrp -R mysql /var/lib/mysql
$> systemctl restart mysqld

After copying mysql directory on destination server, sometimes below error is received and mysql service fails to restart.

Error Received - "Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

Can anyone please suggest how can I take MySQL data directory backup with mysql services running and restore on another server successfully with guranteed success?

I tried executing same commands to take backup after stopping mysql service and restored on destination server with 100% success rate.

1
  • 2
    The answer is simply: you really should not be doing this. Use proper database backup tools, not a makeshift one.
    – Shadow
    Feb 23 at 15:29

1 Answer 1

0

Can anyone please suggest how can I take MySQL data directory backup with mysql services running and restore on another server successfully with guranteed success?

The server needes to be stoped to perform file system-level commands (such as cp, scp, tar, rsync). See Backup and Recovery Types for further details.

As per the options with the running server you could use:

  1. MySQL Enterprise Backup which I have never used and cant give further details.

  2. Percona XtraBackup

I would suggest a Source-Replica configuration , in cases of disasters you could switch the replica as a source.

2
  • Thanks @Ergest Basha! I do understand the limitations of community edition and am aware of Enterprise edition of MySQL. But some other players in India are also providing tools to take MySQL Data Directory backup in community edition without stopping the service. So, out of curiosity, I also want to design a successful process for the same. My current process is successful only when there is no load on the system. Feb 27 at 7:22
  • @VijaySingh even if I do not recommend this kind of backup , use Percona XtraBackup Feb 27 at 10:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.