MySQL-主从复制

前言

前篇说了作为运维在数据库块最起码要会两大技能，今天来说说第二技能--主从复制

随着业务的增长，一台数据库服务器以满足不了需求了，负载过重，这时候就需要减压，实现负载均衡读写分离，一主一从或一主多从

主服务器只管写，从服务器管读，从而提高效率减轻压力。

主从复制分类：

主从同步：当用户写数据主服务器必须和从服务器同步一致了才告诉用户写入成功，等待时间太长

主从异步：只要用户访问写数据主服务器写入立马返回给用户成功

主从半步同步：当用户访问写数据主服务器写入并同步其中一个从服务器就返回给用户成功

备注：通常都是使用的主从异步，根据环境需求来选择，想要数据更安全选择半步同步

主从复制注意事项

注意：selinux策略、防火墙

1、开启二进制日志

log_bin

2、设置二进制记录格式为ROW（推荐）

3、设置唯一server-id

server_id=#

4、设置datadir中日志名称（可选）

log-basename=master

5、创建有复制权限的用户账号

GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';

6、如果要启用级联复制,需要在从服务器启用

log_bin
log_slave_updates

7、限制从服务器为只读

read_only=ON

8、禁止主机名解析

skip_name_resolve = on

9、高可用从服务器要加上

relay_log_purge=0 #不清除中继日志

10、如何保证主从复制的事务安全（根据需求添加）

1）在master节点启用参数：

sync_binlog=1
# 每次写后立即同步二进制日志到磁盘，性能差

2）如果用到的为InnoDB存储引擎：

innodb_flush_log_at_trx_commit=1 
# 每次事务提交立即同步日志写磁盘
innodb_support_xa=ON 
# 默认值，分布式事务MariaDB10.3.0废除
sync_master_info=# 
# #次事件后master.info同步到磁盘

3）在slave节点启用参数：

sync_relay_log=# 
# #次写后同步relay log到磁盘
sync_relay_log_info=#
# #次事务后同步relay-log.info到磁盘

主从复制原理

如图

备注：

主从同步有延迟，为什么？因为它时是单线程传送日志

实战-实现主从复制+高可用

准备工作

准备4台主机67、17、37、57，分别充当角色为管理主机、主服务器、2个从服务器

安装包：和主服务器同版本的mysql数据库包、和高可用安装包

mha4mysql-manager

mha4mysql-node

比如主服务器已经运行了1年了，发现满足不了需求了，需要搭建从服务器，我们先从搭建从服务器开始

备注：首先确保主服务器开启了二进制日志

确认二进制文件已启动

MariaDB [(none)]> show variables like '%log_bin%'

确认server-id

MariaDB [(none)]> show variables like 'server%';

主服务器设置

1、创建可用于复制的账号

grant replication slave on *.* to repluser@'192.168.43.%' identified by 'centos';

2、完全备份数据（用于在从服务器上来还原）

mysqldump -pcentos -A -F --single-transaction --master-data=1 >/data/all-`date +%F`.sql

备注：备份时的某表的状态

为了下面的实验测试在这里我们备份玩再增加一条记录

insert hellodb.students (name,age)values('gaoda001',20);

3、传送到从服务器37、57上

scp…

37、57（从）服务器设置

1、配置文件

vim /etc/my.cnf

1）开启二进制日志

log-bin=mysql-bin

2）设置server-id（要和主服务id区分开）

server-id=2

备注：57设置为3 只要他们不相同就可

3）从服务器设置为只读

read_only=on

4）禁止主机名解析

skip_name_resolve = on

5）数据和索引分开存放

innodb_file_per_table = on 
# 10版本以上默认开启

6）重启或启动服务

备注：以防万一可以查看下最重要的两项开启了没有

mysql -e "show variables like 'log%'"
mysql -e "show variables like 'server%'"

2、实现复制

1）进入数据库查找同步代码

MariaDB [(none)]> help change
MariaDB [(none)]> help change master to 

2）根据情况编辑上图上的信息

注：起始位置可以在完全备份文件里查看

两种方式：

① 先还有完全备份数据再拿以上信息编辑下，在数据库中执行

② 直接把上面的信息添加到完全备份文件里

下面用第二种方式来操作如图

3）37主机还原并查看

mysql < all-2018-08-08.sql

备注：以还原到备份时的状态

           主服务器备份完有用户新加数据对吧，下面开启主从复制看看能不能复制过来最新的数据

4）查看复制状态

MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.43.17
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005  
          Read_Master_Log_Pos: 385       #从主服务器读取到的位置
               Relay_Log_File: centos7_05-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: No       #表示还没开启
            Slave_SQL_Running: No       #表示还没开启
              Replicate_Do_DB:  
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 385
              Relay_Log_Space: 256
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL   ##注意这个 表示同步时间差
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
1 row in set (0.03 sec)

5）开启主从复制并查看状态

start slave;

MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.43.17
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 608
               Relay_Log_File: centos7_05-relay-bin.000003
                Relay_Log_Pos: 778
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 608
              Relay_Log_Space: 1092
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.00 sec)

6）查看表确认有没有从主服务器同步最新的数据

7）57（从服务器同样的设置）

备注：目前现实了异步同步复制，下面来看半同步设置

半同步设置

主服务器设置

1、首先在主服务器上安装插件

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

备注：

show plugins; ##查看当前系统中的插件列表

UNINSTALL PLUGIN rpl_semi_sync_master;##卸载插件

2、查看插件状态

SHOW GLOBAL VARIABLES LIKE '%semi%';

3、开启

set global rpl_semi_sync_master_enabled=on;

备注：最好写在配置文件中

4、查看插件变量状态

SHOW GLOBAL STATUS LIKE '%semi%';

备注：这里记录有几个半同步主机

从服务器设置

1、安装插件

INSTALL PLUGIN rpl_semi_sync_slave SONAME  'semisync_slave.so';

2、启动插件

set global rpl_semi_sync_slave_enabled=on;

备注：同样建议写在配置文件中

3、从服务器查看改插件是否开始工作

SHOW GLOBAL STATUS LIKE '%semi%';

为什么没有开始工作？

因为是先开启的主从复制再安装的次插件

所以这种情况下，先停止从服务器的主从复制功能

1）停止：

stop slave;

2）再次开启主从复制

start slave;

3）再去查看

备注：现在就启用了半同步功能，下面开始搭建高可用，实现主服务器宕机自动提升从服务器当主

MHA高性能

备注：四台主机之间必须是ssh基于key验证登陆，所以要先实现ssh互相链接

步骤：

ssh-keygen

cd .ssh

ssh-copy-id 192.168.43.67

把.ssh目录考到其他主机

1、在主服务器上创建管理者账号

grant all on *.* to mhauser@'192.168.43.%' identified by 'centos';

2、在67管理主机上安装以下包

mha4mysql-manager 
mha4mysql-node

3、在17、37、57安装如下包

mha4mysql-node

4、在管理主机创建管理节点

mkdir /etc/mastermha/
cd /etc/mastermha/
vim app1.cnf

根据上面创建的信息填写

[server default]
user=mhauser
password=centos
manager_workdir=/app/mastermha/app1/
manager_log=/app/mastermha/app1/manager.log
remote_workdir=/app/mastermha/app1/
ssh_user=root
repl_user=repluser
repl_password=centos
ping_interval=1
##以上是全局设置

##以下是针对某一组集群设置
[server1]
hostname=192.168.43.17
candidate_master=1
[server2]
hostname=192.168.43.37
candidate_master=1
[server3]
hostname=192.168.43.57
candidate_master=1

备注：

candidate_master=1：是说主服务器宕机了带有这项的主机有机会当主

5、测试

1）ssh协议

/usr/bin/masterha_check_ssh --conf=/etc/mastermha/app1.cnf

2、检查复制

masterha_check_repl --conf=/etc/mastermha/app1.cnf

3、启动（默认前台执行)

masterha_manager --conf=/etc/mastermha/app1.cnf

备注：如果你是编译安装在/etc/mastermha/app1.cnf配置文件中指定日志路径，最好主从服务器数据库设置的位置都是一致的。