MariaDB基于GTID主从复制及多主复制

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/reblue520/article/details/50564222

一、简单主从模式配置步骤(必须要mysql5.6,此处以maridb10.0.10为例)



1、配置主从节点的服务配置文件
# vim /etc/my.cnf


1.1、配置master节点:

[mysqld]
log-bin=/data/binlogs/master-bin
binlog_format=row


server-id       = 100
log-slave-updates = True
#gtid-mode = ON
#enforce-gtid-consistency=True
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info = 1
slave-parallel-threads = 2
binlog-checksum = CRC32
master-verify-checksum = 1
slave-sql-verify-checksum = 1
binlog-rows-query-log-events = 1
report-port = 3306
report-host = master.chinasoft.com



1.2、配置slave节点:

[mysqld]
log-bin=/data/binlogs/master-bin
binlog_format=row


server-id       = 200
log-slave-updates = True
#gtid-mode = ON
#enforce-gtid-consistency=True
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info = 1
slave-parallel-threads = 2
binlog-checksum = CRC32
master-verify-checksum = 1
slave-sql-verify-checksum = 1
binlog-rows-query-log-events = 1
report-port = 3306
report-host = slave.chinasoft.com


修改文件报错可参考/data/mydata/下的错误日志定位
# tail centossz008.err 



修改master的hostname
在hosts文件中添加master和slave的解析,将传给slave
192.168.8.40 master.chinasoft.com master
192.168.8.43 slave.chinasoft.com slave


# hostname master.chinasoft.com

# vim /etc/hosts


# scp /etc/hosts 192.168.8.42:/etc/


配置中可能碰到的错误:



2、创建复制用户

主服务器192.168.8.40:
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.%.%' IDENTIFIED BY 'replpass';


MariaDB [(none)]> FLUSH PRIVILEGES;


说明:192.168.%.% 是从节点服务器;如果想一次性授权更多的节点,可以自行根据需要修改;


3、为备节点提供初始数据集



锁定主表,备份主节点上的数据,将其还原至从节点;如果没有启用GTID,在备份时需要在master上使用show master status命令查看二进制日志文件名称及事件位置,以便后面启动slave节点时使用。
# mysqldump --all-databases --lock-all-tables --flush-logs --master-data=2 > all.sql
传给从服务器
# scp all.sql:192.168.8.43


4、启动从节点的复制线程


普通主从复制:

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.8.40',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000004',MASTER_LOG_POS=379;

启用GTID,需要使用如下命令:

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.8.40',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_USE_GTID=current_pos;



MariaDB [(none)]> start slave;
查看从服务器状态

MariaDB [(none)]> show slave status\G


可以通过在主服务器中导入一张大的数据表(总数量在4W行左右),通过从服务器观察是否启用了多线程复制:

主服务器:

MariaDB [db1]> CREATE TABLE `ehr_user` (   `LOB_NUMBER` varchar(255) NOT NULL,   `SEX` varchar(255) DEFAULT '',   `LAST_NAME` varchar(255) DEFAULT NULL,   `EMPLOYEE_NUMBER` varchar(255) DEFAULT NULL,   `PHONE` varchar(255) DEFAULT NULL,   `NATIONAL_IDENTIFIER` varchar(255) DEFAULT NULL,   `EMAIL` varchar(255) DEFAULT NULL,   `LOB_GROUP` varchar(255) DEFAULT NULL,   `LOB` varchar(255) DEFAULT NULL,   `BUG` varchar(255) DEFAULT NULL,   `BUDU` varchar(255) DEFAULT NULL,   `DD` varchar(255) DEFAULT NULL,   `REGION` varchar(255) DEFAULT NULL,   `DS` varchar(255) DEFAULT NULL,   `PEOPLE_TYPE` varchar(255) DEFAULT NULL,   `ACTUAL_TERMINATION_DATE` varchar(255) DEFAULT NULL,   `HIRE_DATE` timestamp NULL DEFAULT NULL,   `FLAG` varchar(8) CHARACTER SET gbk COLLATE gbk_bin DEFAULT '0',   `LAST_UPDATETIME` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,   `EMAIL_NO_LOGIN` varchar(8) DEFAULT NULL,   `LOB_NUMBER_BAK` varchar(255) DEFAULT NULL,   PRIMARY KEY (`LOB_NUMBER`),   KEY `EMPLOYEE_NUMBER` (`EMPLOYEE_NUMBER`),   KEY `FLAG` (`FLAG`) USING BTREE,   KEY `LOB` (`LOB`) USING BTREE ) ENGINE=MyISAM DEFAULT CHARSET=gbk;

MariaDB [db1]> source /root/ehr_user.sql

从服务器通过命令观察:

# watch -n .3 "mysql -e 'show processlist\G'"



# watch -n .5 "mysql -e 'show slave status\G'"




报错:
Last_IO_Error: error connecting to master 'repluser@192.168.8.40:3306' - retry-time: 60  retries: 86400  message: Can't connect to MySQL server on '192.168.8.40' (113 "No route to host")


发现是被防火墙阻挡了,在主服务器关闭防火墙
# service iptable stop

验证测试:

通过在主服务器上创建数据库、表,能否在从服务器看到



多主复制:



主服务器1: 192.168.8.40
主服务器2:192.168.8.43
从服务器:192.168.8.44


配置主服务器2:

MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.%.%' IDENTIFIED BY 'replpass';


配置从服务器192.168.8.44:
修改配置文件
vim /etc/my.cnf


加入
relay-log=/data/binlogs/relay-bin
server-id       = 300
注释掉
# bin-log=master-bin


重启服务

# service mysqld restart


MariaDB [(none)]> CHANGE MASTER 'm1' TO MASTER_HOST='192.168.8.40',MASTER_USER='repluser',MASTER_PASSWORD='replpass';
MariaDB [(none)]> CHANGE MASTER 'm2' TO MASTER_HOST='192.168.8.43',MASTER_USER='repluser',MASTER_PASSWORD='replpass';


启动服务:

MariaDB [db1]> start all slaves;

观察服务是否启动正常:

MariaDB [(none)]> show slave 'm2' status\G

MariaDB [(none)]> show slave 'm1' status\G

报错:

Last_SQL_Error: Error 'Can't create database 'mydb'; database exists' on query. Default database: 'mydb'. Query: 'create database mydb'



解决方法:

主服务器m1:


从服务器执行(192.168.8.44):

MariaDB [(none)]> STOP SLAVE 'm1';

MariaDB [(none)]> CHANGE MASTER 'm1' TO MASTER_HOST='192.168.8.40',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000007',MASTER_LOG_POS=335;


再次验证OK



展开阅读全文

没有更多推荐了,返回首页