MYSQL_ two servers for dual-machine configuration and test data synchronization

For the realization of the mutual configuration of the two machines, this article made a detailed introduction, after the test data synchronization, in the 10.168.1.44 server database to modify a piece of data, you can see the data has been synchronized. In turn, modifying the 10.168.0.126 data can also be seen in 10.168.1.44 the corresponding table data in the database changes. In this connection, 10.168.0.126 and 10.168.1.44 are primarily from the database. Apache PHP MySQL

Pre-preparation

Two servers: 10.168.1.44

10.168.0.126

Operating environment: Linux system (Centos6.5)

MySQL version: 5.7.22

Modify Configuration

The information for modifying the/etc/my.conf configuration file on both servers is as follows:

In the 10.168.1.44 server/etc/my.conf configuration file, add:

server_id=10

LOG-BIN=MASTER_01//Turn on the binary log, which is what another server can use to determine the execution of an action

binlog-do-db=test_db//Synchronized tables

Binlog-do-db=my_test//Synchronized tables

In the 10.168.0.126 server/etc/my.conf configuration file, add:

Server_id=20

LOG-BIN=MASTER_02//Turn on the binary log, which is what another server can use to determine the execution of an action

binlog-do-db=test_db//Synchronized tables

Binlog-do-db=my_test//Synchronized tables

Add after execute command service mysqld Restart Restart the database for changes to take effect

Add MySQL Account

Add a MySQL account to perform data synchronization for its authorized users

10.168.1.44 Execute Command:

GRANT FILE on *. copyuser ' @ ' 10.168.0.126 ' identified by ' admin@123 '; GRANT REPLICATION SLAVE on * * to ' copyuser ' @ ' 10.168.0.126 ' identified by ' admin@123 '; flush privileges;

10.168.0.126 Execute Command:

GRANT FILE on *. copyuser ' @ ' 10.168.1.44 ' identified by ' admin@123 '; GRANT REPLICATION SLAVE on * * to ' copyuser ' @ ' 10.168.1.44 ' identified by ' admin@123 '; flush privileges;

Configuration from the database

10.168.1.44 configuration:

To view the current primary database status:

Mysql> Show master status;

Records the current value of file and position;

Enter the 10.168.0.126 Access database to view its primary database status

Executed at 10.168.1.44

Mysql>change MASTER tomaster_host= ' 10.168.0.126 ', master_user= ' copyuser ', master_password= ' admin@123 ', master_ Port=3306,master_log_file= ' master_02.000002 ', master_log_pos=1771,master_connect_retry=10; execution in 10.168.0.126: MySQL >change MASTER tomaster_host= ' 10.168.1.44 ', master_user= ' copyuser ', master_password= ' admin@123 ', master_port= 3306,master_log_file= ' master_01.000008 ', master_log_pos=154,master_connect_retry=10; note: If the above command cannot be executed with the slave open state, you need to first execute stop slave; Close slave, execute start slave after executing the above command; command to turn on slave. After the above command finishes, view the status from service: Execute command:mysql> show slave status\g;*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos              t:10.168.1.44 Master_user:copyuser master_port:3306 Connect_retry:10 master_log_file:master_01.000008 read_master_log_pos:154 Relay_log_file:cdh-2-relay -bin.000004 relay_log_pos:367 Relay_master_log_file:master_01.000008 Slave_io_running:yes Slave_sql_running:yes Replicat E_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_ table:replicate_wild_ignore_table:last_errno:0 Last_error:skip_co               unter:0 exec_master_log_pos:154 relay_log_space:740 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:Se               Conds_behind_master:0master_ssl_verify_server_cert:no last_io_errno:0 Last_io_error:                  last_sql_errno:0 last_sql_error:replicate_ignore_server_ids:master_server_id:10 Master_uuid:778beb1e-8f0f-11e8-a815-00505695cd8c master_info_file:/var/lib/mysql/master.info SQL_ delay:0 Sql_remaining_delay: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_name: Master_tls_version:1 row in Set (0.00 sec)

Note Slave_io_running:yes and Slave_sql_running:yes, only if two are Yes is the configuration is successful.

Test data synchronization

Modify a single piece of data in the 10.168.1.44 server database:

Before modification:

After modification:

To view the data in the corresponding table in the 10.168.0.126 database:

You can see that it has been synced over.

In turn, modifying the 10.168.0.126 data can also be seen in 10.168.1.44 the corresponding table data in the database changes.

In this connection, 10.168.0.126 and 10.168.1.44 are mainly from the database

There may be a problem

1. When viewing the Slave status, you will find slave_io_running:connecting

There are three main causes of this problem:

1. Network does not pass (ping each other to try to ping through)

2. Incorrect password: View the correct password in the command executed when configuring slave

3. Position incorrect: The position is not filled with the correct position when configuring slave (view the master state from the server database: Show Master status can be found)

I have this problem because the user was created on only one server ' Copyuser ' for synchronizing the data, and the user was not created in the other server database. OK after the creation .

4. When viewing the Slave status, you will find slave_sql_running:no

The main reason for this phenomenon is that both sides of the database data are different, you can check the MySQL log to locate the specific block of data anomalies

MySQL logs generally in/var/log/mysqld.log

Note that if you only configure data from the database to synchronize the primary database without being set to synchronize with each other, modifying the data from the database may cause synchronization to fail.

Related articles:

Mysql database dual-Machine hot standby configuration _mysql

MySQL real-time synchronization-dual-machine Interoperability (dual master)

Related videos:

MySQL Data management backup recovery case Resolution video Tutorial