MARIADB master-slave replication and semi-synchronous replication

Master-slave replication:
From the server:
I/O Thread: Requests binary log information from master and saves it to the relay log;
SQL thread: Reads the log information from the relay log and completes the replay locally;

    Async mode: Async 1, from the server behind the main server, 2, the master-slave data inconsistency; binary log format: SET datetime = Now () 1, line 2 based, statement-based        3. Hybrid configuration process: 1, Master (1) Enable binary log; MY.CNF----> Log_bin=log_bin.log (2) set a unique Server-id in the current cluster; MY.CNF----> server_id=1 (3) Create an account with copy permission (REPLICATION SLAVE, REPLICATION CLIENT); Mariadb>grant replic        ATION slave,replication CLIENT on * * to ' SLAVE ' @ ' 192.168.20.% ' identified by ' PASSWORD ';    (4) View master log information, record, use Mariadb>show master STATUS when copying; +----------------+----------+--------------+------------------+    | File | Position | binlog_do_db |    binlog_ignore_db | +----------------+----------+--------------+------------------+    | log_bin.000014 |              30155002 |                  |    | +----------------+----------+--------------+------------------+ 2, slave (1) Enable relay log; MY.CNF---->relay_l Og=relay-bin (2) set a unique Server-id in the current cluster; MY.CNF----&Gt server_id=2 (3) Connect to the primary server using a user account with copy rights and start the replication thread; Mariadb>change Master to master_host= ' 192.168.20.1 ', Master_u Ser= ' slave ', master_password= ' PASSWORD ', master_log_file= ' log_bin.0000014 ', master_log_pos=30155002, Master_        Connect_retry=5 Mariadb>start SLAVE; (4) View SLAVE replication status Mariadb>show SLAVE status\g a master many from, one from one master; Server program version Best of the same summary: Master:binlog dump Slave:io THR EAD, SQL thread Slave: If connecting to master server: Change master to option [, option] ... option:master_bind = ' I Nterface_name ' | Master_host = ' host_name ' Primary server address | Master_user = ' user_name ' user name with copy permission | Master_password = ' PASSWORD ' User password | Master_port = Port of Port_num Primary Server | Master_connect_retry = Interval Connection retry Interval | Master_heartbeat_period = interval Heartbeat detection interval | Master_log_file = ' master_log_name ' primary server binary log file | Master_log_pos = location in master_log_pos binary log file | Relay_log_file = ' relay_lOg_name ' | Relay_log_pos = Relay_log_pos | Master_ssl = {0|1} | Master_ssl_ca = ' Ca_file_name ' | Master_ssl_capath = ' Ca_directory_name ' | Master_ssl_cert = ' Cert_file_name ' | Master_ssl_key = ' Key_file_name ' | Master_ssl_cipher = ' Cipher_list ' | Master_ssl_verify_server_cert = {0|1} | Ignore_server_ids = (server_id_list) Main: Mutual main from: 1, inconsistent data; 2, autogrow ID defines a node using an odd ID a Uto_increment_offset=1 auto_increment_increment=2 defines another node with an even ID auto_increment_of fset=2 auto_increment_increment=2 (1) Each use a different server ID (2) to enable the Binlog and relay log (3) definitions The autogrow ID field growth mode (4) Authorizes the user account with copy permission (5) to designate each other as the primary server GRANT REPLICATION slave,replication CLIENT on *. * to ' s    Lave ' @ ' 192.168.20.% ' identified by ' PASSWORD '; How does replication start?    The primary node runs for a long time, and there is already a certain amount of data, how do I initiate replication? Make a full backup of the primary node and log the binary log files and locations, restore this full backup from the node, and start copying from the logged binary log files andThe point at which the copy should be taken: 1. How to restrict read-only from the server?    Read_Only is started from the server, but only for users who do not have super privileges; block all users:mariadb> FLUSH TABLES with READ LOCK;        2, how to ensure that the master-slave replication transaction security?                On the master node, enable the parameter: Sync_binlog = On if the InnoDB storage engine is used: Innodb_flush_logs_at_trx_commit        Innodb_support_xa=on on slave node: Skip_slave_start master node: sync_master_info = 1  From node Sync_relay_log = 1 Sync_relay_log_info = 1 3, semi-synchronous replication Master node: MariaDB [(None)]>        INSTALL PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so ';        Query OK, 0 rows affected (0.05 sec) MariaDB [(none)]> SHOW GLOBAL VARIABLES like '%semi% '; +------------------------------------+-------+        | variable_name |        Value | +------------------------------------+-------+        | rpl_semi_sync_master_enabled |        OFF | | Rpl_semi_sync_master_timeout |        10000 | | RpL_semi_sync_master_trace_level |        32 | | Rpl_semi_sync_master_wait_no_slave |        On | +------------------------------------+-------+ 4 rows in Set (0.00 sec) MariaDB [(None)]> set GLOBAL Rpl_        semi_sync_master_enabled=1;        Query OK, 0 rows Affected (0.00 sec) MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_timeout=2000; Query OK, 0 rows Affected (0.00 sec) from node: MariaDB [(None)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME ' Semisy        Nc_slave.so ';        Query OK, 0 rows affected (0.05 sec) MariaDB [(none)]> SHOW GLOBAL VARIABLES like '%semi% '; +---------------------------------+-------+        | variable_name |        Value | +---------------------------------+-------+        | rpl_semi_sync_slave_enabled |        OFF | | Rpl_semi_sync_slave_trace_level |        32 | +---------------------------------+-------+ 2 rows in Set (0.00 sec) MariaDB [(None)]> set GLOBAL rpl_semi_sync_slave_enabled=1; Query OK, 0 rows Affected (0.00 sec) If you have started from the server, you also need to restart the io_thread; 4. Replication filters allow slave to replicate only a limited number of databases, not all; there are two implementation ideas: (1) The primary server only records a specific database-related write operation to the binary log; The problem: Point-in-time restore will not be fully implemented; Binlog_d         o_db= # Database Whitelist binlog_ignore_db= # database blacklist (2) from the server Sql_thread only read specific data-related statements in the interrupt log and apply locally; Problem: will cause network bandwidth and disk IO waste; replicate_do_db= replicate_ignore_db= replicate_do_table= Replicat e_ignore_table= replicate_wild_do_table= replicate_wild_ignore_table=5, SSL-based replication prerequisites: Supports SSL (1) Primary server-side configuration    Certificate and private key, and create a copy account (REQUIRE SSL) that requires an SSL connection to be used;    (2) Slav connection master, use the Master_ssl-related options to configure the certificate and other information, 6, copy function related files: master.info: Used to save slave connection to master when the relevant information; Relay-log.info: Saves the current binary log and local relay log log correspondence that have been replicated on the current slave node; 7, replication Monitoring and maintenance (1) Cleanup log: PURGE (2) Replication Monitoring SHOW MASTER S Tatus show BINLOG EVENTS show BINARY LOGS SHOW SLAVE STATUS (3) How to tell if SLAVE is behind master S Econds_behind_master:0 (4) How to determine whether the master-slave node data is consistent? Using the table itself checksum check the use of Percona-tools in Pt-table-checksum (5) Data inconsistency repair method: duplicate copy;

Mariadb master-slave replication and semi-synchronous replication