Simple configuration of mysql database master-slave synchronization, read-write separation

This paper is learning notes, mainly used to record my learning process. Part of the content for reprint!!!!

1, What are the reasons for Mysql's Master-Slave synchronous replication, read-write separation?

For some current large-scale websites, only one database server is not enough to handle a large number of database connection operations. And only have a database server, easy to cause data loss, very insecure.

Deploy the master-slave server, realize the synchronization of master-slave data, and realize the separation of read and write, reduce the pressure of the master server, and also alleviate the problem of data loss.

The master-slave server, in fact, refers to one master and many slaves, which writes on the master server, while all reads are performed on the slave server.

Master-slave server implementation model:

From the above model, we can see that there is a delay between the log files of the slave server and that of the master server.

2, Method of master-slave synchronous replication

1. Synchronous replication

That is to say, each database operation on the master server needs to wait for the copy from the slave server to complete before the master server can carry out the next database operation.

2. Asynchronous replication

That is to say, the master server keeps doing database operations, while the slave server keeps copying logs for synchronization.

3, Implementation of master-slave database

0. Environment configuration:

IP address of main server: 192.168.112.130
IP address of slave server: 192.168.112.131

1. Install and configure mysql database (self search and installation)

# Create a test database
[root@master ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

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

mysql> exit
Bye

Use the same method to install mysql on the slave and create the test database.

# mysql resets the root password without knowing the root password. Edit / etc/my.cnf File, add the following.
[mysqld]
skip-grant-tables
# Restart mysql database
# At this time, you can log in without a password
[root@master ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> set password for root@localhost = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye

# Finally, edit / etc/my.cnf File, comment out skip grant tables
# Restart mysql database and log in with the new password just set.

2. Modify master master server configuration

# Modify / etc/my.cnf As follows:
[mysql]
default-character-set=utf8

[mysqld]
default-storage-engine=INNODB
character_set_server=utf8

server-id = 1
log_bin = master-bin
binlog_format = ROW
binlog_do_db = test

#Server ID server unique ID.
#log_ The mysql binary log of the bin master server is provided to the slave server for synchronizing data. Only the operations of the database that need to be synchronized will be recorded in the log.
#binlog_format binary log file format
#Choose one of the following:
#binlog_do_db specifies the name of the synchronized database
#binlog_ignore_db specifies the database name that is not synchronized
# Grant root on the primary server permission to connect to the secondary server
[root@localhost mysql]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant replication slave on *.* to root@192.168.112.131 identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
# View the status of the primary server
[root@master ~]# systemctl restart mysql
[root@master ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.23-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

3. Modify slave server configuration

# Modify / etc/my.cnf As follows:
[mysql]
default-character-set=utf8

[mysqld]
default-storage-engine=INNODB
character_set_server=utf8

server-id = 2
log-bin=master-bin
binlog_format = ROW


#relay-log=slave-relay-bin
#relay-log-index = slave-relay-bin.index
#replicate-do-db=test
#Server ID server unique ID.
#Relay log is used for data recovery and backup from the mysql binary log of the server. When the master server crashes, it is used to provide other slave servers as the master server.
#Choose one of the following:
#Replicate do DB specifies the name of the synchronized database
#Replicate ignore DB specifies the database name that is not synchronized
[root@slave-server ~]# systemctl restart mysql
[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> stop
    -> slave;
Query OK, 0 rows affected (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST='192.168.112.130',MASTER_PORT=3306,MASTER_USER='root',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-bin.000005',MASTER_LOG_POS= 154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
#master_host [IP address of main server]
#master_port [port of main server]
#master_log_file [show master status file column: master-bin.000001]
#master_log_pos [show master status: 154]

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.112.130
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-bin.000005
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          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: 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: 1593
                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 
             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: 200514 10:47:34
     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)

ERROR: 
No query specified

# The result is server_uuid conflict modification auto.cnf Restart the file data
mysql> show variables like '%server_uuid%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | 24fbce5b-266b-11e7-b0a7-5254008815b6 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.112.130
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-bin.000005
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          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: 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: 1593
                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 
             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: 200514 10:47:34
     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)

ERROR: 
No query specified

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.112.130
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000006
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000003
                Relay_Log_Pos: 369
        Relay_Master_Log_File: master-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          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: 747
              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: 24fbce5b-266b-11e7-b0a7-5254008815b7
             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)

ERROR: 
No query specified

mysql> exit
Bye
# For slave_ IO_ Solution to running in connected state:
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
# Grant all privileges to root
grant all privileges on *.* to root@192.168.112.130 identified by '123456' with grant option; 
# Refresh permissions
flush privileges;


# Just pay attention to the abnormal information

4. Test the operation of master-slave server

IP address of main server: 192.168.112.130
IP address of slave server: 192.168.112.131
Test: create the table on the primary server and insert the data. See if the slave server will synchronize.
① Data on the main server

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` varchar(10) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user` VALUES ('1', 'abong');
INSERT INTO `user` VALUES ('2', 'zhang');

② Verify that the slave server is synchronized. As you can see from the figure below, the user table already exists on the slave server, and the data is correct.

③ Modifying data from a library

④ Check whether the data is modified on the main database. As you can see, the data has not been modified.


4, Separation of reading and writing

In MYSQL database master-slave synchronization, read-write separation technology, the most important is how to achieve master-slave synchronization, and read-write separation can be configured through the permissions of database users. From the above test results, it can be seen that taking the master database as the write database and the slave database as the read database can separate the read and write operations of the database, reduce the pressure of the database, and maintain the consistency of the database.
On the main database, create a mysql user and grant read-write permission.
On the slave database, create a mysql user with read-only permission.

 

 

 

 

 


--------
Original link: https://blog.csdn.net/weixin_36522099/article/details/106123612

Tags: MySQL Database Oracle

Posted on Sun, 07 Jun 2020 23:59:55 -0400 by koughman