1. Disadvantages and advantages of MySQL single server

Disadvantages:

• Single server If disk problems occur, database data will be lost

• Backup of a single server makes it difficult to automatically and regularly back up data files if they are backed up locally

• Single server can not handle high concurrent reading, only a single one, read and write on the same machine, data pressure is high

Advantage:

• Data will not be inconsistent

• Easy maintenance

Note: mysql single server recommendation, consider using single server + cloud snapshot or cloud storage if only one server is available

2. Principle of master-slave replication

(1). From Node:

• I/O Thread(IO thread): Request binary log events from Master and save them in the relay log;

• SQL Thread(SQL thread): Read log events from relay logs and complete replay locally;

(2). Primary node:

• dump Thread(dump thread): Start a dump thread for each Slave node's I/O Thread to send binary log events to it;

(3). Characteristic:

• Asynchronous replication;

• Inconsistent master-slave data is common.

(4). Copy structure:

• M/S (Master-Slave), M/M (Master-Master), Ring Copy

• One master and many slaves: there can be more slaves from the server

3. Binary Logging Event Recording Format:

3.1 View the binlog format of the mysql main library

Bilog is set only in the primary library, not from the library

The default way of binlog is STATEMENT (show variables like'%binlog_format%';)

3.2 There are three ways to binlog the MySQL main library

• Statement: The oldest way, sql-based master-slave replication. Bilog stores sql statements (based on the statement "record") with a small amount of binlog logs.
  But if, like uuid(), the data will be inconsistent, statement: insert into test values(1,UUID())

• row: Ensure data consistency based on the principal and subordinate way of rows. The binlog stores change information (the process performed by sql). The binlog log log is large.
  Aliyun uses this method by default, which is also recommended in production

• Mixed: mixed mode, a combination of statement and row formats, allowing the system to decide which way to base it on

3.3 The way master-slave replication can be dynamically changed without restarting mysql

set binlog_format = ROW;     #For the current session, the session will be restored to STATEMENT after it exits
show variables like '%binlog_format%';
set global binlog_format = ROW;   #For all sessions, but restarts to STATEMENT
mysql Profile Add binlog_format = ROW #Permanently replace the binlog format with ROW, which is recommended online

3.4 Use the mysqlbinlog command to view binlogs in row format

mysqlbinlog -vv --base64-output=decode-rows master-bin.000001

4. Bilog view of Mysql main library

Bilog List View

show master logs; 

Record current binlog+offset information

show master status;  

Two ways to view binlog

# mysqlbinlog -vvv --base64-output=decode-rows master-bin.000001
# mysqlbinlog master-bin.000003;
> show binlog events in 'master-bin.000003';
> show binlog events in 'master-bin.000003' limit 2;
> show binlog events in 'master-bin.000003' from 120;   #Pos:120
> show binlog events in 'master-bin.000003' from 120 limit 1;

Note:

# No data changes, binlog is not recorded, that is, queries do not record binlog
select * from test;    

# Error sql will not write binlog (no test table in MySQL library)
use mysql
insert into test values (1);  

5 Demo in master-slave mode:

master

(1). Enable binary logging

(2). Set a globally unique ID number for the current node

(3). Create a user account with replication slave (replication client)

[@bjsjs_112_56 data]# grep -Ev "#|^$" /etc/my.cnf.d/server.cnf 
[server]
[mysqld]
port = 3306
datadir = /search/mariadb/data
socket = /search/mariadb/logs/mysql.sock
skip-external-locking
key_buffer_size = 1024M
max_allowed_packet = 64M
table_open_cache = 256
sort_buffer_size = 128M
read_buffer_size = 128M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 1024M
thread_concurrency = 8
expire_logs_days = 3
binlog_format=row
log-bin=master-bin
log-bin-index = master-bin.index
server-id   = 11
character-set-server=utf8mb4
collation-server=utf8_unicode_ci 
max_connections = 1000
innodb_buffer_pool_size = 100G
innodb_log_file_size = 64G
innodb_log_buffer_size = 64G
innodb_file_per_table = ON
skip_name_resolve = ON
log_error=/search/mariadb/logs/mysql.err
log_warnings=1
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
innodb_support_xa=1
sync_master_info=1
[galera]
[embedded]
[mariadb]
[mariadb-10.1]
[@bjsjs_112_56 data]#
[@bjsjs_112_56 data]# systemctl start mariadb.service
[@bjsjs_112_56 data]# mysql
mysql > show global variables like '%log%';(log_bin)
mysql > show master logs;
mysql > show global variables like '%server%';
# Grant subordinate Libraries
mysql > grant reload,super,replication slave,replication client on *.* to 'repluser'@'10.%.%.%' identified by 'replpasswd';
# Refresh Permission Table
mysql > flush privileges;

slave

(1). Enable Relay Logging

(2). Set a globally unique ID number for the current node

(3). Connect to the primary server using a user account with replication privileges and start the replication thread

[@bjsjs_116_20 mariadb]# grep -Ev "#|^$" /etc/my.cnf.d/server.cnf
[server]
[mysqld]
port = 3306
datadir = /search/mariadb/data
socket = /search/mariadb/logs/mysql.sock
skip-external-locking
key_buffer_size = 1024M
max_allowed_packet = 64M
table_open_cache = 256
sort_buffer_size = 128M
read_buffer_size = 128M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 1024M
thread_concurrency = 8
expire_logs_days = 3
binlog_format=row
relay_log=relay-log
relay_log_index=relay-log.index
server-id = 22
character-set-server=utf8mb4 
collation-server=utf8_unicode_ci 
max_connections = 5000
innodb_buffer_pool_size = 64G
innodb_file_per_table = ON
skip_name_resolve = ON
log_error=/search/mariadb/logs/mysql.err
log_warnings=1
read_only=ON
sync_relay_log=10
sync_relay_log_info=10
skip_slave_start
slave-skip-errors = all
[galera]
[embedded]
[mariadb]
[mariadb-10.1]
[@bjsjs_116_20 mariadb]#
[@bjsjs_116_20 mariadb]# systemctl start mariadb.service
[@bjsjs_116_20 mariadb]# mysql
mysql > show global variables like '%log%'; (relay_log,relay_log_index)
mysql > show global variables like '%server%';(server_id)
mysql > change master to master_host='10.144.112.56',master_user='replzabbix',
master_password='replzabbix',master_log_file='master-bin.000015',master_log_pos=25713825;(Don't quote here)

# Start master-slave synchronization
mysql > start slave;

# View master-slave synchronization status
mysql > show slave status\G
Slave_IO_Running: Yes		# Request binary log events from Master and save them in the relay log;
Slave_SQL_Running: Yes	    # Read the log events from the relay log and complete the replay locally;
Seconds_Behind_Master: 0	# Delay in master-slave synchronization

# Turn off master-slave synchronization
mysql > stop slave;

# Reset from Library
mysql > reset slave;

# View Master-Slave Synchronization Processes
mysql > show processlist;

6. Steps to handle problems with master-slave synchronization

(1). Log on to the slave library first to record master-slave synchronization location information, files (Master_Log_File) and offsets (Read_Master_Log_Pos)

(2). Stop Slave Library

stop slave;

(3). Reset Slave Library

reset slave;

(4). Resynchronization

change master to master_host='10.144.112.56',master_user='replzabbix',
master_password='replzabbix',master_log_file='master-bin.000015',master_log_pos=25713825;

(5). Turn on synchronization

start slave;

(6). Query Validation Data Consistency

7 Questions to note in replication architecture:

7.1 Restrict read-only mode from server

• Setting read_from the server Only=1, this restriction is not valid for users with SUPPER privileges

• Block all users by starting a single thread and executing MySQL > flush tables with read lock; And never quit this thread

7.2 How do I ensure transaction security during master-slave replication?

7.2.1 Enable parameters on the Master node

sync_binlog = 1 

Sync_ The binlog option controls how mysql refreshes binary logs to disk, defaulting to 0, meaning that mysql does not refresh and the operating system decides when
Refresh Cache to Persistence Settings, if this value is greater than 0, it specifies how many binary log writes are interval between two flushes to disk
If it is not set to 1, then a crash may result in binary logs not having synchronized transaction data, which can easily cause replication interruptions and make them timely
Recovery becomes impossible, however, you can set this value to 1 for security, which requires mysql to synchronize binary and transaction logs
These two files are refreshed to two different locations, which may require disk tracing, which is a relatively slow operation.

Like innodb log files, placing binary logs on a write-cached raid volume with battery protection can greatly improve performance. In fact, write and refresh two
The binary log cache is actually much more expensive than the innodb transaction log. Because unlike innodb transaction log

If InnoDB storage engine is used:

# When a transaction commits, it is safest to immediately flush in-memory transaction-related data to the transaction log on disk
innodb_flush_log_at_trx_commit = 2  
# Let InnoDB support distributed transactions
innodb_support_xa=1  

sync_master_info=1

7.2.2 Enable parameters on the lave node

# Turn on self-starting replication threads, not recommended, so let them skip
skip_slave_start   	
sync_relay_log=10
sync_relay_log_info=10

7.2.3 The main and subordinate cannot connect for the following reasons:

• Ping IPAddress

• Account or password error (show grants for zabbix@'10.144.0.%')

• Port error (telnet ip port)

• Reverse Resolution (mysql-uzabbix-pzabbix-h10.144.112.56)

Think: How do I configure and start a slave node if the primary node has been running for some time and has a lot of data?

• Restore data from backup to server

• Binary log files and position s when copying from the server starts at backup

8. Dual Main Mode Demo

8.1 Mutually dominant and subordinate issues:

• Data is inconsistent, so use caution;

• Automatic Growth ID Problem

Configure a primary node to use an odd id

# Start with an odd number
auto_increment_offset=1
# Increase by 2		
auto_increment_increment=2

Another primary node uses an even id

# Start with even numbers
auto_increment_offset=2
# Increase by 2		
auto_increment_increment=2	

8.2 Configuration steps:

• Each node uses a unique server_id

• Both binary log and relay log are enabled

• Create a user account with copy rights

• Defines an even or odd range of values for an auto-growing id field

• Designate each other as the primary node and start the replication thread

Master1

# vim /etc/my.cnf.d/server.cnf
[mysqld]
server_id=1
log_bin=master-bin
log-bin-index = master-bin.index
relay_log=relay-log
relay-log-index = relay-log.index
auto_increment_offset=1	
auto_increment_increment=2
binlog_format = ROW
innodb_file_per_table=ON
skip_name_resolve=ON
# systemctl start mariadb.service
# mysql
# mysql > show global variables like '%log%';(log_bin,relay_log)
# mysql > grant replication slave,replication client on *.* to 'repluser'@'1.1.1.1' identified by '12345'
# mysql > flush privileges;
# MySQL > show master status; (position 506, used on master 2)
# mysql > change master to master_host='2.2.2.2',master_uer='repluser',
master_password= '12345',master_log_file='mysql-bin.000003',master_log_pos=708(Don't quote here)
# mysql > start slave;
# mysql > create database mydb;
# mysql > show tables;
# mysql > insert into tb1(name) values('Yang Kang'),('Yang Guo'),('Yang Yan Zhao')

Master2

# vim /etc/my.cnf.d/server.cnf
[mysqld]
server_id=2
log_bin=master-bin
log-bin-index = mater-bin.index
relay_log=relay-log
relay-log-index = relay-log.index
auto_increment_offset=2
auto_increment_increment=2
binlog_format = ROW
innodb_file_per_table=ON
skip_name_resolve=ON
# systemctl start mariadb.service
# mysql
# mysql > show global variables like '%log%';(log_bin,relay_log)
# mysql > grant replication slave,replication client on *.* to 'repluser'@'2.2.2.2' identified by '12345'
# mysql > flush privileges;
# mysql > change master to master_host='1.1.1.1',master_uer='repluser',
master_password= '12345',master_log_file='mysql-bin.000003',master_log_pos=506(Don't quote here)
# MySQL > show master status; (location 708, used on master 1)
# mysql > start slave;
# mysql > use mydb;
# mysql > create table tb1(id int unsigend not null auto_increment primary key,name char(30));
# mysql > select * from tb1;
# mysql > insert into tb1(name) values('Zhu Yuan Zhang'),('Zhu Di'),('Zhu Hou Cong')

9. Semi-synchronous mode demo:

Yes, Google is mysql5. A plug-in provided in later versions of 5 is not a true semi-synchronization, but has N-slave nodes, only one of which needs to be synchronized from node to data

Suggestion: Configure only one slave node

Maste

# vim /etc/my.cnf.d/server.cnf
[mysqld]
log_bin=master-bin
server_id=1
innodb_file_per_table=ON
skip_name_resolve=ON
# systemctl start mariadb.service
# mysql
# mysql > grant replication slave,replication client on *.* to 'repluser'@'2.2.2.2' identified by '12345'
# mysql > flush privileges;
# MySQL > install plugin rpl_ Semi_ Sync_ Master soname'semisync_ Master. So'; Install Plugins
# mysql > show global variables like '%semi%';
# mysql > set global  rpl_semi_sync_master_enabled=1
# mysql > show global status like '%semi%';
# mysql > create database mydb;
# mysql > use mydb;
# mysql > create table tb1(id int unsigend not null auto_increment primary key,name char(30));
# mysql > show global status like '%semi%';

Slave

# vim /etc/my.cnf.d/server.cnf
[mysqld]
relay_log=relay-log
server_id=2
innodb_file_per_table=ON
skip_name_resolve=ON
# systemctl start mariadb.service
# mysql
# mysql > change master to master_host='1.1.1.1',master_uer='repluser',
master_password= '12345',master_log_file='mysql-bin.000003',master_log_pos=496(Don't quote here)
# mysql > install plugin rpl_semi_sync_slave  soname 'semisync_slave.so' 
# mysql > show global variables like '%semi%';
# mysql > set global  rpl_semi_sync_slave_enabled=1
# mysql > start slave;
# mysql > show slave status\G 

10. GTID-based master-slave replication

(1). Meaning of gtid

• Global Transaction Identifier, Global Transaction Identifier

• Ali Cloud rds are currently using gtid

(2). Principle of master-slave replication based on gtid

• There is a unique UUID on each mysql database

• Each transaction generates an ID, TID

• gtid consists of both: UUID+TID (transaction ID)

Note: gtid makes it easier to configure master slave and promote from master to master, rather than using binlog+location

(3). Configuration of primary and secondary libraries, Mysql version greater than 5.6

>show variables like '%gtid%';
>system vim /etc/my.cnf
gtid_mode=on
enforce_gtid_consistency=on
log-slave-updates=1 #mysql5.7 This parameter is no longer required
log-bin=master-bin
log-bin-index = master-bin.index
relay-log = relay-log
relay-log-index = relay-log.index
binlog_format=row

Note: log-slave-updates allows binlog s from libraries to be updated as well

Use scenarios: dual main mode, cascade replication mode, mysql5.6 Turn on gtid, which is required for all three scenarios, and is no longer required for mysql 5.7

(4). View the UUID of the database

# Auto in data directory. CNF
show global variables like 'server_uuid'

(5). Primary Library Gives Copy Permission to Slave Library

grant replication slave on *.* to 'replzabbix'@'192.168.237.129' identified by 'replzabbix';
flush privileges; 

(6.) Copy from the library using commands

change master to
  master_host='192.168.237.128',
  master_port=3306,
  master_user='replzabbix',
  master_password='replzabbix',
  master_auto_position = 1;
show slave status\G

(7). Primary and subordinate binlog observations (binlogs are also available from libraries)

show binlog events in 'master-bin.000001';

(8). Steps to handle problems with master-slave synchronization (Note: Location information for master-slave synchronization is not required to be recorded)

Stop Slave Library

stop slave;

Reset Slave Library

reset slave;

Resynchronization

change master to master_host='192.168.237.128',master_port=3306,master_user='replzabbix',
master_password='replzabbix',master_auto_position = 1;

Turn on synchronization

start slave;

5). Query Validation Data Consistency

11. Copy filter

Enables replication from a node to only the specified database, or to the specified tables of the specified database

There are two ways to do this:

Host Server Configuration

The primary server only logs events related to a specific database (specific table) to binary logs

Problem: Because only part of the database is recorded, point-in-time restore is not possible and is not recommended.

If you do not want to use it, specify either of the following two options in the configuration

• binlog_do_db: only those database-related write operations are recorded in the binary log, whitelist list of databases

• binlog_ignore_db: only those database-related write operations are ignored into the binary log, database blacklist

Be careful:

• The above two options cannot be used at the same time

• Not recommended

Configure from Server

From Server SQL_ When THREAD relays events in the replay relay log, only events related to a specific database (specific table) are read and applied locally

Problem: Will cause network and disk IO waste

Library level:

replicate_do_db=mydb
replicate_ignore_db

Table level:

replicate_do_table
replicate_ignore_table

Table-level wildcards:

replicate_wild_do_table
replicate_wild_ignore_table

12. SSL-based replication

Prerequisite: Support for SSL

(1) The master configures the certificate and private key and creates a replicated account requiring an SSL connection

mysql > grant replication slave,replication client on *.* to 'repluser'@'2.2.2.2' identified by '12345' require ssl;

(2) Indicate ssl-related options when the slave side uses the change master to command

13. Other

(1). Files related to copy function now:

• master.info: used to store information about slave connections to master, such as account number, password, server address, and so on

• relay-log.info: Save the relationship between the current binary log that has been copied on the current slave node and the local relay log

(2). Monitoring and maintenance of replication:

2.1) Clean Log

mysql > purge binary logs to 'master-bin.000002'
mysql > show binary logs;

2.2) Replication Monitoring

show master status;
show binlog events;
show binary logs;
show slave status;
show process list; View Number of Threads

2.3) Whether the slave server is behind the master server

mysql > show salve status;
Seconds_Behind_Master: 0

2.4) How to determine whether master-slave node data is consistent

percona-tools

2.5) How can data inconsistencies be repaired?

Primary Node Delete, Replicate