docker MySQL realizes automatic backup every day!

This article mainly introduces how to back up and restore the docker MySQL database, and how to implement automatic backup every day. I hope it will be helpful to friends in need!

Docker MySQL database backup and restore, as well as daily scheduled automatic backup

1: Backup

View docker mysql

ubuntu@ubuntu:~$ sudo docker ps
CONTAINER ID   IMAGE          COMMAND                  CREATED          STATUS         PORTS                                                  NAMES
a30ead8a26fc   mysql:8.0.27   "docker-entrypoint.s…"   10 seconds ago   Up 7 seconds   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   mysql

Method 1:

Step by step, perform regular backup, enter the container, and use mysqldump to back up. [Recommended learning: docker video tutorial]

sudo docker exec -it mysql /bin/bash
#进入mysql容器
mysqldump -uroot -p123456 --all-databases >  emp_`date +%F`.sql
#在容器中备份所有数据库，并以日期命名
exit
#退出容器
sudo docker cp mysql:emp_2022-03-17.sql /var/backup/
#将容器中的备份文件，复制到本地备份文件夹中

is displayed as follows:

ubuntu@ubuntu:~$ sudo docker exec -it mysql /bin/bash
root@a30ead8a26fc:/# mysqldump -uroot -p123456 --all-databases >  emp_`date +%F`.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
root@a30ead8a26fc:/# exit
exit
ubuntu@ubuntu:~$ sudo docker cp mysql:emp_2022-03-17.sql /var/backup/
ubuntu@ubuntu:~$ ll /var/backup/
total 11492
drwxrwxrwx  2 root   root      4096 Mar 17 15:45 ./
drwxr-xr-x 14 root   root      4096 Mar 14 17:49 ../
-rw-r--r--  1 root   root   3915599 Mar 17 15:42 emp_2022-03-17.sql

Method 2:

One step, there are two possible commands here

1): (Recommended, there is no problem with restore)

sudo docker exec mysql(容器名) sh -c 'exec mysqldump --all-databases -uroot -p123456 --all-databases' > /var/backup/music_`date +%F`.sql

2): (Not recommended, there is a problem with restore)

Most of the online backup methods are this, but I There is a problem with the restore. You can try version 5.7.

sudo docker exec -it mysql(容器名) mysqldump -uroot -p123456 --all-databases > /var/backup/music_`date +%F`.sql

Note: It may be due to the version (my version is 8.0). There is no problem with this kind of backup, but there will be problems with the restore. The error is reported as follows:

ubuntu@ubuntu:~$ sudo mysql -uroot -p < emp_2022-03-15.sql 
Enter password: 
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#39;mysqldump: [Warning] Using a password on the command line interface can be insec&#39; at line 1

You can also enter the database and use the command source to restore, but the error will still appear, but will be ignored, resulting in incomplete data recovery in the end. All this is not recommended.

2: Restore

1): Restore in non-container

1: Direct command to restore

mysql -uroot -p123456 < /var/backup/emp_2022-03-15.sql

2: Source restore after entering mysql

mysql -uroot -p
source /var/backup/emp_2022-03-15.sql

2): Container mysql restore

needs to be restored in the mysql container.

#复制备份文件到容器中的var目录
sudo docker cp /var/backup/emp_2022-03-17.sql mysql:/var
#进入容器
sudo docker exec -it mysql bin/bash
#进入mysql
mysql -uroot -p
#使用备份sql恢复数据库
source /var/backup/emp_2022-03-15.sql

#或者不进入mysql直接容器中还原
mysql -uroot -p123456 < /var/backup/emp_2022-03-15.sql

Three: Scheduled tasks

1): Set scheduled tasks

crontab -e

The content is as follows:

0 2 * * * find /backup/ -mtime +1 -name "*.sql" -delete && sudo docker exec mysql sh -c 'exec mysqldump --all-databases -uroot -p123456 --all-databases' > /var/backup/music_`date +%F`.sql

Command description:

Every day Executed at 2 o'clock in the morning

find /backup/ -mtime +1 -name "*.sql" -delete
#删除2天前的备份任务，即保留3个版本。根据需要自己选择。

2): The scheduled task is not executed

Update: The next day it was found that there was no backup file, check the cron log. If not, please enable cron log. The method is as follows:

#编辑系统日志文件
vim /etc/rsyslog.d/50-default.conf
#去掉前面的#注释符号
cron.*      /var/log/cron.log
#重启日志服务
systemctl restart rsyslog.service

View the scheduled task display

cat /var/log/cron.log 
....
CRON[2015636]: (root) CMD (find /backup/ -mtime +1 -name "*.sql" -delete && sudo docker exec mysql sh -c 'exec mysqldump --all-databases -uroot -p123456 --all-databases' > /var/backup/music_`date +)
....

It is found that %F`.sql after the command is not displayed. The reason is that % is a variable in it. Add the \ escape character, so that Keep the original characters.

0 2 * * * find /backup/ -mtime +1 -name "*.sql" -delete && sudo docker exec mysql sh -c 'exec mysqldump --all-databases -uroot -p123456 --all-databases' > /var/backup/music_`date +\%F`.sql

Save and the test will take effect.

The above is the detailed content of docker MySQL realizes automatic backup every day!. For more information, please follow other related articles on the PHP Chinese website!