mysql的主从级联复制的配置

作者:邓聪聪

　　在高级的应用环境中都需要用到高可用，这里配置的是mysql的主从级联复制

　　试验环境：

　　主节点 192.168.0.101/24

　　辅助节点+辅助节点 192.168.0.102/24 ,192.168.0.103/24

　　配置（CentOS Linux release 7.6.1810 (Core) ，mysql  Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1）

master配置：

[mysqld]
server_id=1
log-bin=/data/mysql_bin
binlog_format=row
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[root@mysql-master mariadb]# 

//1；定义全局服务ID,注意，此ID必须保证唯一性，2；定义2进制日志文件的路径，确保该目录的所属权限，如果不是mysql无法启动mysql服务，3；我这里配置二进制日志文件基于行（3种模式，根据业务需求自行决定）保存，4；配置mysql不参与DNS解析

MariaDB [(none)]> grant replication slave on *.* to repliuser@'%' identified by '123456';//定义复制用户的权限

辅助节点：

[root@mysql-node1 ~]# cat /etc/my.cnf
[mysqld]
server_id=2
read_only=ON
log-bin=/data/mysql_bin
log_slave_updates
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[root@mysql-node1 ~]# 

//1；同主节点配置，从节点配置数据库只读权限（root除外），并同步更新主节点的二进制日志文件

MariaDB [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='192.168.0.101',//同步的服务器
    ->   MASTER_USER='repliuser',//用户名
    ->   MASTER_PASSWORD='123456',//密码
    ->   MASTER_PORT=3306,//端口
    ->   MASTER_LOG_FILE='mysql_bin.000002',同步服务器上的二进制文件，选择需要同步的内容起点
    ->   MASTER_LOG_POS=245;//起点位置，245位一个新文件的初始位置

MariaDB [(none)]> start slave;//启动复制进程

MariaDB [(none)]> show slave status\G//查看进程状态

 

从节点：

[root@mysql-node2 ~]# cat /etc/my.cnf
[mysqld]
server_id=3
read_only=ON
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[root@mysql-node2 ~]# 

// 只需要配置数据库的只读即可

MariaDB [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='192.168.0.102',//同步的服务器
    ->   MASTER_USER='repliuser',//用户名
    ->   MASTER_PASSWORD='123456',//密码
    ->   MASTER_PORT=3306,//端口
    ->   MASTER_LOG_FILE='mysql_bin.000002',同步服务器上的二进制文件，选择需要同步的内容起点
    ->   MASTER_LOG_POS=245;//起点位置，245位一个新文件的初始位置

MariaDB [(none)]> start slave;//启动复制进程

MariaDB [(none)]> show slave status\G//查看进程状态

 扩展数据库半同步：

主服务器配置：

MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)

 MariaDB [(none)]> set global rpl_semi_sync_master_timeout=1000;
 Query OK, 0 rows affected (0.00 sec)

　主、从服务器　/etc/my.cnf

　　rpl_semi_sync_master_enabled、rpl_semi_sync_slave_enabled  //开启开机同步

　　rpl_semi_sync_master_timeout=2000 //延迟2秒

从服务器配置：

MariaDB [(none)]> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';           
Query OK, 0 rows affected (0.00 sec)

　　查询：show global variables like '%semi%';