Mysql学习第三课-分析二进制日志进行增量备份和还原
20160831mysqlday3
复习
1.备份的分类
2.冷备和热备的区别
概念优点缺点
冷备
热备
3.冷备的分类
1）
2）
4.备份的两大要素
1）2）
5.备份的工具
1）2）
3）
6.备份的过程
1）tar
2）lvm
3）mysqldump
7.还原的标准流程
1）tar（物理备份恢复）
2）mysqldump（逻辑备份恢复）
===========================================
备份——增量备份
12 15
------------
1.MySQL 日志分类
启动日志 排错日志/var/log/mariadb/mariadb.log排错的 log-error=
写日志二进制日志默认不打开，记录写操作ddl dcl dml备份log-bin=/var/lib/mysql-log/mastera
读日志慢查询日志默认不打开，记录读操作dql性能调优
2.如何打开二进制日志
1)configure修改配置文件/etc/my.cnf
2)log-bin=添加二进制日志存放的路径，以及二进制日志的名字log-bin=/var/lib/mysql-log/mastera
3)mkdir 创建目录
4)UGO,selinux修改权限mysql:mysql;selinux 允许
5)restart mariadb 重启服务
3.如何查看二进制日志
index日志的索引
000001日志
命令mysqlbinlogmariadb-5.5
mysqlbinlog /var/lib/mysql-log/mariadb.000001
--start-datetime=name 起始时间点
--stop-datetime=name 结束时间点
mysqlbinlog --stop-datetime="2016-08-31 11:19:12"
--start-position=pos 位置编号 唯一，增大 at
--stop-position=pos
4.数据库备份恢复模拟一：
1)全备份mysqldump -uroot -puplooking -A --single-transaction --master-data=2 --flush-logs > /tmp/mysql.11.mysql
->11:00 mysqldump db1.t1 1 2 3 4 5 6
##insert into db1.t1 values (1),(2),(3),(4),(5),(6);
2)11:00-12:00 insert 7 8 9 update 1--》10 delete 2
##insert into db1.t1 values (7),(8),(9);
##update db1.t1 set id=10 where id=1;
3)12:00 人为误操作
`##delete from db1.t1
--------------------------
>>>>>>>>恢复数据，还原的标准步骤：
1)停止服务systemctl stop mariadb
2)清空环境rm -rf /var/lib/mysql/*
3)启动服务systemctl start mariadb
4)导入全备份数据mysql < /tmp/mysql.11.sql
5)刷新授权> flush privileges;
6)测试，全备份数据是否正确> select * from db1.t1;
7)查看分析二进制日志---》得到正确的操作命令，跳过错误的mysqlbinlog /var/lib/mysql-log/master.00000X
* 自己记下来，做全备份的时候
* 请别人记，全备份文件 --master-data=2 文件的第22行
##sed -n '22p' /tmp/mysql.11.mysql
分析：
* 自己看，用眼睛找
##mysqlbinlog /var/lib/mysql-log/master.00000X
##mysqlbinlog /var/lib/mysql-log/mastera.000001 |sed 's@\/\*.*\*\/@@'|sed -n '/BEGIN/,$p'#去除注释内容
* grep 截取关键字 ，delete drop -B 显示前面的几行
##mysqlbinlog /var/lib/mysql-log/master.00000X | grep delete -B (-A -C) 5
#B关键词上N行，A下N行，C 上下个N行
8)导入增量备份通过管道导入数据库
mysqlbinlog --stop-datetime='2016-08-31 11:19:12' /var/lib/mysql-log/mastera.000002 | mysql -uroot -puplooking
9)测试，查看增量备份数据是否正确
10)全备份mysqldump -uroot -puplooking -A --single-transaction --master-data=2 --flush-logs > /tmp/mysql.12.mysql
5.数据库备份恢复模拟二：
1)12:00 mysqldump db1.t1 3 4 5 6 7 8 9 10
2)12:00-14:00 正常的写：insert into db1.t1 values (11),(12);
人为误操作 delete from db1.t1;
正常的写：
Create database db2;
Create table db2.t1 (id int);
人为误操作
Drop table db2.t1;
3)14:00 恢复
>>>>>>>>恢复数据，还原的标准步骤：
1)停止服务systemctl stop mariadb
2)清空环境rm -rf /var/lib/mysql/*
3)启动服务systemctl start mariadb
4)导入全备份数据mysql < /tmp/mysql.11.sql
5)刷新授权> flush privileges;
6)测试，全备份数据是否正确> select * from db1.t1;
7)查看分析二进制日志---》得到正确的操作命令，跳过错误的mysqlbinlog /var/lib/mysql-log/master.00000X
* 自己记下来，做全备份的时候
* 请别人记，全备份文件 --master-data=2 文件的第22行
##sed -n '22p' /tmp/mysql.11.mysql
分析：
* 自己看，用眼睛找
##mysqlbinlog /var/lib/mysql-log/master.00000X
##mysqlbinlog /var/lib/mysql-log/mastera.000001 |sed 's@\/\*.*\*\/@@'|sed -n '/BEGIN/,$p'#去除注释内容
* grep 截取关键字 ，delete drop -B 显示前面的几行
##mysqlbinlog /var/lib/mysql-log/master.00000X | grep delete -B (-A -C) 5
#B关键词上N行，A下N行，C 上下个N行
导入增量备份的数据
start开头
stop2016-08-31 13:45:02
##mysqlbinlog --stop-datetime='2016-08-31 17:47:16' /var/lib/mysql-log/mastera.000008 | mysql -uroot -puplooking
start2016-08-31 13:45:10
stop2016-08-31 13:45:42
##mysqlbinlog --start-datetime='2016-08-31 17:48:10' --stop-datetime='2016-08-31 17:49:12' /var/lib/mysql-log/mastera.000008 | mysql -uroot -puplooking
全备份:mysqldump -uroot -puplooking -A --single-transaction --master-data=2 --flush-logs > /tmp/mysql.14.mysql
5.数据库备份恢复模拟三：
1)14:00 mysqldump db1.t1 3 4 5 6 7 8 9 10 11 12 db2.t1
2)14:00-16:00
写入脚本:vim mysql.test.mysql
-----------------------------------------------
insert into db1.t1 values (13),(14);
delete from db1.t1;
insert into db2.t1 values (1),(2),(3);
delete from db2.t1;
insert into db2.t1 values (4),(5);
------------------------------------------------
mysql -uroot -puplooking < mysql.test.mysql#在数据库执行脚本
3)16:00 恢复
>>>>>>>>恢复数据，还原的标准步骤：
1)停止服务systemctl stop mariadb
2)清空环境rm -rf /var/lib/mysql/*
3)启动服务systemctl start mariadb
4)导入全备份数据mysql < /tmp/mysql.11.sql
5)刷新授权> flush privileges;
6)测试，全备份数据是否正确> select * from db1.t1;
7)查看分析二进制日志---》得到正确的操作命令，跳过错误的mysqlbinlog /var/lib/mysql-log/master.00000X
* 自己记下来，做全备份的时候
* 请别人记，全备份文件 --master-data=2 文件的第22行
##sed -n '22p' /tmp/mysql.11.mysql
分析：
* 自己看，用眼睛找
##mysqlbinlog /var/lib/mysql-log/master.00000X
##mysqlbinlog /var/lib/mysql-log/mastera.000001 |sed 's@\/\*.*\*\/@@'|sed -n '/BEGIN/,$p'#去除注释内容
* grep 截取关键字 ，delete drop -B 显示前面的几行
##mysqlbinlog /var/lib/mysql-log/master.00000X | grep delete -B (-A -C) 5
#B关键词上N行，A下N行，C 上下个N行
导入增量备份的数据
start at 245at 598 at 953
stop at 430at 785at 1136
##mysqlbinlog --start-position=245 --stop-position=430 /var/lib/mysql-log/mastera.0000012 | mysql -uroot -puplooking
##mysqlbinlog --start-position=598 --stop-position=785 /var/lib/mysql-log/mastera.0000012 | mysql -uroot -puplooking
##mysqlbinlog --start-position=953 --stop-position=1136 /var/lib/mysql-log/mastera.0000012 | mysql -uroot -puplooking
全备份:mysqldump -uroot -puplooking -A --single-transaction --master-data=2 --flush-logs > /tmp/mysql.16.mysql
============================
详细步骤
# 打开二进制日志 open binlog
[root@mastera0 ~]# setenforce 0
[root@mastera0 ~]# getenforce
Permissive
[root@mastera0 ~]# vim /etc/my.cnf
[root@mastera0 ~]# mkdir /var/lib/mysql-log
[root@mastera0 ~]# chown mysql. /var/lib/mysql-log
[root@mastera0 ~]# ll -d /var/lib/mysql-log
drwxr-xr-x. 2 mysql mysql 6 Aug 31 10:33 /var/lib/mysql-log
[root@mastera0 ~]# systemctl start mariadb
[root@mastera0 ~]# cd /var/lib/mysql-log
[root@mastera0 mysql-log]# ll
total 8
-rw-rw----. 1 mysql mysql 245 Aug 31 10:35 mastera.000001
-rw-rw----. 1 mysql mysql 34 Aug 31 10:35 mastera.index
# 执行写操作ddl dcl dml
[root@mastera0 ~]# mysql -uroot -puplooking
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from db1.t1;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)
MariaDB [(none)]> insert into db1.t1 values (3),(4);
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [(none)]> select * from db1.t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)
MariaDB [(none)]> insert into db1.t1 values (5),(6);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [(none)]> select * from db1.t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+----+
6 rows in set (0.00 sec)
# 查看二进制日志mysqlbinlog
[root@mastera0 mysql-log]# mysqlbinlog /var/lib/mysql-log/mastera.000001 |sed 's@\/\*.*\*\/@@'|sed -n '/BEGIN/,$p'
BEGIN
;
# at 309
#160831 10:44:02 server id 1 end_log_pos 401 Querythread_id=2exec_time=0error_code=0
SET TIMESTAMP=1472611442;
insert into db1.t1 values (3),(4)
;
# at 401
#160831 10:44:02 server id 1 end_log_pos 428 Xid = 5
COMMIT;
# at 428
#160831 10:44:29 server id 1 end_log_pos 492 Querythread_id=2exec_time=0error_code=0
SET TIMESTAMP=1472611469;
BEGIN
;
# at 492
#160831 10:44:29 server id 1 end_log_pos 584 Querythread_id=2exec_time=0error_code=0
SET TIMESTAMP=1472611469;
insert into db1.t1 values (5),(6)
;
# at 584
#160831 10:44:29 server id 1 end_log_pos 611 Xid = 7
COMMIT;
DELIMITER ;
# End of log file
ROLLBACK ;
;
;
[root@mastera0 mysql-log]# mysqlbinlog /var/lib/mysql-log/mastera.000001 |sed 's@\/\*.*\*\/@@'|sed -n '/BEGIN/,$p'|sed -n '/^[^#]/p'
BEGIN
;
SET TIMESTAMP=1472611442;
insert into db1.t1 values (3),(4)
;
COMMIT;
SET TIMESTAMP=1472611469;
BEGIN
;
SET TIMESTAMP=1472611469;
insert into db1.t1 values (5),(6)
;
COMMIT;
DELIMITER ;
ROLLBACK ;
;
;
---------------------------------------
# 数据库备份恢复演习1
## 模拟场景
### 全备份
[root@mastera0 ~]# mysqldump -uroot -puplooking -A --single-transaction --master-data=2 --flush-logs > /tmp/mysql.11.mysql
### 模拟正确操作和错误操作
MariaDB [(none)]> insert into db1.t1 values (7),(8),(9);
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [(none)]> update db1.t1 set id=10 where id=1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [(none)]> delete from db1.t1 where id=2;
Query OK, 1 row affected (0.04 sec)
MariaDB [(none)]> select * from db1.t1;
+----+
| id |
+----+
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+----+
8 rows in set (0.00 sec)
MariaDB [(none)]> delete from db1.t1;
Query OK, 8 rows affected (0.04 sec)
MariaDB [(none)]> select * from db1.t1;
Empty set (0.00 sec)
MariaDB [(none)]> \q
## 开始恢复数据
[root@mastera0 ~]# mysql -uroot -puplooking
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from db1.t1;
+----+
| id |
+----+
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
+----+
10 rows in set (0.01 sec)
MariaDB [(none)]> desc db2.t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> select * from db2.t1;
Empty set (0.00 sec)
MariaDB [(none)]> \q
Bye
## 全备份
[root@mastera0 ~]# mysqldump -uroot -puplooking -A --single-transaction --master-data=2 --flush-logs > /tmp/mysql.12.mysql
---------------------------------------
# 数据库备份恢复演习2
## 模拟场景
[root@mastera0 ~]# mysql -uroot -puplooking
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> insert into db1.t1 values (11),(12);
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [(none)]> delete from db1.t1;
Query OK, 10 rows affected (0.03 sec)
MariaDB [(none)]> create database db2;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create table db2.t1 (id int primary key);
Query OK, 0 rows affected (0.05 sec)
MariaDB [(none)]> drop table db2.t1;
Query OK, 0 rows affected (0.03 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> select * from db1.t1;
Empty set (0.00 sec)
MariaDB [(none)]> select * from db2.t1;
ERROR 1146 (42S02): Table 'db2.t1' doesn't exist
MariaDB [(none)]> \q
Bye
## 数据还原
[root@mastera0 ~]# systemctl stop mariadb
[root@mastera0 ~]# rm -rf /var/lib/mysql/*
[root@mastera0 ~]# systemctl start mariadb
[root@mastera0 ~]# mysql < /tmp/mysql.12.mysql
[root@mastera0 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> \q
Bye
[root@mastera0 ~]# mysql -uroot -puplooking
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from t1;
ERROR 1046 (3D000): No database selected
MariaDB [(none)]> select * from db1.t1;
+----+
| id |
+----+
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+----+
8 rows in set (0.00 sec)
MariaDB [(none)]> \q
Bye
### 增量备份还原
[root@mastera0 mysql-log]# mysqlbinlog --stop-datetime='2016-08-31 13:45:02' /var/lib/mysql-log/mastera.000006|mysql -uroot -puplooking
[root@mastera0 mysql-log]# mysqlbinlog --start-datetime='2016-08-31 13:45:10' --stop-datetime='2016-08-31 13:45:42' /var/lib/mysql-log/mastera.000006|mysql -uroot -puplooking
### 检测
[root@mastera0 mysql-log]# mysql -uroot -puplookingWelcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, 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 |
+--------------------+
| information_schema |
| db1 |
| db2 |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> select * from db1.t1;
+----+
| id |
+----+
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
+----+
10 rows in set (0.00 sec)
MariaDB [(none)]> use db2;
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 [db2]> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
MariaDB [db2]> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)
MariaDB [db2]> show table status\G;
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 10485760
Auto_increment: NULL
Create_time: 2016-08-31 14:56:22
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR: No query specified
MariaDB [db2]> exit
Bye
[root@mastera0 mysql-log]# mysqldump -uroot -puplooking -A --master-data=2 --flush-logs > /tmp/mysql.14.mysql
----------------------------------
# 数据库备份恢复演习3
## 模拟场景
[root@mastera0 mysql-log]# vim /tmp/mysql.test.sql
[root@mastera0 mysql-log]# cat /tmp/mysql.test.sql
insert into db1.t1 values (13),(14);
delete from db1.t1;
insert into db2.t1 values (1),(2),(3);
delete from db2.t1;
insert into db2.t1 values (4),(5);
[root@mastera0 mysql-log]# mysql -uroot -puplooking < /tmp/mysql.test.sql
[root@mastera0 mysql-log]# mysql -uroot -puplooking
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from db1.t1;
Empty set (0.00 sec)
MariaDB [(none)]> select * from db2.t1;
+----+
| id |
+----+
| 4 |
| 5 |
+----+
2 rows in set (0.00 sec)
MariaDB [(none)]> \q
Bye
## 数据还原
[root@mastera0 ~]# systemctl stop mariadb
[root@mastera0 ~]# rm -rf /var/lib/mysql/*
[root@mastera0 ~]# systemctl start mariadb
[root@mastera0 ~]# mysql < /tmp/mysql.14.mysql
[root@mastera0 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> \q
Bye
[root@mastera0 ~]# mysql -uroot -puplooking
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from db1.t1;
+----+
| id |
+----+
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
+----+
10 rows in set (0.00 sec)
MariaDB [(none)]> select * from db2.t1;
Empty set (0.00 sec)
MariaDB [(none)]> \q
Bye
[root@mastera0 ~]# sed -n '22p' /tmp/mysql.14.mysql
-- CHANGE MASTER TO MASTER_LOG_FILE='mastera.000015', MASTER_LOG_POS=245;
[root@mastera0 ~]# mysqlbinlog /var/lib/mysql-log/mastera.000015
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160831 15:06:16 server id 1 end_log_pos 245 Start: binlog v 4, server v 5.5.44-MariaDB-log created 160831 15:06:16
BINLOG '
6IHGVw8BAAAA8QAAAPUAAAAAAAQANS41LjQ0LU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAKeZqQ==
'/*!*/;
# at 245
#160831 15:09:29 server id 1 end_log_pos 309 Querythread_id=11exec_time=0error_code=0
SET TIMESTAMP=1472627369/*!*/;
SET @@session.pseudo_thread_id=11/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 309
#160831 15:09:29 server id 1 end_log_pos 403 Querythread_id=11exec_time=0error_code=0
SET TIMESTAMP=1472627369/*!*/;
insert into db1.t1 values (13),(14)
/*!*/;
# at 403
#160831 15:09:29 server id 1 end_log_pos 430 Xid = 646
COMMIT/*!*/;
# at 430
#160831 15:09:29 server id 1 end_log_pos 494 Querythread_id=11exec_time=0error_code=0
SET TIMESTAMP=1472627369/*!*/;
BEGIN
/*!*/;
# at 494
#160831 15:09:29 server id 1 end_log_pos 571 Querythread_id=11exec_time=0error_code=0
SET TIMESTAMP=1472627369/*!*/;
delete from db1.t1
/*!*/;
# at 571
#160831 15:09:29 server id 1 end_log_pos 598 Xid = 647
COMMIT/*!*/;
# at 598
#160831 15:09:29 server id 1 end_log_pos 662 Querythread_id=11exec_time=0error_code=0
SET TIMESTAMP=1472627369/*!*/;
BEGIN
/*!*/;
# at 662
#160831 15:09:29 server id 1 end_log_pos 758 Querythread_id=11exec_time=0error_code=0
SET TIMESTAMP=1472627369/*!*/;
insert into db2.t1 values (1),(2),(3)
/*!*/;
# at 758
#160831 15:09:29 server id 1 end_log_pos 785 Xid = 648
COMMIT/*!*/;
# at 785
#160831 15:09:29 server id 1 end_log_pos 849 Querythread_id=11exec_time=0error_code=0
SET TIMESTAMP=1472627369/*!*/;
BEGIN
/*!*/;
# at 849
#160831 15:09:29 server id 1 end_log_pos 926 Querythread_id=11exec_time=0error_code=0
SET TIMESTAMP=1472627369/*!*/;
delete from db2.t1
/*!*/;
# at 926
#160831 15:09:29 server id 1 end_log_pos 953 Xid = 649
COMMIT/*!*/;
# at 953
#160831 15:09:29 server id 1 end_log_pos 1017 Querythread_id=11exec_time=0error_code=0
SET TIMESTAMP=1472627369/*!*/;
BEGIN
/*!*/;
# at 1017
#160831 15:09:29 server id 1 end_log_pos 1109 Querythread_id=11exec_time=0error_code=0
SET TIMESTAMP=1472627369/*!*/;
insert into db2.t1 values (4),(5)
/*!*/;
# at 1109
#160831 15:09:29 server id 1 end_log_pos 1136 Xid = 650
COMMIT/*!*/;
# at 1136
#160831 15:10:32 server id 1 end_log_pos 1155 Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@mastera0 ~]#
[root@mastera0 ~]# mysqlbinlog --start-position=245 --stop-position=430 /var/lib/mysql-log/mastera.000015|mysql -uroot -puplooking
[root@mastera0 ~]# mysqlbinlog --start-position=598 --stop-position=785 /var/lib/mysql-log/mastera.000015|mysql -uroot -puplooking
[root@mastera0 ~]# mysqlbinlog --start-position=953 --stop-position=1136 /var/lib/mysql-log/mastera.000015|mysql -uroot -puplooking
[root@mastera0 ~]# mysql -uroot -puplooking
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from db1.t1;
+----+
| id |
+----+
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
+----+
12 rows in set (0.00 sec)
MariaDB [(none)]> select * from db2.t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+
5 rows in set (0.00 sec)
MariaDB [(none)]> \q
[root@mastera0 mysql-log]# vim /tmp/mysql.test.sql
[root@mastera0 mysql-log]# cat /tmp/mysql.test.sql
insert into db1.t1 values (13),(14);
delete from db1.t1;
insert into db2.t1 values (1),(2),(3);
delete from db2.t1;
insert into db2.t1 values (4),(5);
[root@mastera0 mysql-log]# mysql -uroot -puplooking < /tmp/mysql.test.sql
[root@mastera0 mysql-log]# mysql -uroot -puplooking
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from db1.t1;
Empty set (0.00 sec)
MariaDB [(none)]> select * from db2.t1;
+----+
| id |
+----+
| 4 |
| 5 |
+----+
2 rows in set (0.00 sec)
MariaDB [(none)]> \q
Bye
[root@mastera0 mysql-log]# mysqldump -uroot -puplooking -A --master-data=2 --flush-logs > /tmp/mysql.16.mysql
===================================
晚自习作业
1.重置mastera
2.安装mariadb-server5.5，并启动服务
3.完成3个备份恢复演习，流程在http://classroom.example.com/content/MYSQL/mysqlbinlog.pdf
4.尝试通过shell脚本来完成安装数据库
#!/bin/bash
if ! rmp -q mariadb &> /dev/null
then
yum -y install mariadb-server &>/dev/null
systemctl stop firewalld
systemctl start mariadb
mysqladmin -uroot password 'uplooking'
fi
~
5.尝试通过shell脚本来备份数据库
#!/bin/bash
systemctl stop mariadb
tar -cf /tmp/mysql.all.tar /var/lib/mysql &>/dev/null
systemctl start mariadb
------------------------------
#
复习
1.备份的分类
2.冷备和热备的区别
概念优点缺点
冷备
热备
3.冷备的分类
1）
2）
4.备份的两大要素
1）2）
5.备份的工具
1）2）
3）
6.备份的过程
1）tar
2）lvm
3）mysqldump
7.还原的标准流程
1）tar（物理备份恢复）
2）mysqldump（逻辑备份恢复）
===========================================
备份——增量备份
12 15
------------
1.MySQL 日志分类
启动日志 排错日志/var/log/mariadb/mariadb.log排错的 log-error=
写日志二进制日志默认不打开，记录写操作ddl dcl dml备份log-bin=/var/lib/mysql-log/mastera
读日志慢查询日志默认不打开，记录读操作dql性能调优
2.如何打开二进制日志
1)configure修改配置文件/etc/my.cnf
2)log-bin=添加二进制日志存放的路径，以及二进制日志的名字log-bin=/var/lib/mysql-log/mastera
3)mkdir 创建目录
4)UGO,selinux修改权限mysql:mysql;selinux 允许
5)restart mariadb 重启服务
3.如何查看二进制日志
index日志的索引
000001日志
命令mysqlbinlogmariadb-5.5
mysqlbinlog /var/lib/mysql-log/mariadb.000001
--start-datetime=name 起始时间点
--stop-datetime=name 结束时间点
mysqlbinlog --stop-datetime="2016-08-31 11:19:12"
--start-position=pos 位置编号 唯一，增大 at
--stop-position=pos
4.数据库备份恢复模拟一：
1)全备份mysqldump -uroot -puplooking -A --single-transaction --master-data=2 --flush-logs > /tmp/mysql.11.mysql
->11:00 mysqldump db1.t1 1 2 3 4 5 6
##insert into db1.t1 values (1),(2),(3),(4),(5),(6);
2)11:00-12:00 insert 7 8 9 update 1--》10 delete 2
##insert into db1.t1 values (7),(8),(9);
##update db1.t1 set id=10 where id=1;
3)12:00 人为误操作
`##delete from db1.t1
--------------------------
>>>>>>>>恢复数据，还原的标准步骤：
1)停止服务systemctl stop mariadb
2)清空环境rm -rf /var/lib/mysql/*
3)启动服务systemctl start mariadb
4)导入全备份数据mysql < /tmp/mysql.11.sql
5)刷新授权> flush privileges;
6)测试，全备份数据是否正确> select * from db1.t1;
7)查看分析二进制日志---》得到正确的操作命令，跳过错误的mysqlbinlog /var/lib/mysql-log/master.00000X
* 自己记下来，做全备份的时候
* 请别人记，全备份文件 --master-data=2 文件的第22行
##sed -n '22p' /tmp/mysql.11.mysql
分析：
* 自己看，用眼睛找
##mysqlbinlog /var/lib/mysql-log/master.00000X
##mysqlbinlog /var/lib/mysql-log/mastera.000001 |sed 's@\/\*.*\*\/@@'|sed -n '/BEGIN/,$p'#去除注释内容
* grep 截取关键字 ，delete drop -B 显示前面的几行
##mysqlbinlog /var/lib/mysql-log/master.00000X | grep delete -B (-A -C) 5
#B关键词上N行，A下N行，C 上下个N行
8)导入增量备份通过管道导入数据库
mysqlbinlog --stop-datetime='2016-08-31 11:19:12' /var/lib/mysql-log/mastera.000002 | mysql -uroot -puplooking
9)测试，查看增量备份数据是否正确
10)全备份mysqldump -uroot -puplooking -A --single-transaction --master-data=2 --flush-logs > /tmp/mysql.12.mysql
5.数据库备份恢复模拟二：
1)12:00 mysqldump db1.t1 3 4 5 6 7 8 9 10
2)12:00-14:00 正常的写：insert into db1.t1 values (11),(12);
人为误操作 delete from db1.t1;
正常的写：
Create database db2;
Create table db2.t1 (id int);
人为误操作
Drop table db2.t1;
3)14:00 恢复
>>>>>>>>恢复数据，还原的标准步骤：
1)停止服务systemctl stop mariadb
2)清空环境rm -rf /var/lib/mysql/*
3)启动服务systemctl start mariadb
4)导入全备份数据mysql < /tmp/mysql.11.sql
5)刷新授权> flush privileges;
6)测试，全备份数据是否正确> select * from db1.t1;
7)查看分析二进制日志---》得到正确的操作命令，跳过错误的mysqlbinlog /var/lib/mysql-log/master.00000X
* 自己记下来，做全备份的时候
* 请别人记，全备份文件 --master-data=2 文件的第22行
##sed -n '22p' /tmp/mysql.11.mysql
分析：
* 自己看，用眼睛找
##mysqlbinlog /var/lib/mysql-log/master.00000X
##mysqlbinlog /var/lib/mysql-log/mastera.000001 |sed 's@\/\*.*\*\/@@'|sed -n '/BEGIN/,$p'#去除注释内容
* grep 截取关键字 ，delete drop -B 显示前面的几行
##mysqlbinlog /var/lib/mysql-log/master.00000X | grep delete -B (-A -C) 5
#B关键词上N行，A下N行，C 上下个N行
导入增量备份的数据
start开头
stop2016-08-31 13:45:02
##mysqlbinlog --stop-datetime='2016-08-31 17:47:16' /var/lib/mysql-log/mastera.000008 | mysql -uroot -puplooking
start2016-08-31 13:45:10
stop2016-08-31 13:45:42
##mysqlbinlog --start-datetime='2016-08-31 17:48:10' --stop-datetime='2016-08-31 17:49:12' /var/lib/mysql-log/mastera.000008 | mysql -uroot -puplooking
全备份:mysqldump -uroot -puplooking -A --single-transaction --master-data=2 --flush-logs > /tmp/mysql.14.mysql
5.数据库备份恢复模拟三：
1)14:00 mysqldump db1.t1 3 4 5 6 7 8 9 10 11 12 db2.t1
2)14:00-16:00
写入脚本:vim mysql.test.mysql
-----------------------------------------------
insert into db1.t1 values (13),(14);
delete from db1.t1;
insert into db2.t1 values (1),(2),(3);
delete from db2.t1;
insert into db2.t1 values (4),(5);
------------------------------------------------
mysql -uroot -puplooking < mysql.test.mysql#在数据库执行脚本
3)16:00 恢复
>>>>>>>>恢复数据，还原的标准步骤：
1)停止服务systemctl stop mariadb
2)清空环境rm -rf /var/lib/mysql/*
3)启动服务systemctl start mariadb
4)导入全备份数据mysql < /tmp/mysql.11.sql
5)刷新授权> flush privileges;
6)测试，全备份数据是否正确> select * from db1.t1;
7)查看分析二进制日志---》得到正确的操作命令，跳过错误的mysqlbinlog /var/lib/mysql-log/master.00000X
* 自己记下来，做全备份的时候
* 请别人记，全备份文件 --master-data=2 文件的第22行
##sed -n '22p' /tmp/mysql.11.mysql
分析：
* 自己看，用眼睛找
##mysqlbinlog /var/lib/mysql-log/master.00000X
##mysqlbinlog /var/lib/mysql-log/mastera.000001 |sed 's@\/\*.*\*\/@@'|sed -n '/BEGIN/,$p'#去除注释内容
* grep 截取关键字 ，delete drop -B 显示前面的几行
##mysqlbinlog /var/lib/mysql-log/master.00000X | grep delete -B (-A -C) 5
#B关键词上N行，A下N行，C 上下个N行
导入增量备份的数据
start at 245at 598 at 953
stop at 430at 785at 1136
##mysqlbinlog --start-position=245 --stop-position=430 /var/lib/mysql-log/mastera.0000012 | mysql -uroot -puplooking
##mysqlbinlog --start-position=598 --stop-position=785 /var/lib/mysql-log/mastera.0000012 | mysql -uroot -puplooking
##mysqlbinlog --start-position=953 --stop-position=1136 /var/lib/mysql-log/mastera.0000012 | mysql -uroot -puplooking
全备份:mysqldump -uroot -puplooking -A --single-transaction --master-data=2 --flush-logs > /tmp/mysql.16.mysql
============================
详细步骤
# 打开二进制日志 open binlog
[root@mastera0 ~]# setenforce 0
[root@mastera0 ~]# getenforce
Permissive
[root@mastera0 ~]# vim /etc/my.cnf
[root@mastera0 ~]# mkdir /var/lib/mysql-log
[root@mastera0 ~]# chown mysql. /var/lib/mysql-log
[root@mastera0 ~]# ll -d /var/lib/mysql-log
drwxr-xr-x. 2 mysql mysql 6 Aug 31 10:33 /var/lib/mysql-log
[root@mastera0 ~]# systemctl start mariadb
[root@mastera0 ~]# cd /var/lib/mysql-log
[root@mastera0 mysql-log]# ll
total 8
-rw-rw----. 1 mysql mysql 245 Aug 31 10:35 mastera.000001
-rw-rw----. 1 mysql mysql 34 Aug 31 10:35 mastera.index
# 执行写操作ddl dcl dml
[root@mastera0 ~]# mysql -uroot -puplooking
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from db1.t1;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)
MariaDB [(none)]> insert into db1.t1 values (3),(4);
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [(none)]> select * from db1.t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)
MariaDB [(none)]> insert into db1.t1 values (5),(6);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [(none)]> select * from db1.t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+----+
6 rows in set (0.00 sec)
# 查看二进制日志mysqlbinlog
[root@mastera0 mysql-log]# mysqlbinlog /var/lib/mysql-log/mastera.000001 |sed 's@\/\*.*\*\/@@'|sed -n '/BEGIN/,$p'
BEGIN
;
# at 309
#160831 10:44:02 server id 1 end_log_pos 401 Querythread_id=2exec_time=0error_code=0
SET TIMESTAMP=1472611442;
insert into db1.t1 values (3),(4)
;
# at 401
#160831 10:44:02 server id 1 end_log_pos 428 Xid = 5
COMMIT;
# at 428
#160831 10:44:29 server id 1 end_log_pos 492 Querythread_id=2exec_time=0error_code=0
SET TIMESTAMP=1472611469;
BEGIN
;
# at 492
#160831 10:44:29 server id 1 end_log_pos 584 Querythread_id=2exec_time=0error_code=0
SET TIMESTAMP=1472611469;
insert into db1.t1 values (5),(6)
;
# at 584
#160831 10:44:29 server id 1 end_log_pos 611 Xid = 7
COMMIT;
DELIMITER ;
# End of log file
ROLLBACK ;
;
;
[root@mastera0 mysql-log]# mysqlbinlog /var/lib/mysql-log/mastera.000001 |sed 's@\/\*.*\*\/@@'|sed -n '/BEGIN/,$p'|sed -n '/^[^#]/p'
BEGIN
;
SET TIMESTAMP=1472611442;
insert into db1.t1 values (3),(4)
;
COMMIT;
SET TIMESTAMP=1472611469;
BEGIN
;
SET TIMESTAMP=1472611469;
insert into db1.t1 values (5),(6)
;
COMMIT;
DELIMITER ;
ROLLBACK ;
;
;
---------------------------------------
# 数据库备份恢复演习1
## 模拟场景
### 全备份
[root@mastera0 ~]# mysqldump -uroot -puplooking -A --single-transaction --master-data=2 --flush-logs > /tmp/mysql.11.mysql
### 模拟正确操作和错误操作
MariaDB [(none)]> insert into db1.t1 values (7),(8),(9);
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [(none)]> update db1.t1 set id=10 where id=1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [(none)]> delete from db1.t1 where id=2;
Query OK, 1 row affected (0.04 sec)
MariaDB [(none)]> select * from db1.t1;
+----+
| id |
+----+
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+----+
8 rows in set (0.00 sec)
MariaDB [(none)]> delete from db1.t1;
Query OK, 8 rows affected (0.04 sec)
MariaDB [(none)]> select * from db1.t1;
Empty set (0.00 sec)
MariaDB [(none)]> \q
## 开始恢复数据
[root@mastera0 ~]# mysql -uroot -puplooking
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from db1.t1;
+----+
| id |
+----+
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
+----+
10 rows in set (0.01 sec)
MariaDB [(none)]> desc db2.t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> select * from db2.t1;
Empty set (0.00 sec)
MariaDB [(none)]> \q
Bye
## 全备份
[root@mastera0 ~]# mysqldump -uroot -puplooking -A --single-transaction --master-data=2 --flush-logs > /tmp/mysql.12.mysql
---------------------------------------
# 数据库备份恢复演习2
## 模拟场景
[root@mastera0 ~]# mysql -uroot -puplooking
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> insert into db1.t1 values (11),(12);
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [(none)]> delete from db1.t1;
Query OK, 10 rows affected (0.03 sec)
MariaDB [(none)]> create database db2;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create table db2.t1 (id int primary key);
Query OK, 0 rows affected (0.05 sec)
MariaDB [(none)]> drop table db2.t1;
Query OK, 0 rows affected (0.03 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> select * from db1.t1;
Empty set (0.00 sec)
MariaDB [(none)]> select * from db2.t1;
ERROR 1146 (42S02): Table 'db2.t1' doesn't exist
MariaDB [(none)]> \q
Bye
## 数据还原
[root@mastera0 ~]# systemctl stop mariadb
[root@mastera0 ~]# rm -rf /var/lib/mysql/*
[root@mastera0 ~]# systemctl start mariadb
[root@mastera0 ~]# mysql < /tmp/mysql.12.mysql
[root@mastera0 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> \q
Bye
[root@mastera0 ~]# mysql -uroot -puplooking
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from t1;
ERROR 1046 (3D000): No database selected
MariaDB [(none)]> select * from db1.t1;
+----+
| id |
+----+
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+----+
8 rows in set (0.00 sec)
MariaDB [(none)]> \q
Bye
### 增量备份还原
[root@mastera0 mysql-log]# mysqlbinlog --stop-datetime='2016-08-31 13:45:02' /var/lib/mysql-log/mastera.000006|mysql -uroot -puplooking
[root@mastera0 mysql-log]# mysqlbinlog --start-datetime='2016-08-31 13:45:10' --stop-datetime='2016-08-31 13:45:42' /var/lib/mysql-log/mastera.000006|mysql -uroot -puplooking
### 检测
[root@mastera0 mysql-log]# mysql -uroot -puplookingWelcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, 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 |
+--------------------+
| information_schema |
| db1 |
| db2 |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> select * from db1.t1;
+----+
| id |
+----+
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
+----+
10 rows in set (0.00 sec)
MariaDB [(none)]> use db2;
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 [db2]> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
MariaDB [db2]> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)
MariaDB [db2]> show table status\G;
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 10485760
Auto_increment: NULL
Create_time: 2016-08-31 14:56:22
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR: No query specified
MariaDB [db2]> exit
Bye
[root@mastera0 mysql-log]# mysqldump -uroot -puplooking -A --master-data=2 --flush-logs > /tmp/mysql.14.mysql
----------------------------------
# 数据库备份恢复演习3
## 模拟场景
[root@mastera0 mysql-log]# vim /tmp/mysql.test.sql
[root@mastera0 mysql-log]# cat /tmp/mysql.test.sql
insert into db1.t1 values (13),(14);
delete from db1.t1;
insert into db2.t1 values (1),(2),(3);
delete from db2.t1;
insert into db2.t1 values (4),(5);
[root@mastera0 mysql-log]# mysql -uroot -puplooking < /tmp/mysql.test.sql
[root@mastera0 mysql-log]# mysql -uroot -puplooking
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from db1.t1;
Empty set (0.00 sec)
MariaDB [(none)]> select * from db2.t1;
+----+
| id |
+----+
| 4 |
| 5 |
+----+
2 rows in set (0.00 sec)
MariaDB [(none)]> \q
Bye
## 数据还原
[root@mastera0 ~]# systemctl stop mariadb
[root@mastera0 ~]# rm -rf /var/lib/mysql/*
[root@mastera0 ~]# systemctl start mariadb
[root@mastera0 ~]# mysql < /tmp/mysql.14.mysql
[root@mastera0 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> \q
Bye
[root@mastera0 ~]# mysql -uroot -puplooking
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from db1.t1;
+----+
| id |
+----+
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
+----+
10 rows in set (0.00 sec)
MariaDB [(none)]> select * from db2.t1;
Empty set (0.00 sec)
MariaDB [(none)]> \q
Bye
[root@mastera0 ~]# sed -n '22p' /tmp/mysql.14.mysql
-- CHANGE MASTER TO MASTER_LOG_FILE='mastera.000015', MASTER_LOG_POS=245;
[root@mastera0 ~]# mysqlbinlog /var/lib/mysql-log/mastera.000015
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160831 15:06:16 server id 1 end_log_pos 245 Start: binlog v 4, server v 5.5.44-MariaDB-log created 160831 15:06:16
BINLOG '
6IHGVw8BAAAA8QAAAPUAAAAAAAQANS41LjQ0LU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAKeZqQ==
'/*!*/;
# at 245
#160831 15:09:29 server id 1 end_log_pos 309 Querythread_id=11exec_time=0error_code=0
SET TIMESTAMP=1472627369/*!*/;
SET @@session.pseudo_thread_id=11/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 309
#160831 15:09:29 server id 1 end_log_pos 403 Querythread_id=11exec_time=0error_code=0
SET TIMESTAMP=1472627369/*!*/;
insert into db1.t1 values (13),(14)
/*!*/;
# at 403
#160831 15:09:29 server id 1 end_log_pos 430 Xid = 646
COMMIT/*!*/;
# at 430
#160831 15:09:29 server id 1 end_log_pos 494 Querythread_id=11exec_time=0error_code=0
SET TIMESTAMP=1472627369/*!*/;
BEGIN
/*!*/;
# at 494
#160831 15:09:29 server id 1 end_log_pos 571 Querythread_id=11exec_time=0error_code=0
SET TIMESTAMP=1472627369/*!*/;
delete from db1.t1
/*!*/;
# at 571
#160831 15:09:29 server id 1 end_log_pos 598 Xid = 647
COMMIT/*!*/;
# at 598
#160831 15:09:29 server id 1 end_log_pos 662 Querythread_id=11exec_time=0error_code=0
SET TIMESTAMP=1472627369/*!*/;
BEGIN
/*!*/;
# at 662
#160831 15:09:29 server id 1 end_log_pos 758 Querythread_id=11exec_time=0error_code=0
SET TIMESTAMP=1472627369/*!*/;
insert into db2.t1 values (1),(2),(3)
/*!*/;
# at 758
#160831 15:09:29 server id 1 end_log_pos 785 Xid = 648
COMMIT/*!*/;
# at 785
#160831 15:09:29 server id 1 end_log_pos 849 Querythread_id=11exec_time=0error_code=0
SET TIMESTAMP=1472627369/*!*/;
BEGIN
/*!*/;
# at 849
#160831 15:09:29 server id 1 end_log_pos 926 Querythread_id=11exec_time=0error_code=0
SET TIMESTAMP=1472627369/*!*/;
delete from db2.t1
/*!*/;
# at 926
#160831 15:09:29 server id 1 end_log_pos 953 Xid = 649
COMMIT/*!*/;
# at 953
#160831 15:09:29 server id 1 end_log_pos 1017 Querythread_id=11exec_time=0error_code=0
SET TIMESTAMP=1472627369/*!*/;
BEGIN
/*!*/;
# at 1017
#160831 15:09:29 server id 1 end_log_pos 1109 Querythread_id=11exec_time=0error_code=0
SET TIMESTAMP=1472627369/*!*/;
insert into db2.t1 values (4),(5)
/*!*/;
# at 1109
#160831 15:09:29 server id 1 end_log_pos 1136 Xid = 650
COMMIT/*!*/;
# at 1136
#160831 15:10:32 server id 1 end_log_pos 1155 Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@mastera0 ~]#
[root@mastera0 ~]# mysqlbinlog --start-position=245 --stop-position=430 /var/lib/mysql-log/mastera.000015|mysql -uroot -puplooking
[root@mastera0 ~]# mysqlbinlog --start-position=598 --stop-position=785 /var/lib/mysql-log/mastera.000015|mysql -uroot -puplooking
[root@mastera0 ~]# mysqlbinlog --start-position=953 --stop-position=1136 /var/lib/mysql-log/mastera.000015|mysql -uroot -puplooking
[root@mastera0 ~]# mysql -uroot -puplooking
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from db1.t1;
+----+
| id |
+----+
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
+----+
12 rows in set (0.00 sec)
MariaDB [(none)]> select * from db2.t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+
5 rows in set (0.00 sec)
MariaDB [(none)]> \q
[root@mastera0 mysql-log]# vim /tmp/mysql.test.sql
[root@mastera0 mysql-log]# cat /tmp/mysql.test.sql
insert into db1.t1 values (13),(14);
delete from db1.t1;
insert into db2.t1 values (1),(2),(3);
delete from db2.t1;
insert into db2.t1 values (4),(5);
[root@mastera0 mysql-log]# mysql -uroot -puplooking < /tmp/mysql.test.sql
[root@mastera0 mysql-log]# mysql -uroot -puplooking
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from db1.t1;
Empty set (0.00 sec)
MariaDB [(none)]> select * from db2.t1;
+----+
| id |
+----+
| 4 |
| 5 |
+----+
2 rows in set (0.00 sec)
MariaDB [(none)]> \q
Bye
[root@mastera0 mysql-log]# mysqldump -uroot -puplooking -A --master-data=2 --flush-logs > /tmp/mysql.16.mysql
===================================
晚自习作业
1.重置mastera
2.安装mariadb-server5.5，并启动服务
3.完成3个备份恢复演习，流程在http://classroom.example.com/content/MYSQL/mysqlbinlog.pdf
4.尝试通过shell脚本来完成安装数据库
#!/bin/bash
if ! rmp -q mariadb &> /dev/null
then
yum -y install mariadb-server &>/dev/null
systemctl stop firewalld
systemctl start mariadb
mysqladmin -uroot password 'uplooking'
fi
~
5.尝试通过shell脚本来备份数据库
#!/bin/bash
systemctl stop mariadb
tar -cf /tmp/mysql.all.tar /var/lib/mysql &>/dev/null
systemctl start mariadb
------------------------------
#
本文来自希曼博客-www.ximan.tech，作者：希曼博客，转载请注明原文链接：https://www.cnblogs.com/lihuaichen/p/8270199.html
