mysql主从复制

1. 首先配置主从复制结构
2. 主节点：
   1. # vim /etc/my.cnf.d/server.cnf

[mysqld]

server_id=1

log_bin=master-bin

innodb_file_per_table=on

skip_name_resolve=on

1. # systemctl start mairadb
2. mysql> grant replication slave,replication client on *.* to ‘USERNAME’@’172.16.%.%’ identified by ‘PASSWORD’
3. mysql> flush privileges;
4. mysql> install plugin rpl_semi_sync_master soname ‘semisync_master.so’; rpl_semi_sync_master：插件的名称；semisync_master.so：插件文件的名称
5. mysql> show global variables like ‘%semi%’; 插件安装完后就多了些参数
   1. rpl_semi_sync_master_enabled ：半同步复制的功能，master端是否启动
   2. rpl_semi_sync_master_timeout ：超时时间，同步复制时需要从节点必须完成数据重放后，主节点才能释放，如果从节点一直不做响应，超过这个时间，主节点释放，并降级为异步，单位ms
   3. rpl_semi_sync_master_wait_no_slave ：同步复制时，如果没有一个从节点连上来时，是否等待，on表示等待
6. mysql> set @@global.rpl_semi_sync_master_enabled=on;
7. mysql> show global status like ‘%semi%’; 查看到统计数据，基本都是一些计数器
   1. Rpl_semi_sync_master_clients
   2. Rpl_semi_sync_master_net_avg_wait_time 主节点平均等待时间，总时间/等待次数=平均时间，如果这个数值过大，可能网络有问题
   3. Rpl_semi_sync_master_net_wait_time 主节点等待和从节点同步复制的整个网络传输过程的总时间
   4. Rpl_semi_sync_master_net_waits 主节点等待次数
8. 测试：在主节点上添加数据库或表，并查看status统计数据
9. 从节点：
   1. # vim /etc/my.cnf.d/server.cnf

[mysqld]

server_id=2

relay_bin=relay-log

innodb_file_per_table=on

skip_name_resolve=on

1. mysql> change master to master_host=’172.16.100.67’ , master_user=’USERNAME’ , master_password=’PASSWD’ , master_log_file=’master-bin.000003’ , master_log_pos=496;
2. mysql> start slave;
3. mysql> install plugin rpl_semi_sync_slave soname ‘semisync_slave.so’;
4. mysql> show plugins; 可以查看到对应的插件安装完成
5. mysql> show global variables like ‘%semi%’;
   1. rpl_semi_sync_slave_enabled ：半同步复制功能，slave端是否开启
6. mysql> set @@global.rpl_semi_sync_slave_enabled=on; 设定完后并不能立即从异步升级为同步，需要将连接主节点的线程io_thread重启，才能在主节点上看到“连接到半同步复制主节点的客户端数量”的状态数据rpl_semi_sync_master_clients为1
7. mysql> stop slave io_thread;
8. mysql> start slave io_thread;
9. 下面添加节点3
10. # vim /etc/my.cnf.d/server.cnf

11. [mysqld]

    server_id=3

    relay_bin=relay-log

    innodb_file_per_table=on

    skip_name_resolve=on

12. 先对节点3做全备恢复：因为节点3是从中途加入的，那么就需要将主节点先做全备
    1. 在主节点上：# mysqldump –all-databases –master-data=2 –routine –trigger –event –lock-all-tables > all.sql
       1. MariaDB [(none)]> show binary logs;

13. +——————-+———–+

    | Log_name          | File_size |

    +——————-+———–+

    | master-bin.000001 |     30343 |

    | master-bin.000002 |   1038814 |

    | master-bin.000003 |      1013 |

    | master-bin.000004 |       332 |

    +——————-+———–+

14. 将all.sql复制到节点3上：# scp all.sql root@172.16.8.93:/root
15. # mysql < all.sql
16. 再对节点3做二进制恢复
    1. mysql> change master to master_host='172.16.8.95',master_user='repluser',master_password='replpass',master_log_file='master-bin.000004',master_log_pos=332;

1. 加入读写分离节点：
   1. ProxySQL：
      1. 在172.16.8.91上安装proxysql：# yum install proxysql-1.4.13-1-centos7.x86_64.rpm
      2. # rpm -ql proxysql
         1. /etc/init.d/proxysql
         2. /etc/proxysql.cnf proxysql的配置文件
         3. /usr/bin/proxysql
      3. 配置有两种方式：一是连入proxysql将数据插入到main数据库的表中，二是编辑配置文件
      4. 编辑配置文件：# vim /etc/proxysql.conf

datadir="/var/lib/proxysql" 定义工作目录

admin_variables= 定义proxysql管理进程的属性

{

       admin_credentials="admin:admin" 指定管理proxsql服务的用户名和密码

       mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock" proxysql管理进程监听的ip地址和端口（客户端和proxysql通信的管理IP和端口），用以管理员的连接；

     refresh_interval=2000

       debug=true

}

mysql_variables= 定义proxysql服务进程的工作属性

{

        threads=4 服务进程提供的线程数

        max_connections=2048 响应客户端的最大并发连接数

        default_query_delay=0

        default_query_timeout=36000000

        have_compress=true

        poll_timeout=2000

      interfaces="0.0.0.0:6033;/tmp/mysql.sock" 

        interfaces="0.0.0.0:6033"

        default_schema="information_schema"

        stacksize=1048576

        server_version="5.5.30"

        connect_timeout_server=3000

        monitor_username="monitor"

        monitor_password="monitor"

        monitor_history=600000

        monitor_connect_interval=60000

        monitor_ping_interval=10000 proxysql时隔多久发送一次心跳信息给后端mysql服务器

        monitor_read_only_interval=1500

        monitor_read_only_timeout=500

        ping_interval_server_msec=120000

        ping_timeout_server=500

        commands_stats=true

        sessions_sort=true

        connect_retries_on_failure=10

}

mysql_servers = 定义反代到的后端mysql服务器

(

       {

               address = "172.16.8.95" 定义反代到的后端mysql服务器的IP（proxysql和后端mysql通信的ip和端口）

               port = 3306           

               hostgroup = 0            定义这个主机所属组的id，主节点和从节点的组id需要不同， no default, required

               status = "ONLINE"     # default: ONLINE

               weight = 1            # default: 1 权重

               compression = 0       # default: 0 压缩与否

 #max_replication_lag = 10   定义从节点复制时延迟主节点多少时间，防止主节点误操作，默认0不延迟

       }, 各服务器之间用逗号隔开，最后一个无需逗号

)

mysql_users: 定义proxysql使用什么用户来连接后端mysql服务器，需要在mysql中事先为该用户授权，该用户为mysql上的用户

(

{

username= 指定连接的用户名，（构建好了主从结构后，可在主节点上授权，从节点上自动就授权了），mysql> grant all on  *.* to 'tom'@'172.16.8.92' identified by 'tom';

password=

default_hostgroup=# 默认连接到哪个组上

max_connections=# proxysql和后端mysql服务器的最大并发连接数

default_schema=”DATABASE” 默认连接到的数据库

active=#

}, 多个用户使用逗号隔开

)

mysql_query_rules: 定义语句路由规则

(

{

rule_id=#

active=#

match_pattern=”^select .* for update$” 匹配语句

destination_hostgroup=0 指定路由到组的id

apply=1

}

)

mysql_replication_hostgroups= 定义组的属性

(

        {

                writer_hostgroup=0 定义哪个组为写

                reader_hostgroup=1 定义哪个组为读

                comment="test repl 1" 注释

       }

)

1. 测试：
   1. 配置完后重启服务：# systemctl restart proxysql
   2. 在任意一台安装了mysql客户端的主机上连接到proxsql的服务进程：# mysql -utom -ptom -h172.16.8.91 -P3306
   3. 通过proxysql对数据做修改，可以看到主节点和从节点上都有数据变化

1. 安装配置 MHA
   1. 准备基于 ssh 互信通信环境
      1. MHA 集群中的各节点彼此之间均需要基于 ssh 互信通信，以实现远程控制及数据管理功能。简单起见，可在 Manager 节点生成密钥对儿，并设置其可远程连接本地主机后，将私钥文件及 authorized_keys 文件复制给余下的所有节点即可。
      2. 在任意一台节点操作，下面在manager节点上操作
         1. ~]# ssh-keygen -t rsa -P ''
         2. ~]# cat .ssh/id_rsa.pub >> .ssh/authorized_keys
         3. ~]# chmod 600 .ssh/authorized_keys
         4. ~]# scp -p .ssh/id_rsa .ssh/authorized_keys   root@node95:/root/.ssh/
         5. ~]# scp -p .ssh/id_rsa .ssh/authorized_keys   root@node94:/root/.ssh/
         6. ~]# scp -p .ssh/id_rsa .ssh/authorized_keys   root@node93:/root/.ssh/
         7. 如果节点较多时，以上步骤也可以直接用for循环实现
            1. # for i in 91 92 93 94 95 96 97;do scp -p /root/.ssh/id_rsa /root/.ssh/authorized_keys root@172.16.8.$i:/root/.ssh/ ;done
      3. 注意：请事先确保所有机器上的/root/.ssh 目录存在，且在/root/.ssh 目录下不能有id_rsa.pub，否则有id_rsa.pub的主机连接其他机器还是需要输入密码。或者，可以直接把manager上的id_rsa.pub也都拷贝到其他机器上就不存在这个问题了。
   2. 安装 MHA
      1. 除了源码包，MHA官方也提供了rpm 格式的程序包。CentOS 7 系统可直接使用适用于 el6 的程序包。另外，MHA Manage 和 MHA Node 程序包的版本并不强制要求一致
      2. Manager节点安装manager包：注意，如果 mha4mysql-manager安装不成功，建议先安装mha4mysql-node后安装mha4mysql-manager，因为存在依赖关系。另外，server端也没有启动脚本，需要手动启动
         1. ~]# yum install mha4mysql-manager-0.56-0.el6.noarch.rpm
      3. 所有节点，包括 Manager，安装node包：
         1. ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm
   3. 初始化 MHA
      1. Manger 节点需要为每个监控的 master/slave 集群提供一个专用的配置文件，而所有的master/slave 集群也可共享全局配置。全局配置文件默认为/etc/masterha_default.cnf，其为可选配置。如果仅监控一组 master/slave 集群，也可直接通过application 的配置来提供各服务器的默认配置信息。而每个application 的配置文件路径为自定义，例如，本示例中将使用/etc/masterha/app1.cnf，其内容如下所示。
      2. 首先创建相关目录首先创建相关目录：# mkdir  /etc/masterha
      3. # vim /etc/masterha/app1.cnf

[server default]

user=mhaadmin MySQL 管理员，用来管理各个节点

password=mhaadmin MySQL 管理员mhaadmin的密码

manager_workdir=/data/masterha/app1   这个目录/data/masterha/app1不需要创建，不存在时会自动创建

manager_log=/data/masterha/app1/manager.log

remote_workdir=/data/masterha/app1 指各个node节点的工作目录

ssh_user=root 基于密钥连接，所以不需要配置密码

ssh_port=22   这个选项是全局，如果不定义，就是使用默认的端口22，可以在每个节点上分别再定义端口，基于安全考虑，可以把22端口改成其他的

repl_user=repluser

repl_password=replpass

ping_interval=1 多长时间对各个节点发送一次心跳信息

[server1]

hostname=172.16.8.95

#ssh_port=22022 这个选项如果不定义，就是默认的22端口

candidate_master=1 将来是否可以成为主节点，如果不定义，就不能成为候选的主节点

[server2]

hostname=172.16.8.94

#ssh_port=22022

candidate_master=1

[server3]

hostname=172.16.8.93

#ssh_port=22022

#no_master=1 如果定义no_master就不能成为主节点

1. 检测各节点间 ssh 互信通信配置是否 OK：–conf指定配置文件路径
   1. ~]# masterha_check_ssh –conf=/etc/masterha/app1.cnf
   2. 输出信息最后一行类似如下信息，表示其通过检测。

[info] All SSH connection tests passed successfully.

1. 检查管理的 MySQL 复制集群的连接配置参数是否 OK：
   1. ~]# masterha_check_repl –conf=/etc/masterha/app1.cnf
   2. 输出信息如下所示，最后一行的“Health is OK”信息表示通过检测。

Tue Jun 6 17:22:48 2017 – [info] Slaves settings check done.

Tue Jun 6 17:22:48 2017 – [info]

172.16.8.95(172.16.8.95:3306) (current master)

+–172.16.8.94(172.16.8.94:3306)

+–172.16.8.93(172.16.8.93:3306)

…… ……

MySQL Replication Health is OK.

1. 启动 MHA
   1. 后台运行，注意，masterha_manager没有启动的程序，需要手动启动服务
      1. ~]# nohup masterha_manager –conf=/etc/masterha/app1.cnf > /data/masterha/app1/manager.log 2>&1 &
   2. 启动成功后，可通过如下命令来查看 master 节点的状态。
      1. ~]# masterha_check_status –conf=/etc/masterha/app1.cnf
      2. app1 (pid:4978) is running(0:PING_OK), master:172.16.8.95
      3. 上面的信息中“app1 (pid:4978) is running(0:PING_OK)”表示 MHA 服务运行 OK，否则，则会显示为类似“app1 is stopped(1:NOT_RUNNING).”。
   3. 如果要停止 MHA，需要使用 masterha_stop 命令。
      1. ~]# masterha_stop –conf=/etc/masterha/app1.cnf Stopped app1 successfully.