mysql 级联复制 配置_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=1log-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 unitfile formariadb according to the

# instructionsin 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=2read_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 unitfile formariadb according to the

# instructionsin 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=3read_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 unitfile formariadb according to the

# instructionsin 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%';