mysql备份的数据与恢复

一．录入数据 1.登录mysql [[email protected] ~]# mysql -uroot [email protected] 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.配置日志分割 [[email protected] ~]# mysqladmin -uroot [email protected] flush-log 7.备份bdqn数据库的1q表 [[email protected] ~]# mysqldump -uroot [email protected] bdqn 1q > bdqn_1ban.sql 6.查看日志文件 [[email protected] ~]# cd /usr/local/mysql/data/ [[email protected] data]# mysqlbinlog mysql-bin.000004

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