流行的mysql集群的使用
环境
服务器环境是Centos7.8,MariaDB版本 5.5.65-MariaDB
| 主机 | ip | 备注 |
| mmm-monitor | 192.168.137.124 | 独立机器 |
| mmm-master1 | 192.168.137.201 | 与master2互为备份，是slave1的主 |
| mmm-master2 | 192.168.137.202 | 和master互为高可用。 |
| mmm-slave1 | 192.168.137.203 | 是master1的从 |
虚拟ip： 192.168.137.100-102
总图架构。
安装mariadb
如果要安装mariadb版本，添加仓库,然后可以安装Mariadb 10的版本
echo <<EOF >/etc/yum.repos.d/maraidb.repo
[mariadb]
name = MariaDB
baseurl = http://mirrors.aliyun.com/mariadb/yum/10.4/centos7-amd64/
gpgkey = http://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck = 1
EOF1） 在mmm-master,mmm-master2,mmm-slave2 上安装mariadb
在这三台机器上 执行查找我们需要安装的版本
[root@mmm-monitor ~]# yum list |grep mariadb
mariadb.x86_64 1:5.5.65-1.el7 @base
mariadb-libs.x86_64 1:5.5.65-1.el7 @anaconda
mariadb-bench.x86_64 1:5.5.65-1.el7 base
mariadb-devel.i686 1:5.5.65-1.el7 base
mariadb-devel.x86_64 1:5.5.65-1.el7 base
mariadb-embedded.i686 1:5.5.65-1.el7 base
mariadb-embedded.x86_64 1:5.5.65-1.el7 base
mariadb-embedded-devel.i686 1:5.5.65-1.el7 base
mariadb-embedded-devel.x86_64 1:5.5.65-1.el7 base
mariadb-libs.i686 1:5.5.65-1.el7 base
mariadb-server.x86_64 1:5.5.65-1.el7 base
mariadb-test.x86_64 1:5.5.65-1.el7 ba2）安装到上述三台机器上
yum install mariadb.x86_64 mariadb-server.x86_64 -y
安装MMM
1) 在所有机器上安装epel源, 然后安装 mmm监控和代理
yum -y install epel-releaseyum -y install mysql-mmm-agent
yum -y install mysql-mmm-monitor
测试并且配置maraidb
1）默认启用maraidb,并启动，在mmm-maste1,mmm-master2,mmm-slave1上都要去执行。
[root@mmm-master1 ~]# systemctl enable mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
root@mmm-master1 ~]# systemctl start mariadb
[root@mmm-master1 ~]# mysql -uroot -p -hlocalhost
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.65-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> exit
Bye
[root@mmm-master1 ~]# mysql -uroot -p -hlocalhost
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.65-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> 2）配置所有服务器的host文件 ,在四台机器上都需要配置。
[root@mmm-monitor ~]# cat <<EOF >>/etc/hosts
> 192.168.137.124 mmm-monitor
> 192.167.137.201 mmm-master1
> 192.167.137.202 mmm-master2
> 192.167.137.203 mmm-slave1
> EOF3）参照官方文档 https://mariadb.com/kb/en/setting-up-replication/ 进行配置。
在master1的机器上/etc/my.cnf 增加如下配置。注意不要配置到mysqld段。
[mariadb]
log-bin
server_id=1
log-basename=master1
binlog-format=mixed在master1上增加复制的用户,进入到mysql命令行执行如下语句
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.65-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> CREATE USER 'repl_user'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]>flush privilegs #这个不能缺少，更新权限表。
由于要进行复制，注意好服务器的侦听的地址不能是127.0.0.1（bind_address）,参数skip-networks 应该为0.
为了使用mmm-monitor在master1上操作，再创建如下三个账户
GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'192.168.137.%' IDENTIFIED BY '123456';GRANT SUPER,REPLICATION CLIENT,PROCESS ON *.* TO 'mmm_agent'@'192.168.137.%' IDENTIFIED BY '123456';重启master1上的Mariadb，然后执行SHOW MASTER STATUS 命令看log-bin已经生成。
MariaDB [(none)]> show master status-> ;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| master1-bin.000001 | 245 | | |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)修改slave1上的配置文件
[mariadb]
server_id=3重启启动slave1上的MariaDB服务器。然后执行
change master to master_host='192.168.137.201', master_user='repl_user', master_password='123456';
Query OK, 0 rows affected (1.36 sec)在启动slave，start slave命令，然后看状态。
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.137.201Master_User: repl_userMaster_Port: 3306Connect_Retry: 60Master_Log_File: master1-bin.000001Read_Master_Log_Pos: 245Relay_Log_File: mariadb-relay-bin.000002Relay_Log_Pos: 531Relay_Master_Log_File: master1-bin.000001Slave_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: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 245Relay_Log_Space: 827Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1
1 row in set (0.00 sec)MariaDB [(none)]> 如果出现 slave_sql_running=no,可以使用规则去做
解决办法
首先停掉Slave服务：slave stop
到主服务器上查看主机状态：
记录File和Position对应的值
进入master
mysql> show master status;
±---------------------±---------±-------------±-----------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
±---------------------±---------±-------------±-----------------+
| localhost-bin.000094 | 33622483 | | |
±---------------------±---------±-------------±-----------------+
1 row in set (0.00 sec)然后到slave服务器上执行手动同步：
mysql> change master to
master_host=‘192.168.137.141’,
master_user=‘repl_user’,
master_password=‘123456’,
master_log_file=localhost-bin.000094’,
master_log_pos=33622483 ;
1 row in set (0.00 sec)
mysql> start slave ;
1 row in set (0.00 sec)一般就将出现的错误语句不在执行。
如果所有的mysql都是重新安装的，那么使用上述的方法是可行的。实际出现的错误是mysql,test数据库同步出现的问题。也可以在配置文件当中设置
binlog-ignore-db=mysql,test 进行忽略
以上是slave1为从，master1为主，我们设置master2和master1互为主从。
使用master2同master1一样的设置，包括配置文件，创建复制用户，mmm的2个用户。
并且在master1的mysql客户端执行如下
change master to master_host='192.168.137.202', master_user='repl_user', master_password='123456';
Query OK, 0 rows affected (1.36 sec)master2上执行同slave1上一样的
change master to master_host='192.168.137.201', master_user='repl_user', master_password='123456';
Query OK, 0 rows affected (1.36 sec)使用show slave status \G （\G表示格式化输出），可以看到已经成功。
在master1上执行，创建数据库。
MariaDB [(none)]> create database replication-test;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-test' at line 1
MariaDB [(none)]> create database repltest;
Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> 在master2上查看
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| repltest |
| test |
+--------------------+
5 rows in set (0.02 sec)
在slave1上查看
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| repltest |
| test |
+--------------------+
5 rows in set (0.02 sec)
在master2上创建数据库 frommaster2
MariaDB [(none)]> create database frommaster2;
Query OK, 1 row affected (0.00 sec)在master1即可；
如果在 show slave status \G 中出现错误，修改配置文件即可。
Last_SQL_Errno: 1396Last_SQL_Error: Error 'Operation CREATE USER failed for 'repl_user'@'%'' on query. Default database: ''. Query: 'CREATE USER 'repl_user'@'%' IDENTIFIED BY '123456''请在当前的my.cnf 的mariadb段下增加slave-skip-errors = 1062,1146,1396注意：如果不需要同步某些数据库，直接在配置文件中增加
# 不同步哪些数据库 binlog-ignore-db = mysql binlog-ignore-db = test binlog-ignore-db = information_schema # 只同步哪些数据库，除此之外，其他不同步 binlog-do-db = game
MMM的配置
我们定义master1为db1，master2为db2，slave1为db3，我们先在master1上进行配置/etc/mysql-mmm/mmm-common.conf文件,mmm_agent文件是agent文件，common文件为基础配置文件，monitor文件为monitor服务器上需要配置的文件。
拷贝mmm_common.conf到其他三台服务器相同位置，保护monitor服务器。
[root@mmm-master1 mysql-mmm]# vi mmm_common.conf
active_master_role writer<host default>cluster_interface enp0s3 #这个是你网卡名字，不能错，否则不能分配虚拟IPpid_path /run/mysql-mmm-agent.pidbin_path /usr/libexec/mysql-mmm/replication_user repl_user #复制账号replication_password 123456 #复制的账号密码agent_user mmm_agent #agent的账户，先前已经创建过agent_password 123456 #agent账户的密码
</host><host db1>ip 192.168.137.201 #master1，这里定义成db1.ip地址mode master #角色peer db2 #与master1对等的服务器的host名，也就是master2的服务器host名
</host><host db2>ip 192.168.137.202mode masterpeer db1
</host><host db3>ip 192.168.137.203mode slave
</host><role writer>hosts db1, db2ips 192.168.137.100mode exclusive
</role><role reader>hosts db1, db2ips 192.168.137.101, 192.168.137.102 #只读的虚拟IP，数量可以多于hosts，这样一个服务可以绑定几个虚拟IPmode balanced
</role>然后配置master1的agent文件，实际上master1上不要修改，但其他的要相应修改 "this db1"
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 db1分别把mmm_common.conf文件复制到master2,slave1,然后修改每台服务器上的mmm_agent.conf，master2为db2，slave1为db3.
再到124的机器上配置mmm_mon.conf文件。
<monitor>ip 192.168.137.124pid_path /run/mysql-mmm-monitor.pidbin_path /usr/libexec/mysql-mmmstatus_path /var/lib/mysql-mmm/mmm_mond.statusping_ips 192.168.137.201,192.168.137.202,192.168.137.203auto_set_online 60# 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><host default>monitor_user mmm_monitormonitor_password 123456
</host>debug 0测试高可用
#每台数据机器上执行
systemctl restart mysql-mmm-agent
#在monitor上执行
systemctl restart mysql-mmm-monitor然后使用 命令查看
[root@mmm-monitor mysql-mmm]# mmm_control showdb1(192.168.137.201) master/ONLINE. Roles: reader(192.168.137.101)db2(192.168.137.202) master/ONLINE. Roles: reader(192.168.137.102), writer(192.168.137.100)db3(192.168.137.203) slave/ONLINE. Roles:
到db1(master1)上看ip地址，101已经分配好了
[root@mmm-master1 mysql-mmm]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope host valid_lft forever preferred_lft forever
2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 08:00:27:c6:6c:52 brd ff:ff:ff:ff:ff:ffinet 192.168.137.201/24 brd 192.168.137.255 scope global noprefixroute enp0s3valid_lft forever preferred_lft foreverinet 192.168.137.101/32 scope global enp0s3valid_lft forever preferred_lft foreverinet6 fe80::be0a:e8f2:197e:be87/64 scope link tentative noprefixroute dadfailed valid_lft forever preferred_lft foreverinet6 fe80::3445:c251:976:e8c6/64 scope link noprefixroute valid_lft forever preferred_lft forever测试链接不上，没有授权
[root@mmm-master1 mysql-mmm]# mysql -uroot -h192.168.137.101
ERROR 1045 (28000): Access denied for user 'root'@'192.168.137.101' (using password: NO)修改下ip地址授权，成功！