MySQL主从复制
MySQL复制解决了什么问题？
- 实现在不同服务器上的数据分布
- 实现数据读取的负载均衡
- 增加了数据安全性
- 实现数据库高可用和故障切换
- 实现数据库在线升级
一、二进制日志
服务层日志
- 二进制日志
- 慢查日志
- 通用日志
存储引擎层日志
- innodb 重做日志
- innodb 回滚日志
1.1 STATEMENT格式
记录的日志就是平时写的sql语句
# 设置格式
mysql> set session binlog_format=STATEMENT;
Query OK, 0 rows affected (0.00 sec)
# 查看格式
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
# 刷新
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
# 再查看，产生了一条新的二进制日志
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 69420 |
| mysql-bin.000002 | 1388213 |
| mysql-bin.000003 | 143 |
| mysql-bin.000004 | 6604 |
| mysql-bin.000005 | 101147 |
| mysql-bin.000006 | 10198 |
| mysql-bin.000007 | 3830 |
| mysql-bin.000008 | 115983488 |
| mysql-bin.000009 | 143 |
| mysql-bin.000010 | 1793 |
| mysql-bin.000011 | 167 |
| mysql-bin.000012 | 120 |
+------------------+-----------+
12 rows in set (0.00 sec)
操作数据库
# 创建一个数据库
mysql> create database cms;
Query OK, 1 row affected (0.00 sec)
# 进入该数据库创建一个表
mysql> use cms
Database changed
mysql> create table t1(id int, name varchar(20));
Query OK, 0 rows affected (0.02 sec)
# 插入一条记录
mysql> insert into t1 (id, name) values (1, 'zhangsan');
Query OK, 1 row affected (0.00 sec)
查看二进制日志文件
# 查看二进制日志文件存储在哪
mysql> show variables like 'log_bin%';
+---------------------------------+--------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------+
| log_bin | ON |
| log_bin_basename | /data/mysql/mysql3306/logs/mysql-bin |
| log_bin_index | /data/mysql/mysql3306/logs/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+--------------------------------------------+
5 rows in set (0.00 sec)
# 用命令查看二进制日志
# 进入存储二进制日志的目录
mysqlbinlog mysql-bin.000013
1.2 ROW格式
# binlog_row_image三个值 FULL|MINIMAL|NOBLOB
# FULL所有列都记录,MINMAL修改哪列记哪列,NOBLOB记录所有列除了BLOB列
mysql> show variables like 'binlog_row%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
+------------------------------+-------+
2 rows in set (0.00 sec)
修改二进制日志格式，刷新日志，操作数据库
# 修改二进制日志格式为ROW
set session binary_log=ROW;
# 刷新日志
flush logs;
# 操作数据库
# 增加一列
mysql> alter table t1 add age int(3);
# 插入两行
mysql> insert into t1 (id, name, age) values (2, 'lisi', 20), (3, 'wangwu', 21);
# 删除一行
mysql> delete from t1 where id = 1;
查看ROW格式的二进制日志
mysqlbinlog -vv mysql-bin.
把binlog_row_image分别改为FULL|MINIMAL|NOBLOB，分别查看对应的日志格式
1.3 MIXED格式
根据SQL语句由系统决定在基于段和基于行的日志格式中进行选择
数据量的大小由所执行的SQL语句决定
1.4 建议
binary_log=ROW
binlog_row_image=MINIMAL
基于SQL语句的复制-SBR
基于行的复制-RBR
二、复制的工作方式
三、基于日志点的复制
主库IP：192.168.5.211
从库IP：192.168.5.212
select version();
+------------+
| version() |
+------------+
| 5.6.37-log |
+------------+
3.1 主库配置
[mysqld]
server_id = 211
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql/mysql3306/data
socket = /tmp/mysql3306.sock
tmpdir = /data/mysql/mysql3306/tmp
log-bin = /data/mysql/mysql3306/logs/mysql-bin
innodb_data_home_dir = /data/mysql/mysql3306/data
innodb_log_group_home_dir = /data/mysql/mysql3306/logs
log-error = /data/mysql/mysql3306/logs/error.log
user=mysql
symbolic-links=0
innodb_data_file_path=ibdata1:100M:autoextend
character-set-server = utf8
lower_case_table_names = 1
3.2 从库配置
[mysqld]
server_id = 212
# [可选]
#log_slave_upate = on
# [可选]
read_only = on
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql/mysql3306/data
socket = /tmp/mysql3306.sock
tmpdir = /data/mysql/mysql3306/tmp
log-bin = /data/mysql/mysql3306/logs/mysql-bin
relay-bin = /data/mysql/mysql3306/logs/mysql-relay-bin
innodb_data_home_dir = /data/mysql/mysql3306/data
innodb_log_group_home_dir = /data/mysql/mysql3306/logs
log-error = /data/mysql/mysql3306/logs/error.log
user=mysql
symbolic-links=0
innodb_data_file_path=ibdata1:100M:autoextend
character-set-server = utf8
lower_case_table_names = 1
[mysqld_safe]
# log-error=/data/mysql/mysql3306/error.log
# pid-file=mysqldb.pid
# malloc-lib = /usr/local/mysql/lib/mysql/libjemalloc.so
3.3 创建复制账号
在主库中创建复制账号及授权
create user 'repl'@'192.168.5.%' identified by '123456';
grant replication slave on *.* to 'repl'@'192.168.5.%';
3.4 初始化从库
备份主库数据
# 备份主库数据，使用mysqldump
# xtrabackup --salve-info
mysqldump \
--single-transaction \
--master-data \
--triggers \
--routines \
--all-databases >> /data/backup/all`date +%F`.sql
# 把备份脚本复制到从库，先要ssh-copy-id
scp /data/backup/all2018-03-13.sql vagrant@192.168.5.212:~
初始化从库数据
mysql < ~vagrant/all2018-03-13.sql
3.5 启动复制链路
# 获取master_log_file和master_log_pos
more all2018-03-13.sql
# 找到如下这行
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=946;
# 启动：mysql终端下执行
change master to master_host = '192.168.5.211',
master_user = 'repl',
master_password = '123456',
master_log_file = 'mysql-bin.000013',
master_log_pos=946;
在从库中操作
# 查看
show slave status\G
# 启动
start slave;
# 启动后再查看，可以看到IO进程和SQL进程都启动了
在从库上查看 show processlist;
在主库上查看 show processlist;
四、基于GTID的复制
GTID即全局事务ID，其保证为每一个在主库上提交的事务在复制集群中可以生成一个唯一的ID
GTID=source_id:transaction_id
4.1 主库追加配置
gtid_mode = on
enforce-gtid-consiste
log-slave-updates = on
4.2 从库追加配置
gtid_mode = on
enforce-gtid-consistency
log-slave-updates = on
read_only=on
master_info_repository = TABLE
relay_log_info_repository = TABLE
4.3 启动
change master to master_host = '192.168.5.211',
master_user = 'repl',
master_password = '123456',
master_auto_position = 1;
4.4 注意
保证代码中没有如下类似SQL
create table abc select;
在事务中使用create temporary table 建立临时表
使用关联更新事务表和非事务表
4.5 多线程复制
在从库中操作
stop slave;
# MySQL 5.6.37没有这个参数
# set global slave_parallel-type='logical_clock';
set global slave_parallel_workers=4;
start slave;
