-------------------------------------------------------------------------------主从复制------------------------------------------------------------------------------
1.主节点设置
#配置文件设置
server-id=#
log-bin
#创建复制权限账号(针对mariadb的10版本,mysql的8版本需授权和创建账号分两步)
GRANT REPLICATION SLAVE ON *.* TO ‘repluser‘@‘HOST‘ IDENTIFIED BY ‘replpass‘;
#查看记录二进制日志文件名和位置(从此位置传送给从节点,从此位置开始同步)
SHOW MASTER LOG;
2.从节点设置
#配置文件设置
server-id=#
read_only=ON
relay_log=relay-log (可选)
relay_log_index=relay-log.index (可选)
#使用有复制权限的用户账号连接至主服务器,并启动复制线程
CHANGE MASTER TO MASTER_HOST=‘masterhost‘,
MASTER_USER=‘repluser‘,
MASTER_PASSWORD=‘replpass‘,
MASTER_LOG_FILE=‘mariadb-bin.xxxxxx‘,
MASTER_LOG_POS=#;
START SLAVE [IO_THREAD|SQL_THREAD];
SHOW SLAVE STATUS;
-------------------------------------------------------------------------------级联复制------------------------------------------------------------------------------
1.主节点设置
#配置文件设置
server-id=#
log-bin
#创建复制权限账号
grant replication slave on *.* to repluser@‘用户IP‘
#备份数据库
mysqldump -A -F --single-transaction --master-data=1 > /backup.sql
2.级联从节点设置
#配置文件设置
server-id=#
log-bin
read-only
log_slave_updates
#编辑备份文件
vim /backup.sql
CHANGE MASTER TO
MASTER_HOST=‘master节点的iP‘,
MASTER_USER=‘repluser‘,
MASTER_PASSWORD=‘centos‘,
MASTER_PORT=3306,
MASTER_LOG_FILE=‘mysql-bin.000004‘,
MASTER_LOG_POS=523;
#还原备份文件
set sql_log_bin=0;
source /backup.sql
show master logs; (记录二进制位置,给第三个节点使用)
set sql_log_bin=1;
start slave;
3.第三节点设置
#配置文件设置
server-id=#
read-only
#编辑备份文件
vim /backup.sql
CHANGE MASTER TO
MASTER_HOST=‘中间节点的IP‘,
MASTER_USER=‘repluser‘,
MASTER_PASSWORD=‘magedu‘,
MASTER_PORT=3306,
MASTER_LOG_FILE=‘mariadb-bin.000002‘, MASTER_LOG_POS=344;
#还原备份文件
set sql_log_bin=0;
source /backup.sql
set sql_log_bin=1;
start slave;
------------------------------------------------------------------------------主主复制------------------------------------------------------------------------------
1.第一个主节点设置
server-id=#
log-bin
show master logs;
grant replication slave on *.* to repluser@‘第二个主节点IP‘ identified by ‘password‘;(授权在二进制日志记录点后,第二节点会复制本条命令不用配置)
2.第二个主节点设置
server-id=#
log-bin
CHANGE MASTER TO
MASTER_HOST=‘第一主节点的IP‘,
MASTER_USER=‘repluser‘,
MASTER_PASSWORD=‘password‘,
MASTER_PORT=3306,
MASTER_LOG_FILE=‘mariadb-bin.000002‘,
MASTER_LOG_POS=344;
start slave;
show master logs;
3.第一个主节点设置
CHANGE MASTER TO
MASTER_HOST=‘第二主节点的IP‘,
MASTER_USER=‘repluser‘,
MASTER_PASSWORD=‘password‘,
MASTER_PORT=3306,
MASTER_LOG_FILE=‘mariadb-bin.000002‘,
MASTER_LOG_POS=344;
start slave;
------------------------------------------------------------------------------半同步复制------------------------------------------------------------------------------
1.主节点设置(针对mariadb10版本后,本设置是建立主从设置成功基础上)
server-id=#
log-bin
plugin-load-add = semisync_master
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000(设置同步等待时间)
SHOW GLOBAL VARIABLES LIKE ‘%semi%‘;(查看半同步变量)
SHOW GLOBAL STATUS LIKE ‘%semi%‘;(查看半同步变量状态)
2.从节点设置
server-id=#
plugin_load_add = semisync_slave
rpl_semi_sync_slave_enabled=ON