mysqldump: MySQL 数据库备份工具

发布时间： 更新时间： 总字数：2907 阅读时间：6m 作者：IP：上海 网址

mysqldump 是 MySQL、Mariadb 数据库的备份程序

安装

# mysql
apt install mysql-client

# mariadb
apt install mariadb-client

查看版本

$ mysqldump --version
mysqldump  Ver 8.0.41-0ubuntu0.24.04.1 for Linux on x86_64 ((Ubuntu))

help

mysqldump--help ...

使用示例

mysqldump -h$MYSQL_IP -u$MYSQL_USER -p$MYSQL_PASS db1 > db1.sql

mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql
mysqldump --all-databases > all_databases.sql
mysqldump --all-databases --source-data --single-transaction > all_databases.sql
mysqldump --all-databases --flush-logs --source-data=2 > all_databases.sql

# 备份与还原
mysqldump -h$MYSQL_IP -u$MYSQL_USER -p$MYSQL_PASS <db-name> > /root/<db-name>-$(date +%s).sql
mysql -h$MYSQL_IP -u$MYSQL_USER -p$MYSQL_PASS <db-name> < <db-name>-<time>.sql

# 导出指定表的数据
mysqldump -h$MYSQL_IP -u$MYSQL_USER -p$MYSQL_PASS -t database --tables table_name1 table_name2 table_name3 > /root/db_script.sql

mysqldump -h$MYSQL_IP -u$MYSQL_USER -p$MYSQL_PASS --default-character-set=utf8 --database database_name --ignore-table=database_name.table_name1
--ignore-table=database_name.table_name2 --ignore-table=database_name.table_name3 > /root/db_script.sql
mysqldump -h <ip> -u <user> -p<password> \
--skip-opt --create-option --single-transaction -q \
--no-autocommit -R --triggers --default-character-set=utf8 -E -B \
-e --max_allowed_packet=67108864 --net_buffer_length=8192 --databases > databases.sql

说明：

  • 参数 --single-transaction 可以在备份库的时候不锁表
  • mysqldump导出的SQL语句在导入到其他数据库的时候会相当慢，甚至几十秒才处理一条SQL；现有两个参数会影响导入的速度：
--max_allowed_packet=67108864  客户端/服务器之间通信的缓存区的最大大小
--net_buffer_length=8192       TCP/IP和套接字通信缓冲区大小
  • 在导出的时候，使用-e选项设定上面两个值，需要注意的是max_allowed_packet和net_buffer_length的导出值不能比目标数据库的值大，参看数据库的参数值，使用如下命令；
mysql> show variables like 'max_allowed_packet';
mysql> show variables like 'net_buffer_length';

过滤部分库

mysql --socket=/var/lib/mysql/mysql.sock \
-e "show databases"|grep \
-Ewv "Database|information_schema|performance_schema|mysql|test|bksuite_common"|xargs mysqldump \
--socket=/data/bkee/logs/mysql/mysql.sock \
--skip-opt --create-options --single-transaction -q \
--no-autocommit -R --triggers --default-character-set=utf8 \
-e --max_allowed_packet=67108864 --net_buffer_length=8192 \
--events --databases | gzip > /data/dbbak/mysql_$(date +%F_%T).gz

crontab 定时执行备份命令

$ crontab -e
0 3 * * 0 /bin/bash -x /usr/local/bin/mysql-backup.sh >/dev/null 2>&1

F&Q

mysqldump: unknown variable ‘set-gtid-purged=ON’

  • MySQL 5.6 及更高版本中，mysqldump 增加选项 --set-gtid-purged，允许在导出数据时包括或排除 GTID 信息
  • 更多参考

导入 SQL 太慢的解决方法

# 1. 推荐一条 SQL 包含多行数据的形式
# 2. 临时关闭自动提交
set global autocommit = 0;
set global max_allowed_packet = 20 *1024* 1024 * 1024; # 20G

# 3. 使用事务
use <db>;

# 开启事务
START TRANSACTION;

# 导入 SQL 文件并 COMMIT
source /path-to/xxx.sql; COMMIT;
最新评论
本文总阅读量 次 本站总访问量 次 本站总访客数 人