High availability clustering and performance tuning for the deployment database

IP planning
Role IP Address host name
Master Database Server 192.168.4.51 master51
Standby 1 master database server 192.168.4.52 master52
Standby 2 master database server 192.168.4.53 master53
1th Slave Server 192.168.4.54 slave54
2nd slave Server 192.168.4.55 Slave55
Mha_manager Server 192.168.4.56 Mgm56
VIP Address 192.168.4.100

One, the configuration of all data node between the host can ssh key to each other authentication login
Second, configure MANAGER56 host without password SSH login all data node host
Third, the configuration master-slave synchronization, the requirements are as follows:
51 Main Library Open semi-synchronous replication
52 semi-synchronous replication from library (alternate main library)
53 semi-synchronous replication from library (alternate main library)
54 No backup main library from library so no half-synchronous replication
55 No backup main library from library so no half-synchronous replication

3.1, MASTER51 configuration:
Vim/etc/my.cnf
[Mysqld]
Plugin-load = "Rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1

server_id=51
Log-bin=master51
binlog-format= "Mixed"
: Wq

[Email protected]~]# systemctl Restart Mysqld

[Email protected] ~]# ls/var/lib/mysql/master51.*
/var/lib/mysql/master51.000001/var/lib/mysql/master51.index

[Email protected] ~]# mysql-uroot-p123456
mysql> grant replication Slave on . to [email protected] "%" identified by "123456";
Query OK, 0 rows affected, 1 Warning (10.04 sec)

mysql> set global Relay_log_purge=off;
Query OK, 0 rows affected (0.15 sec)

Mysql> Show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+-----------------+----------+--------------+------------------+-------------------+
| master51.000001 | 441 | | | |
+-----------------+----------+--------------+------------------+-------------------+
Mysql> quit;

3.2, the configuration of the standby master52
Vim/etc/my.cnf
[Mysqld]
Plugin-load = "Rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1

server_id=52
Log-bin=master52
binlog-format= "Mixed"

]# systemctl Restart Mysqld
[[Email protected]~]# ls/var/lib/mysql/master52.*
/var/lib/mysql/master52.000001/var/lib/mysql/master52.index

[Email protected] ~]# mysql-uroot-p123456

mysql> set global Relay_log_purge=off;
Query OK, 0 rows affected (0.13 sec)

Mysql> Change Master to
-master_host= "192.168.4.51",
-Master_user= "Repluser",
-master_password= "123456",
-master_log_file= "master51.000001",
master_log_pos=441;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

[Email protected] ~]# mysql-uroot-p123456-e "show Slave Status\g" | Grep-i YES
MySQL: [Warning] Using a password on the command line interface can is insecure.
Slave_io_running:yes
Slave_sql_running:yes
[Email protected] ~]#

3.3, the configuration of the standby master53
]# vim/etc/my.cnf
[Mysqld]
Plugin-load = "Rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1

server_id=53
Log-bin=master53
binlog-format= "Mixed"
: Wq

[Email protected] ~]# systemctl restart mysqld
[Email protected] ~]# ls/var/lib/mysql/master53.*
/var/lib/mysql/master53.000001/var/lib/mysql/master53.index
[Email protected] ~]#

[Email protected] ~]# mysql-uroot-p123456
mysql> set global Relay_log_purge=off;
Query OK, 0 rows affected (0.14 sec)

mysql> Change Master to master_host= "192.168.36.51", master_user= "Repluser", master_password= "123456", Master_log_ File= "master51.000001", master_log_pos=441;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> start slave;
Query OK, 0 rows Affected (0.00 sec)

Mysql>

[Email protected] ~]# mysql-uroot-p123456-e "show Slave Status\g" | Grep-i Yes
MySQL: [Warning] Using a password on the command line interface can is insecure.
Slave_io_running:yes
Slave_sql_running:yes

3.4. Configuring from Server 54
[Email protected] ~]# VIM/ETC/MY.CNF
[Mysqld]
server_id=54
: Wq

[Email protected]~]# systemctl Restart Mysqld
[Email protected] ~]# mysql-uroot-p123456
mysql> Change Master to master_host= "192.168.36.51", master_user= "Repluser", master_password= "123456", Master_log_ File= "master51.000001", master_log_pos=441;
Query OK, 0 rows affected, 2 warnings (0.09 sec)

mysql> start slave;
Query OK, 0 rows Affected (0.00 sec)

Mysql> quit;
Bye
[Email protected] ~]#
[Email protected] ~]# mysql-uroot-p123456-e "show Slave Status\g" | Grep-i Yes
MySQL: [Warning] Using a password on the command line interface can is insecure.
Slave_io_running:yes
Slave_sql_running:yes

3.5. Configuring from Server 55
[Email protected] ~]# VIM/ETC/MY.CNF
[Mysqld]
Server_id=55
: Wq

[Email protected] ~]# systemctl restart mysqld
[Email protected] ~]# mysql-uroot-p123456
mysql> Change Master to master_host= "192.168.36.51", master_user= "Repluser", master_password= "123456", Master_log_ File= "master51.000001", master_log_pos=441;
Query OK, 0 rows affected, 2 warnings (0.09 sec)

mysql> start slave;
Query OK, 0 rows Affected (0.00 sec)

Mysql> quit;
Bye
[Email protected] ~]#
[Email protected] ~]# mysql-uroot-p123456-e "show Slave Status\g" | Grep-i Yes
MySQL: [Warning] Using a password on the command line interface can is insecure.
Slave_io_running:yes
Slave_sql_running:yes
[[Email protected]~]#

3.6. Test the master-slave synchronization configuration on the client
3.6.1 Adding authorized users to access data on the main library 51
[[Email protected]~]# mysql-uroot-p123456
Mysql> Grant all on gamedb.* to [e-mail protected] "%" identified by "123456";

3.6.2 database table records in the client host connection Main Library 51
mysql> CREATE DATABASE Gamedb;
Query OK, 1 row affected (0.01 sec)

Mysql> CREATE TABLE gamedb.t1 (id int);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT into GAMEDB.T1 values (999);
Query OK, 1 row affected (0.15 sec)

mysql> INSERT into GAMEDB.T1 values (999);
Query OK, 1 row affected (0.05 sec)

Mysql> select * from Gamedb.t1;
+------+
| ID |
+------+
| 999 |
| 999 |
+------+
2 rows in Set (0.00 sec)

Mysql>

3.6.3 the same library tables and records can be seen on the client using an authorized user connection from library 52-55

#mysql-H from the library IP address-uadmin-p123456
Mysql> select * from Gamedb.t1;
+------+
| ID |
+------+
| 999 |
| 999 |
+------+
4.0.1 Down We configure the proxy server
Configuring MHA Clusters
To install the package:
Installing Perl packages on all hosts (51~56)
Install the Mha_node software package (51~56) on all hosts
Install Mha_manager software packages only on administrative hosts (56)
Check the configuration environment
Copy command (56)
Create working directory and master profile (56)
Create a Failover script (56)
Edit Master Profile app1.cnf
Verifying the configuration
Verify SSH password-free login Data node host
Verifying the master-Slave synchronization configuration of data nodes

Edit the management host master configuration file
]# CP mha4mysql-manager-0.56/bin/*/usr/local/bin/
] #mkdir/etc/mha_manager/
[email protected] mha4mysql-manager-0.56]# CP samples/conf/app1.cnf/etc/mha_manager/
] #vim/etc/mha_manager/app1.cnf

[Server default]
Manager_workdir=/etc/mha_manager
Manager_log=/etc/mha_manager/manager.log
Master_ip_failover_script=/usr/local/bin/master_ip_failover

（
]# Vim/usr/local/bin/master_ip_failover
My $vip = ' 192.168.4.100/24 '; # Virtual IP
My $key = "1";
My $ssh _start_vip = "/sbin/ifconfig eth0: $key $vip";
My $ssh _stop_vip = "/sbin/ifconfig eth0: $key down";
: Wq
) Modify the script file

Ssh_user=root
Ssh_port=22

Repl_user=repluser
repl_password=123456

User=root
password=123456

[Server1]
hostname=192.168.36.51
port=3306
Candidate_master=1

[Server2]
hostname=192.168.36.52
port=3306
Candidate_master=1

[Server3]
hostname=192.168.36.53
port=3306
Candidate_master=1
4.3 Start the Management Service and view the service status
]# Masterha_manager--conf=/etc/mha/app1.cnf
--remove_dead_master_conf--ignore_last_failover
[Server4]
hostname=192.168.36.54
port=3306
No_master=1

[Server5]
hostname=192.168.36.55
port=3306
No_master=1
: Wq

Test SSH

[Email protected] ~]# masterha_check_ssh--conf/etc/mha_manager/app1.cnf
All SSH connection tests passed successfully.

Test Master-Slave synchronization
[Email protected] mhasoft]# MASTERHA_CHECK_REPL--conf/etc/mha_manager/app1.cnf

Start the Management Service and view the service status
]# Masterha_manager--conf=/etc/mha_manager/app1.cnf--ignore_last_failover

Another open terminal
View service status on an administrative host
[Email protected] ~]# Masterha_check_status--conf=/etc/mha_manager/app1.cnf

Take 51 off and see if 52 is the main library.
But 51 is good. Manually configured to 52 from

High availability cluster and performance tuning for the deployment database