Shulou(Shulou.com)06/01 Report--

IP planning

Role IP address hostname

Master database server 192.168.4.51 master51

Standby 1 master database server 192.168.4.52 master52

Backup 2 master database server 192.168.4.53 master53

1st 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

Configure that hosts of all data nodes can authenticate and log in to each other by means of ssh key pairs

Configure manager56 host password-free ssh to log in to all data node hosts

3. Configure master-slave synchronization. The requirements are as follows:

51 open semi-synchronous replication of main library

52 open semi-synchronous replication from the library (standby primary database)

53 semi-synchronous replication from the library (standby primary library)

54 the slave library does not act as a standby primary database, so there is no need to open semi-synchronous replication.

55 the slave library does not act as a backup primary database, so there is no need to open semi-synchronous replication.

3. 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

[root@host51~] # systemctl restart mysqld

[root@host51 ~] # ls / var/lib/mysql/master51.*

/ var/lib/mysql/master51.000001 / var/lib/mysql/master51.index

[root@host51] # mysql-uroot-p123456

Mysql > grant replication slave on. To repluser@ "%" 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.Configuration of 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

[root@host52~] # ls / var/lib/mysql/master52.*

/ var/lib/mysql/master52.000001 / var/lib/mysql/master52.index

[root@host52] # 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)

[root@host52 ~] # mysql-uroot-p123456-e "show slave status\ G" | grep-I YES

Mysql: [Warning] Using a password on the command line interface can be insecure.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

[root@host52 ~] #

3.3.Configuration of 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

[root@host53 ~] # systemctl restart mysqld

[root@host53 ~] # ls / var/lib/mysql/master53.*

/ var/lib/mysql/master53.000001 / var/lib/mysql/master53.index

[root@host53 ~] #

[root@host53] # 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 >

[root@host53 ~] # mysql-uroot-p123456-e "show slave status\ G" | grep-I yes

Mysql: [Warning] Using a password on the command line interface can be insecure.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

3.4. Configure slave server 54

[root@host54 ~] # vim / etc/my.cnf

[mysqld]

Server_id=54

: wq

[root@host54~] # systemctl restart mysqld

[root@host54] # 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

[root@host54 ~] #

[root@host54 ~] # mysql-uroot-p123456-e "show slave status\ G" | grep-I yes

Mysql: [Warning] Using a password on the command line interface can be insecure.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

3.5. Configure slave server 55

[root@host55 ~] # vim / etc/my.cnf

[mysqld]

Server_id=55

: wq

[root@db111 ~] # systemctl restart mysqld

[root@db111] # 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

[root@host55 ~] #

[root@host55 ~] # mysql-uroot-p123456-e "show slave status\ G" | grep-I yes

Mysql: [Warning] Using a password on the command line interface can be insecure.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

[root@host55~] #

3.6. Test the master-slave synchronization configuration on the client side

3.6.1 add an authorized user to access data on the main library 51

[root@host51~] # mysql-uroot-p123456

Mysql > grant all on gamedb.* to admin@ "" identified by "123456"

3.6.2 create a database table record when the client host connects to the main library 51

Mysql > create database gamedb

Query OK, 1 row affected (0.01sec)

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.05sec)

Mysql > select * from gamedb.t1

+-+

| | id |

+-+

| | 999 |

| | 999 |

+-+

2 rows in set (0.00 sec)

Mysql >

3.6.3 you can also see the same database tables and records by using authorized users to connect to slave library 52-55 on the client side.

# mysql-h Slave IP address-uadmin-p123456

Mysql > select * from gamedb.t1

+-+

| | id |

+-+

| | 999 |

| | 999 |

+-+

4.0.1 Let's configure the proxy server

Configure MHA Cluster

Install the package:

Install the perl package (51x56) on all hosts

Install the mha_node package (51x56) on all hosts

Install the mha_manager package on the management host only (56)

Check the configuration environment

Copy command (56)

Create a working directory and main configuration file (56)

Create a failover script (56)

Edit the main configuration file app1.cnf

Verify the configuration

Verify ssh password-free login to the data node host

Verify the master-slave synchronization configuration of the data node

Edit the management host master configuration file

] # cp mha4mysql-manager-0.56/bin/* / usr/local/bin/

] # mkdir / etc/mha_manager/

[root@host56 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

(

[root@host56 ~] # cd mha4mysql-manager-0.56/samples/scripts/

[root@host56 scripts] # ls

Master_ip_failover master_ip_online_change power_manager send_report

[root@host56 scripts] # cp master_ip_failover / usr/local/bin/

] # vim / usr/local/bin/master_ip_failover

My $vip = '192.168.4.100 Universe 2400; # 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

[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

[root@host56] # masterha_check_ssh-- conf / etc/mha_manager/app1.cnf

All SSH connection tests passed successfully.

Test master-slave synchronization

[root@host56 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

Open another terminal

View service status on the management host

[root@host56] # masterha_check_status-- conf=/etc/mha_manager/app1.cnf

After shutting down 51, see if 52 is the main library.

But when 51 is ready, it needs to be manually configured to 52 from

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.