Mysql implements master-slave configuration and multi-master-multi-slave configuration

What we are simulating now is a master-slave system (1 host and 1 slave). The principle of master-slave synchronization is to synchronize the bin-log binary file and synchronize the content of this file from the host to the slave.

1. Modification of configuration files

1. Modify the host configuration file

We first need the /etc/my.cnf configuration file of the mysql host (192.168.254.130) and add the following configuration:

#Host unique ID
server-id=1
#Binary log log-bin=mysql-bin
#Database that does not need to be synchronized binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#Synchronized database name binlog-do-db=mycat
#Binary format binlog_format=STATEMENT

Let's take a look at the entire my.cnf file

[root@localhost Desktop]# cat /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
socket=/usr/local/mysql/data/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

server-id=1
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-do-db=mycat
binlog_format=STATEMENT


[mysqld_safe]
log-error=/usr/local/mysql/data/mysqld.log
pid-file=/usr/local/mysql/data/mysqld/mysqld.pid
[root@localhost Desktop]#

After modifying the host's configuration file, we need to restart the service through the command:

[root@localhost support-files]# ls
magic mysqld_multi.server mysql-log-rotate mysql.server
[root@localhost support-files]# pwd
/usr/local/mysql/support-files
[root@localhost support-files]# ./mysql.server restart

Then we modify the configuration file of the slave machine (192.168.254.131).

2. Slave configuration

The configuration modification of the slave is relatively simple:

#Slave machine unique ID
server-id=2
#Relay log relay-log=mysql-relay

After modifying the configuration, we restart the slave

2. MySQL client command operation

Next we can connect to the mysql command line through the command:

[root@localhost bin]# 
[root@localhost bin]# pwd
/usr/local/mysql/bin
[root@localhost bin]# ./mysql -uroot -p

1. Host operation

1) Create a synchronization user

First, we can create a user dedicated to master-slave synchronization on the host by running the command:

GRANT REPLICATION SLAVE ON *.* TO 'SLAVE'@'%' IDENTIFIED BY '123456';

2) Check the status of synchronized files

Then we use show master status; to view the synchronization status of the host:

mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 | 154 | mycat | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

2. Slave operation

1) Set the slave host

Execute the following command, which sets the relevant information for us to establish synchronization with the host

CHANGE MASTER TO MASTER_HOST='192.168.254.130',
MASTER_USER='SLAVE',
MASTER_PASSWORD = '123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=430;

If it is prompted here that the host configuration has been set, you can reset it by stop slave&reset master.

2) Start synchronization

Next we start synchronization by starting slave:

You can see:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.254.130
                  Master_User: SLAVE
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:mysql-bin.000001
          Read_Master_Log_Pos: 592
               Relay_Log_File:mysql-relay.000002
                Relay_Log_Pos: 482
        Relay_Master_Log_File: mysql-bin.000001
             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: 592
              Relay_Log_Space: 685
              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: 1
                  Master_UUID: 74397a99-accf-11eb-ae0d-000c2912d302
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay logs; 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)

mysql>

Here we can see that Slave_IO_Running and Slave_SQL_Running are both YES, which means it is successful. If it is the following:

*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.254.130
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:mysql-bin.000001
          Read_Master_Log_Pos: 430
               Relay_Log_File:mysql-relay.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Connecting
            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: 430
              Relay_Log_Space: 154
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1045
                Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 60 retries: 1
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay logs; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 210505 00:18:08
     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)

We can see that there is an error here in Last_IO_Error, so we can go and see what the log reports. At present, my problem is that the synchronization user cannot be synchronized because of an error. As mentioned above, stop the synchronization and reset it first, modify the synchronization command, and then operate it again.

3. Master-slave synchronization test

1. Host creation library

We first create the database mycat that we set up earlier to synchronize on the host:

mysql> create database mycat;
Query OK, 1 row affected (0.00 sec)

mysql> use mycat;
Database changed
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mycat |
|mysql |
| performance_schema |
|sys|
+--------------------+
5 rows in set (0.00 sec)

mysql>

2. View the library from the machine

Then we can see this library on the slave machine

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mycat |
|mysql |
| performance_schema |
|sys|
+--------------------+
5 rows in set (0.00 sec)

3. Initialization of table data

Next we test the table data

1) Host First, we create a table on the host and insert data

mysql> use mycat;
Database changed
mysql> 
mysql> create table `test1`(
    -> id int auto_increment not null primary key,
    -> name varchar(10) default null
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test1(`id`,`name`) value(1,"petty");
Query OK, 1 row affected (0.16 sec)

mysql> select * from test1;
+----+-------+
| id | name |
+----+-------+
| 1 | petty |
+----+-------+
1 row in set (0.00 sec)

mysql>

2) Next, we check whether the slave is synchronized successfully:

mysql> use mycat;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_mycat |
+-----------------+
| test1 |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from test1;
+----+-------+
| id | name |
+----+-------+
| 1 | petty |
+----+-------+
1 row in set (0.00 sec)

mysql>

You can see that our master-slave configuration has been successful.

4. Multiple Masters and Multiple Slaves

We can also have multiple masters and multiple slaves. For example, our master-slave sequence is number 1 as the master, number 2 as the slave, then number 3 as the master, and number 4 as the slave. At the same time, host number 1 and host number 3 are master and slave to each other. In this way, even if one of the hosts has a problem, the entire MySQL cluster can still work normally.

Since there are only three machines at present, only three are used to write the demo (one Windows and two Linux).

1. Host No. 1 (192.168.254.30)

1) To modify the configuration, we first need to modify the original etc/my.cnf file and add:

# As a slave, also modify its bin-log log log-slave-updates
#Auto-increment-increment=2
#Start position of self-increment auto-increment-offset=1

Information about the entire file

[root@localhost Desktop]# cat /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
socket=/usr/local/mysql/data/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

server-id=1
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-do-db=mycat
binlog_format=STATEMENT

log-slave-updates
auto-increment-increment=2
auto-increment-offset=1

[mysqld_safe]
log-error=/usr/local/mysql/data/mysqld.log
pid-file=/usr/local/mysql/data/mysqld/mysqld.pid
[root@localhost Desktop]#

After modifying this file we need to restart the machine

2. Slave No. 2 (192.168.254.31)

This machine has already been configured to connect to 30 machines, so no need to modify it this time

3. Host No. 3 (192.168.254.1)

1) Modify the configuration file. Since this machine is Windows, we need to modify its my.ini file and add

server-id=3
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-do-db=mycat
binlog_format=STATEMENT

log-slave-updates
auto-increment-increment=2
auto-increment-offset=2

Note that we changed the server-id above and also changed its growth starting point auto-increment-offset=2. Restart the service at the same time.

2) Create a synchronization user First, we can create a user dedicated to master-slave synchronization on the host by running the command:

 GRANT REPLICATION SLAVE ON *.* TO 'SLAVE'@'%' IDENTIFIED BY '123456';

3) Check the status

mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 | 154 | mycat | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

mysql>

4) Set the synchronization status

Next we run the host (30) information to which it is connected

CHANGE MASTER TO MASTER_HOST='192.168.254.130',
MASTER_USER='SLAVE',
MASTER_PASSWORD = '123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=430;

5) Host No. 1 synchronization (192.168.254.30)
We need to set it to synchronize with host number 3 (192.168.254.1), which is the master status of number 3 we viewed earlier:

CHANGE MASTER TO MASTER_HOST='192.168.254.1',
MASTER_USER='SLAVE',
MASTER_PASSWORD = '123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;

Then we execute synchronous start slave; on host number 1 and synchronous start slave; on host number 3.

4. Test and view

1) Possible problems (can be skipped)

Now we test and then check the master status of the two machines separately: show master status;.

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.254.1
                  Master_User: SLAVE
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
........

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.254.130
                  Master_User: SLAVE
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:mysql-bin.000002
          Read_Master_Log_Pos: 462
               Relay_Log_File: LAPTOP-QR83QEC0-relay-bin.000003
                Relay_Log_Pos: 675
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
.........

You can see that their synchronization is yes. There may be a problem here, and we need to solve it ourselves. For example, I modify the configuration on machine number 1 and then check its status.

mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000002 | 462 | mycat | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

mysql>

If I use this message to let machine No. 3 synchronize with machine No. 1, it will report (because I ran a new insert statement), but the table creation statement is in the log mysql-bin.000001, and here I have restarted, and there is a new mysql-bin.000002, so the synchronization information of the original No. 2 has been modified.

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.254.130
                  Master_User: SLAVE
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:mysql-bin.000002
          Read_Master_Log_Pos: 462
               Relay_Log_File: LAPTOP-QR83QEC0-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1146
                   Last_Error: Error 'Table 'mycat.test1' doesn't exist' on query. Default database: 'mycat'. Query: 'insert into test1(`id`,`name`) value(2,"TOm")'

2) Insert data in number 3. Let’s insert data in number 3 again to see if numbers 1 and 2 can be seen.

In operation number 3:

mysql> insert into test1(`id`,`name`) value(3,"kitt");
Query OK, 1 row affected (0.01 sec)

mysql> select * from test1;
+----+-------+
| id | name |
+----+-------+
| 1 | petty |
| 2 | TOm |
| 3 | kitt |
+----+-------+
3 rows in set (0.00 sec)

mysql>

View at No. 1

mysql> select * from test1;
+----+-------+
| id | name |
+----+-------+
| 1 | petty |
| 2 | TOm |
| 3 | kitt |
+----+-------+
3 rows in set (0.00 sec)

mysql>

View at No. 2

mysql> select * from test1;
+----+-------+
| id | name |
+----+-------+
| 1 | petty |
| 2 | TOm |
| 3 | kitt |
+----+-------+
3 rows in set (0.00 sec)

mysql>

It can be seen that we have successfully synchronized, and the insertion information of host number 3 can be viewed in number 1.

3) Processing data with number 1

Next, we will check the number 1 operation

No. 1:

mysql> insert into test1(`id`,`name`) value(4,"lisa");
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+----+-------+
| id | name |
+----+-------+
| 1 | petty |
| 2 | TOm |
| 3 | kitt |
| 4 | lisa |
+----+-------+
4 rows in set (0.00 sec)

mysql>

Number 3:

mysql> select * from test1;
+----+-------+
| id | name |
+----+-------+
| 1 | petty |
| 2 | TOm |
| 3 | kitt |
| 4 | lisa |
+----+-------+
4 rows in set (0.00 sec)

mysql>

You can see that they are synchronized with each other.

This is the end of this article about MySQL master-slave configuration and multi-master-multi-slave configuration. For more information about MySQL master-slave configuration and multi-master-multi-slave configuration, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in: