Mysql数据恢复

Mysql备份命令

Mysql数据库的备份

1.命令行备份使用工具mysqldump

mysqldump -uroot -proot --database 数据库A 数据库B > D:/备份.sql

2.参数详解
-h127.0.0.1:连接远程数据库
-A:表示全库备份数据和结构
-d:表示备份结构
-t:表示备份数据
--add-drop-table:默认；带有删除表的sql文件
--databases:后边可以追加多个数据库

Mysql还原备份内容

方式一：在命令行中直接进行

mysql -uroot -p123456 数据库名< D:/mydb.sql

方式二：shell脚本，登入mysql系统后执行source

mysql> source /data/mysqlDump/mydb.sql

增量备份

本质上，就是备份二进制文件日志来恢复，重新执行一遍sql；

binlog日志

查看binlog日志是否开启

show variables like 'log_%';

查看binlog所有日志文件名和大小

show binary logs;
-- 或者
show master logs;

±---------------------------±----------±----------+
| Log_name | File_size | Encrypted |
±---------------------------±----------±----------+
| DESKTOP-H5618RO-bin.000026 | 2443762 | No |
| DESKTOP-H5618RO-bin.000027 | 178 | No |
| DESKTOP-H5618RO-bin.000028 | 178 | No |
| DESKTOP-H5618RO-bin.000029 | 178 | No |
| DESKTOP-H5618RO-bin.000030 | 178 | No |
| DESKTOP-H5618RO-bin.000031 | 178 | No |
| DESKTOP-H5618RO-bin.000032 | 4291003 | No |
| DESKTOP-H5618RO-bin.000033 | 9675895 | No |
| DESKTOP-H5618RO-bin.000034 | 57671793 | No |
| DESKTOP-H5618RO-bin.000035 | 212 | No |
| DESKTOP-H5618RO-bin.000036 | 155 | No |
±---------------------------±----------±----------+

查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值

mysql> show master status\G;

刷新使用新的日志文件，一般在还原前执行。

mysqladmin -uroot -proot flush-logs
-- 或者
mysql> flush logs

重置binlog日志（等价于删除）

reset master;

查看binlog日志
方式一

mysqlbinlog 'binlog.00003';
-- 可读性不高
 ...............................................................................
         # at 552
         #131128 17:50:46 server id 1  end_log_pos 665   Query   thread_id=11    exec_time=0     error_code=0 ---->执行时间:17:50:46；pos点:665
         SET TIMESTAMP=1385632246/*!*/;
         update zyyshop.stu set name='李四' where id=4              ---->执行的SQL
         /*!*/;
         # at 665
         #131128 17:50:46 server id 1  end_log_pos 692   Xid = 1454 ---->执行时间:17:50:46；pos点:692 
         ...............................................................................

         注: server id 1     数据库主机的服务号；
             end_log_pos 665 pos点
             thread_id=11    线程号

方式二：

mysql> show binlog events in 'binlog.000003';

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

             选项解析：
               IN 'log_name'   指定要查询的binlog文件名(不指定就是第一个binlog文件)
               FROM pos        指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
               LIMIT [offset,] 偏移量(不指定就是0)
               row_count       查询总条数(不指定就是所有行)

恢复命令的格式

mysqlbinlog [options] file | mysql -uroot -proot_password database_name

常用参数
–start-datetime 开始时间，格式 2020-06-19 18:00:00
–stop-datetime 结束时间，格式同上
–start-positon 开始位置，（需要查看binlog文件）
–stop-position 结束位置，同上

在MySQL5.5以下版本使用mysqlbinlog命令时如果报错，就加上 “–no-defaults”选项

小知识点：初始化mysql容器时，添加参数–binlog-rows-query-log-events=ON。或者到容器当中修改/etc/mysql/my.cnf文件，添加参数binlog_rows_query_log_events=ON，然后重启mysql容器。这样可以把原始的SQL添加到binlog文件当中。

Windows命令脚本

实现功能：分库备份保存。

@echo off
:: mysqldump程序文件位置
set "mysql=C:/Program Files/MySQL/MySQL Server 8.0/bin"
:: 备份保存的位置
set "baseBackupDir=D:\corn\mysql\worsoftx_pm\"
set "yMd=%date:~,4%%date:~5,2%%date:~8,2%"
set "hms=%time:~,2%%time:~3,2%%time:~6,2%"
set daysAgo=3

:: 删除之前的数据，仅保留3天的数据
forfiles /p %baseBackupDir% /s /m *.* /d -%daysAgo% /c "cmd /c del @path"

cd "%mysql%"

for /f "usebackq delims=" %%i in (`mysql -uroot -proot -e "show databases"`) do ( 
    mysqldump -uroot -proot -B --single-transaction --events --master-data=2 %%i >%baseBackupDir%%%i_%yMd%_%hms%.sql 
)
@echo on

参考1：数据库备份命令和Linux环境下的备份脚本
参考2：mysql定时备份任务以及还原
参考3：MySQL的binlog日志