个人学习

MySQL全量、增量备份与恢复

2020-01-11  本文已影响0人  SCOR_PIO

数据备份的重要性

实操
创建数据库、表、插入数据

mysqldump命令对单个库进行完全备份
添加多个数据库

[root@localhost mysql]# mysqldump -u root -p school > /opt/school.sql
Enter password: 
[root@localhost mysql]# ls /opt/
dir_SC_UTF8              mysql-5.7.20  php-7.1.10  school.sql
mysql-2020-01-07.tar.xz  nginx-1.12.2  rh          说明.htm
[root@localhost mysql]# cd /opt
[root@localhost opt]# ls school.sql 
school.sql
[root@localhost opt]# vim school.sql 
[root@localhost opt]#

mysqldump命令对多个库进行完全备份

[root@localhost opt]# mysql -uroot -p
Enter password: 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbs                |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> use school;
Database changed

mysql> drop table info;
Query OK, 1 row affected (0.01 sec)
mysql> show tables;
Empty set (0.00 sec)

mysql> source /opt/school.sql
Query OK, 0 rows affected (0.00 sec)
......
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
1 row in set (0.00 sec)

使用mysql命令恢复数据
mysql -u -p [密码] < 库备份脚本的路径

mysql> drop table info;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)
mysql> quit
Bye
[root@localhost opt]# 
[root@localhost opt]# mysql -uroot -p12341234 school < /opt/school.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost opt]# mysql -uroot -p
Enter password: 
mysql> use school;
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_school |
+------------------+
| info             |
+------------------+
1 row in set (0.00 sec)
mysql> select * from info;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | stu01 |  88.0 |
|  2 | stu02 |  77.0 |
+----+-------+-------+
2 rows in set (0.00 sec)

mysql 命令恢复举例

恢复表的操作

mysql   -u  用户名 -p  [密码]    <   表备份脚本的路径
mysql -u -p mysql < /backup/mysql-user.sql

mysql备份思路:
定期实施备份，制定备份计划或者策略，并严格遵守
除了进行完全备份，开启mysql服务器的日志功能是很重要的
完全备份加上日志，可以对mysql进行最大化还原
使用统一的和易理解的备份文件名称
不要使用backup1/2这样没有意义的名字
推荐使用库名或者表名加上时间的命名规则
备份文件名使用时间+业务名+库名
要开启服务器的日志功能

mysql增量备份
诞生增量备份的原因
解决使用mysqldump进行完全备份时的存在的问题

[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
'log-bin=mysql-bin
server-id = 1
default-storage-engine=Myisam

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES
[root@localhost opt]# systemctl restart mysqld

查看二进制日志文件

[root@localhost opt]# cd /usr/local/mysql/
[root@localhost mysql]# cd data/
[root@localhost data]# ls
auto.cnf        ibdata1      ibtmp1            mysql-bin.index     sys
bbs             ib_logfile0  mysql             performance_schema
ib_buffer_pool  ib_logfile1  'mysql-bin.000001'  school
[root@localhost data]#

做增量备份前，要先进行一次完全备份

[root@localhost data]# mysqldump -uroot -p12341234 school > /opt/school.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost data]# cd /opt
[root@localhost opt]# ls
dir_SC_UTF8              mysql-5.7.20  php-7.1.10  school.sql
mysql-2020-01-07.tar.xz  nginx-1.12.2  rh          说明.htm
[root@localhost opt]# vim school.sql

接下来做增量备份，此时，之前的操作被存放到001当中，接下来的操作会被存放到002当中

[root@localhost opt]# mysqladmin -uroot -p12341234 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost opt]# ls /usr/local/mysql/data/
auto.cnf        ibdata1      ibtmp1            mysql-bin.000002    school
bbs             ib_logfile0  mysql             mysql-bin.index     sys
ib_buffer_pool  ib_logfile1  mysql-bin.000001  performance_schema

mysql> use school;
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> select * from info;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | stu01 |  88.0 |
|  2 | stu02 |  77.0 |
+----+-------+-------+
2 rows in set (0.00 sec)

mysql> insert into info (name,score) values ('test01',66);
//这个是正常操作
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | stu01  |  88.0 |
|  2 | stu02  |  77.0 |
|  3 | test01 |  66.0 |
+----+--------+-------+
3 rows in set (0.00 sec)

mysql> delete from info where name='stu01';
//误操作
Query OK, 1 row affected (0.01 sec)

mysql> insert into info (name,score) values ('test02',99);
//正常操作
Query OK, 1 row affected (0.01 sec)

mysql> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  4 | test02 |  99.0 |
|  2 | stu02  |  77.0 |
|  3 | test01 |  66.0 |
+----+--------+-------+
3 rows in set (0.01 sec)
mysql> quit
Bye

此时在不知情的情况下，进行增量备份，此时误操作写在了002中

[root@localhost opt]# mysqladmin -uroot -p12341234 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost opt]# cd /usr/local/mysql/data/
[root@localhost data]# ls
auto.cnf        ib_logfile0  mysql-bin.000001  performance_schema
bbs             ib_logfile1  mysql-bin.000002  school
ib_buffer_pool  ibtmp1       mysql-bin.000003  sys
ibdata1         mysql        mysql-bin.index

查看日志文件：-v 显示内容在界面，–base64解码器 output输出 decode-rows 读取按行读取

[root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000002 
//查看二进制日志文件，不过可以发现看不懂
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
//这个是位置点
#200107 16:54:11 server id 1  end_log_pos 123 CRC32 0x76a9dc26  Start: binlog v 4, server v 5.7.20-log created 200107 16:54:11
//这个是时间点
BINLOG '
M0cUXg8BAAAAdwAAAHsAAAAAAAQANS43LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
//像这样的就是被加密的命令
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002  > /opt/back.txt
//-v 显示内容在界面，--base64解码器 output输出 decode-rows 读取按行读取
[root@localhost data]# cd /opt
[root@localhost opt]# vim back.txt 
//下面的截图就是bak.txt中的数据信息

错误操作截图


200107 16:57:56 --stop-datetime /指从这个日志文件开始，执行到这个时间点时就停止
200107 16:58:46 --start-datetime /指这个日志文件中，从这个时间点开始向后面执行
先完全备份恢复，source /opt/school.sql

[root@localhost opt]# mysql -uroot -p12341234
mysql> use school;
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> drop table info;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from info;
ERROR 1146 (42S02): Table 'school.info' doesn't exist
mysql> show tables;
Empty set (0.00 sec)

mysql> source /opt/school.sql;
Query OK, 0 rows affected (0.00 sec)
。。。。。。

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
1 row in set (0.00 sec)

mysql> select * from info;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | stu01 |  88.0 |
|  2 | stu02 |  77.0 |
+----+-------+-------+
2 rows in set (0.00 sec)

mysql>

然后再增量恢复，即时间上的断点恢复

[root@localhost opt]# mysqlbinlog --no-defaults --stop-datetime='2020-01-07 16:57:56' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p
Enter password: 

[root@localhost opt]# mysql -uroot -p12341234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.20-log Source distribution

Copyright (c) 2000, 2017, 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> use school;
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> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | stu01  |  88.0 |
|  2 | stu02  |  77.0 |
|  3 | test01 |  66.0 |
+----+--------+-------+
3 rows in set (0.00 sec)

mysql> quit
Bye
[root@localhost opt]# mysqlbinlog --no-defaults --start-datetime='2020-01-07 16:58:46' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p
Enter password: 

[root@localhost opt]# mysql -uroot -p12341234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.20-log Source distribution

Copyright (c) 2000, 2017, 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> use school;
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> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | stu01  |  88.0 |
|  2 | stu02  |  77.0 |
|  3 | test01 |  66.0 |
|  4 | test02 |  99.0 |
+----+--------+-------+
4 rows in set (0.00 sec)
//误操作删除的stu01 没有被删掉
mysql>

基于位置的恢复
错误操作的日志文件

[root@localhost opt]# mysqlbinlog --no-defaults --stop-postion='612' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p
mysqlbinlog: [ERROR] unknown variable 'stop-postion=612'
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@localhost opt]# mysqlbinlog --no-defaults --stop-position='612' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p
Enter password: 
[root@localhost opt]# mysql -uroot -p12341234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.20-log Source distribution

Copyright (c) 2000, 2017, 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> use school;
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> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | stu01  |  88.0 |
|  2 | stu02  |  77.0 |
|  3 | test01 |  66.0 |
+----+--------+-------+
3 rows in set (0.00 sec)

mysql> quit
Bye
[root@localhost opt]# mysqlbinlog --no-defaults --start-position='716' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p
Enter password: 
[root@localhost opt]# mysql -uroot -p12341234mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.20-log Source distribution

Copyright (c) 2000, 2017, 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> use school;
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> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | stu01  |  88.0 |
|  2 | stu02  |  77.0 |
|  4 | test02 |  99.0 |
|  3 | test01 |  66.0 |
+----+--------+-------+
4 rows in set (0.00 sec)

mysql> desc info;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(4)       | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)  | NO   |     | NULL    |                |
| score | decimal(4,1) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> quit
Bye
[root@localhost opt]# mysqlbinlog --no-defaults  /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p
//这个是增量恢复，将日志文件内的所有操作全部执行
上一篇下一篇

猜你喜欢

热点阅读