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 [email protected] 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 [email protected] 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