[系统运维]Mysql (2)

1. binlog 日志

MySQL的二进制日志可以说是MySQL最重要的日志了，它以事件de形式记录了所有的DDL和DML(除了数据查询语句)语句，
记录内容还包含语句所执行的消耗的时间。
binlog是 Server 层 的日志

binlog日志 主要作用：

1. 恢复数据
2. 主从复制

binlog日志 两类文件：

1. 二进制日志索引文件，文件名后缀为.index，
2. 二进制日志文件，文件名后缀为.00000*，记录数据库所有的DDL和DML

开启

配置开启

# 在配置文件中
[mysqld]
log_bin=mysql-bin
server-id=721657

# 重启mysql服务

• log_bin=mysql-bin
  mysql会根据这个配置自动设置log_bin为on状态，自动设置log_bin_index、log_bin_basename文件
• server-id=721657
  如果是5.7及以上版本 加入了很多集群相关的代码，如果没有该配置，重启mysql服务会报错。该配置 指定 当前mysql的唯一id标识，不能和其他集群中机器重名的字符串。

查看是否开启

show variables like '%log_bin%';  

+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |

| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+

binlog的三种模式

Statement （默认模式）

每一条会修改数据的sql都会记录在binlog中。

• 优点

不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。

• 缺点

slave可与master上要保持一致会有很多问题.

Row （重量模式）

不记录sql语句上下文相关信息，仅保存哪条记录被修改。

• 优点

rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。
而且不会出现某些特定情况下的存储过程，或function，
以及trigger的调用和触发无法被正确复制的问题。

• 缺点

可能会产生大量的日志内容,
比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，
特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

Mixed （自动模式， 推荐使用）

是以上两种level的混合使用。
一般的语句修改使用statment格式保存binlog，
如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,
MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式。

binlog日志详解

查看 当前 binlog 状态信息

show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |   410713 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

mysql-bin.000001 是 当前正在被mysql写入的 二进制日志文件名。
410713 这个数值 表示的就是binlog的当前位置。每次执行dml操作，position都会改变。

查看 binlog 内容

查看某个binlog日志内容，常用有两种方式

• 方式一： mysqlbinlog

文本的方式(cat、less、more) 查看 mysql-bin.000001的内容是一堆乱码，
mysql给我们提供了一个用于查看binlog日志的工具，叫做mysqlbinlog，
mysqlbinlog mysql-bin.000001

• 方式二： show binlog events 查看binlog日志

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       查询总条数(不指定就是所有行)

# 指定查询 mysql-bin.000002 这个文件，从pos点:534620开始查起，偏移2行，查询10条
show binlog events in 'mysql-bin.000002' from 534620 limit 2,10


# 实操
show binlog events in 'mysql-bin.000003'\G

*************************** 1. row ***************************
   Log_name: mysql-bin.000003          　　　　　　　　　　     ----> 查询的binlog日志文件名
        Pos: 4                 　　　　　　　　　　　　　　       ----> pos起始点:
 Event_type: Format_desc               　　　　　　　　　　      ----> 事件类型
  Server_id: 1                         　　　　　　　　　　      ----> 标识是由哪台服务器执行的
End_log_pos: 120                　　　　　　　　　　　　　      　----> pos结束点:120(即：下行的pos起始点)
       Info: use `sdf`; INSERT INTO `wtt` VALUES (1,'tom')   ---> 执行的sql语句

*************************** 2. row ***************************

binlog文件操作

• 查看当前

show master status;

• 查看全部

show master logs;

• 刷新当前

flush logs;

该操作会新建一个 日志文件，并指定为当前文件。
mysql 重启一次，会自动执行此命令，所以也会自动生成一个新的binlog文件
在mysqldump备份数据时加 -F 选项,在备份完成后 也会自动执行此命令。

• 手动删除

# 删除 指定日期 以前 的 
purge master logs before '2017-03-30 00:20:00'; 

# 删除 指定日志文件 以前 的
mysql> purge master logs to 'mysql-bin.000009';           

• 自动删除

# 自动删除 超过一周 的日志文件
set global expire_logs_days=7;

# 查看 设置的 日志有效期
show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     | # 如果是 0， 代表不要自动删除。 
+------------------+-------+

• 清空所有

reset master;

在生产环境中，这个操作是非常危险的

• 常用配置

[mysqld]
#binlog日志格式，可选值有：ROW / STATEMENT / MIXED， 推荐使用 MIXED 模式
binlog_format=MIXED 

# binlog过期清理时间
expire_logs_days=7

# binlog每个日志文件大小
max_binlog_size 100m

# 需要备份的数据库名，如果备份多个数据库，重复设置这个选项即可
binlog-do-db=wtt

# 不需要备份的数据库苦命，如果备份多个数据库，重复设置这个选项即可
binlog-ignore-db=test

数据恢复

本质： 让 binlog记录的操作 再 执行一遍。

################################################################
  mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 [数据库名]
################################################################

mysqlbinlog 常用命令参数：
        --start-position=953                    起始pos点
        --stop-position=1437                    结束pos点
        --start-datetime="2017-6-20 13:18:54"   起始时间点
        --stop-datetime="2017-6-20 13:21:53"    结束时间点
        --database=wtt                          指定只恢复 wtt 数据库


mysqlbinlog 不常用命令参数：   
        -u --user=name              连接到远程主机的用户名
        -p --password[=name]        连接到远程主机的密码
        -h --host=name              从远程主机上获取binlog日志
        --read-from-remote-server   从某个MySQL服务器上读取binlog日志


######### 使用案例
mysqlbinlog --stop-position=759 --database=wtt mysql-bin.000007 | mysql -uroot -p123456 -v wtt

mysqlbinlog --start-position=759 --stop-position=880 --database=wtt mysql-bin.000007 | mysql -uroot -p123456 wtt

mysqlbinlog --start-datetime="2017-6-20 13:18:54" --stop-datetime="2013-16-20 14:21:53" --database=wtt mysql-bin.000007 | mysql -uroot -p123456 -v juzidb

当要某个数据库被删除了，要恢复数据库 这个级别时，mysql -uroot -p123456 后面 无需带 数据库名称，
当要恢复某个数据库的 数据表or记录时， mysql -uroot -p123456 后面 需带 数据库名称，

mysql 的 -v 说明：
-V是查看版本的， -v 主要用在脚本、命令中可以用来简化代码

• 若要同时显示语句本身：-v
• 若要增加查询结果行数：-vv
• 若要增加执行时间：-vvv

上面的-v完全是就可以省略的。

2. 主从复制

简单来说，就是保证主SQL（Master）和从SQL（Slave）的数据是一致性的，
向Master插入数据后，Slave会自动从Master把修改的数据同步过来（避免不了的是会有一定的延迟），
通过这种方式来保证数据的一致性，就是主从复制。

注意：

• 主从服务器操作系统版本和位数一致,
• Master和Slave数据库的版本要一致,
  Master和Slave的 server_id 必须唯一

原理

主要基于MySQL二进制日志,主要包括三个线程（2个I/O线程，1个SQL线程）

1. MySQL将数据变化记录到二进制日志中；
2. Slave将MySQL的二进制日志拷贝到Slave的中继日志中；
3. Slave将中继日志中的事件再做一次，将数据变化，反应到自身（Slave）的数据库

• 复制过滤

只复制服务器中的一部分数据，有两种复制过滤：

• 在Master上过滤二进制日志中的事件
• 在Slave上过滤中继日志中的事件。

• 复制类型

• 基于语句的复制, 对应 binlog的 默认模式；
• 基于行的复制， 对应 binlog的 重量模式；
• 默认采用 基于语句的复制， 对应 binlog的 智能模式；

实践

配置 master

• step1: 配置文件

[mysqld]
#设置server_id，一般设置为IP,注意要唯一
server_id=100

#复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
binlog-ignore-db=mysql 

#开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin=edu-mysql-slave1-bin 

#为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M 

#日志模式
binlog_format=mixed 

#日志有效期
expire_logs_days=7 

#跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
#如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062 

#relay_lo g配置中继日志
relay_log=edu-mysql-relay-bin 

#log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1 

#防止改变数据(除了特殊的线程)
read_only=1

配置完成后重启mysql

• step2: 创建数据同步用户

CREATE USER 'slave'@'%' IDENTIFIED BY '123456';

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

flush privileges;

配置 slave

• step1: 配置文件

[mysqld]
server_id=101 
binlog-ignore-db=mysql 
log-bin=edu-mysql-slave1-bin 
binlog_cache_size=1M 
binlog_format=mixed 
expire_logs_days=7 
slave_skip_errors=1062 
relay_log=edu-mysql-relay-bin 
log_slave_updates=1 
read_only=1

建立连接

保证 带同步的数据库 中数据一致。

• step1: 查看master的日志状态

# master

show master status\G
# 记录 当前二进制日志文件的 文件名（假设为 aaa.000001） 和 Position列的 值 （假设为：721657）

• step2: Slave中设置Master信息

# slave

change master to master_host='192.168.100.70', master_user='slave', \ 
master_password='123456', master_port=3306, \
master_log_file='aaa.000001', master_log_pos=721657, \
master_connect_retry=30;


### 解释
#Master的IP地址
master_host=’192.168.100.70′
 
#用于同步数据的用户（在Master中授权的用户）
master_user=’slave’
 
#同步数据用户的密码
master_password=’123456′
 
#Master数据库服务的端口
master_port=3306
 
#指定Slave从哪个日志文件开始读复制数据（Master上执行命令的结果的File字段）
master_log_file=’edu-mysql-bin.000001′
 
#从哪个POSITION号开始读（Master上执行命令的结果的Position字段）
master_log_pos=120
 
#当重新建立主从连接时，如果连接建立失败，间隔多久后重试。单位为秒，默认设置为60秒，同步延迟调优参数。
master_connect_retry=30

• step3: Slave中 查看主从同步状态

# slave

show slave status;
# SlaveIORunning和SlaveSQLRunning是No，表明Slave还没有开始复制过程。
# SlaveIORunning和SlaveSQLRunning是Yes表明已经开始工作了.

• step4: Slave中 开启主从同步

# slave

start slave; 

# 再次查看，发现SlaveIORunning和SlaveSQLRunning是Yes了，表明开启成功。
show slave status;

3. 完整、差异、增量备份

4. 查看 锁

• 查看锁表情况

show status like 'Table%';

• 查看正在被锁定的的表

show OPEN TABLES where In_use > 0;

• 查询进程

show processlist # 查询到相对应的进程可以 kill    id

• 查看未提交事务:

select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx;

• 查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

• 查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;