mysql数据库的备份

参考：https://blog.51cto.com/13154101/2044251?utm_source=oschina-app

https://www.cnblogs.com/jerry-rock/p/7412178.html

 

binlog_format=row  设置成row  应该可以解决问题？？？？？？？？

如果mysql未启动二进制日志，则show master status;  会显示为空，

mysql> show master status;
Empty set (0.00 sec)

只需在C:\ProgramData\MySQL\MySQL Server 5.7中my.ini文件添加即可。(binlog_format=MIXED  需要探索，不加的话，二进制文件中没有insert,还需要继续探索？？？？？？？？？？)

# Binary Logging.
# log-bin
log-bin=bin.log
binlog_format=MIXED

接着：

mysql> show master status
    -> ;
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| bin.000001 |      154 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

备份用：在CMD下，非SQL下

mysqldump  -h 127.0.0.1 -uroot -p12345  --all-databases  --lock-all-tables --flush-logs --master-data=2 >D:\data\2020-3-29-2322.sql

 

指定开启二进制日志时指定的文件（每flush logs一次二进制日志重重新记录一个新的文件），此时为bin.000002(增加了1)

 

mysql> show master status;
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| bin.000002 |      430 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+
1 row in set

mysql> 

 插入数据后：

mysql> show master status;
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| bin.000002 |      982 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+
1 row in set

mysql>