MySQL数据库的备份还原以及mysqldump的使用

标签：-- 备份 mysql 二进制 mysqldump MySQL 日志 root

今天学习分享的是MySQL数据库中的慢查询日志以及备份和恢复的方法，还有mysqldump备份工具的使用，结合实例进行的操作。

MySQL备份还原

慢查询日志

慢查询日志：记录执行查询时长超出指定时长的操作

慢查询相关变量

slow_query_log=ON|OFF                  #开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件
long_query_time=N                      #慢查询的阀值，单位秒,默认为10s
slow_query_log_file=HOSTNAME-slow.log  #慢查询日志文件log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk #上述查询类型且查询时长超过long_query_time，则记录日志
log_queries_not_using_indexes=ON       #不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录
log_slow_rate_limit = 1                #多少次查询才记录，mariadb特有
log_slow_verbosity= Query_plan,explain #记录内容
log_slow_queries = OFF                 #同slow_query_log，MariaDB 10.0/MySQL 5.6.1 版后已删除

范例: 慢查询分析工具mysqldumpslow

[root@centos8 ~]#mysqldumpslow --help
[root@centos8 ~]#mysqldumpslow -s c -t 2 /var/lib/mysql/centos8-slow.log

二进制日志(备份)

记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖于存储引擎类型

事务日志和二进制日志区别

事务日志在线,二进制离线
事务日志记录事务执行的过程,包括提交和未提交,二进制日志记录只记提交的过程
事务日志只支持innodb,二进制都支持MyiSAM和innoDB

二进制日志记录三种格式

基于"语句"记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下），日志量较少
基于"行"记录：row，记录数据，日志量较大，更加安全，建议使用的格式,MySQL8.0默认格式
混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）

二进制日志文件的构成

有两类文件
1.日志文件：mysql|mariadb-bin.文件名后缀，二进制格式,如： on.000001,mariadb-bin.000002
2.索引文件：mysql|mariadb-bin.index，文本格式,记录当前已有的二进制日志文件列表

二进制日志相关的服务器变量：

sql_log_bin=ON|OFF                 #是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项
log_bin=/PATH/BIN_LOG_FILE         #指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可以
binlog_format=STATEMENT|ROW|MIXED  #二进制日志记录的格式，mariadb5.5默认STATEMENT
max_binlog_size=1073741824         #单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G #说明：文件达到上限时的大小未必为指定的精确值
binlog_cache_size=4m               #此变量确定在每次事务中保存二进制日志更改记录的缓存的大小（每次连接）
max_binlog_cache_size=512m         #限制用于缓存多事务查询的字节大小。
sync_binlog=1|0                    #设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
expire_logs_days=N                 #二进制日志可以自动删除的天数。 默认为0，即不自动删除

二进制日志相关配置

查看mariadb自行管理使用中的二进制日志文件列表，及大小

SHOW {BINARY | MASTER} LOGS

查看使用中的二进制日志文件

SHOW MASTER STATUS

在线查看二进制文件中的指定内容

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

范例：

show binlog events in 'mysql-bin.000001' from 6516 limit 2,3

mysqlbinlog：二进制日志的客户端命令工具，支持离线查看二进制日志

mysqlbinlog [OPTIONS] log_file…
 --start-position=# 指定开始位置
 --stop-position=#
 --start-datetime=  #时间格式：YYYY-MM-DD hh:mm:ss
 --stop-datetime= 
 --base64-output[=name]
        -v -vvv

范例：同步远程主机的二进制日志

#从10.0.0.201远程主机实时同步从指定的二进制日志binlog.000002开始向后进行同步到当前目录
#先在10.0.0.201的MySQL创建用户并授权
[root@centos8 ~]#mysql -uroot -p123456
mysql> create user test@'10.0.0.%' identified by '123456';
mysql> grant REPLICATION SLAVE on *.* to test@'10.0.0.%' ;

#安装和MySQL服务器相同版本的客户端
[root@centos8 ~]#yum -y install mysql

#前台执行,自动同步二进制日志
[root@centos8 data]#mysqlbinlog -R --host=10.0.0.8 --user=test --password=123456 
--raw --stop-never binlog.000002

二进制日志事件的格式：

# at 328
#151105 16:31:40 server id 1 end_log_pos 431   Query   thread_id=1     
exec_time=0     error_code=0
use `mydb`/*!*/;
SET TIMESTAMP=1446712300/*!*/;
CREATE TABLE tb1 (id int, name char(30))
/*!*/;  
事件发生的日期和时间：151105 16:31:40
事件发生的服务器标识：server id 1
事件的结束位置：end_log_pos 431
事件的类型：Query 
事件发生时所在服务器执行此事件的线程的ID：thread_id=1
语句的时间戳与将其写入二进制文件中的时间差：exec_time=0
错误代码：error_code=0
事件内容：
GTID：Global Transaction ID，mysql5.6以mariadb10以上版本专属属性：GTID

清除指定二进制日志

PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }

范例：

PURGE BINARY LOGS TO 'mariadb-bin.000003'; #删除mariadb-bin.000003之前的日志
PURGE BINARY LOGS BEFORE '2017-01-23';
PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';

删除所有二进制日志，index文件重新记数

RESET MASTER [TO #]; #删除所有二进制日志文件，并重新生成日志文件，文件名从#开始记数，默认从1开始，一般是master主机第一次启动时执行，MariaDB 10.1.6开始支持TO #

切换日志文件：

FLUSH LOGS;

范例

[root@master ~]#file /var/lib/mysql/mariadb-bin.000001
/var/lib/mysql/mariadb-bin.000001: MySQL replication log, server id 18 MySQL V5+, server version 10.3.17-MariaDB-log

范例: 切换二进制日志

[root@centos8 ~]#mysqladmin   flush-binary-log
[root@centos8 ~]#mysqladmin   flush-logs
[root@centos8 ~]#mysql
MariaDB [hellodb]> flush logs;

MySQL 备份和恢复

#实战案例：数据库冷备份和还原
1.先做备份，先停掉数据库
[root@ubuntu2204 ~]#systemctl stop mysql.service

2.将文件拷贝出来，打个包，最好保留属性。其中a是保留属性
[root@ubuntu2204 ~]#ls /var/lib//mysql
 auto.cnf        binlog.000010       '#ib_16384_1.dblwr'   qwer
 binlog.000001   binlog.index         ib_buffer_pool       server-cert.pem
 binlog.000002   ca-key.pem           ibdata1              server-key.pem
 binlog.000003   ca.pem              '#innodb_redo'        sys
 binlog.000004   client-cert.pem     '#innodb_temp'        test
 binlog.000005   client-key.pem       mysql                undo_001
 binlog.000006   db1                  mysql.ibd            undo_002
 binlog.000007   debian-5.7.flag      performance_schema
 binlog.000008   hellodb              private_key.pem
 binlog.000009  '#ib_16384_0.dblwr'   public_key.pem

[root@ubuntu2204 ~]#mkdir /data/backup/
[root@ubuntu2204 ~]#cp -a /var/lib/mysql /data/backup/`date +%F`
[root@ubuntu2204 ~]#ls /data/backup/
2022-11-14
[root@ubuntu2204 ~]#ls /data/backup/2022-11-14/

3.启动服务
[root@ubuntu2204 ~]#systemctl start mysql.service

4.不小心删库了
[root@ubuntu2204 ~]#mysql hellodb
mysql> drop database hellodb;
Query, Ok, 6 rows affected(0.03sec)

5.先停掉数据库
[root@ubuntu2204 ~]#systemctl stop mysql.service

6.在把其他文件移走并查看文件是否有东西
[root@ubuntu2204 ~]#mv /var/lib/mysql/* /opt
[root@ubuntu2204 ~]#ls /var/lib/mysql

7.在把之前备份的数据拷贝过来
[root@ubuntu2204 ~]#cp -a /data/backup/`date +%F` /var/lib/mysql
完成！。。。。。。。。。。。。。。。

mysqldump 备份工具

命令格式:

mysqldump [OPTIONS] database [tables]   #支持指定数据库和指定多表的备份，但数据库本身定义不备份
mysqldump [OPTIONS] -B DB1 [DB2 DB3...] #支持指定数据库备份，包含数据库本身定义也会备份
mysqldump [OPTIONS] -A [OPTIONS]        #备份所有数据库，包含数据库本身定义也会备份

mysqldump 常见通用选项：

-u, --user=name               User for login if not current user
-p, --password[=name]         Password to use when connecting to server
-A, --all-databases           #备份所有数据库，含create database
-B, --databases db_name…      #指定备份的数据库，包括create database语句
-E, --events                  #备份相关的所有event scheduler
-R, --routines                #备份所有存储过程和自定义函数
--triggers：                  #备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8  #指定字符集
--master-data[=#]             #注意：MySQL8.0.26版以后，此选项变为--source-data #此选项须启用二进制日志 #1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复制多机使用 #2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用,适用于备份还原 #此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启--single-transaction）
-F, --flush-logs              #备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--single-transaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志
--compact                     #去掉注释，适合调试，节约备份占用的空间,生产不使用
-d, --no-data                 #只备份表结构,不备份数据,即只备份create table 
-t, --no-create-info          #只备份数据,不备份表结构,即不备份create table 
-n,--no-create-db             #不备份create database，可被-A或-B覆盖
--flush-privileges            #备份mysql或相关时需要使用
-f, --force                   #忽略SQL错误，继续执行
--hex-blob                    #使用十六进制符号转储二进制列，当有包括BINARY,VARBINARY,BLOB，BIT的数据类型的列时使用，避免乱码
-q, --quick                   #不缓存查询，直接输出，加快备份速度

mysqldump的MyISAM存储引擎相关的备份选项：

MyISAM不支持事务，只能支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作

-x,--lock-all-tables #加全局读锁，锁定所有库的所有表，同时加--single-transaction或--lock-tables选项会关闭此选项功能，注意：数据量大时，可能会导致长时间无法并发访问数据库
-l,--lock-tables #对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致 #注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用

mysqldump的InnoDB存储引擎相关的备份选项：

InnoDB 存储引擎支持事务,可以利用事务的相应的隔离级别,实现热备，也可以实现温备但不建议用

--single-transaction  #此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务
#此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE,此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥,备份大型表时，建议将--single-transaction选项和--quick结合一起使用

生产环境实战备份策略

InnoDB建议备份策略

mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --master-data=2 --flush-privileges --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql

#新版8.0.26以上
mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --source-data=2 --flush-privileges --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql

MyISAM建议备份策略

mysqldump -uroot -p123456 -A -F -E -R -x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql

mysqldump 备份还原实战案例

实战案例：特定数据库的备份脚本

[root@centos8 ~]#cat mysql_backup.sh 
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
DB=hellodb
PASS=wang
[ -d $DIR ] || mkdir $DIR
mysqldump -uroot -p "$PASS" -F -E -R --triggers  --single-transaction --master-data=2 --default-character-set=utf8 -q  -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz

实战案例：分库备份并压缩

[root@centos8 ~]#for db in `mysql -uroot -e 'show databases'|grep -Ewv '^(Database|information_schema|performance_schema|sys)$'`;do mysqldump -B $db | gzip > /backup/$db.sql.gz;done

[root@centos8 ~]#mysql -uroot -e 'show databases'|grep -Ewv '^(Database|information_schema|performance_schema\sys)$'|while read db;do mysqldump -B $db | gzip > /backup/$db.sql.gz;done

[root@centos8 ~]#mysql -uroot -e 'show databases'|grep -Ewv '^(Database|information_schema|performance_schema|sys)$' | sed -rn 's#(.*)#mysqldump -B \1 | gzip > /backup/\1.sql.gz#p' |bash

[root@centos8 ~]#mysql -uroot -e 'show databases'|sed -rn '/^(Database|information_schema|performance_schema|sys)$/!s#(.*)#mysqldump -B \1 | gzip > /backup/\1.sql.gz#p' |bash

实战案例：分库备份的实战脚本

[root@centos8 ~]#cat backup_db.sh 
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
PASS=123456
[ -d "$DIR" ] || mkdir $DIR for DB in `mysql -uroot -p "$PASS" -e 'show databases' | grep -Ewv "^Database|.*schema$"`;do
 mysqldump -F --single-transaction --master-data=2 --default-character-set=utf8 -q -B $DB | gzip >  ${DIR}/${DB}_${TIME}.sql.gz done

实战案例：完全备份和还原

#开启二进制日志
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log-bin

#备份
[root@centos8 ~]#mysqldump -uroot -p123456 -A -F --single-transaction --master-data=2 |gzip > /backup/all-`date +%F`.sql.gz

#还原
[root@centos8 backup]#dnf install mariadb-server
[root@centos8 backup]#gzip -d all-2019-11-27.sql.gz

[root@centos8 ~]#mysql
MariaDB [(none)]> set sql_log_bin=off;
MariaDB [(none)]> source /backup/all-2019-11-27.sql
MariaDB [(none)]> set sql_log_bin=on;

实战案例：利用二进制日志，还原数据库最新状态

#二进制日志独立存放
[mysqld]
log-bin=/data/mysql/mysql-bin 

#完全备份，并记录备份的二进制位置
mysqldump  -uroot -p123456 -A -F --default-character-set=utf8  --single-transaction --master-data=2 | gzip > /backup/all_`date +%F`.sql.gz

#修改数据库
insert students (name,age,gender)value('mage',20,'M');
insert students (name,age,gender)value('wang',22,'M');

#损坏数据库
rm -rf /var/lib/mysql/*

#还原
cd /backup gzip -d all_2019-11-25.sql.gz

#CentOS 8 需要事先生成数据库相关文件，CentOS7 不需要执行此步
mysql_install_db  --user=mysql
systemctl restart mariadb
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       998 |
| mysql-bin.000002 |     28090 |
| mysql-bin.000003 |       342 |
+------------------+-----------+
3 rows in set (0.000 sec)
MariaDB [(none)]>set sql_log_bin=0;
MariaDB [(none)]>source /data/all_2019-11-25.sql
[root@centos8 ~]#grep '^-- CHANGE MASTER TO' /data/all_2019-11-25.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=328;

#二进制日志的备份
[root@centos8 mysql]#mysqlbinlog mysql-bin.000001 --start-position=328 > /backup/inc.sql
[root@centos8 mysql]#mysqlbinlog mysql-bin.000002 >> /backup/inc.sql
MariaDB [(none)]>set sql_log_bin=0;
MariaDB [(none)]>source /backup/inc.sql
MariaDB [(none)]>set sql_log_bin=1;

实战案例：mysqldump 和二进制日志结合实现差异(增量)备份

[root@centos8 ~]#mysqldump -uroot -p -A -F --single-transaction --master-data=2 |gzip > /backup/all-`date +%F`.sql.gz

#观察上面备份文件中记录的二进制文件和位置，定期将其之后生成的所有二进制日志进行复制备份
[root@centos8 ~]#cp /var/lib/mysql/mariadb-bin.000003 /backup     #假设mariadb-bin.000003是后续生成的二进制日志
[root@centos8 ~]#mysqlbinlog backup/mariadb-bin.000003 > /backup/inc.sql

实战案例：恢复误删除的表

案例说明：每天2：30做完全备份，早上10：00误删除了表students，10：10才发现故障，现需要将数据库还原到10：10的状态，且恢复被删除的students表

#完全备份,要求必须开启二进制日志
[root@centos8 ~]#mysqldump -uroot -p -A -F --single-transaction --master-data=2 > /backup/allbackup_`date +%F_%T`.sql
[root@centos8 ~]#ll /backup/total 2992
-rw-r--r-- 1 root root 3060921 Nov 27 10:20 allbackup_2019-11-27_10:20:08.sql

#完全备份后数据更新
MariaDB [testdb]> insert students (name,age,gender) values('rose',20,'f');
Query OK, 1 row affected (0.001 sec)
MariaDB [testdb]> insert students (name,age,gender) values('jack',22,'M');
Query OK, 1 row affected (0.001 sec)

#10：00误删除了一个重要的表
MariaDB [testdb]> drop table students;
Query OK, 0 rows affected (0.021 sec)

#后续其它表继续更新
MariaDB [testdb]> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> insert teachers (name,age,gender)values('wang',30,'M');
Query OK, 1 row affected (0.002 sec)
MariaDB [hellodb]> insert teachers (name,age,gender)values('mage',28,'M');
Query OK, 1 row affected (0.002 sec)
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name         | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang   |  45 | M     |
|   2 | Zhang Sanfeng |  94 | M     |
|   3 | Miejue Shitai |  77 | F     |
|   4 | Lin Chaoying |  93 | F     |
|   5 | wang         |  30 | M     |
|   6 | mage         |  28 | M     |
+-----+---------------+-----+--------+
6 rows in set (0.001 sec)

#10：10发现表删除，进行还原
#停止数据库访问
#从完全备份中，找到二进制位置
[root@centos8 ~]#grep '\-\- CHANGE MASTER TO' /backup/allbackup_2019-11-27_10\:20\:08.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;

#备份从完全备份后的二进制日志
[root@centos8 ~]#mysqlbinlog --start-position=389 /var/lib/mysql/mariadb-bin.000003 > /backup/inc.sql
[root@centos8 ~]#mysqlbinlog /var/lib/mysql/mariadb-bin.000004 >> /backup/inc.sql
[root@centos8 ~]#mysqlbinlog /var/lib/mysql/mariadb-bin.000005 >> /backup/inc.sql

#找到误删除的语句，从备份中删除此语句
[root@centos8 ~]#vim /backup/inc.sql
#DROP TABLE `students` /* generated by server */

#如果文件过大，可以使用sed实现
[root@centos8 ~]#sed -i '/DROP TABLE `students`/d' /backup/inc.sql

#利用完全备份和修改过的二进制日志进行还原
[root@centos8 ~]#mysql -uroot -p
MariaDB [hellodb]> set sql_log_bin=0;
MariaDB [hellodb]> source /backup/allbackup_2019-11-27_10:20:08.sql;
MariaDB [hellodb]> source /backup/inc.sql
MariaDB [hellodb]> set sql_log_bin=1;

今天的分享就到这里，谢谢各位的观摩！

标签：--,备份,mysql,二进制,mysqldump,MySQL,日志,root
From： https://blog.51cto.com/chengpitang/6086464