欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MariaDB 10 (MySQL DB) 多主复制并实现读写分离_MySQL

程序员文章站 2024-02-02 13:51:22
...
MariaDB

二、资源配置

  • 主机属性

系统 名字 角色 主机名 ip地址 关系
Centos6.5x86_64 DB1 Master essun.mariadb1.com 192.168.1.109 与DB2互为主从
Centos6.5x86_64 DB2 Master essun.mariadb2.com 192.168.1.112 与DB1互为主从
Centos6.5x86_64 DB3 Slave essun.mariadb3.com 192.168.1.113 DB1的从库
Centos6.5x86_64 Monitor Monitor essun.monitor.com 192.168.1.116 监控所有主机
  • 虚拟ip(VIP)

DB1 192.168.1.109 192.168.1.24

DB2 192.168.1.112 192.168.1.24,192.168.1.22

DB3 192.168.1.113 192.168.1.23

三、拓扑图

MariaDB 10 (MySQL DB) 多主复制并实现读写分离_MySQL

四、实现过程

1、配置DB1

修改配置文件/etc/my.cnf,添加如下语句

server-id=1log_bin=/mariadb/data/mysql-binbinlog_format=rowlog-slave-updates sync_binlog=1auto_increment_increment=2# 默认地,AUTO_INCREMENT 的开始值是 1,每条新记录递增 1。auto_increment_offset=1 

授权用户

MariaDB [(none)]> grant replication slave,replication client on *.* to 'repluser'@'192.168.1.112' identified by 'replpass';Query OK, 0 rows affected (0.12 sec)MariaDB [(none)]> grant replication slave,replication client on *.* to 'repluser'@'192.168.1.113' identified by 'replpass';Query OK, 0 rows affected (0.00 sec)

查看binlog日志标记

MariaDB [(none)]> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000003 |756 |||+------------------+----------+--------------+------------------+1 row in set (0.00 sec)

2、配置DB2

修改配置文件/etc/my.cnf,添加如下语句

log-bin=mysql-binbinlog_format=ROWlog-slave-updatessync_binlog=1auto_increment_increment=2auto_increment_offset=2server-id=2

授权用户

MariaDB [(none)]> grant replication slave,replication client on *.* to 'repluser'@'192.168.1.109' identified by 'replpass';Query OK, 0 rows affected (0.15 sec)

查看binlog日志标记

MariaDB [(none)]> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000007 |548 |||+------------------+----------+--------------+------------------+1 row in set (0.00 sec)

连接DB1

MariaDB [(none)]> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000007 |548 |||+------------------+----------+--------------+------------------+1 row in set (0.00 sec)

3、配置DB3

修改配置文件/etc/my.cnf添加如下语句

server-id=3log-bin=mysql-binlog-slave-updates
relay-log=relay-log-bin

连接DB1

MariaDB [(none)]> change master to master_host='192.168.1.109',master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000003',master_log_pos=756;Query OK, 0 rows affected (0.03 sec)MariaDB [(none)]> start slave;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show slave status/G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.1.109Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 756 Relay_Log_File: relay-log-bin.000002Relay_Log_Pos: 535Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 756Relay_Log_Space: 830Until_Condition: None Until_Log_File:Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/slave/cacert.pem Master_SSL_CA_Path:Master_SSL_Cert: /etc/slave/mysql.crtMaster_SSL_Cipher: Master_SSL_Key: /etc/slave/mysql.keySeconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error:Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: /etc/slave/cacert.pem Master_SSL_Crlpath: Using_Gtid: NoGtid_IO_Pos:1 row in set (0.00 sec) 

DB1连接DB2

MariaDB [(none)]> change master to master_host='192.168.1.112',master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000007',master_log_pos=548;Query OK, 0 rows affected (0.03 sec)MariaDB [(none)]> start slave;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show slave status/G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.1.112Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000007Read_Master_Log_Pos: 548 Relay_Log_File: essun-relay-bin.000002Relay_Log_Pos: 535Relay_Master_Log_File: mysql-bin.000007 Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 548Relay_Log_Space: 832Until_Condition: None Until_Log_File:Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher: Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error:Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: NoGtid_IO_Pos:1 row in set (0.00 sec)

4、测试

在DB2中建立一个数据库testdb

MariaDB [(none)]> change master to master_host='192.168.1.112',master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000007',master_log_pos=548;Query OK, 0 rows affected (0.03 sec)MariaDB [(none)]> start slave;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show slave status/G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.1.112Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000007Read_Master_Log_Pos: 548 Relay_Log_File: essun-relay-bin.000002Relay_Log_Pos: 535Relay_Master_Log_File: mysql-bin.000007 Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 548Relay_Log_Space: 832Until_Condition: None Until_Log_File:Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher: Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error:Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: NoGtid_IO_Pos:1 row in set (0.00 sec)

在DB1中对testdb,插入一条数据

MariaDB [testdb]> insert t1 values ('tom',24);Query OK, 1 row affected (0.01 sec)

在DB3中查看结果

MariaDB [(none)]> select * from testdb.t1;+------+-----+| name | age |+------+-----+| tom|24 || king |24 |+------+-----+2 rows in set (0.00 sec)MariaDB [(none)]>

OK!三台DB的主从配置正常工作

在DB1~3上安装mysql-mmmo-agent

注:mysql-mmm-agent是在epel源中,所以要下载EPEL源安装包即可http://download.fedoraproject.org/pub/epel/6/i386/repoview/epel-release.html

下载对应的版本就可以的。

#rpm -ivh epel-release-6-8.noarch.rpmyum -y install mysql-mmm-agent

每一个节点都要安装

在每一个节点上要给Monitor授权用户

MariaDB [(none)]> GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'192.168.1.116' IDENTIFIED BY '123456';Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]> GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'192.168.1.116' IDENTIFIED BY '123456';Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]> GRANT REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.1.116' IDENTIFIED BY 'replpass';Query OK, 0 rows affected (0.01 sec)

6、在Monitor节点上要安装

#yum -y install mysql-mmm*

此包同样也在epel源中

7、在Monitor端的设置/etc/mysql-mmm/mmm_common.conf

active_master_role	writer	cluster_interface	 eth0	pid_path				/var/run/mysql-mmm/mmm_agentd.pid	bin_path				/usr/libexec/mysql-mmm/	replication_user		repluser #复制用户	replication_password	replpass #复制密码	agent_user			mmm_agent #代理用户	agent_password		123456 #代理用户的密码	ip	192.168.1.109	mode	master	peer	db2	ip	192.168.1.112	mode	master	peer	db1	ip	192.168.1.113	mode	slave	hosts db1, db2	ips	 192.168.1.24	mode	exclusive #排它	hosts	db2, db3	ips	192.168.1.22, 192.168.1.23	mode	balanced #均衡

将此文件分发到各DB1~3中的/etc/mysql-mmm/下

8、每一个DB中都会有mmm_agent的配置文件,编辑mmm_agent.conf

在数据库服务器上,还有一个mmm_agent.conf需要修改,其内容是:

include mmm_common.conf# The 'this' variable refers to this server.Proper operation requires# that 'this' server (db1 by default), as well as all other servers, have the# proper IP addresses set in mmm_common.conf.this db2

第一行表示:将之前Monitor中的mmm_common.conf文件载入到此文件中,供此文件中的参数设用。

最后一行标记此主机的角色(引用mmm_common.conf中的host段)在不同的数据库服务器上要分别改为db1和db3否则代理就会无法启动。

9、编辑mmm_mon.confg

在Monitor上,修改mmm_mon.conf文件,修改后内容为:

include mmm_common.conf	ip				192.168.1.116 #当前monitor主机地址	pid_path			/var/run/mysql-mmm/mmm_mond.pid	bin_path			/usr/libexec/mysql-mmm	status_path		 /var/lib/mysql-mmm/mmm_mond.status	ping_ips			192.168.1.109, 192.168.1.112 #真实DB地址	auto_set_online	 10	# The kill_host_bin does not exist by default, though the monitor will	# throw a warning about it missing.See the section 5.10 "Kill Host	# Functionality" in the PDF documentation.	#	# kill_host_bin	 /usr/libexec/mysql-mmm/monitor/kill_host	#	monitor_user		mmm_monitor #监控DB的用户名	monitor_password	123456 #密码debug 0 #关闭debug功能,如果程序无法监控得到,可以使用debug 1查错

10、启动MMM

在各DB端启动mmm-agent

#cd /etc/init.d/# chkconfig mysql-mmm-monitor on# service mysql-mmm-monitor start

在Monitor端启动监控程序

#cd /etc/init.d/# chkconfig mysql-mmm-monitor on# service mysql-mmm-monitor start

过几秒钟,就可以使用mmm_control show查看在线监控端(DB)了

[root@essun ~]# service mysql-mmm-monitor statusmmm_mond (pid5395) is running...[root@essun ~]# mmm_control showdb1(192.168.1.109) master/ONLINE. Roles:db2(192.168.1.112) master/ONLINE. Roles: reader(192.168.1.22), writer(192.168.1.24)db3(192.168.1.113) slave/ONLINE. Roles: reader(192.168.1.23)

注:可以使用

[root@essun ~]# mmm_control --helpInvalid command '--help'Valid commands are:	help							- show this message	ping							- ping monitor	show							- show status	checks [|all [|all]] - show checks status	set_online 				 - set host  online	set_offline 				- set host  offline	mode							- print current mode.	set_active						- switch into active mode.	set_manual						- switch into manual mode.	set_passive					 - switch into passive mode.	move_role [--force]  - move exclusive role  to host 										(Only use --force if you know what you are doing!)	set_ip 				- set role with ip  to host 

查看mmm_control的可用参数

11、模拟DB2下线

Monitor当前状态

MariaDB 10 (MySQL DB) 多主复制并实现读写分离_MySQL

让DB2下线,当前可写主机是db1,db3

MariaDB 10 (MySQL DB) 多主复制并实现读写分离_MySQL

db2没有下线之前还可以读写,当下线之后,可写的切换到DB1上了,所有读的都到了db3上了

当DB2重新上线后的情况如下

MariaDB 10 (MySQL DB) 多主复制并实现读写分离_MySQL

注:DB1、DB同时只能一有个写,一个读!

========================================== Mariadb高可用演示完毕========================