大圣Blog

MySQL

数据库操作

数据表操作

Mysql列类型及列属性

MySQL常用函数

MySQL增删改

MySQL查询

JOSN的支持

MySQL流程控制语句

视图

存储过程及存储函数

变量与游标

事务

触发器

权限及安全管理

MySQL日志

二进制日志

Mysql5.7复制功能

数据备份与恢复

性能优化

课程导航

MySQL日志

Mysql5.7复制功能

二进制日志包含了引起或可能引起数据库改变(如delete语句但没有匹配行)的事件信息，但绝不会包括select和show这样的查询语句。语句以"事件"的形式保存，所以包含了时间、事件开始和结束位置等信息。

二进制日志是以事件形式记录的，不是事务日志(但可能是基于事务来记录二进制日志)，不代表它只记录innodb日志，myisam表也一样有二进制日志。

二进制日志只在事务提交的时候一次性写入(基于事务的innodb二进制日志)。

MariaDB/MySQL默认没有启动二进制日志，要启用二进制日志使用 --log-bin=[on|off|file_name] 选项指定，如果没有给定file_name，则默认为datadir下的主机名加"-bin"，并在后面跟上一串数字表示日志序列号，如果给定的日志文件中包含了后缀(logname.suffix)将忽略后缀部分。
##   开启bin-log日志功能
在mysql配置文件中加上一行
```
log-bin=mysql-bin
或者用绝对路径
log-bin=/usr/local/mariadb/var/mysql-bin
```
![图片](http://blog.1024phper.com/static/qiniu/blog19090613551581550)  
注意：二进制日志文件与数据库数据文件最好不要放在同一块硬盘上，如果存放数据文件的硬盘坏了，可以用另一块硬盘的二进制日志来恢复数据

binlog日志一般存放在/usr/local/mariadb(mysql)/var/  目录下，其中mysql-bin.index用于存储所有二进制文件清单；  
![图片](http://blog.1024phper.com/static/qiniu/blog19090613551612082)
## 查看bin-log配置信息
    show variables like '\%binlog\%';
![图片](http://blog.1024phper.com/static/qiniu/blog19090613551680554)
##     生成新的bin-log文件
命令：flush logs

![图片](http://blog.1024phper.com/static/qiniu/blog19090613551646392)
```
MariaDB [(none)]> flush logs;
Query OK, 0 rows affected (0.02 sec)

```
![图片](http://blog.1024phper.com/static/qiniu/blog19090613551620790)  
每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；
##     查看最后一个bin-log日志状态 
    命令：show master status
![图片](http://blog.1024phper.com/static/qiniu/blog19090613551687449)
##     清空所有bin-log文件
    命令：reset master
![图片](http://blog.1024phper.com/static/qiniu/blog19090613551715741)

此时，binlog文件只剩mysql-bin-000001
![图片](http://blog.1024phper.com/static/qiniu/blog19090613551752766)

## 查看bin-log日志
**方法一 使用mysqlbinlog**  
因为 bin-log文件为二进制文件，所以需要mysql自带的工具（msyqlbinlog）才能查看
```
[root@localhost ~]# mysqlbinlog /usr/local/mariadb/var/mysql-bin.000001
```
![图片](http://blog.1024phper.com/static/qiniu/blog19090613551779050)
解释：
server id 1 ： 数据库主机的服务号；  
end_log_pos 796： sql结束时的pos节点  
thread_id=11： 线程号

mysqlbinlog常见的选项有以下几个：
--start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间  
--stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样  
--start-position：从二进制日志中读取指定position 事件位置作为开始。  
      --stop-position：从二进制日志中读取指定position 事件位置作为事件截至  
       -d,--database=name：只查看指定数据库的日志操作  
```
/usr/local/mariadb/bin/mysqlbinlog --start_datetime='2018-7-6 12:00' mysql-bin.000001
/usr/local/mariadb/bin/mysqlbinlog --start_datetime='2018-7-6 12:00' --stop_datetime='2018-7-6 13:00'  mysql-bin.000001
/usr/local/mariadb/bin/mysqlbinlog  --start_position=1420 mysql-bin.000001
```
**方法二**  
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];  
参数解释：  
IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)  
FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)  
LIMIT [offset,] ：偏移量(不指定就是0)  
row_count ：查询总条数(不指定就是所有行)  
```
MariaDB [binlog_test]> show binlog events\G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 248
       Info: Server ver: 10.0.30-MariaDB, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
        Pos: 248
 Event_type: Gtid_list
  Server_id: 1
End_log_pos: 273
       Info: []
*************************** 3. row ***************************
   Log_name: mysql-bin.000001
        Pos: 273
 Event_type: Binlog_checkpoint
  Server_id: 1
End_log_pos: 312
       Info: mysql-bin.000001

MariaDB [binlog_test]> show binlog events IN 'mysql-bin.000001' LIMIT 1\G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 248
       Info: Server ver: 10.0.30-MariaDB, Binlog ver: 4
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [binlog_test]> show binlog events IN 'mysql-bin.000001' FROM 4 LIMIT 1\G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 248
       Info: Server ver: 10.0.30-MariaDB, Binlog ver: 4
1 row in set (0.00 sec)

ERROR: No query specified

```

## 使用bin-log日志来恢复数据
**导出sql文件命令**：mysqldump 数据库名字 [数据表名字1[ 数据表名字2...]] > 外部文件目录(建议使用.sql)  
**sql文件导入数据库**：mysql -u** -p**  数据库名字 < 备份文件目录

现在模拟一种场景：一数据库每晚3点定时备份,第二天网站正常运行了半天，突然在下午5点钟的时候，程序员小A不小心，DELETE 时候没有加WHERE 条件，然后其中某张表数据全没了。然后小A找到技术总监大圣，让大圣帮忙恢复数据。

```
# binlog_test 数据库只有一张user表 
# 凌晨三点时没备份前的数据如下：
+---------+----------+---------------------+
| user_id | username | add_time            |
+---------+----------+---------------------+
|       1 | gwx      | 2018-07-05 13:00:31 |
|       2 | snn      | 2018-07-05 14:00:00 |
|       3 | zy       | 2018-07-05 15:00:00 |
+---------+----------+---------------------+

# 凌晨3点到了，备份数据
[root@localhost var]# mysqldump binlog_test -l -F > /root/sql_backup/20180706.sql 
# -F flush logs 
# -l 为开始导出锁定所有表。
[root@localhost var]# ll /root/sql_backup/
总用量 4
-rw-r--r-- 1 root root 2149 7月   6 13:42 20180706.sql
=======数据备份完成=========

# 网站正常运行一段时间，有许多用户注册
MariaDB [binlog_test]> INSERT INTO `user` (username) values('user1'),('user2'),('user3');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [binlog_test]> select * from user;
+---------+----------+---------------------+
| user_id | username | add_time            |
+---------+----------+---------------------+
|       1 | gwx      | 2018-07-05 13:00:31 |
|       2 | snn      | 2018-07-05 14:00:00 |
|       3 | zy       | 2018-07-05 15:00:00 |
|       4 | user1    | 2018-07-06 15:01:18 |
|       5 | user2    | 2018-07-06 15:01:18 |
|       6 | user3    | 2018-07-06 15:01:18 |
+---------+----------+---------------------+
==============新增了3个用户user1 user2 及user3==============

# 到了下午5点钟,小A开始犯傻了
MariaDB [binlog_test]> DELETE FROM user;
Query OK, 6 rows affected (0.00 sec)
=========没where条件，数据全没了===========

# 小A找到大圣帮忙恢复数据,大圣先把昨晚凌晨三点数据给恢复了
[root@localhost var]# service nginx stop;  # 大圣先关闭了nginx，使网站用户暂时访问不了数据库
Stoping nginx...  done 
MariaDB [binlog_test]> flush logs;  #生成新的binlog日志
MariaDB [binlog_test]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |     1536 |              |                  |
+------------------+----------+--------------+------------------+

[root@localhost var]# mysql -v -f binlog_test < /root/sql_backup/20180706.sql  
# 这时候大圣已经把昨晚凌晨3点时候数据恢复了
MariaDB [binlog_test]> select * from user;
+---------+----------+---------------------+
| user_id | username | add_time            |
+---------+----------+---------------------+
|       1 | gwx      | 2018-07-05 13:00:31 |
|       2 | snn      | 2018-07-05 14:00:00 |
|       3 | zy       | 2018-07-05 15:00:00 |
+---------+----------+---------------------+

=============昨晚凌晨三点数据恢复完成===============

# 接下来恢复凌晨三点到DELETE间段数据
#首先找到delete的pos点,备份之后log是000002
#删除之后也flush logs为000003，所以只要找000002 delete之前的pos即可

```
![图片](http://blog.1024phper.com/static/qiniu/blog19090614241080345)

```
# 通过binlog恢复数据
[root@localhost var]# /usr/local/mariadb/bin/mysqlbinlog --stop-position=629  >
'mysql-bin.000002' >
| mysql binlog_test;

MariaDB [binlog_test]> select * from user;
+---------+----------+---------------------+
| user_id | username | add_time            |
+---------+----------+---------------------+
|       1 | gwx      | 2018-07-05 13:00:31 |
|       2 | snn      | 2018-07-05 14:00:00 |
|       3 | zy       | 2018-07-05 15:00:00 |
|       4 | user1    | 2018-07-06 15:01:18 |
|       5 | user2    | 2018-07-06 15:01:18 |
|       6 | user3    | 2018-07-06 15:01:18 |
+---------+----------+---------------------+
==============数据都回来了========================

```
    
## mysql主从
###    简介
MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。

下图就描述了一个多个数据库间主从复制与读写分离的模型(来源网络)：
![图片](http://blog.1024phper.com/static/qiniu/blog19090614241043552)
###    原理
Mysql主从复制的实现原理图大致如下(来源网络)：
![图片](http://blog.1024phper.com/static/qiniu/blog19090614241199517)  
MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

###    主从配置   
  * 主服务器：
    * 开启二进制日志
    * 配置唯一的server-id
    * 获得master二进制日志文件名及位置
    * 创建一个用于slave和master通信的用户账号
  * 从服务器：
    * 配置唯一的server-id
    * 使用master分配的用户账号读取master二进制日志
    * 启用slave服务

###    实践 
准备好两台服务器，ip分别为192.168.1.110（主）、192.168.1.104（从），这两台服务器都安装了MariaDB10.0.35的版本。拿binlog_test库做测试。

#### 阿里云服务器上的mysql配置文件信息：
```
datadir = /usr/local/mysql/var
 log-bin=mysql-bin
 server-id  = 1
 # binlog-do-db = binlog_test #哪些库产生二进制文件
 # binlog-ignore-db = mysql #哪些库不产生二进制文件
``` 
#### 分配账号：
```
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.104' identified by "slavepass";
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
```
主服务器上有个binlog_test库，只有一张表，里面数据如下  
```
+---------+----------+------------+
| user_id | username | add_time   |
+---------+----------+------------+
|       1 | gwx      | 2018-05-02 |
|       2 | snn      | 2018-02-03 |
|       3 | zz       | 2018-10-01 |
+---------+----------+------------+
```
#### 导入sql文件
将binlog_test导出为sql文件，给到从服务器执行该sql文件  
```
mysqldump -uroot -p --single-transaction --master-data=2 binlog_test > binlog_test.sql
```
-- single-transaction:该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。本选项和--lock-tables 选项是互斥的，因为LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用--quick 选项。  
--master-data:该选项将binlog的位置和文件名追加到输出文件中。如果为1，将会输出CHANGE MASTER 命令；如果为2，输出的CHANGE  MASTER命令前添加注释信息。该选项将打开--lock-all-tables 选项，除非--single-transaction也被指定（在这种情况下，全局读锁在开始导出时获得很短的时间；其他内容参考下面的--single-transaction选项）。该选项自动关闭--lock-tables选项。

从数据库导入了该sql，目前两个服务器binlog_test库数据是一样的。

![图片](http://blog.1024phper.com/static/qiniu/blog19090614241151168)

#### 配置从服务器
#log-bin=mysql-bin 关闭binlog日志  
server-id = 2 #不能和主服务器的server-id一样  
replicate-do-db = binlog_test  
#replicate-ignore-db  
#replicate-do-table  
#replicate-ignore-table  
#replicate-wild-do-table  
#replicate-wild-ignore-table  
```
[root@yifu2016 ~]# systemctl restart mysql
MariaDB [binlog_test]> change master to 
-> master_host='192.168.0.110',
-> master_user='repl',
-> master_password='slavepass',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=969;
Query OK, 0 rows affected (0.02 sec)

MariaDB [binlog_test]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [binlog_test]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.110
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1175
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 741
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: binlog_test
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
========注意 slave_Io_running 和 slave_sql_running 都为yes情况才，主从复制才真的成功

```
#### 测试
在主服务器中插入数据，看从服务器数据会不会也更新。

MySQL日志

Mysql5.7复制功能

MySQL

课程导航

二进制日志

文章目录