Linux:MYSQL（十三）-MYSQL主从，主主复制

MySQL复制

• 扩展方式： Scale Up ，Scale Out
• MySQL的扩展
  • 读写分离
  • 复制：
    每个节点都有相同的数据集
    向外扩展
    基于二进制日志
    单向
• 复制的功用：
  数据分布
  负载均衡读
  物理性备份
  高可用和故障切换
  MySQL升级测试

MySQL读写分离

• 读写分离应用：
  mysql-proxy：Oracle，https://downloads.mysql.com/archives/proxy/
  Atlas：Qihoo360，https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md
  dbproxy：美团，https://github.com/Meituan-Dianping/DBProxy
  Cetus：网易乐得，https://github.com/Lede-Inc/cetus
  Amoeba：https://sourceforge.net/projects/amoeba/
  Cobar：阿里巴巴，Amoeba的升级版
  Mycat：基于Cobar， http://www.mycat.io/
  ProxySQL：https://proxysql.com/

主从复制

主从复制原理：
Master:（需开启二进制日志）

1.数据更新
2.写入Bin log
3.开启Slave服务线程 通过网络传送二进制日志

Slave:（如充当从服务器的从服务器，需开启二进制日志）

4.开启一个io线程，接收二进制日志
5.写入Relay log,以防网络断了，意外，先写入磁盘
6.开启SQL线程，读入数据库
7.数据更新

MYSQL垂直分区

可按其他 用户信息 用户ID分区

MYSQL水平分片

可按USER_ID分片，单数放一个服务器，偶数放一个服务器

MySQL复制

• 主从复制线程：

  • 主节点：
    dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
  • 从节点：
    I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
    SQL Thread：从中继日志中读取日志事件，在本地完成重放

• 跟复制功能相关的文件：

  • master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
  • relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地replay log日志的对应关系

MySQL复制

• 主从复制特点：
  异步复制
  主从数据不一致比较常见

• 复制架构：
  Master/Slave, Master/Master, 环状复制
  一主多从（推荐）
  从服务器还可以再有从服务器
  一从多主:适用于多个不同数据库

• 复制需要考虑二进制日志事件记录格式
  STATEMENT（5.0之前）
  ROW（5.1之后，推荐）
  MIXED

MySQL复制

• 主从配置过程：参看官网
  https://mariadb.com/kb/en/library/setting-up-replication/
  https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html

• 主节点配置：
  (1) 启用二进制日志
  [mysqld]
  log_bin
  (2) 为当前节点设置一个全局惟一的ID号
  [mysqld]
  server_id=#
  log-basename=master 可选项，设置datadir中日志名称，确保不依赖主机名
  (3) 创建有复制权限的用户账号
  GRANT REPLICATION SLAVE ON . TO ‘repluser’@‘HOST’ IDENTIFIED BY ‘replpass’;

• 从节点配置：
  (1) 启动中继日志
  [mysqld]
  server_id=# 为当前节点设置一个全局惟的ID号
  relay_log=relay-log relay log的文件路径，默认值hostname-relay-bin
  relay_log_index=relay-log.index 默认值hostname-relay-bin.index
  (2) 使用有复制权限的用户账号连接至主服务器，并启动复制线程
  mysql>
  CHANGE MASTER TO MASTER_HOST=‘host’, MASTER_USER=‘repluser’,
  MASTER_PASSWORD=‘replpass’,
  MASTER_LOG_FILE=‘mysql-bin.xxxxx’,
  MASTER_LOG_POS=#;
  mysql>
  START SLAVE [IO_THREAD|SQL_THREAD];

MySQL复制 三级主从时

• 如果要启用级联复制,需要在从服务器启用以下配置 （中间级添加以下设置）
  [mysqld]
  log_bin
  log_slave_updates

MySQL复制 !!!

复制架构中应该注意的问题：reset master清除原主的二进制日志，之后当从服务器

1、限制从服务器为只读
在从服务器上设置
read_only=ON
注意：此限制对拥有SUPER权限的用户均无效
阻止所有用户, 包括主服务器复制的更新
mysql> FLUSH TABLES WITH READ LOCK;

2、RESET SLAVE
在从服务器清除master.info ，relay-log.info, relay log ，开始新的relay log ,
注意：需要先STOP SLAVE
RESET SLAVE ALL 清除所有从服务器上设置的主服务器同步信息如：PORT, HOST, USER和 PASSWORD 等

3、sql_slave_skip_counter = N 从服务器忽略几个主服务器的复制事件，global变量 （忽略错误，N为跳过的数量）

4、如何保证主从复制的事务安全
参看https://mariadb.com/kb/en/library/server-system-variables/

• 在master节点启用参数：
  sync_binlog=1 每次写后立即同步二进制日志到磁盘，性能差
  如果用到的为InnoDB存储引擎：
  innodb_flush_log_at_trx_commit=1 每次事务提交立即同步日志写磁盘
  innodb_support_xa=ON 默认值，分布式事务MariaDB10.3.0废除
  sync_master_info=# #次事件后master.info同步到磁盘

• 在slave节点启用服务器选项：
  skip_slave_start=ON 不自动启动slave

• 在slave节点启用参数：
  sync_relay_log=# #次写后同步relay log到磁盘
  sync_relay_log_info=# #次事务后同步relay-log.info到磁盘

实验操作:实现主从 192.168.32.7主 192.168.32.17从

一、主：192.168.32.7

vim /etc/my.cnf
[mysqld]
server_id=1
innodb_file_per_table
log_bin
skip_name_resolve  

systemctl start mairadb

mysql>
show master status;   查看文件名及文件位置
show master logs;
grant replication slave on *.* to repluser@'192.168.32.%' identified by 'centos';

二、从：192.168.32.17

vim /etc/my.cnf
[mysqld]
server_id=2
relay_log=relay-log
relay_log_index=relay-log.index
innodb_file_per_table
skip_name_resolve  

systemctl start mairadb

mysql>
help change
help change master to help可查看范例

CHANGE MASTER TO
  MASTER_HOST='192.168.32.7',
  MASTER_USER='repluser',
  MASTER_PASSWORD='centos',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mariadb-bin.000001',
  MASTER_LOG_POS=245;

show slave status\G 可以看从状态

start slave; 启动线程

三、在192.168.32.7上创建账号测试是否同步成功

mysql>
create user test@'%' identified by 'centos';

四、在192.168.32.17上查看

mysql>
select user,host from mysql.user;

实验 服务器跑一段时间后，配置从服务器

一、主：192.168.32.7

mysqldump -A -F --single-transaction --master-data=1 >all.sql

scp all.sql 192.168.32.27:

二、从：192.168.32.27

vim /etc/my.cnf
[mysqld]
server_id=3
read_only=ON

vim all.sql 编辑一下，把change master语句填全再导入
help change master to 查看格式，修改好后填入all.sql

mysql <all.sql
mysql>
start slave;

主主复制：互为主从

• 容易产生的问题：数据不一致；因此慎用

• 考虑要点：自动增长id
  配置一个节点使用奇数id
  auto_increment_offset=1 开始点
  auto_increment_increment=2 增长幅度
  另一个节点使用偶数id
  auto_increment_offset=2
  auto_increment_increment=2

主主复制：

主主复制的配置步骤：
(1) 各节点使用一个惟一server_id
(2) 都启动binary log和relay log
(3) 创建拥有复制权限的用户账号
(4) 定义自动增长id字段的数值范围各为奇偶
(5) 均把对方指定为主节点，并启动复制线程

实验

一、192.168.32.7

vim /etc/my.cnf
[mysqld]
server_id=1
log_bin
auto_increment_offset=1
auto_increment_increment=2
skip_name_resolve  禁止反向解析

systemctl start mariadb

mysql>
grant replication slave on *.* to repluser@'192.168.32.%' indentified by 'centos';

show master logs;

二、192.168.32.17

vim /etc/my.cnf
[mysqld]
server_id=2
log_bin
auto_increment_offset=2
auto_increment_increment=2
skip_name_resolve  禁止反向解析

systemctl start mariadb

mysql>
change master to
master_host='192.168.32.7',
master_user='repluser',
master_password='centos',
master_port=3306,
master_log_file='mariadb-bin.000001',master_log_pos=245;

start slave;           开启子进程

show slave status\G;   查看状态

select user,host from mysql.user;  验证账号是否同步

三、192.168.32.7

mysql>
change master to
master_host='192.168.32.17',
master_user='repluser',
master_password='centos',
master_port=3306,
master_log_file='mariadb-bin.000001',master_log_pos=245;

create table test (id int auto_increment primary key,name char(20));  创建一个表测试

select * from test; 

insert test (name)values('a'); 

select * from test;

insert test (name)values('c'),('d');

四、192.168.32.7

mysql>

insert test (name)values('b'); 添加数据测试