MySQL build master-slave server dual-machine hot standby configuration

First, prepare the environment

　　1. master server A (192.168.0.176), from Server B (192.168.0.13).

2. The MySQL database is deployed on all two servers. (The database version on the two server must be consistent)

3. Create the Rb-test database separately on the two servers and create the data table user.

Second, master server A dual-Machine hot standby configuration steps

1. Create a sync user

Enter the MySQL operator interface to establish a connection account for the slave server on master server A, which must grant replication slave permissions. Since MySQL version 3.2 can be used by the replication to the dual-machine hot standby function operation.

' Zhutong '@'192.168.0.13'zhutong#123'0  1 Warning (0.00  sec) MySQL>0 rows affected (0.00 sec)

2. After creating a sync Connection account, we can see if the connection succeeds by accessing the primary Server A (master) database with the Zhutong account from Server B.

[Email protected] ~]# mysql-h192.168.0.176-uzhutong-pzhutong#123MySQL: [Warning] Using a password on the command lineInterfacecan be insecure.  Welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is  OneServer Version:5.7. +MySQL Community Server (GPL) Copyright (c) -,2018, Oracle and/or its affiliates. All rights reserved. Oracle isA registered trademark of Oracle Corporation and/or Itsaffiliates. Other names trademarks of their respectiveowners. Type'Help ;'Or'\h'  forHelp. Type'\c'To clear the current input statement.mysql>

3. Modify the configuration of the master server a database

vi/etc/my.cnf# plus server1log-bin=mysql-binbinlog-do-db = Rbbinlog -ignore-db = MySQL

4. Restart MySQL Database

Service restart MySQL

5. View the status of the primary server

[[Email protected]0003use-tools]# Mysql-uroot-Penter Password:welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 765Server Version:5.7. +-log MySQL Community Server (GPL) MySQL> Flush table with ReadLock; Query OK,0Rows Affected (0.00sec) MySQL>show Master status\g;***************************1. Row ***************************File:mysql-bin.000006Position:154BINLOG_DO_DB:RB Binlog_ignore_db:mysqlexecuted_gtid_set:1Rowinch Set(0.00sec) Error:no query specified

Note the parameters inside, especially the first two file and position, which can be useful to configure the master-slave relationship from the server (Slave).

6. Lock table

Note: The lock table is used here, in order to generate the environment in the new data, so that from the server location synchronization location, the initial synchronization is completed, remember to unlock.

Unlock tables;

Third, from server B dual machine hot standby configuration steps

　1. Modify the configuration file

vi/etc/my.cnf# Add the following [Mysqld]server2log-bin=mysql-binreplicate-do- db = rbreplicate-ignore-db = Mysql,information_schema,performance_schema

2. Restart the database

Service restart MySQL

3. Specify the synchronization location with the change Mster statement

This step is the most critical step, after entering the MySQL operator interface, enter the following command:

[[Email protected]0002db]# Mysql-uroot-Penter Password:welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 2Server Version:5.7. +-log MySQL Community Server (GPL) MySQL>stop Slave; Query OK,0Rows affected,1Warning (0.00sec) MySQL> Change Master to master_host='192.168.0.176', master_user='Zhutong', master_password='zhutong#123', master_log_file='mysql-bin.000001', master_log_pos=154; Query OK,0Rows affected,2Warnings (0.02sec) #成功后, you can turn on the slave thread. mysql> start slave;
Query OK, 0 rows Affected (0.00 sec)

4. View from server (Slave) status

Mysql>show slave status\g;***************************1. Row ***************************slave_io_state:waiting forMaster to sendEventMaster_host:192.168.0.176Master_user:zhutong Master_port:3306Connect_retry: -Master_log_file:mysql-bin.000006Read_master_log_pos:154Relay_log_file:ecs-d68e-0002-relay-bin.000007Relay_log_pos:367Relay_master_log_file:mysql-bin.000006 slave_io_running:yes slave_sql_running:yes REPLICATE_DO_DB:RB Replicate_ignore_db:mysql,information_schema,performance_schema replicate_do_table:replicate_ignore_t Able:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_error:skip_counter:0Exec_master_log_pos:154Relay_log_space:795until_condition:none Until_log_file:until_log_pos:0Master_ssl_allowed:no Master_SSL_CA_File:Master_SSL_CA_Path:Master_SS L_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0master_ssl_verify_server_cert:no Last_io_errno:0Last_io_error:last_sql_errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:1master_uuid:31e0c365-7e73-11e8-b6ee-fa163ee46a49 master_info_file:/home/zhutong/db/datas/mysql/master.info Sql_delay:0Sql_remaining_delay:null Slave_sql_running_state:slave have read all relay log; waiting forMore updates Master_retry_count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL _crl:master_ssl_crlpath:retrieved_gtid_set:executed_gtid_set:auto_po Sition:0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1Rowinch Set(0.00sec) Error:no query specified

Viewing the following two key values are yes, which means that the setting is successful from the server.
Slave_io_running:yes
Slave_sql_running:yes

Iv. Test Synchronization

　　1. Check the data of RB Database user table in master/slave database (we have only one table in RB database);

Master-A

mysql> use rb;database changedmysql> show tables; +--------------+| TABLES_IN_RB |+--------------+| User         |+--------------+1inset (0.01  sec) MySQLSelect  from  Set (0.00  sec) MySQL

From-B

mysql> use rb;database changedmysql> show tables; +--------------+| TABLES_IN_RB |+--------------+| User         |+--------------+1inset (0.00  sec) MySQLselect  from Set (0.00 sec) 

You can now insert data into the master server to see if it can be synchronized.

　　2, the database write data on the primary server to see if it can be synchronized.

Mysql>Use rb;database changedmysql> INSERT INTO user values ('Zbbiex','15352453201'); ERROR1136(21s01): Column Count doesn't match value count at row 1mysql> INSERT into user values (1,'Zbbiex','15352453201'); Query OK,1Row affected (0.00sec) MySQL>Select* fromuser;+----+--------+-------------+| ID | name | Mobile |+----+--------+-------------+|1| Zbbiex |15352453201|+----+--------+-------------+1Rowinch Set(0.00sec) MySQL>

3, on the server B to see if the data has been synchronized past

mysql> use rb;database changedmysqlSelect from user; +----+--------+-------------+| ID | Name   | Mobile      |+----+--------+-------------+|  1 15352453201 |+----+--------+-------------+1inset (0.00 sec) MySQL

You can see that the data has been synced in the past, completely no problem;

MySQL build master-slave server dual-machine hot standby configuration