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 日志

命令 mysqlbinlog mariadb-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 开头
stop 2016-08-31 13:45:02
##mysqlbinlog --stop-datetime='2016-08-31 17:47:16' /var/lib/mysql-log/mastera.000008 | mysql -uroot -puplooking
start 2016-08-31 13:45:10
stop 2016-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 245 at 598  at 953
stop  at 430 at 785 at 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 Query thread_id=2 exec_time=0 error_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 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1472611469;
BEGIN
;
# at 492
#160831 10:44:29 server id 1  end_log_pos 584 Query thread_id=2 exec_time=0 error_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 Query thread_id=11 exec_time=0 error_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 Query thread_id=11 exec_time=0 error_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 Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1472627369/*!*/;
BEGIN
/*!*/;
# at 494
#160831 15:09:29 server id 1  end_log_pos 571 Query thread_id=11 exec_time=0 error_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 Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1472627369/*!*/;
BEGIN
/*!*/;
# at 662
#160831 15:09:29 server id 1  end_log_pos 758 Query thread_id=11 exec_time=0 error_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 Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1472627369/*!*/;
BEGIN
/*!*/;
# at 849
#160831 15:09:29 server id 1  end_log_pos 926 Query thread_id=11 exec_time=0 error_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 Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1472627369/*!*/;
BEGIN
/*!*/;
# at 1017
#160831 15:09:29 server id 1  end_log_pos 1109 Query thread_id=11 exec_time=0 error_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
------------------------------
#