时时备份 使用mysql自带binlog日志实现
binlog日志文件的使用:
1 什么是binlog日志 记录除查询之外的sql命令
2 启用binlog日志
[root@dbsvr1 ~]# vim /etc/my.cnf
[mysqld]
.. ..
log-bin //启用binlog日志
server_id=100 //指定id值
[root@dbsvr1 ~]# systemctl restart mysqld
mysql> show master status;
]# ls /var/lib/mysql/
手动生成新的日志文件:
]# mysqldump -uroot -pabc123 --flush-logs db3 > /dbbak/db3.sql
]# mysql -uroot -pabc123 -e "flush logs"
]# systemctl restart mysqld
mysql> flush logs
3 删除binlog日志
mysql> purge master logs to "host50-bin.000004";
mysql> reset master;
4 自定义日志存储目录及日志文件名
]# mkdir /mylog
]# chown mysql /mylog
]# vim /etc/my.cnf
[mysqld]
server_id=50
log-bin=/mylog/plj
:wq
]# systemctl restart mysqld
]# ls /mylog
mysql> show master status;
5 使用binlog日志恢复数据
格式: ]# mysqlbinlog binlog日志文件 | mysql -uroot -p密码
192.168.4.50
mysql> show master status;
mysql> create database db6;
mysql> create table db6.t1(id int);
mysql> insert into db6.t1 values(99999);
mysql> insert into db6.t1 values(99998);
mysql> insert into db6.t1 values(99997);
mysql> select * from db6.t1;
mysql> show master status;
]# scp /mylog/plj.000001 root@192.168.4.51:/root/
192.168.4.51
]# mysqlbinlog /root/plj.000001 | mysql -uroot -pabc123
mysql> select * from db6.t1;
修改binlog日志文件记录sql命令的格式:
查看默认的记录格式:
mysql> show variables like "%binlog%";
mysql> show variables like "binlog_format";
修改binlog日志格式
]# vim /etc/my.cnf
[mysqld]
binlog_format="mixed"
:wq
]# systemctl restart mysqld
mysql> show variables like "binlog_format";
mysql> show master status;
mysql> insert into db6.t1 values(123);
mysql> insert into db6.t1 values(456);
mysql> insert into db6.t1 values(789);
mysql> insert into db6.t1 values(1010);
mysql> select * from db6.t1;
mysql> delete from db6.t1 where id>1000;
mysql> delete from db6.t1;
mysql> drop db6.t1;
]# mysqlbinlog /mylog/plj.000002
]# mysqlbinlog /mylog/plj.000002 | grep -i insert
]# mysqlbinlog /mylog/plj.000002 | grep -i delete
]# mysqlbinlog /mylog/plj.000002 | grep -i drop
日志文件如何区分记录的多条sql命令 ? 偏移量 和 命令执行时间
格式: ]# mysqlbinlog 选项 binlog日志文件 | mysql -uroot -p密码
--start-position=数字 //起始偏移量
--stop-position=数字 //结束偏移量
--start-datetime="yyyy-mm-dd hh:mm:ss" //起始时间
--stop-datetime="yyyy-mm-dd hh:mm:ss" //结束时间
192.168.4.50 ]# scp /mylog/plj.000002 root@192.168.4.51:/root/
使用binlog日志文件恢复部分数据
格式: ]# mysqlbinlog 选项 binlog日志文件 | mysql -uroot -p密码
192.168.4.51]# mysqlbinlog --start-position=293 --stop-position=1724 /root/plj.000002 | mysql -uroot -pabc123
192.168.4.51 mysql> select * from db6.t1 ;