mysql --log-bin_MYSQL 日志(log-bin)备份

1.启动日志

mysqld_safe --log-bin --user=mysql &

2.修改my.cnf文件

[mysqld]

server-id=1

log-bin=master 日志名称

log-bin-index=master

binlog_format=statement

3.查看是否启动了binlog日志

mysql> set binlog_rows_query_log_events=1;

4.重新启动mysqld

systemctl restart systemd

mysql> show binlog events \G

*************************** 1. row ***************************

Log_name: 131-bin.000001

Pos: 4

Event_type: Format_desc

Server_id: 1

End_log_pos: 106

Info: Server ver: 5.1.34-log, Binlog ver: 4

1 row in set (0.00 sec)

5.创建库 　　创建表 　　刷新日志 　　flush logs 　　插入数据　　物理删除库

mysql >create database upup;

mysql>use upup;

mysql> create table t1(id int,name char(10));

mysql>flush logs

mysql> insert into t1 values(1 ,'ww');

mysql> drop database upup;

6.杀mysql进程(关闭2进制binlog)

pkill mysqld

[mysqld]

secure_file_priv =

#server-id=1

#log-bin=master

#log-bin-index=master

#binlog_format=statement

7.初始化数据库

[root@localhost mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data

8.启动mysql

systemctl restart mysqldd

9.恢复全备

mysql -u root -p123456 < /backup/all.sql

10.恢复binlog

mysqlbinlog /backup/master.000001 | mysql -u root -p123456