Docker & MySQL Manual

原创已于 2026-06-07 02:21:14 修改 · 84 阅读

2 ·

本内容遵循CC 4.0 BY-SA版权协议

创作源自于MyFreeIT

GEO检测

标签

#docker #mysql #容器

于 2026-06-05 22:33:53 首次发布

数据库专栏收录该内容

60 篇文章

订阅专栏

数据库的备份一定要指定字符集，不必要的错误

1. 备份数据库

docker exec mysql_ope mysqldump -uroot -pABCD 
--single-transaction 
--quick 
--hex-blob 
openerp > /opt/wor/p20260605.sql

加上编码

 docker exec mysql_docker mysqldump -uroot -pOpABC 
--single-transaction 
--quick 
--hex-blob 
--default-character-set=utf8mb4 
openerp > /opt/w0260605.sql

或者压缩

docker exec mysql_ope mysqldump -uroot -pOp12212 
--single-transaction --quick --hex-blob opeewe | gzip > /opt/rp20260605.sql.gz

排除几个表格

docker exec mysql_ope mysqldump -uroot -pOp12212 
--single-transaction --quick --hex-blob 
--ignore-table=opeewe.c_note 
--ignore-table=opeewe.c_a
opeewe | gzip > /opt/rp20260605.sql.gz

2. 本地恢复

mysql -u root -p openerp < D:\workspace\DB\data_back\openerp20260605.sql

指定字符集導入

mysql -u root -p 
--default-character-set=utf8mb4 
--binary-mode=1 opeDB < D:\worp20260605-2.sql

3. Ubuntu 創建自動備份腳本

## create script

sudo mkdir -p opt/workspace/script
sudo nano /opt/workspace/script/mysql_auto_backup.sh

## add execute right
sudo chmod +x /opt/workspace/script/mysql_auto_backup.sh

或者
sudo -u root /opt/workspace/script/mysql_auto_backup.sh

## create system service
sudo nano /etc/systemd/system/mysql_auto_backup.service

[Unit]
Description=MySQL database openerp Backup

[Service]
Type=oneshot
ExecStart=/opt/workspace/script/mysql_auto_backup.sh


## check && create system timer
systemctl cat mysql_auto_backup.timer
sudo nano /etc/systemd/system/mysql_auto_backup.timer

[Unit]
Description=Run MySQL database openerp Backup Daily

[Timer]
OnCalendar=*-*-* 00:00:00
Persistent=true
Unit=mysql_auto_backup.service

[Install]
WantedBy=timers.target


##Enable timer & start
sudo systemctl daemon-reload

sudo systemctl enable mysql_auto_backup.timer
sudo systemctl start  mysql_auto_backup.timer

##Verify
systemctl status mysql_auto_backup.timer

## Run manually for testing
sudo systemctl start mysql_auto_backup.service

4. 常用命令

查看所有的定時器

systemctl list-unit-files | grep timer

or

systemctl list-unit-files | grep mysql

重新設定時間後，需要重啟

sudo systemctl daemon-reload
sudo systemctl restart mysql-backup.timer

監測service的運行

journalctl -fu mysql-backup.service

附件

导出没有指定字符集，造成的问题

Unknown command '\'' ERROR 1064 (42000) at line 7980:

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 ''lily@whitw.com.hk' at line 1