作者:邓聪聪
在高级的应用环境中都需要用到高可用，这里配置的是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 statusG//查看进程状态
从节点：
[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 statusG//查看进程状态
扩展数据库半同步：
主服务器配置：
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%';