#简单恢复MySQL数据
#步骤
开启bin-log日志
1.修改配置文件
etc/mysql/mysql.conf.d/mysqld.cnf
打开注释 :记录一下log_bin指定的的地址 这个地址使我们日志地址
server-id = 123456
log_bin = /var/lib/mysql/mysql-bin
2.重启服务
service mysql restart
3.进入mysql 输入show binary logs 查看日志
当我们进行增删改操作的时候 日志会记录我们的操作
4.刷新日志 flush logs;
通过binlog日志恢复数据
数据库凌晨2:00进行数据备份
2:00-早上6:00数据还没有备份
但是早上六点误操作 删除了数据库
======模拟场景=======
1.清空日志文件 reset master;
2.创建库
create database database_name
创建表
create table table_name(字段名 字段约束,字段名 字段约束)
插入3条数据
insert into table_name values(),(),()
3.备份数据库 全备份
mysqldump -uroot -p -B -F -R -x --master-data=2 ops >/home/yc/ops.sql
参数说明:
-B:指定数据库
-F:刷新日志
-R:备份存储过程等
-x:锁表
--master-data:在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息
4.再添加数据
insert into table_name values(),(),()
5.误操作 删库
drop database database_name
====开始恢复数据=======
6.找binlog日志 并导出成.sql
mysqlbinlog -uroot -p 日志文件名 > 日志文件.sql
7.打开导出的日志文件 找到删除语句 删除 保存退出
8.先导入备份的数据
mysql -uroot -p < 备份的数据库文件.sql
9.导入修改后的日志文件
mysql -uroot -p 库名< 日志文件.sql
hanbing@hanbing-VirtualBox:/$ mysql -uroot -p;
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.7.21-0ubuntu0.16.04.1-log (Ubuntu)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
mysql> create database demo;
Query OK, 1 row affected (0.01 sec)
mysql> use demo;
Database changed
mysql> create table demo1(sex int,age int); ;
Query OK, 0 rows affected (0.03 sec)
mysql> create table demo1(sex int,age int);
ERROR 1050 (42S01): Table 'demo1' already exists
mysql> insert into demo1 values(1,18),(0,17);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> exit;
Bye
hanbing@hanbing-VirtualBox:/$ mysqldump -uroot -p -B -F -R -x --master-data=2 demo >/home/hanbing/demo.sql;
Enter password:
hanbing@hanbing-VirtualBox:/$ mysql -uroot -p;
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.7.21-0ubuntu0.16.04.1-log (Ubuntu)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> insert into demo1 values(1,16),(0,15);
ERROR 1046 (3D000): No database selected
mysql> use demo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into demo1 values(1,16),(0,15);
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> drop database demo;
Query OK, 1 row affected (0.04 sec)
mysql> exit;
Bye
hanbing@hanbing-VirtualBox:/$ sudo mysqlbinlog -uroot -p /var/log/mysql/mysql-bin.000002 > /home/hanbing/demo1.sql;
[sudo] hanbing 的密码:
Enter password:
hanbing@hanbing-VirtualBox:/$ sudo vi /home/hanbing/demo1.sql;
hanbing@hanbing-VirtualBox:/$ mysql -uroot -p < /home/hanbing/demo.sql;
Enter password:
hanbing@hanbing-VirtualBox:/$ mysql -uroot -p demo < /home/hanbing/demo1.sql;
Enter password:
hanbing@hanbing-VirtualBox:/$ mysql -uroot -p;
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.7.21-0ubuntu0.16.04.1-log (Ubuntu)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show detabases;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'detabases' at line 1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| database_name |
| demo |
| mysql |
| ops |
| performance_schema |
| sys |
| username |
+--------------------+
8 rows in set (0.00 sec)
mysql> use demo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| demo1 |
+----------------+
1 row in set (0.00 sec)
mysql> select * from demo1;
+------+------+
| sex | age |
+------+------+
| 1 | 18 |
| 0 | 17 |
| 1 | 16 |
| 0 | 15 |
+------+------+
4 rows in set (0.00 sec)
mysql>
兄弟连学python
Python学习交流、资源共享群:563626388 QQ