使用mysqldump备份MySQL

技宅之家 2021-09-01

1148

点击蓝字

关注我们

先决条件

拥有root或sudo 权限。

创建用于备份的用户

创建备份用户

CREATE USER 'mysql_backup'@'%' IDENTIFIED BY 'Backup@123' WITH MAX_USER_CONNECTIONS 5;
FLUSH PRIVILEGES;

2.为备份用户赋权限

注意：备份用户需要的权限从MySQL 5.7到MySQL 8.0各个版本变化比较大，参见4.1.2 Grant MySQL Privileges to Backup Administrator

GRANT SELECT ON *.* TO 'mysql_backup'@'%'; -- For release 8.0.19 and later
GRANT BACKUP_ADMIN ON *.* TO 'mysql_backup'@'%'; -- For release 8.0.16 and later
GRANT SELECT ON performance_schema.variables_info TO 'mysql_backup'@'%'; -- For release 8.0.16 to 8.0.18
GRANT SELECT ON performance_schema.log_status TO 'mysql_backup'@'%'; -- For release 8.0.16 to 8.0.18
GRANT RELOAD ON *.* TO 'mysql_backup'@'%';
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'mysql_backup'@'%';
GRANT CREATE, INSERT, DROP, UPDATE, SELECT, ALTER ON mysql.backup_history TO 'mysql_backup'@'%';
GRANT REPLICATION CLIENT ON *.* TO 'mysql_backup'@'%';
GRANT SUPER ON *.* TO 'mysql_backup'@'%';
GRANT PROCESS ON *.* TO 'mysql_backup'@'%';
GRANT SELECT ON performance_schema.replication_group_members TO 'mysql_backup'@'%'; -- For release 8.0.12 to 8.0.18
FLUSH PRIVILEGES;

使用mysqldump备份数据

官方文档，参见 7.4.1 Dumping Data in SQL Format with mysqldump

备份用户权限，参见Backing up users and privileges in MySQL

高级用法，参见how to back up and restore mysql databases with mysqldump

1.创建数据备份目录

mkdir home/deployer/backup
chown -R deployer:deployer /home/deployer/backup
mkdir home/deployer/backup_remote
chown -R deployer:deployer home/deployer/backup_remote

2.导出全库

mysqldump --all-databases > dump.sql

3.导出自定义数据库

mysqldump --databases db1 db2 db3 > dump.sql

4.导出DDL语句

mysqldump --no-data --databases treasurer_test treasurer > 104_ddl.sql

5.导出用户权限

mysqldump --exclude-databases=% --add-drop-user --users > 104_users.sql

6.还原

mysql -u root -p < dump.sql

注意：mysql 8.0.19需要SYSTEM_USER权限

GRANT CREATE USER, SYSTEM_USER ON *.* TO 'root'@'%';

其他常用mysqldump使用场景**：

复制database，参见 7.4.5.1 Making a Copy of a Database
将数据复制到另一个MySQL server，参见 7.4.5.2 Copy a Database from one Server to Another
导出procedures、functions、triggers、events ，参见 7.4.5.3 Dumping Stored Programs
使用mysqldump导出表结构，参见 7.4.5.4 Dumping Table Definitions and Content Separately

使用Crontab开启定时功能

1.创建证书

vim etc/.mysql_backup.cnf
输入如下内容：
[client]
socket = var/lib/mysql/mysql.sock
user = mysql_backup
password = Backup@123
修改证书文件权限为600：
chmod 600 /etc/.mysql_backup.cnf

2.编写sh脚本调用mysqldump备份数据库

cd /home/deployer
touch mysql_backup.sh
chmod +x mysql_backup.sh
vim mysql_backup.sh
输入如下内容：
# 切换到备份目录
cd /home/deployer/backup




# 调用mysqldump导出数据，输出备份文件格式为“yyyyMMdd_HHmmss.tar.gz”
mysqldump --defaults-extra-file=/etc/.mysql_backup.cnf -u mysql_backup --single-transaction --quick --lock-tables=false --all-databases -f | gzip > $(date +%Y%m%d_%H%M%S).sql.gz




# 删除超过7天的数据
find ./ -type f -mtime +6 -delete

3.使用crontab定时触发

编辑crontab
crontab -e
输入如下内容：
# 每天3:00 am触发，执行mysql_backup.sh
0 3 * * * /home/deployer/mysql_backup.sh

长

按二维码关注我们

mysql

文章转载自技宅之家，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

使用mysqldump备份MySQL

评论