Logpoint-based replication 1. Create a dedicated replication account on the master and slave databases MariaDB [employees]> create user 'repl'@'172.%' identified by '123456'; Note that the password in production must meet the relevant specifications to achieve a certain password strength, and it is required that the master database can only be accessed on a specific network segment on the slave database. 2. Grant replication permissions on the master and slave databases MariaDB [employees]> grant replication slave on *.* to 'repl'@'172.%'; 3. Configure the main database Note that enabling binary logging requires restarting the service, and server_id is a dynamic parameter that can be combined with command lines and configuration files to achieve restart-free persistent configuration. Note that server_id is unique in the cluster. [mysqld] log_bin = /var/log/mysql/mariadb-bin log_bin_index = /var/log/mysql/mariadb-bin.index binlog_format = row server_id = 101 NOTE: It is a good habit to separate logs from data, preferably in different data partitions. 4. Configure the slave library The option log_slave_update determines whether to store the relay log relay_log in the local binlog. If link replication is configured, this option is required. Note that server_id is unique in the cluster. [mysqld] # replication log_bin = /var/log/mysql/mariadb-bin log_bin_index = /var/log/mysql/mariadb-bin.index server_id = 102 # slaves relay_log = /var/log/mysql/relay-bin relay_log_index = /var/log/mysql/relay-bin.index relay_log_info_file = /var/log/mysql/relay-bin.info log_slave_updates = ON read_only 5. Initialize the data from the library Here we use mysqldump to back up the master database. In production, we recommend that you use xtrabackup for lock-free hot backup (based on the innodb engine). Back up the data of the employees database on the main database Copy the code as follows: mysqldump --single-transaction --master-data=1 --triggers --routines --databases employees -u root -p >> backup.sql Mount the backup file backup.sql to the slave server via scp or docker volume, and import it into the slave library mysql -u root -p < backup.sql 6. Start the replication link There are [email protected] and [email protected], and the data has been synchronized to the slave through mysqldump. Now configure the replication link on the slave server MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mariadb-bin.000029', MASTER_LOG_POS=516; Query OK, 0 rows affected (0.02 sec) Start the replication link on the slave MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.01 sec) 7. Check the slave status on the slave library Slave_IO_Running and Slave_SQL_Running must be YES. If an error occurs, read the prompt information of Last_IO_Error or Last_SQL_Error in detail. MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: master Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000029 Read_Master_Log_Pos: 516 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 539 Relay_Master_Log_File: mariadb-bin.000029 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: 516 Relay_Log_Space: 831 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: 101 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative 1 row in set (0.00 sec) 8. Check the dump thread in the main library Check whether the binlog dump thread has been started correctly MariaDB [(none)]> show processlist \G *************************** 1. row *************************** Id: 7 User: root Host: 172.20.0.1:41868 db: employees Command: Sleep Time: 56 State: Info: NULL Progress: 0.000 *************************** 2. row *************************** Id: 10 User: repl Host: 172.20.0.3:45974 db: NULL Command: Binlog Dump Time: 246 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL Progress: 0.000 You can see that the command Binlog Dump is started on row 2, which proves that the replication thread has been successfully started. 9. Summary advantage
shortcoming
In a one-master-multiple-slave environment, if the old master crashes and a new master is elected in the cluster, the other slaves need to resynchronize with the new master. Since the binlog of each DB exists independently, it is difficult to find the log point to start synchronization. The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM. You may also be interested in:
|
<<: 10 reasons why Linux is becoming more and more popular
>>: Manually implement the two-way data binding principle of Vue2.0
rep / egrep Syntax: grep [-cinvABC] 'word'...
I took the bus to work a few days ago. Based on m...
It was found in the test that when the page defini...
1 Introduction PostgreSQL is a free software obje...
Preface: Docker port mapping is often done by map...
Table of contents 1. Node.js and Vue 2. Run the f...
Achieve results Implementation Code html <div ...
Table of contents text LOCK parameter ALGORITHM p...
Table of contents Preface Setting up with Vue CLI...
1. IE browser mode Hack logo 1. CSS hack logo Copy...
0. What is a tag? XML/HTML CodeCopy content to cl...
Table of contents 1. What is JSON 1.1 Array liter...
1. Compatibility As shown below: The compatibilit...
Table of contents 1. Affairs: Four major characte...
Preface I have been working on some front-end pro...