MySQL High-availability cluster

MySQL high-availability cluster skill target

• Familiar with MMM high-availability architecture
• Familiarity with MHA high-availability architectures
• Learn to configure MMM high availability
• Learn to configure MHA high availability

About MMM high Availability architecture description

• Mmm_mon: The monitoring process is responsible for all monitoring, determining, and processing node role activities
• Mmm_agent: The agent process running on each MySQL server completes monitoring of probe work and performs simple remote service settings
• Mmm_control: A simple script that provides the process commands for managing Mmm_mond
• MYSQL-MMM's regulation provides multiple virtual IPs (VIPs), including a writable VIP, multiple readable VIPs, and regulated management bindings on top of available MySQL, and when one MySQL shuts down, the supervisor migrates the VIP to other MySQL

Lab environment Mysql-monitor is the monitoring server

Host	Operating System	IP Address	as long as the software
Mysql-master1	Centos 7 x86_64	192.168.32.222	Mysql-mmm
Mysql-master2	Centos 7 x86_64	192.168.32.223	Mysql-mmm
Mysql-slave1	Centos 7 x86_64	192.168.32.224	Mysql-mmm
Mysql-slave2	Centos 7 x86_64	192.168.32.225	Mysql-mmm
Mysql-monitor	Centos 7 x86_64	192.168.32.226	Mysql-mmm
master1,2	Centos 7 x86_64	vip:192.168.32.221	Mysql-mmm
Slave1	Centos 7 x86_64	vip1:192.168.32.220	Mysql-mmm
Slave2	Centos 7 x86_64	vip1:192.168.32.199	Mysql-mmm

Building the master server Master1 modifying the Master1 master configuration file

[[email protected] ~] vim/etc/my.cnf #删除从第一行开始到第九行删除添加下列语句 [client]port = 3306default-character-set=utf8 Socket =/usr/local/mysql/mysql.sock[mysql]port = 3306default-character-set=utf8socket =/usr/local/mysql/mysql.sock [Mysqld]user = Mysqlbasedir =/usr/local/mysqldatadir =/usr/local/mysql/dataport = 3306character_set_server=                                               Utf8pid-file =/usr/local/mysql/mysqld.pidsocket =/usr/local/mysql/mysql.sockserver-id = 1                                          #server-id Each database server value is unique Log-error=/usr/local/mysql/data/mysql_error.log #开启错误日志general_log =on                                       #开启访问日志general_log_file =/usr/local/mysql/data/mysql_general.loglog_bin=mysql-bin #开启二进制日志slow_query_log =on #开启慢日志slow_query_log_                                               File=mysql_slow_query.loglong_query_time=5 log_slave_updates=truesync_binlog=1 #同步auto_increment_increment=2 #增量值auto_increment_offset =1 #起点值binlog-ignore-db= Mysql,information_schema #关闭mysql, information_schema these two binary logs [[email protected] ~]# Systemctl Resta RT Mysqld.service #重启数据库 [[email protected] data]# NETSTAT-ANPT | grep 3306tcp6 0 0::: 3306:::* LISTEN 3074/mysqld #查看端口有没开启

Master2, slave1, slave2 copy master1 configuration file change the Server-id to a different master from synchronous primary master replication, master-Slave synchronization I'm in this one with a command to save time to change the configuration file scp/etc/my.cnf [ email protected]:/etc/Configuring Primary master replication-two primary servers replicate with each other on M1 to grant permissions to M2 on the M2, and also to grant permissions from M1

#先用show Master Status View binary log files and offsets for two primary servers #master1mysql> show Master status;+------------------+----------+------ --------+--------------------------+-------------------+| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+------------------+----------+--------------+--------------------------+------------------- +|      mysql-bin.000004 |              154 | |                   Mysql,information_schema | |+------------------+----------+--------------+--------------------------+-------------------+1 row in Set (0.00 SEC) #master2mysql > show Master status;+------------------+----------+--------------+-------------------------- +-------------------+| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+------------------+----------+--------------+--------------------------+------------------- +|      mysql-bin.000004 |              154 | |                   Mysql,information_schema | |+------------------+----------+--------------+--------------------------+-------------------+1 row in Set (0.00 sec) 

Authorize Master2 to Master1 first.

mysql> grant replication slave on *.* to ‘replication‘@‘192.168.32.%‘ identified by ‘123456‘;  Query OK, 0 rows affected, 1 warning (0.03 sec)mysql> change master to master_host=‘192.168.32.184‘,master_user=‘replication‘,master_passwordd=‘123456‘,master_log_file=‘mysql_bin.000002‘,master_log_pos=154;Query OK, 0 rows affected, 2 warnings (0.04 sec)### 开启同步功能 #slave代表开启同步不是代表从服务器，我上面设置的只是个名称与这边的意思不同mysql> start slave;Query OK, 0 rows affected (0.03 sec)

Authorize Master1 to Master2 first.

mysql> grant replication Slave on * * to ' replication ' @ ' 192.168.32.% ' identified by ' 123456 '; Query OK, 0 rows affected, 1 Warning (0.00 sec) mysql> Change master to master_host= ' 192.168.32.151 ', master_user= ' replic ation ', master_password= ' 123456 ', master_log__file= ' mysql_bin.000004 ', master_log_pos=154; Query OK, 0 rows affected, 2 warnings (0.02 sec) #开启同步功能mysql > start slave; Query OK, 0 rows affected (0.03 sec) #查看IO线程和SQL线程是否都已经开启是否显示为yesmysql >show slave status\g************************** * 1. Row *************************** slave_io_state:waiting for master to send event Master_hos t:192.168.32.151 master_user:replication master_port:3306 Connect_retr Y:60 master_log_file:mysql-bin.000005 read_master_log_pos:154 relay_log_file:local host-relay-bin.000011 relay_log_pos:367 relay_master_log_file:mysql-bin.000005 Slave_I O_runniNg:yes Slave_sql_running:yes Replicate_Do_DB:Replicate_Ignore_DB:Repli                    Cate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:              last_errno:0 last_error:skip_counter:0 exec_master_log_pos:154 relay_log_space:625 Until_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_ser               Ver_cert:no last_io_errno:0 last_io_error:last_sql_errno:0 Last_sql_error:replicate_ignore_server_ids:master_server_id:1 master_uuid:111eb6e9-78d d-11e8-9337-000c2940c252             Master_info_file:/usr/local/mysql/data/master.info sql_delay:0 Sql_remaining_de Lay:null Slave_sql_running_state:slave have read all relay log;      Waiting for more updates master_retry_count:86400 Master_bind:last_io_error_timestamp: Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set : executed_gtid_set:auto_position:0 Replicate_rewrite_db:channel_n Ame:master_tls_version:1 row in Set (0.00 sec) if only the SQL thread is turned on and the IO thread is not turned on then check that the firewall has no shutdown the Enhanced Security module has no shutdown and then executes the following statement Mysql>sto p slave; #先停止slavemysql >reset Slave; #刷新slave同步功能mysql >start Slave; #最后再开启然后查看

Create a database to see if you can synchronize

• Main 1

MariaDB [(none)]> create database info;Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> show databases;+--------------------+| Database           |+--------------------+| information_schema || info               || mysql              || performance_schema || test               |+--------------------+

• Main 2

MariaDB [(none)]> show databases;+--------------------+| Database           |+--------------------+| information_schema || info               || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.00 sec)

• From 1

MariaDB [(none)]> show databases;+--------------------+| Database           |+--------------------+| information_schema || info               || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.00 sec)

• From 2

MariaDB [(none)]> show databases;+--------------------+| Database           |+--------------------+| information_schema || info               || info1              || mysql              || performance_schema || test               |+--------------------+6 rows in set (0.00 sec)

In the creation of a database from 2 to see the Master 1 Master 2 from 1 will not sync will not synchronize the instructions from the server will only synchronize the primary server, the primary server will not synchronize from the server configuration from the server two from the server to be able to synchronize at the same time two primary server installation MMM on all servers installed attention, Epel source to be configured first configure Epel source

配置ALI云源，然后安装epel-release源。wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repoyum clean all && yum makecacheyum -y install epel-releaseyum -y install mysql-mmm*

Modify configuration File//Configure on all hosts, copy multiple copies directly

[[email protected] etc]# cd/etc/mysql-mmm/[[email protected] mysql-mmm]# vim Mmm_common.confactive_master _role writer

Uploading a configuration file

scp mmm_common.conf [email protected]:/etc/mysql-mmm/scp mmm_common.conf [email protected]:/etc/mysql-mmm/scp mmm_common.conf [email protected]:/etc/mysql-mmm/

------------------Configure the----on the monitor (listening) server

#修改IP两主两从的IP地址在ping_ips后面，auto_set_online 自动设置在线时长为10秒<monitor>    ip                  127.0.0.1    pid_path            /run/mysql-mmm-monitor.pid    bin_path            /usr/libexec/mysql-mmm    status_path         /var/lib/mysql-mmm/mmm_mond.status    ping_ips             192.168.32.222,192.168.32.223,192.168.32.224,192.168.32.225    auto_set_online     10cd /etc/mysql-mmm/ //改密码vi mmm_mon.conf

The--------------is authorized for Mmm_moniter and mmm_agent on all databases-----

#为mmm_agent授权MariaDB [(none)]> grant super, replication client, process on *.* to ‘mmm_agent‘@‘192.168.32.%‘ identified by ‘123456‘;Query OK, 0 rows affected (0.01 sec)#为mmm_moniter授权MariaDB [(none)]> grant replication client on *.* to ‘mmm_monitor‘@‘192.168.32.%‘ identified by ‘123456‘; Query OK, 0 rows affected (0.01 sec)# 刷新MariaDB [(none)]> flush privileges;Query OK, 0 rows affected (0.01 sec)

---------------Modify the mmm_agent.conf of all databases----

vi /etc/mysql-mmm/mmm_agent.confthis 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 db4

--------------start the mysql-mmm-agent on all database servers---

#开启服务systemctl start mysql-mmm-agent.service#设置开机自启动systemctl enable mysql-mmm-agent.service#用 mmm_control show 查看各个节点[[email protected] mysql-mmm]# mmm_control show   db1(192.168.32.222) master/ONLINE. Roles: writer(192.168.32.221)  db2(192.168.32.223) master/ONLINE. Roles:   db3(192.168.32.224) slave/ONLINE. Roles: reader(192.168.32.199)  db4(192.168.32.225) slave/ONLINE. Roles: reader(192.168.32.220)

The failure test stops DB1 confirm that virtual address 221 is moved to DB2. Note: The Lord does not preempt

[[email protected] mysql-mmm]# systemctl stop mariadb.service [[email protected] mysql-mmm]# mmm_control show   db1(192.168.32.222) master/HARD_OFFLINE. Roles:   db2(192.168.32.223) master/ONLINE. Roles: writer(192.168.32.221)  db3(192.168.32.224) slave/ONLINE. Roles: reader(192.168.32.199)  db4(192.168.32.225) slave/ONLINE. Roles: reader(192.168.32.220)

Open another MySQL server as the client on the DB1 server for the last client server address authorization logon

MariaDB [(None)]> grant all on * * to ' testdba ' @ ' 192.168.32.222 ' identified by ' 123456 '; MariaDB [(None)]> flush privileges; #客户端登陆虚拟IP [[email protected] ~]# mysql-utestdba-p-h 192.168.32.221 Enter PA  ssWOrd: #设置代理授权是的登陆密码Welcome to the MariaDB Monitor. Commands End With; or \g.your MariaDB connection ID is 1358Server version:5.5.56-mariadb MariaDB servercopyright (c) April, Oracle, Mar IADB Corporation Ab and others. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement. #在客户端创建数据库, Test sync mariadb [(none)]> CREATE DATABASE info; #在客户端创建的Query OK, 1 row affected (0.01 sec) MariaDB [(none)]> show databases; +--------------------+| Database |+--------------------+| Information_schema | | info | | MySQL | | Performance_schema | | Test |+--------------------+5 rows in Set (0.00 sec) #在服务端查询MariaDB [(none)]> show databases; #db1 +--------------------+| Database |+--------------------+| InformAtion_schema | | info | | MySQL | | Performance_schema | | Test |+--------------------+5 rows in Set (0.00 sec) MariaDB [(none)]> show databases; #db2 +--------------------+| Database |+--------------------+| Information_schema | | info | | MySQL | | Performance_schema | | Test |+--------------------+5 rows in Set (0.00 sec) MariaDB [(none)]> show databases; #db3 +--------------------+| Database |+--------------------+| Information_schema | | MySQL | | info | | Performance_schema | | Test |+--------------------+4 rows in Set (0.00 sec) MariaDB [(none)]> show databases; #db4 +--------------------+| Database |+--------------------+| Information_schema | | MySQL | | info | | Performance_schema | | Test |+--------------------+4 rows in Set (0.00 sec) #在客户端创建就是在主服务器上创建从服务器同步

---Authentication will stop one from the server and the other will take over two virtual IPs to ensure that the business does not stop-----

[[email protected] mysql-mmm]# mmm_control show   db1(192.168.32.222) master/ONLINE. Roles:   db2(192.168.32.223) master/ONLINE. Roles: writer(192.168.32.221)  db3(192.168.32.224) slave/ONLINE. Roles: reader(192.168.32.199), reader(192.168.32.220)  

= = The above synchronization is done there is a problem in the Master sync from the time of synchronization is the primary server above the real IP but when the synchronization of the main server down from the server will not be in sync so we set the master-slave synchronization when the IP address with their set VIP (virtual IP) = =

MySQL High-availability cluster