proxysql+MHA+半同步复制

先配置成主从同步

先在各节点安装服务

[root@inotify ~]# yum install mariadb-server -y

编辑主节点的配置文件，并启动

[root@centos7 ~]# vim /etc/my.cnf

[mysqld]

server_id=28

log-bin=master-bin

innodb_file_per_table=ON

skip_name_resolve=ON
[root@centos7 ~]# systemctl restart mariadb

配置各从节点的配置文件并启动

[root@inotify ~]# vim /etc/my.cnf

[mysqld]

server_id=26

log-bin=master-bin

relay-log=relay-bin

read_only=1

relay_log_purge=0      #不清除中继日志文件

skip_name_resolve=on

innodb_file_per_table=On

[root@slave-master ~]# vim /etc/my.cnf
[mysqld]
server_id=33
log-bin=master-bin
relay_log_purge=0
relay_log=relay-bin
read_only=1
skip_name_resolve=On
innodb_file_per_table=on

在主节点授权一个可复制的账号

MariaDB [(none)]> show master status;

+-------------------+----------+--------------+------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-------------------+----------+--------------+------------------+

| master-bin.000003 |      245 |              |                  |

+-------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

MariaDB [(none)]> grant replication slave on *.* to 'repluser'@'192.168.37.%' identified by 'centos';

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.01 sec)

#先查看日志位置，否则创建的复制账号在各节点不会同步，导致主节点宕机，从节点没有复制账号

在各从节点加入并开启复制

MariaDB [(none)]> change master to master_host='192.168.37.28',master_user='repluser',master_password='centos',master_log_file='master-bin.000003',master_log_pos=245;

MariaDB [(none)]> start slave;

在将这个主从复制架构配置成半同步复制，防止数据丢失

在主节点安装插件

MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=1;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_timeout = 1000;

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';

+------------------------------------+-------+

| Variable_name                      | Value |

+------------------------------------+-------+

| rpl_semi_sync_master_enabled       | ON    |

| rpl_semi_sync_master_timeout       | 1000  |

| rpl_semi_sync_master_trace_level   | 32    |

| rpl_semi_sync_master_wait_no_slave | ON    |

+------------------------------------+-------+

4 rows in set (0.00 sec)

在各从服务器安装插件

MariaDB [(none)]> install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set global rpl_semi_sync_slave_enabled=1;

实现MHA

准备mha的管理包和node包，在管理节点安装管理包和node包（管理包依赖epel源）

[root@nfs ~]# ls

anaconda-ks.cfg  lrzsz                                    mha4mysql-node-0.56-0.el6.noarch.rpm  pcre-devel

bc               mha4mysql-manager-0.56-0.el6.noarch.rpm  original-ks.cfg

[root@nfs ~]# yum install mha*

在各mariadb节点安装node包

[root@inotify ~]# ls

anaconda-ks.cfg  mha4mysql-node-0.56-0.el6.noarch.rpm  original-ks.cfg

[root@inotify ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm

在管理节点将密钥拷贝到各节点（实现ssh互通）

[root@nfs ~]# ssh-keygen

[root@nfs ~]# ssh-copy-id 192.168.37.23  #先拷贝给自己

[root@nfs ~]# scp -r .ssh/ 192.168.37.28:/root/   #拷贝到各mariadb节点

在管理节点配置文件

[root@nfs ~]# mkdir /etc/mha

[root@nfs ~]# vim /etc/mha/app1.cnf

[server default]

user=mhauser

password=centos

manager_workdir=/data/mastermha/app1/

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

remote_workdir=/data/mastermha/app1/

ssh_user=root

repl_user=repluser

repl_password=centos

ping_interval=1

[server1]

hostname=192.168.37.28

candidate_master=1

[server2]

hostname=192.168.37.26

candidate_master=1

[server3]

hostname=192.168.37.33

在主节点授权一个可以监控用户

MariaDB [(none)]> grant all on *.* to 'mhauser'@'192.168.37.%' identified by 'centos';

Query OK, 0 rows affected (1.00 sec)

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

在管理节点做测试

[root@nfs ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf

[root@nfs ~]# masterha_check_repl --conf=/etc/mha/app1.cnf

在管理节点启动

[root@nfs ~]# masterha_manager --conf=/etc/mha/app1.cnf

宕到主几点测试

注意：记得将新提升的主节点的配置文件中的read_only值注释或删除，否则重启后会有问题

配置PorxySQL服务器的Yum源安装proxysql

[root@msyql-proxy ~]# cat <<EOF | tee /etc/yum.repos.d/proxysql.repo

> [proxysql_repo]

> name= ProxySQL YUM repository

> baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever

> gpgcheck=1

> gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key

> EOF

[root@msyql-proxy ~]# yum install proxysql -y
[root@msyql-proxy ~]# systemctl start proxysql

[root@msyql-proxy ~]# mysql -uadmin -padmin -P 6032 -h127.0.0.1

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.37.28',3306);
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.37.26',3306);
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.37.33',3306);
Query OK, 1 row affected (0.00 sec) #：将主从加入到proxysql

MySQL [(none)]> load mysql servers to runtime; # 加载到内存
Query OK, 0 rows affected (0.00 sec)　　　　　　　　

MySQL [(none)]> save mysql servers to disk; #保存到磁盘
Query OK, 0 rows affected (0.01 sec)

在master节点添加一个监控后端服务器的用户

 MariaDB [(none)]> grant replication client on *.* to monitor@'192.168.37.%' identified by 'magedu';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

在proxysql服务器添加监控后端服务器的用户

MySQL [(none)]> set mysql-monitor_username='monitor';

Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> set mysql-monitor_password='magedu';

Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> save mysql variables to disk;
Query OK, 97 rows affected (0.00 sec)

MySQL [(none)]> insert into mysql_replication_hostgroups values(10,20,"test");
Query OK, 1 row affected (0.00 sec) #设置分组

MySQL [(none)]> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> save mysql servers to disk;
Query OK, 0 rows affected (0.01 sec)

在主节点创建一个用户可以让该用户操作主从节点的数据库

MariaDB [(none)]> grant all on *.* to sqluser@'192.168.8.%' identified by 'magedu';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

在proxysql上配置路由规则

MySQL [(none)]> insert into mysql_query_rules

    -> (rule_id,active,match_digest,destination_hostgroup,apply)VALUES

    -> (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);

Query OK, 2 rows affected (0.01 sec)

MySQL [(none)]> load mysql query rules to runtime;

Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> save mysql query rules to disk;

Query OK, 0 rows affected (0.00 sec)

巴特西

proxysql+MHA+半同步复制

最新文章

热门文章