MySql日志
开启bin-log日志
1,使用vim或其他编辑器,打开mysql配置文件
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
2,找到一下两个配置项
server-id =123456
log_bin = var/log/mysql/mysql-bin
3,把注释打开,改完后,保存退出
4,重启mysql
sudo service muysql restart
5,查看bin-log日志:
mysql>show binary logs;
6,ls 查看是否存在mysql日志文件
cd /var/lib/mysql
7,清空所有的bin-log日志
mysql>reset master
8,查看binlog日志文件
mysqlbinlog mysql-bin.000001
备份数据时,刷新日志:
mysqldumo -u root -pwei test -1 -F '/tmp/test.sql'
其中:-F即flush logs,可以重新生成新的日志文件,当然包括log-bin日志
数据库的恢复
前提开启了bin-log日志,并定期备份
主要思路如下:
1,通过定期备份的文件恢复大量数据
2,通过日志恢复备份后的数据操作
– 刷新日志
mysql> reset master;
Query OK, 0 rows affected (0.39 sec)
–查询最新日志
show binary logs;
–创建数据库
mysql>create database ops;
Query OK, 1 row affcted (0.28 sec)
–选择并打开库
musql>use ops;
Database changed
–创建表
create table user(
id int not null auto_increment,
name char(20) not null,
age int not null,
primary key(id)
) engine=InnoDB;
–添加数据
insert into user values(1,"wangbo","22"),(2,"guohui","22"),(3,"zhangheng","27");
–查询
mysql>select * from user;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 1 | wangbo | 24 |
| 2 | guohui | 22 |
| 3 | zhangheng | 27 |
+----+-----------+-----+
3 rows in set (0.00 sec)
–现在进行数据备份
mysqldump -u root -p -B -F -R -x --master-data=2 ops>/home/yc/py08/04-mysql/ops.sql
参数说明:
-B:指定数据库
-F:刷新日志
-R:备份存储过程等
-X:锁表
–再添加新的数据
inset into user values(4,"liupeng","21"),(5,"xiaoda","31"),(6,"fuaiai","26");
–查询数据
mysql> select * from user;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 1 | wangbo | 24 |
| 2 | guohui | 22 |
| 3 | zhangheng | 27 |
| 4 | liupeng | 21 |
| 5 | xiaoda | 31 |
| 6 | fuaiai | 26 |
+----+-----------+-----+
6 rows in set (0.00 sec)
–此时误操作,删除了test数据库
drop databases ops;
mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| python4 |
| sys |
| wx |
+--------------------+
6 rows in set (0.00 sec)
1,先将binlog文件导出
–将binlog文件导出sql文件,并vim编辑它删除其中的drop语句
--[root@vm-002 backup]# mysqlbinlog -d ops /var/lib/mysql/mysql-bin.000002>/home/yc/002bir.sql
sudo mysqlbinlog -d ops /var/log/mysql/mysql-bin.000002>/home/yc/py08/04-mysql/002bin.sql
2,–删除里面的drop语句
vim 002bin.sql
3,–导入备份的数据文件
mysql -u root -p </home/yc/py08/04-mysql/ops.sql
4,–再导入删除 drop语句后的binlog日志文件
mysql -u root -p ops</home/yc/py08/04-mysql/002bin.sql