参考

精选：https://mp.weixin.qq.com/s/wMxBrKJDk70dLGmUFO-aEg

mysqldump 导出与导入

https://juejin.im/entry/6844903511201677325

注意： 为了保证数据备份的一致性，myisam 存储引擎在备份时需要加上 -l 参数 , 表示将所有表加上读锁，在备份期间，所有表将只能读而不能进行数据更新。但是对于事务存储引擎来说，可以采用更好的选项 –single-transaction，此选项使得 innodb 存储引擎得到一个快照 (snapshot)，使得备份的数据能够保证一致性

导出

如果没有指定数据库中的任何表，默认导出所有数据库中的所有表

所有数据库「用户 scm 权限内」

语法：mysqldump [options] –all-database > filename.sql

1
mysqldump -hlocalhost -u scm -p -l -F --all-database > alldatabase.sql

数据库 test

语法：mysqldump [options] –database DB1 [DB2,DB3…] > filename.sql

1
mysqldump -u scm -p -l -F test > test.sql

数据库 test 下的表

用法：mysqldump [options] db_name [table1 table2 table3…]

单个表 emp
1
mysqldump -u scm -p -l -F test emp > test_emp.sql
多个表 emp 和 dept
1
mysqldump -u scm -p -l -F test emp dept > test_emp_dept.sql

导入

mysqldump 的恢复也很简单，将备份作为输入执行即可：

1
mysql -u scm -p db_name < alldatabase.sql

注意，将备份恢复后数据并不完整，还需要将备份后执行的日志进行重做：

1
mysqlbinlog binlog-file | mysql -uroot -p

完整的 mysqldump 备份与恢复示例

凌晨 2:00 备份数据库

1
2
root@bogon:/usr/local/mysql/bin$ ./mysqldump -uroot -p -l -F t2 > t2.dmp
Enter password:

其中 -l 参数表示给所有表加读锁，-F 表示生成一个新的日志文件，此时，t2 中 emp 表的数据如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
# 为了便于测试，执行 reset master 删除所有 binlog。
MySQL [(none)]> reset master;
Query OK, 0 rows affected (0.00 sec)

# 此时只有一个 binlog 日志文件   mysql-bin.000001
MySQL [t2]> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)

备份完毕后，插入新的数据

1
2
3
4
5
6
# 因为上一步执行是加入了 -F 选项, 所以接下来的操作会被记录到新的二进制文件，即名为 mysql-bin.000002 的文件
MySQL [t2]> insert into test values (3,'c');
Query OK, 1 row affected (0.00 sec)

MySQL [t2]> insert into test values (4,'d');
Query OK, 1 row affected (0.00 sec)

数据库突然故障

模拟删库跑路：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# 这里为了便于测试，不把删库操作记入日志，当前 session 设置 sql_log_bin 为 off。
# 删库后，执行 flush logs，让后续的 binlog 到新的文件中，即名为 mysql-bin.000003中
MySQL [t2]> set sql_log_bin = 0;
Query OK, 0 rows affected (0.00 sec)

MySQL [t2]> show variables like "%sql_log_bin%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

MySQL [t2]> drop database t2;
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> flush logs;
Query OK, 0 rows affected (0.22 sec)


MySQL [t2]> drop database t2;
Query OK, 3 rows affected (0.23 sec)

MySQL [(none)]> exit;
Bye

数据恢复

1
2
3
4
5
6
7
8
9
10
11
12
root@bogon:/usr/local/mysql/bin# ./mysql -e "create database t2"   
root@bogon:/usr/local/mysql/bin# ./mysql t2 < t2.dmp 

*******************************************************************
MySQL [t2]> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)

使用 mysqlbinlog 恢复自 mysqldump 备份以来的 binglog

根据前面操作的内容，可知从备份的时间点到删库的时间点之间的操作被记录到了 mysql-bin.000002 文件中

1
2
3
4
5
6
7
8
9
10
11
12
13
root@bogon:/usr/local/mysql/bin# ./mysqlbinlog --no-defaults /data/mysql/mysql-bin.000002 | ./mysql t2

*******************************************************
MySQL [t2]> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)

至此，数据恢复成功。

参考《Navicat-Premium》

MySQL Workbench 导入、导出

参考《MySQL-Workbench》

MySQL 定时备份「重点」

注意：千万不要以为有了备份就万无一失了，平时也要多检查备份脚本执行是否正确，生产的备份脚本内容是否可用，最好可以定期做恢复演练

Linux

  1. 备份脚本

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    #!/bin/bash
    # -------------------------------------------------------------------------------
    # FileName:    mysql_backup.sh 
    # Describe:    Used for database backup
    # Revision:    1.0
    # Date:        2020/08/11
    # Author:      wang

    # 设置mysql的登录用户名和密码(根据实际情况填写)
    mysql_user = "root"
    mysql_password = "yourpassword"
    mysql_host = "localhost"
    mysql_port = "3306"
    backup_dir = /data/mysql_backup

    dt=date +'%Y%m%d_%H%M'
    echo "Backup Begin Date:" $(date +"%Y-%m-%d %H:%M:%S")

    # 备份全部数据库
    mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -R -E --all-databases --single-transaction ｜ gzip > $backup_dir/mysql_backup_$dt.sql.gz

    mysqladmin -uroot -p123456 flush-logs

    find $backup_dir -mtime +90 -type f -name '*.sql' -exec rm -rf {} \;
    echo "Backup Succeed Date:" $(date +"%Y-%m-%d %H:%M:%S")

  2. 定时任务

    1
    2
    # 每天凌晨 2 点进行备份; 注意脚本执行权限及修改脚本路径
    00 02 * * * sh /root/scripts/mysql_backup.sh > /root/scripts/mysql_backup.log 2>&1

Windows

先安装 gzip，安装路径示例：”C:\Program Files (x86)\GnuWin32\bin\gzip.exe”

gzip 下载地址：Gzip for Windows (sourceforge.net)

选择安装包 【Complete package, except sources】

  1. 备份脚本

    比如我们可以在 E 盘下创建一个 MySQLdata_Bak 目录，此目录下创建 mysql_backup 目录存放备份文件，mysql_bak.bat 是备份脚本，脚本内容如下（自动删除 90 天前的备份文件）

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    rem auther:wang
    rem date:20200811
    rem ******MySQL backup start********
    @echo off

    forfiles /p "D:\prod\MySQL\MySQL8DataBack" /m tcboot_*.sql -d -90 /c "cmd /c del /f @path"

    set "Ymd=%date:~0,4%%date:~5,2%%date:~8,2%0%time:~1,1%%time:~3,2%%time:~6,2%"

    "D:\prod\MySQL\MySQL8\bin\mysqldump" -utcboot_zhzf -ptcboot_zhzf -P3306 --default-character-set=utf8 -R -E --single-transaction tcboot_zhzf | "C:\Program Files (x86)\GnuWin32\bin\gzip.exe" > "D:\prod\MySQL\MySQL8DataBack\tcboot_zhzf_%Ymd%.sql.gz"

    "D:\prod\MySQL\MySQL8\bin\mysqladmin" -uroot -p123456 flush-logs

    @echo on
    rem ******MySQL backup end********

  2. 脚本调试完成后就可以加入计划任务