mysql备份的数据与恢复

一．录入数据
1.登录mysql
[root@centos01 ~]# mysql -uroot -ppwd@123
2.创建数据库
create database bdqn;
show databases;
3.创建表格式 1q
create table bdqn.1q(名字 char(5),班级 char (2));
desc bdqn.1ban;
4.插入1ban数据
insert into bdqn.1q values(‘鹏鹏’,‘1班’);
insert into bdqn.1q values(‘朋朋’,‘1班’);
select * from bdqn.1q;
5.配置日志分割
[root@centos ~]# mysqladmin -uroot -ppwd@123 flush-log
7.备份bdqn数据库的1q表
[root@centos01 ~]# mysqldump -uroot -ppwd@123 bdqn 1q > bdqn_1ban.sql
6.查看日志文件
[root@centos ~]# cd /usr/local/mysql/data/
[root@centos data]# mysqlbinlog mysql-bin.000004

7.插入2q数据
insert into bdqn.1q values(‘砰砰’,‘1班’);
insert into bdqn.1q values(‘蓬蓬’,‘1班’);
select * from bdqn.1q;
9.配置日志分割
[root@centos ~]# mysqladmin -uroot -ppwd@123 flush-log
8.查看日志文件
[root@centos data]# mysqlbinlog mysql-bin.000005
9.插入3班数据
insert into bdqn.1q values('嘭嘭,‘3班’);
insert into bdqn.1q values(‘彭鹏’,‘3班’);
select * from bdqn.3q;
10.配置日志分割
[root@centos ~]# mysqladmin -uroot -ppwd@123 flush-log
11.查看日志文件
[root@centos data]# mysqlbinlog mysql-bin.000006
12.备份二进制日志
[root@centos01 ~]# cp /usr/local/mysql/data/mysql-bin.000006 ./
13.模拟数据损坏
[root@centos01 ~]# mysql -uroot -ppwd@123 bdqn < bdqn_1q.sql
14.将要恢复的文件复制到root
[root@centos ~]# cp /usr/local/mysql/data/mysql-bin.000006 /root/
14.通过二进制日志恢复数据
[root@centos01 ~]# mysqlbinlog --no-defaults ./mysql-bin.000006 | mysql -uroot -ppwd@123