Mysqldump分库备份以及二进制bin_log备份和误删某表还原

最新推荐文章于 2021-05-11 14:24:10 发布
最新推荐文章于 2021-05-11 14:24:10 发布
阅读量182 收藏 1
点赞数
分类专栏： mysql linux 文章标签： mysqldump 分库备份 二进制bin_log linux
kaivi's blog
本文链接：https://blog.csdn.net/weixin_45651006/article/details/103255838
版权
linux 同时被 2 个专栏收录
14 篇文章 0 订阅
订阅专栏
mysql
10 篇文章 0 订阅
订阅专栏

1）分库备份并压缩

由于一台mysql数据库服务器上有多个数据库，所以在考虑备份的时候也将多个库分开进行，以便在单个库出现问题时恢复。

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| kaivi              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

MariaDB [(none)]> use kaivi
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
MariaDB [kaivi]> 
[1]+  Stopped                 mysql

[root@centos7K ~]#mysql -u root -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'
|while read db;do mysqldump -B $db |gzip > /data/$db.sql.gz;done
[root@centos7K ~]#
[root@centos7K data]#ll
total 148
-rw-r--r-- 1 root root   1913 Nov 26 14:32 hellodb.sql.gz
-rw-r--r-- 1 root root    755 Nov 26 14:32 kaivi.sql.gz
-rw-r--r-- 1 root root 136598 Nov 26 14:32 mysql.sql.gz
-rw-r--r-- 1 root root    517 Nov 26 14:32 test.sql.gz

写法二：
[root@centos7K ~]#for db in `mysql -u root -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'`;
do mysqldump -B $db |gzip > /data/$db.sql.gz;done


写法三：
[root@centos7K ~]#mysql -u root -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'
|sed -nr 's#(.*)#mysqldump -B \1 |gzip > /data/\1.sql.gz#p'
mysqldump -B hellodb |gzip > /data/hellodb.sql.gz
mysqldump -B kaivi |gzip > /data/kaivi.sql.gz
mysqldump -B mysql |gzip > /data/mysql.sql.gz
mysqldump -B test |gzip > /data/test.sql.gz
[root@centos7K ~]#mysql -u root -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'
|sed -nr 's#(.*)#mysqldump -B \1 |gzip > /data/\1.sql.gz#p'|bash

写法四：
[root@centos7K ~]#mysql -u root -e 'show databases'|sed -nr '/Database|information_schema|performance_schema/!s#
(.*)#mysqldump -B \1 |gzip > /data/\1.sql.gz#p'
mysqldump -B hellodb |gzip > /data/hellodb.sql.gz
mysqldump -B kaivi |gzip > /data/kaivi.sql.gz
mysqldump -B mysql |gzip > /data/mysql.sql.gz
mysqldump -B test |gzip > /data/test.sql.gz
[root@centos7K ~]#mysql -u root -e 'show databases'|sed -nr '/Database|information_schema|performance_schema/!s#
(.*)#mysqldump -B \1 |gzip > /data/\1.sql.gz#p'|bash


计划任务：工作日的2点半执行
[root@centos7K ~]#crontab -e
[root@centos7K ~]#crontab -l
30 2 * * 1-5 /data/scripts/backup_mysql.sh
#脚本名称为backup_masql.sh 放在路径/data/scripts/下

还原分库备份

从备份恢复指定库时，指定相应的备份文件即可

[root@centos7K ~]#gzip -d /data/mysql/bakup/db1.sql.gz
[root@centos7K ~]#mysql -uroot -p"" < /data/mysql/bakup/db1.sql

[root@centos7K data]#mysqldump --help|grep -C 3 '\-x'
经典查询方式

2）mysqldump和log_bin二进制备份还原

Usage:
mysqldump [OPTIONS] database [tables] # 备份单库，可以只备份其中的一部分表（部分备份）;表级别备份，还原时不会自动生成数据库；
mysqldump [OPTIONS] --databases|-B [OPTIONS] DB1 [DB2 DB3…] # 备份多库；库级别备份，还原时自动生成数据库

mysqldump [OPTIONS] --all-databases|-A [OPTIONS] # 备份所有库；全量备份

–master-data=1|2：指明备份时二进制日志文件的名字和所处的位置，备份之后的数据就可以从二进制日志文件的此处位置进行重放
1：记录为CHANGE MASTER TO语句，此语句不被注释；
2：记录为CHANGE MASTER TO语句，此语句被注释；

开启一个新的数据库
在这里插入图片描述
修改设置，把log_bin二进制日志功能打开
在这里插入图片描述
修改对应的权限并且重启一下

在这里插入图片描述

MariaDB [kaivi]> show master status;
MariaDB [kaivi]>show variables like 'log_bin';
MariaDB [kaivi]> select @@sql_log_bin;
查看二进制文件是否开启。这里注意这个是会话级，所以当前会话在操作的时候不能关闭。

修改配置文件，使得二进制文件独立出来，这个实验要配合log_bin二进制实现还原，必须把log_bin二进制文件独立到单独的文件中。
在这里插入图片描述

3）模拟全备份：

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> create database kaivi;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kaivi              |  创建的新库
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
  
MariaDB [(none)]> use kaivi            进入新库
Database changed
MariaDB [kaivi]> create table duanxin( id int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)       创建新表


MariaDB [kaivi]> insert into duanxin(id,name) values(1,"likai");   
Query OK, 1 row affected (0.00 sec)         增加一条记录
 
MariaDB [kaivi]> select * from duanxin;
+------+-------+
| id   | name  |
+------+-------+
|    1 | likai |
+------+-------+
1 row in set (0.00 sec)

MariaDB [kaivi]> insert into duanxin(id,name) values(2,"duanxin");
Query OK, 1 row affected (0.01 sec)       增加第二条记录
 
MariaDB [kaivi]> select * from duanxin;
+------+---------+
| id   | name    |
+------+---------+
|    1 | likai   |
|    2 | duanxin |
+------+---------+
2 rows in set (0.00 sec)

MariaDB [kaivi]> grant all on kaivi.* to test@'192.168.32.%' identified by 'centos';
Query OK, 0 rows affected (0.00 sec)

MariaDB [kaivi]> select user,host from mysql.user;   创建一个新用户
+------+--------------+
| user | host         |
+------+--------------+
| root | 127.0.0.1    |
| test | 192.168.32.% |
| root | ::1          |
|      | centos7k     |
| root | centos7k     |
|      | localhost    |
| root | localhost    |
+------+--------------+
7 rows in set (0.00 sec)

MariaDB [kaivi]> show master status;
ERROR 2006 (HY000): MySQL server has gone away
以上模拟每天的自动备份，也就是说在凌晨2点半之前的备份。

现在模拟在每天凌晨2点半做的全备份。这里可以写在脚本中自动备份全备份。这里实验就单独做一次全备份。

[root@centos7K ~]#mysqldump -A --master-data=2 |gzip > /data/all_`date +%F`.sql.gz

在这里插入图片描述

这里的全部备份截至的时间点为凌晨2点半，也就是上面模拟所增加的表中的2个人信息 likai duanxin 以及创建一个新用户test的信息。

假如早上8点的时候数据库损坏，那么在凌晨2点半到早上8点这段时间是没有备份的，要恢复也只能恢复到凌晨2点半的时候的数据，不过独立出来了二进制文件，可以通过二进制文件来恢复凌晨2点半之后的数据。

4）单机模拟二进制log_bin备份：

接下来修改一些数据库文件，模拟在凌晨2点半之后的数据库操作

MariaDB [kaivi]> insert into duanxin(id,name) values(3,"liudehua");
Query OK, 1 row affected (0.00 sec)

MariaDB [kaivi]> insert into duanxin(id,name) values(4,"xietingfeng");
Query OK, 1 row affected (0.00 sec)

MariaDB [kaivi]> grant all on kaivi.* to test2@'192.168.32.%' identified by 'centos';
Query OK, 0 rows affected (0.00 sec)

MariaDB [kaivi]> select * from duanxin;
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | likai       |
|    2 | duanxin     |
|    3 | liudehua    |    凌晨2点半之后操作
|    4 | xietingfeng |    凌晨2点半之后操作
+------+-------------+
4 rows in set (0.00 sec)

MariaDB [kaivi]> select user,host from mysql.user;
+-------+--------------+
| user  | host         |
+-------+--------------+
| root  | 127.0.0.1    |
| test  | 192.168.32.% |
| test2 | 192.168.32.% |  凌晨2点半之后操作
| root  | ::1          |
|       | centos7k     |
| root  | centos7k     |
|       | localhost    |
| root  | localhost    |
+-------+--------------+
8 rows in set (0.00 sec)

MariaDB [kaivi]>

5）模拟数据库损坏以及恢复

在这里插入图片描述
发现已经起不来数据库

先构建一个空的数据库，利用安装包里面的安装脚本。

[root@centos7K ~]#mysql_install_db --user=mysql
Installing MariaDB/MySQL system tables in '/var/lib/mysql' ...

[root@centos7K ~]#systemctl start mariadb
[root@centos7K ~]#mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.29-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 


MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> Ctrl-C -- exit!
Aborted

然后把之前备份的全备份文件解压之后和二进制文件导入新的数据库，重新生成数据库即可
在这里需要注意的是：在新库之中，默认是打开二进制文件的，这个是导入，不需要生成二进制日志文件，所以，需要先去数据库中把二进制日志文件临时的关闭之后才导入备份文件。


MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql_bin.000001 |       927 |
| mysql_bin.000002 |     29291 |
| mysql_bin.000003 |       342 |
+------------------+-----------+
3 rows in set (0.00 sec)

MariaDB [(none)]> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

MariaDB [(none)]> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 

这里注意的是，这个临时日志是会话级的，不能关闭改会话。

MariaDB [(none)]> source /data/all_2019-11-26.sql;

MariaDB [test]> 
MariaDB [test]> use kaivi;
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
MariaDB [kaivi]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kaivi              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [kaivi]> select * from duanxin;
+------+---------+
| id   | name    |
+------+---------+
|    1 | likai   |
|    2 | duanxin |
+------+---------+
2 rows in set (0.00 sec)

MariaDB [kaivi]> select user,host from mysql.user;
+------+--------------+
| user | host         |
+------+--------------+
| root | 127.0.0.1    |
| test | 192.168.32.% |
| root | ::1          |
|      | centos7k     |
| root | centos7k     |
|      | localhost    |
| root | localhost    |
+------+--------------+
7 rows in set (0.00 sec)

发现只有凌晨2点半的全备份的数据。之后的数据在二进制日志文件中，也需要导入。

[root@centos7K ~]#cd /data/

[root@centos7K data]#ll /data/mysql/
total 44
-rw-rw---- 1 mysql mysql   927 Nov 26 17:24 mysql_bin.000001
-rw-rw---- 1 mysql mysql 29291 Nov 26 17:26 mysql_bin.000002
-rw-rw---- 1 mysql mysql   342 Nov 26 17:27 mysql_bin.000003
-rw-rw---- 1 mysql mysql    87 Nov 26 17:27 mysql_bin.index
[root@centos7K data]#

这么多日志如何区分从哪里开始。这时候只需要打开之前备份的完全备份all_2019-11-26.sql可以查看到相关信息
在这里插入图片描述
也就是说在328之后，才是新发生的事件。所以我们只需要找到328之后的二进制日志备份即可

在这里插入图片描述

[root@centos7K data]#cd mysql/

[root@centos7K mysql]#mysqlbinlog mysql_bin.000001 --start-position=328 > inc.sql

[root@centos7K mysql]#mysqlbinlog mysql_bin.000002 >> inc.sql
[root@centos7K mysql]#

所以在这里inc.sql就是凌晨2点半之后到早上8点数据库的操作内容
在刚才没有关闭的新数据库会话中继续导入inc.sql即可

MariaDB [mysql]> source /data/mysql/inc.sql


MariaDB [mysql]> 
MariaDB [mysql]> use kaivi 
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
MariaDB [kaivi]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kaivi              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [kaivi]> select * from duanxin;
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | likai       |
|    2 | duanxin     |
|    3 | liudehua    |
|    4 | xietingfeng |
+------+-------------+
4 rows in set (0.00 sec)

MariaDB [kaivi]> select user,host from mysql.user;
+-------+--------------+
| user  | host         |
+-------+--------------+
| root  | 127.0.0.1    |
| test  | 192.168.32.% |
| test2 | 192.168.32.% |
| root  | ::1          |
|       | centos7k     |
| root  | centos7k     |
|       | localhost    |
| root  | localhost    |
+-------+--------------+
8 rows in set (0.00 sec)

MariaDB [kaivi]>

还原完成

建议:在还原期间最好数据库网络是断开状态。可以在配置文件中增加配置服务器选项：skip-networking=1

6）不同主机模拟二进制log_bin备份

node1（192.168.32.77）,node2（192.168.32.7）两个主机，把node1(192.168.32.77）上的数据在node2(192.168.32.7）上还原

2台主机都要是同个版本的数据库

node1：

[root@centos7K ~]#vim /etc/my.cnf.d/server.cnf

在这里插入图片描述
node2：

[root@centos7 ~]#vim /etc/my.cnf.d/server.cnf

在这里插入图片描述
在node1上创建数据库和表

MariaDB [mydb]> create database mydb;

MariaDB [mydb]> use mydb

MariaDB [mydb]> create table duanxin(name varchar(10) not null,age tinyint,gender char(1));

导入数据

[root@centos7K ~]#GENDER=('F' 'M')
[root@centos7K ~]#for i in {1..10};do mysql -e "insert into mydb.duanxin(name,age,gender)
 values ('student$i','$[$RANDOM%80+18]','${GENDER[$RANDOM%2]}');";done

导入数据第二次

[root@centos7K ~]#for i in {1..10};do mysql -e "insert into mydb.duanxin(name,age,gender) 
values ('student$i','$[$RANDOM%30+18]','${GENDER[$RANDOM%2]}');";done

在这里插入图片描述
可以在node1(192.168.32.77）上用mysqldump工具进行全备份，然后把备份文件发往node2(192.168.32.7）上
也可以在node2上用mysqldump远程备份，但是需要用户权限

MariaDB [mydb]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.32.7' IDENTIFIED BY 'centos';

在这里插入图片描述
在node2（192.168.32.7）上面：

[root@centos7 ~]#mysqldump  -A -F --flush-privileges --single-transaction -uroot -p'centos' -h 
192.168.32.77 --master-data=2 -R --triggers -E > /data/all_back_`date +%F`.sql

在node1（192.168.32.77）确认二进制日志开启：

MariaDB [mydb]> show master status;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    9
Current database: mydb

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000001 |      328 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)

MariaDB [mydb]> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)
如果没有开启，开启方法就是修改配置文件，然后重启数据库：
[root@centos7K ~]#vim /etc/my.cnf.d/server.cnf 
[mysqld]
log-bin=/data/mysql/mysql_bin
server-id=1

在node1（192.168.32.77）模拟二进制文件日志修改

MariaDB [mydb]> delete from duanxin where name='student10';

MariaDB [mydb]> delete from duanxin where name='student9';

MariaDB [mydb]> insert into duanxin(name,age) values("likai",20);

MariaDB [mydb]> update duanxin set name='duanxin' where name="student5";

在这里插入图片描述
注意，这里前面就已经为二进制bin_log文件已经重新生成路径：
在这里插入图片描述
在node1(192.168.32.77）中查看二进制日志文件重定向到其他路径
在这里插入图片描述
这么多日志如何区分从哪里开始。这时候只需要打开之前备份的完全备份all_back_2019-11-26.sql 可以查看到相关信息

[root@centos7 ~]#vim /data/all_back_2019-11-26.sql

在这里插入图片描述
也就是说在328之后，才是新发生的事件。所以我们只需要找到328之后的二进制日志备份即可
在node1（192.168.32.77）操作

[root@centos7K ~]#mysqlbinlog /data/mysql/mysql_bin.000002 --start-position=371 > /data/incr.sql

[root@centos7K ~]#ll /data/
total 4
-rw-r--r-- 1 root  root  3415 Nov 26 19:50 incr.sql
drwxr-xr-x 2 mysql mysql   77 Nov 26 19:40 mysql

[root@centos7K ~]#scp /data/incr.sql 192.168.32.7:/data/

The authenticity of host '192.168.32.7 (192.168.32.7)' can't be established.
ECDSA key fingerprint is SHA256:R+9CsnYLEXDvGpX/n78dCH/1p+m9or3SAfMDQ8YetrU.
ECDSA key fingerprint is MD5:94:46:5f:28:f3:c0:6b:dd:7e:90:c2:ca:0e:13:0f:05.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.32.7' (ECDSA) to the list of known hosts.
root@192.168.32.7's password: 
incr.sql                                                          100% 3415     2.4MB/s   00:00

在node2（192.168.32.7）查看是否传输成功

[root@centos7 ~]#ll /data/
total 476
-rw-r--r-- 1 root root 482703 Nov 26 19:40 all_back_2019-11-26.sql
-rw-r--r-- 1 root root   3415 Nov 26 19:51 incr.sql
[root@centos7 ~]#

在node2(192.168.32.7） 上是一个新库。在这里需要注意的是：在新库之中，默认是打开二进制文件的，这个是导入，不需要生成二进制日志文件，所以，需要先去数据库中把二进制日志文件临时的关闭之后才导入备份文件。

[root@centos7 ~]#mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.2.29-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

MariaDB [(none)]> select @@sql_log_bin;     查看二进制文件日志是否开启

+---------------+
| @@sql_log_bin |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

MariaDB [(none)]> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 

这里注意的是，这个临时日志是会话级的，不能关闭改会话。

之后把备份的数据导入新的数据库即可

[root@centos7 ~]#mysql < /data/all_back_2019-11-26.sql 

[root@centos7 data]#mysql < /data/incr.sql

进入node2（192.168.32.7）查看是否导入成功

[root@centos7 ~]#mysql mydb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.2.29-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [mydb]> select * from duanxin;
+----------+------+--------+
| name     | age  | gender |
+----------+------+--------+
| student1 |   83 | M      |
| student2 |   94 | F      |
| student3 |   89 | F      |
| student4 |   94 | M      |
| duanxin  |   67 | M      |
| student6 |   83 | M      |
| student7 |   68 | M      |
| student8 |   88 | M      |
| student1 |   45 | F      |
| student2 |   32 | M      |
| student3 |   20 | F      |
| student4 |   18 | F      |
| duanxin  |   47 | M      |
| student6 |   28 | M      |
| student7 |   47 | F      |
| student8 |   25 | M      |
| likai    |   20 | NULL   |
+----------+------+--------+
17 rows in set (0.00 sec)

7）误删某表还原

模拟时间如图
在这里插入图片描述
全备份时间点之前的数据模拟：

确保实验日志是开启的状态

[root@centos7K ~]#mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.2.29-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |  #模拟数据hellodb数据库
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)


MariaDB [(none)]> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

现在模拟全备份：

[root@centos7K ~]#mysqldump -F -A --single-transaction --master-data=2 > 
/data/backup/all-`date +%F`.sql

[root@centos7K ~]#ll /data/backup/
total 476
-rw-r--r-- 1 root root 487073 Nov 27 19:41 all-2019-11-27.sql

正常的生产环境中这里可以使用gzip先压缩成.gz文件然后再重定向 之后用 gzip -d 解压缩即可

模拟误删表时间点之前的更新的数据：（完全备份后数据更新）

MariaDB [(none)]> create database kaivi;
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| kaivi              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.01 sec)

MariaDB [(none)]> use kaivi
Database changed

MariaDB [kaivi]> create table duanxin(id int,name varchar(20));
Query OK, 0 rows affected (0.00 sec)
MariaDB [kaivi]> insert into duanxin(id,name) values(1,'likai');
Query OK, 1 row affected (0.00 sec)
MariaDB [kaivi]> insert into duanxin(id,name) values(2,'duanxin');
Query OK, 1 row affected (0.00 sec)

MariaDB [kaivi]> select * from duanxin;
+------+---------+
| id   | name    |
+------+---------+
|    1 | likai   |
|    2 | duanxin |
+------+---------+
2 rows in set (0.00 sec)

模拟误删除表操作：（10点整误删除了一个重要的表）

MariaDB [kaivi]> drop table duanxin;
Query OK, 0 rows affected (0.00 sec)

模拟再误删表之后的数据新增：（后续其它表继续更新）

MariaDB [kaivi]> use hellodb;
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
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> insert into teachers (name,age,gender) values("likai",20,"F");
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> insert into teachers (name,age,gender) values("kaivi",30,"M");
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | likai         |  20 | F      |
|   6 | kaivi         |  30 | M      |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)

MariaDB [hellodb]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| kaivi              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

直到误删除表不能访问的时间点，开始恢复还原：

#停止数据库访问
在配置文件中添加如下，断开网络设置。或者用防火墙来禁止访问也行。

[root@centos7K backup]#vim /etc/my.cnf.d/server.cnf 
[mysqld]
skip-networking=1

#从完全备份中，找到二进制位置

[root@centos7K ~]#grep -i '\-\- change master to' /data/backup/all-2019-11-27.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=371;
表示从全备份截断开始，是从二进制文件日志mysql-bin.000003开始，具体位置为371.

#备份从完全备份后的二进制日志
在这里插入图片描述
生产中建议把二进制文件日志单独放在其他单独目录


[root@centos7K ~]#mysqlbinlog --start-position=371 /var/lib/mysql/mysql-bin.000003 > /data/backup/inc.sql

如果后面还有其他的日志，则追加到inc.sql中

[root@centos7K ~]#ll /data/backup/
total 484
-rw-r--r-- 1 root root 487073 Nov 27 19:41 all-2019-11-27.sql
-rw-r--r-- 1 root root   4717 Nov 27 20:05 inc.sql

#找到误删除的语句，从备份中删除此语句

[root@centos7K ~]#mysqlbinlog --start-datetime='2019-11-27 19:41:00' 
/var/lib/mysql/mysql-bin.000003 |grep -i drop

DROP TABLE `duanxin` /* generated by server */
搜索是否在大概的时间点是否有对应的误操作 
[root@centos7K ~]#mysqlbinlog --start-datetime='2019-11-27 19:41:00' 
/var/lib/mysql/mysql-bin.000003 |grep -C4 -i drop
# at 1075
#191127 19:55:50 server id 1  end_log_pos 1189 CRC32 0x092f5299 	Query	thread_id=11	exec_time=0    error_code=0
SET TIMESTAMP=1574855750/*!*/;
SET @@session.pseudo_thread_id=11/*!*/;
DROP TABLE `duanxin` /* generated by server */
/*!*/;
# at 1189
#191127 19:57:40 server id 1  end_log_pos 1231 CRC32 0x2612e83a 	GTID 0-1-6 trans
/*!100001 SET @@session.gtid_seq_no=6*//*!*/;
搜索前后四行，看是否有关键字。这里的关键字就是at 1075 或者是at 1189 都可。可以帮助快速定位到对应误操作的地方，从而方便删除误操作语句。


[root@centos7K ~]#vim /data/backup/inc.sql 

#DROP TABLE `duanxin` /* generated by server */

在这里插入图片描述

利用完全备份和修改过的二进制日志进行还原
登入数据库，临时关闭二进制日志记录

[root@centos7K ~]#mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.2.29-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 

MariaDB [none]> source /data/backup/all-2019-11-27.sql

MariaDB [test]> source /data/backup/inc.sql

MariaDB [hellodb]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| kaivi              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | likai         |  20 | F      |
|   6 | kaivi         |  30 | M      |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)

MariaDB [hellodb]> use kaivi
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
MariaDB [kaivi]> show tables;
+-----------------+
| Tables_in_kaivi |
+-----------------+
| duanxin         |
+-----------------+
1 row in set (0.00 sec)

MariaDB [kaivi]> select * from duanxin;
+------+---------+
| id   | name    |
+------+---------+
|    1 | likai   |
|    2 | duanxin |
+------+---------+
2 rows in set (0.00 sec)

MariaDB [kaivi]> 
MariaDB [kaivi]> set sql_log_bin=1;  最后开启二进制文件日志
Query OK, 0 rows affected (0.00 sec)

最后关闭防火墙或者开启网络，正常开启数据库服务。

KAIVI-Blog
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql 备份binlog,Mysql备份与还原——mysqldump结合binlog
weixin_42467768的博客
03-17 799
MySQL 备份一般采取全库备份加日志备份的方式，例如每天执行一次全备份，每小时执行一次二进制日志备份。这样在 MySQL 故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间。一、mysqldump 备份结合 binlog 日志恢复1、binlog介绍mysql 的二进制日志记录着该数据库的所有增删改的操作日志，还包括了这些操作的执行时间。我们可以使用mysqlbi...
用mysqldump备份和恢复指定表的方法
09-11
用mysqldump备份和恢复指定表的方法
MySQL中基于mysqldump和二进制日志log-bin进行逻辑备份以及基于时间点的还原
weixin_33893473的博客
06-07 321
  本文出处：http://www.cnblogs.com/wy123/p/6956464.html      本文仅模拟使用mysqldump和log-bin二进制日志进行简单测试，仅作为个人学习笔记，可能离实际应用还有很大差距，仅参考。   开启MySQL的bin-log二进制日志 　　模拟还原是需要mysqldump出来的文件和log-bin，因此需要开始log-bin二进制日...
MySQL备份方案-->（利用mysqldump以及binlog二进制日志）
caobihole
09-11 258
MySQL备份方案--&gt;（利用mysqldump以及binlog二进制日志） 随着数据不断增加，而且为了兼容以后的innodb存储引擎, 所以考虑采用mysqldump全备+日志增量备份的策略。使用mysqldump对于mysql大部分mysql存储引擎比如myisam、innodb都有很好的支持。 方案一：mysqldum...
mysql bin-log日志与mysqldump备份与恢复
linuxheik的专栏
05-11 350
mysql bin-log日志与mysqldump备份与恢复 标签： mysql二进制备份 2016-05-09 22:23 825人阅读 评论(0) 收藏 举报  分类： java（31）  mysql（6）  版权声明：本文为博主原创文章，未经博主允许不得转载。 目录(?)[+] 简介： MySQL的bin
MySQL 数据库备份和恢复探讨（全量mysqldump 和 增量mysqlbinlog）
m0_37477061的博客
12-21 176
背景介绍： 在开发、测试以及生产环境中，每次修改MySQL数据库，严格上来说，都应该事先备份数据库，以便修改错误后导致数据库挂掉或者不能用，能够及时还原为修改前的数据库；另外，在实际使用中也可能因为不正确的使用MySQL数据库，导致数据库不可用。因此，做好MySQL数据库的备份和恢复具有重要的意义。 MySQL 相关信息： 【ip地址】：127.0.0.1 【用户名】：root 【密  ...
mysqldump备份还原和mysqldump导入导出语句大全详解
09-10
主要介绍了mysqldump备份还原和mysqldump导入导出语句大全详解,需要的朋友可以参考下
mysql数据库备份还原实战操作步骤-mysqldump备份还原
05-06
mysql数据库备份还原实战操作步骤-mysqldump备份还原
mysql mysqldump数据备份和增量备份
09-10
本篇文章主要讲如何使用shell实现mysql全量，增量备份，还可以按时间备份。
mysql数据库单表备份和调用mysqldump.exe进行数据库备份
07-06
介绍mysql数据库，单表备份，整库备份。
PXE自动化安装CentOS 8
李凯的博客
11-14 6663
安装前准备：关闭防火墙和SELINUX，DHCP服务器静态IP。 切勿用桥接模式 最好是NET模式而且把自动分配DHCP对勾取消。 查看防火墙和seelinux是否关闭 [root@centos8 ~]#firewall-cmd --state not running [root@centos8 ~]#getenforce Disabled 安装对应的软件包 安装syslinux的时候...
Cobbler实现centos 8系统自动化安装
李凯的博客
11-15 4846
cobbler部署 由于在centos8上面现在还没有cobbler包，所以部署就在centos7上面进行部署 DHCP服务器： centos7 192.168.32.7 TFTP服务器： centos7 192.168.32.7 HTTP服务器： centos7 192.168.32.7 COBBLER服务器 centos7 192.168.32.7 cobbler部署前提 关闭防火墙...
DNS服务架构
李凯的博客
11-16 2925
实验目的： 环境要求: 需要8台主机 DNS客户端：192.168.32.6/24 本地DNS服务器（只缓存）：192.168.32.8/24 转发目标DNS服务器：192.168.32.18/24 根DNS服务器：192.168.32.28/24 org域DNS服务器：192.168.32.38/24 likai.org域主DNS服务器：192.168.32.48/24 likai.org域...
Linux排查服务器是否已经被入侵
李凯的博客
05-11 1645
入侵者可能会删除机器的日志信息 可以查看日志信息是否还存在或者是否被清空，相关命令示例： [root@centos8 ~]# ls -h /var/log/* /var/log/boot.log /var/log/dnf.librepo.log-20210502 /var/log/hawkey.log-20210502 /var/log/boot.log-20200828 /var/log/dnf.librepo.log-20210509 /var/lo
MariaDB 基础使用实验二———权限设置
李凯的博客
11-23 543
1.3.1 MariaDB 数据库用户介绍 数据库作为一个数据存储的软件服务程序，可以便捷高效的存储关系型数据信息，比我们平时使用的 office 表格程序效率高并易于管理。不过这个数据库程序到底谁有权限使用呢？ 我们之前学习 Linux 操作系统时，为了管理系统中资源使用的权限，Linux 系统设置了超级用户root、系统用户、普通用户等概念。和 Linux 系统相仿， MariaDB 也设置了...
ansible 自动化运维工具——ansible Ad-Hoc 使用
李凯的博客
12-05 538
CentOS7下使用ansible远程连接被管理主机 前提都是关闭防火墙以及selinux 准备阶段 准备三台纯新的 CentOS7 服务器，一台安装 ansible1 ，其他2台可以不安装ansible。分别为ansible2和ansible3. hostname ip地址 用途 ansible1 192...
Mysql（mariadb）实现主从复制（一主双从）以及简单故障排错
李凯的博客
11-28 515
一、环境介绍 LNMP【 centos8（mariadb10.3.11），centos7（mariadb 5.5.64）】 centos8（192.168.32.8） 主服务器 centos7（192.168.32.7） 从服务器1 centos7（192.168.32.77） 从服务器2 二、原理 （1）主数据库进行增删改操作后，相应操作记录的语句（比如 create dat...
Ansible 自动化运维工具——实验一
李凯的博客
12-04 316
第一节 ansible 软件安装 1.1.1 CentOS7 环境下 yum 安装 ansible 1. 准备一台纯新的 CentOS7.6 服务器，关闭selinux，清空防火墙规则 查看是否关闭selinux，清空防火墙规则。 [root@ansible1 ~]#firewall-cmd --state not running [root@ansible1 ~]#getenforce Dis...
MariaDB 基础使用实验一 ——马哥教育
李凯的博客
11-21 304
1.2.1 SQL基本概念 学习了数据库服务 MariaDB 的安装和基本配置，实现操作数据库。使用xshell客户端程序 mysql 连接了已经安装好的 MariaDB 服务。要想在数据库软件中存储和查找数据我们需要使 用 SQL (结构化查询语言) 来操作数据库，一般我们根据功能把 SQL 分为以下四类： 名称 作用 语法关键字 DQL 数据查询语言 selecte DDL 数据定义语言 c...
mysql的数据备份
最新发布
11-29
以下是两种MySQL数据备份的方法： 1.使用mysqldump命令备份数据到文件中： ```shell mysqldump -u username -p database_name > backup.sql ``` 其中，username是MySQL的用户名，database_name是要备份的数据库名，backup.sql是备份文件名。执行该命令后，会将整个数据库备份到backup.sql文件中。 2.使用MySQL的二进制日志进行备份： 首先，需要在MySQL配置文件中开启二进制日志功能。在my.cnf文件中添加以下内容： ```shell log-bin=mysql-bin ``` 然后，重启MySQL服务使配置生效。接下来，可以使用以下命令进行备份： ```shell mysqlbinlog mysql-bin.00000X > backup.sql ``` 其中，mysql-bin.00000X是二进制日志文件名，backup.sql是备份文件名。执行该命令后，会将二进制日志文件中的内容备份到backup.sql文件中。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值