Shulou(Shulou.com)06/01 Report--

The so-called cascading replication is the master server, which synchronizes the data to only one slave server, and then the slave server synchronizes the data to all the back-end slave servers, reducing the write pressure on the master server, and the network IO of replicating the data.

First, configure the master server

1. Modify the main configuration file

Vim / etc/my.cnf

Add the following two-line configuration under the [mysql] configuration block

[mysql] log_bin # enable binary logging function server_id=1 # sets a globally unique ID number for the current node

2. Restart the mysql service to make the configuration effective

Systemctl restart mairadb

3. Create a user account with replication permission

GRANT REPLICATION SLAVE ON *. * TO 'repluser'@'HOST' IDENTIFIED BY' replpass'

Command parsing:

'repluser'@'HOST': set the user name, that is, the host ip or network segment, and the network segment is represented by%, for example, 10.0.0.%IDENTIFIED BY: set password *. *: all databases and all tables GRANT REPLCATION SLAVE: allow the user to copy data

The command is used to authorize repluser to copy all the contents of the database

Second, relay slave server configuration

1. Modify the main configuration file

Vim / etc/my.cnf

Add the following two lines of configuration to the [mysql] configuration block

[mysqld] log_binserver_id=2 # sets a globally unique ID number read_only=ON # for the current node to restrict slaves to read-only. "Note: this restriction is not valid for users with SUPER privileges." the purpose of this item is to count the binary logs of the master server into the local machine, and then copy the binary logs to other slave servers at the back end.

2. Restart the mysql service to make the configuration effective

Systemctl restart mariadb

3. Use a user account with replication permission to connect to the main server and start the replication thread

CHANGE MASTER TO MASTER_HOST='host', # specify master host IP MASTER_USER='repluser', # specify master authorized user name MASTER_PASSWORD='replpass',# specify authorized user password MASTER_LOG_FILE='mysql-bin.xxxxx', # specify that MASTER_LOG_POS=# is replicated from that binary log on the master server # binary log location, which can be viewed by executing this command on the master server. Show master logs; starts replication threads IO_THREAD and SQL_THREAD START SLAVE

4. Check the status of the relay slave server

MariaDB [(none)] > start slave Query OK 0 rows affected (0.00 sec) MariaDB [(none)] > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.68.7 Master_ User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000001 Read_Master_Log_Pos: 557 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 843 Relay_Master_Log_File: mariadb-bin.000001 Slave_IO_Running: Yes "focus on NO means that the thread is not up" Slave_SQL_Running: Yes "focus on if NO indicates that the thread is not up" 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: 557 Relay_Log_Space: 1139 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 "item indicates synchronization time 0 means even if synchronization" 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

Third, backend slave configuration

1, modify the configuration file

Vim / etc/my.cnf

Add the following two lines of configuration to the [mysql] configuration block

[mysqld] server_id=3 # sets a globally unique ID number for the current node read_only=ON # restricts slave servers to read-only. "Note: this restriction is not valid for users with SUPER permissions"

2. Restart the mysql service to make the configuration effective

Systemctl restart mariadb

3. Use a user account with replication permission to connect to the main server and start the replication thread

CHANGE MASTER TO MASTER_HOST=' relay host', # specify relay slave host IP MASTER_USER='repluser', # specify master authorized user name MASTER_PASSWORD='replpass',# specify authorized user password MASTER_LOG_FILE='mysql-bin.xxxxx', # specify which binary log of the relay slave server to start replication MASTER_LOG_POS=# # binary log location, which can be viewed by executing this command on the slave server. Show master logs; starts replication threads IO_THREAD and SQL_THREAD START SLAVE

4. Check the status of slave server

MariaDB [(none)] > start slave Query OK 0 rows affected (0.00 sec) MariaDB [(none)] > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.68.17 Master_ User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000001 Read_Master_Log_Pos: 557 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 843 Relay_Master_Log_File: mariadb-bin.000001 Slave_IO_Running: Yes "focus on NO means that the thread is not up" Slave_SQL_Running: Yes "focus on if NO indicates that the thread is not up" 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: 557 Relay_Log_Space: 1139 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 "item indicates synchronization time 0 means even if synchronization" 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

5. Finally, create a database test on the master server to see whether it is synchronized.

Characteristics of cascade replication

Reduce the pressure on the master server and the network io, but it will cause data inconsistency.

Summary

The relay slave needs to open the binary log. The log_slave_updates configuration item must be added to pay attention to the role of read_only=ON, which limits the slave server to read-only. "Note: this restriction is not valid for users with SUPER permissions"

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.