Two mysql servers realize dual-machine mutual backup configuration and test data synchronization

This article provides a detailed introduction to the mutual backup configuration of two machines. After the test data is synchronized, modify a piece of data in the 10.168.1.44 server database, and you can see that the data has been synchronized. . Conversely, if you modify the data of 10.168.0.126, you can also see the corresponding table data changes in the 10.168.1.44 database. At this point, 10.168.0.126 and 10.168.1.44 have a master-slave database relationship with each other.

Recommended related mysql video tutorials: "mysql tutorial"

apache php mysql

Preliminary preparation

Two servers: 10.168.1.44

10.168.0.126

Running environment: Linux system (Centos6.5)

Mysql version: 5.7.22

Modify configuration

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

Add in the 10.168.1.44 server/etc/my.conf configuration file:

server_id=10

log-bin=master_01 //Enable the binary log, so that another server can use the log to determine the execution operation

binlog-do-db=test_db //Synchronized table

binlog-do-db=my_test //The synchronized table

is added to the 10.168.0.126 server/etc/my.conf configuration file:

server_id=20

log-bin=master_02 //Open Binary log, the function is that another server can use the log to determine the execution operation

binlog-do-db=test_db //Synchronized table

binlog-do-db=my_test //Synchronization After adding the table

execute the command service mysqld restart to restart the database to make the modification effective

Add mysql account

Add a mysql account and perform data synchronization by giving its authorized users

10.168.1.44 execution command:

GRANT FILE ON *.* TO '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 execution command:

GRANT FILE ON *.* TO '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;

Configure the slave database

##10.168.1.44 configuration:

View the current master database status:

mysql> show master status;

Record the current file and position values;

Enter 10.168.0.126 to access the database to view its master database status

Execute on 10.168.1.44

mysql>CHANGE MASTER TO

MASTER_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;

在10.168.0.126执行：

mysql>CHANGE MASTER TO

MASTER_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;

注：若slave开启状态无法执行以上命令，需要首先执行 stop slave；关闭slave，执行完上述命令后执行start slave；命令开启slave。

上述命令执行完后，查看从服务状态：

执行命令：

mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 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

              Replicate_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_Counter: 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:

        Seconds_Behind_Master: 0

Master_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 has 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, the configuration is successful only when both are yes.

Test data synchronization

Modify a piece of data in the 10.168.1.44 server database:

Before modification:

After modification:

## Check the data in the corresponding table in the 10.168.0.126 database:

You can see that it has been synchronized.

Conversely, if you modify the data of 10.168.0.126, you can also see the changes in the corresponding table data in the 10.168.1.44 database.

At this point, the mutual master-slave database relationship between 10.168.0.126 and 10.168.1.44

##may have problems

When you check the slave status, you will find that

Slave_IO_Running: Connecting

1. ##There are three main reasons for this problem:

The network is unavailable (try pinging each other to see if they can ping successfully)

1. Password Incorrect: Check whether the password in the command executed when configuring the slave is correct

2. ##Position is incorrect: When configuring the slave, the corresponding position is not filled in with the correct position (check Corresponding to the master status of the slave server database: show master status can be found)

The reason why I have this problem is that the user 'copyuser' used to synchronize data is only created on one server, and the user is not created in the database of the other server. OK after creation.

4. When checking the slave status, you will find Slave_SQL_Running: No

The main reason for this phenomenon is that there are differences in the data in the databases on both sides. You can pass Check the mysql log to locate the specific piece of data where the exception occurs

The Mysql log is usually in /var/log/mysqld.log

It should be noted that if you only configure the slave database to synchronize the master database data , and are not set to synchronize with each other, modifying the slave database data may cause synchronization failure.

Related articles:

Mysql database dual-machine hot backup configuration_MySQL

Mysql real-time synchronization-dual-machine mutual backup ( Dual master)

Related videos:

MySQL Data Management Backup and Recovery Case Analysis Video Tutorial

The above is the detailed content of Two mysql servers realize dual-machine mutual backup configuration and test data synchronization. For more information, please follow other related articles on the PHP Chinese website!