MYSQL/MARIADB configuration of master-slave replication architecture and problems in the process

Two CentOS7 system virtual hosts:
are: master server 172.16.75.1, 172.16.75.2 from server
Using the mariadb-5.5.56, which is the software version that comes with CentOS
To make the experimental results accurate, shut down the firewall and SELinux for two servers here:
[Email protected] ~]# Setenforce 0
[Email protected] ~]# iptables-f

First, configure on the primary server 172.16.75.1:
The configuration in/ETC/MY.CNF is as follows:
[Mysqld]
# # #定义二进制日志的存放位置 # #
Log_bin=/var/lib/mysql/binlog
# # #配置server_id来保证服务器的唯一性 # #
Server_id=101
Innodb_file_per_table=on
# # #跳过域名反解 # #
Skip_name_resolve=on
# # #每写入一次二进制日志event , will be written to disk once, generally 1, to prevent the binary log has not been written to disk downtime resulting in data loss, ensure data integrity # # #
Sync_binlog=1
# # #事务每次提交都会将 The log in the transaction log Buffer writes to the operating system's buffer and immediately calls Fsync () to write to the disk, even if the system crashes without losing any data # # #
Innodb_flush_log_at_trx_commit=1

Save exit and restart the MARIADB service.

Log in to MySQL interactive mode, create a user account on master to implement the replication function, and authorize Replication slave,replication client permissions:
MariaDB [(none)]> Grant Replication slave on . to ' repuser ' @ '% ' identified by ' reppass ';
Query OK, 0 rows affected (0.01 sec)
and then update the authorization to prevent the refresh from not being able to log on:
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
View authorized users:
MariaDB [(none)]> show grants for "Repuser" @ "%";
+-------------------------------------------------------------------------------------------------------------- ------+
| Grants for [email protected]% |
+---------------------------------------------------------------------------------------------------------- ----------+
| GRANT REPLICATION SLAVE on . To ' repuser ' @ '% ' identified by PASSWORD ' *304a91f0e46bbb1e641d3d95e225e9aaa27077ce ' |
+---------------------------------------------------------------------------------------------------------- ----------+
1 row in Set (0.00 sec)

Record the binary log file name and location coordinates before starting replication on master, and configure the position location coordinates for the file file name and response to be used from the server:
MariaDB [(None)]> Show Master status;
+---------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+---------------+----------+--------------+------------------+
| binlog.000014 | 553 | | |
+---------------+----------+--------------+------------------+
1 row in Set (0.01 sec)

The experiment here is to backup all of the database data and tables on the primary server, which can be fully backed up by the mysqldump command:
[Email protected] ~]# mysqldump--all-databases--lock-tables > Alldb.sql
The backup alldb.sql file is then sent to the home directory from the server side (which is sent here to root from the server):
[Email protected] ~]# SCP alldb.sql 172.16.72.2:/root/

Second, in the configuration from the server 172.16.75.2:
The configuration in/ETC/MY.CNF is as follows:
[Mysqld]
Innodb_file_per_table=on
Skip_name_resolve=on
#设置服务器id, distinguish between master and slave #
server_id=201
#中继日志存放位置 #
Relay_log=/var/lib/mysql/slavelog
#设置从服务器全局只读 #
#read_only =on or SET @ @global. read_only=on#
SET @ @global. read_only=on

**补充**：关于read_only，可以防止从服务器修改数据使得主从服务器端数据不一致（因为从服务器没有开启二进制日志记录，所以从服务器上修改的数据将不为主服务器所知），但是该服务器参数仅能限制那些不具有"SUPER"权限的用户的写操作行为(例如root用户除外)，在低版本的mariadb中可以在从服务器上开启mysql会话，并使用"flush tables with read lock;"给所有表加读锁，用来禁止root用户在从服务器上进行写操作；在后期的mariadb版本中，会提供限制root用户在从服务器上的写权限的参数：root_read_only。

Save exit and restart the MARIADB service.

To enter MARIADB interactive mode, first import the backed up data to the slave server:
MariaDB [(none)]> Source/root/alldb.sql
Then lock all the tables and prohibit the root user from writing:
MariaDB [(None)]> flush tables with read lock;
Use change MASTER to ... on slave. Statement to specify the related property information for master:
Ensure that the slave replication thread is closed before specifying:
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.06 sec)
To start specifying master:
MariaDB [(none)]> change master to master_host= ' 172.16.75.1 ', master_port=3306, master_user= ' Repuser ', master_ Password= ' Reppass ', master_log_file= ' binlog.000014 ', master_log_pos=553;
Query OK, 0 rows affected (0.02 sec)
To turn on replication threads on Slave:
MariaDB [(None)]> start slave;
Query OK, 0 rows affected (0.01 sec)
To view the master-slave replication schema status:
MariaDB [(none)]> show Slave status\g;
1. Row
Slave_io_state:waiting for Master to send event
master_host:172.16.75.1
Master_user:repuser
master_port:3306
Connect_retry:60
master_log_file:binlog.000014
read_master_log_pos:553
relay_log_file:slavelog.000002
relay_log_pos:526
relay_master_log_file:binlog.000014
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:553
relay_log_space:813
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
1 row in Set (0.00 sec)

Error:no query specified

Slave_io_running:yes
Slave_sql_running:yes
If both the IO thread and the SQL thread show Yes, the build succeeds.

Test : Build a database in the master server, if the new database appears from the server, then there is no problem on the test.
In MySQL interactive mode in the master server 172.16.75.1:
MariaDB [(None)]> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Backuptest |
| MySQL |
| MyTest |
| Performance_schema |
+--------------------+
5 rows in Set (0.00 sec)
To create a new database TestDB:
MariaDB [(None)]> CREATE database TestDB;
Query OK, 1 row affected (0.07 sec)

On the server 172.16.75.2, the query database appears as follows, with the new database TestDB on the primary server side:
MariaDB [(None)]> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Backuptest |
| MySQL |
| MyTest |
| Performance_schema |
| TestDB |
| Zabbix |
+--------------------+
7 rows in Set (0.07 sec)

At this point, MySQL's master-slave replication architecture was built successfully.

Problems in setting up the configuration process:
1. There was an unsuccessful connection to the main server side:
MariaDB [(none)]> show Slave status\g;
1. Row
Slave_io_state:connecting to Master
master_host:172.16,75.1
Master_user:repuser
master_port:3306
Connect_retry:60
master_log_file:binlog.000014
read_master_log_pos:553
relay_log_file:slavelog.000001
Relay_log_pos:4
relay_master_log_file:binlog.000014
Slave_io_running:connecting
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:553
relay_log_space:245
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:2005
Last_io_error:error connecting to master ' [email protected],75.1:3306 '-retry-time:60 retries:86400 message:unknown M Ysql Server Host ' 172.16,75.1 ' (2)
last_sql_errno:0
Last_sql_error:
Replicate_ignore_server_ids:
master_server_id:0
1 row in Set (0.01 sec)

Error:no query specified

At this point, clean the master.info and relay-log.info under/var/lib/mysql/, clear the last copied flag bit record and relay logging information, and then restart the MARIADB service from the server, and the connection is back to normal:
[Email protected] mysql]# RM-FR master.info
[Email protected] mysql]# rm-fr relay
[Email protected] mysql]# systemctl restart Mariadb.service

2. There is a request to prohibit the authorized user connection problem, slave_io_running:connecting:
MariaDB [(none)]> show Slave status\g;
1. Row
Slave_io_state:connecting to Master
master_host:172.16.75.1
Master_user:repuser
master_port:3306
Connect_retry:60
master_log_file:binlog.000011
read_master_log_pos:245
relay_log_file:slavelog.000001
Relay_log_pos:4
relay_master_log_file:binlog.000011
Slave_io_running:connecting
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:245
relay_log_space:245
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:1045
Last_io_error:error connecting to master ' [email protected]:3306 '-retry-time:60 retries:86400 message:access denied For user ' repuser ' @ ' 172.16.75.2 ' (using Password:yes)
last_sql_errno:0
Last_sql_error:
Replicate_ignore_server_ids:
master_server_id:0
1 row in Set (0.00 sec)

Error:no query specified

Workaround:
The main experience here is that the primary server-side authorization update is not timely, causing the authorized user to be invalid and naturally unable to connect:
In the interactive mode of the primary server side mariadb:
MariaDB [(None)]> flush privileges;
To see if an authorized user is in effect:
MariaDB [(none)]> show grants for "Repuser" @ "%";
+-------------------------------------------------------------------------------------------------------------- ------+
| Grants for [email protected]% |
+-------------------------------------------------------------------------------------------------------------- ------+
| GRANT REPLICATION SLAVE on . To ' repuser ' @ '% ' identified by PASSWORD ' *304a91f0e46bbb1e641d3d95e225e9aaa27077ce ' |
+-------------------------------------------------------------------------------------------------------------- ------+
1 row in Set (0.00 sec)

Here is a list of the following blogs (with links) in the search for slave_io_running:connecting:
54606894
50504321
18707599

3. The first time I took a command directly from the shell command line when I imported the backup good database file from the server side: Mysql-uroot-p < Alldb.sql, and found that the wait was not responding for a long time
At this time, we found that the primary server side of the initial experiment, the backup of all databases and tables before the read lock:
FLUSH TABLES with READ LOCK;
Read lock No problem, the most important thing is to import from the server side, remember to unlock the read lock, otherwise it will appear I 10,000 years are not in the phenomenon, if you want to in the case of unlocked, can only be imported from the server mariadb interactive mode:
MARIADB [(None)]source/root/alldb.sql

The above is mysql/mariadb a master one from the replication architecture of the entire process and small problem resolution.

About MySQL dual master replication architecture:
The two MySQL servers are mainly from each other, so we have to turn on the binary log and the relay log function on both servers;
Architecture of the dual-master architecture Objective: To prevent single point of failure;

Schema issues that may exist with the dual-master architecture:
1. A significant increase in the risk of inconsistent data;
2. In some or some fields of some tables, if the automatic growth of the field is set using the Auto_increment modifier, the data may be disturbed, resulting in failure to perform operations such as inserting, modifying, or deleting the data;
Define the auto-growth value of one of the server's main nodes is all odd;
auto_increment_increment=2
Auto_increment_offset=1

定义另一个服务器主节点的自动增长的值全部为偶数；    auto_increment_increment=2    auto_increment_offset=2使用上述方式设置的序列号，可以避免主键冲突，但是有可能出现数值的不连续现象，也可以专门提供一台服务器，用来生成序列号；

Configuration scenarios:
In the configuration process, you need to be aware of the following issues:
1. The server_id of two MySQL servers must be set to a different value;
2. All need to start the binary log and relay log, and to ensure that the MySQL user for both log files have write permission;
3. For tables that have an automatic growth ID, there is no problem of primary key conflicts;
4. The user should be authorized to copy the operation on both MySQL servers;
5. On both MySQL servers, you need to use the change MASTER to statement to specify the value of the replication property of the other server;

In configuration file/etc/my.cnf:
[Mysqld]
Innodb_file_per_table=on
Skip_name_resolve=on
Log_bin=binlog
Server_id=101
Sync_binlog=1
Innodb_flush_log_at_trx_commit=1
Relay_log=slavelog
Auto_increment_offset=1
auto_increment_increment=2

Authorized users:
MariaDB [(None)]> grant replication Slave on . to ' repuser ' @ '% ' identified by ' reppass ';

Specify Master as another primary server:
MariaDB [(none)]> change master to master_host= ' 172.16.75.2 ',
Master_user= ' Repuser2 ',
Master_password= ' Reppass ',
master_port=3306,
Master_log_file= ' binlog.000001 ',
master_log_pos=245;

[Mysqld]
Innodb_file_per_table=on
Skip_name_resolve=on
Log_bin=binlog
server_id=202
Sync_binlog=1
Innodb_flush_log_at_trx_commit=1
Relay_log=slavelog
auto_increment_offset=2
auto_increment_increment=2

Authorized users:
MariaDB [(None)]> grant replication Slave on . to ' repuser2 ' @ '% ' identified by ' reppass ';

Specify Master as the first server:
MariaDB [(none)]> change master to master_host= ' 172.16.75.1 ', master_user= ' repuser ', master_password= ' Reppass ', Master_port=3306,master_log_file= ' binlog.000005 ', master_log_pos=245;

On the first primary server side and the second primary server, start the replication thread separately:
Start slave;

There is not much difference between the two-master architecture and the one-master-one from the architecture, in addition to the problem of setting the growth step, it is necessary to turn off read-only from the server in the previous master one from the schema, and the other problems are basically the same as one from the main problem, with the basic experience of one master, the deployment of

About the semi-synchronous replication schema:
In a master multi-slave MySQL architecture, you can keep master synchronized with one server in many slave, and continue using the default asynchronous replication with other slave;

If you want MySQL to support semi-synchronous replication, additional plugins are required; mysql/mariadb installed with RPM package, the plugin is stored by default at:/usr/lib64/mysql/plugin
Semisync_master.so-Rpl_semi_sync_master;
Semisync_slave.so-Rpl_semi_sync_slave;

How to install the plugin:
MariaDB [(None)] > INSTALL PLUGIN plugin_name SONAME ' so_file_name ';

To install a semi-synchronous plug-in:
To install the master server's semi-synchronous plug-in:
MariaDB [(None)] > Install plugin rpl_semi_sync_master soname ' semisync_master.so ';

To view server parameters related to semi-synchronous replication on the primary server: MariaDB [hellodb]> 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 | +------------------------------------+-------+ View status parameters related to semi-synchronous replication on the primary server: MariaDB [hellodb]> show global status like '%    semi% '; +--------------------------------------------+-------+    | variable_name |    Value | +--------------------------------------------+-------+    | rpl_semi_sync_master_clients |    0 | | Rpl_semi_sync_master_net_avg_wait_time |    0 | | Rpl_semi_sync_master_net_wait_time |    0 | | Rpl_semi_sync_master_net_waits |    0 | | Rpl_semi_sync_master_no_times |    0 | | Rpl_semi_sync_master_no_tx |    0 | | Rpl_semi_sync_master_status |    OFF | | Rpl_semi_sync_master_timefunc_failures |    0 | | Rpl_semi_sync_master_tx_avg_wait_time |    0 | | Rpl_semi_sync_master_tx_wait_time |    0 | | Rpl_semi_sync_master_tx_waits |    0 | | Rpl_semi_sync_master_wait_pos_backtraverse |    0 | | rpl_semi_sync_master_wait_sessions |    0 | | Rpl_semi_sync_master_yes_tx |    0 | +--------------------------------------------+-------+ Install the semi-synchronous plug-in from the server: MariaDB [(None)] > Install plugin rpl_semi_    Sync_slave soname ' semisync_slave.so '; View server parameters related to semi-synchronous replication from the server: MariaDB [(None)]> show global variables like '%semi% '; +---------------------------------+-------+    | variable_name |    Value | +---------------------------------+-------+    | rpl_semi_sync_slave_enabled |    OFF | | Rpl_semi_sync_slave_trace_level |    32 | +---------------------------------+-------+ View the status parameters associated with the semi-synchronous replication from the server: MariaDB [(None)]> show global status like '%semi%    ‘; +----------------------------+-------+    | variable_name |    Value | +----------------------------+-------+    | Rpl_semi_sync_slave_status |    OFF | +----------------------------+-------+

To test the method of semi-synchronous replication:
Primary server:
MariaDB [hellodb]> SET @ @global. Rpl_semi_sync_master_enabled=on;
From the server:
MariaDB [(None)]> set @ @global. Rpl_semi_sync_slave_enabled=on;

正常情况下，主服务器上进行的所有的数据修改，会立即同步到开启了半同步负责的从服务器上；验证半同步降级的方法：    在从服务器上，关闭IO_THREAD线程，而后再在主服务器上进行数据修改操作，为了等待从服务器的同步数据更新，主服务器会阻塞所有的其他写操作，直到收到从服务器的确认信息为止；但是如果超过rpl_semi_sync_master_timeout服务器参数所规定的时间，主服务器仍然没有收到从服务器数据同步的确认信息，则自动降级为异步模式；

The above is the MYSQL/MARIADB replication architecture to be configured, from low and high, from more to less, step-by-step, seeking stability.

The following is a list of monitoring and related maintenance operations during the copy process:
1. Monitoring and cleanup of binary logs:
SHOW MASTER | BINARY LOGS;
SHOW MASTER STATUS;
SHOW BINLOG EVENTS in ' binlog_file ';
PURGE MASTER | BINARY LOGS to ' log_name ' | Before datetimme_expr;

    2.复制的监控：        主服务器：            SHOW MASTER | BINARY LOGS;            SHOW MASTER STATUS;            SHOW BINLOG EVENTS IN ‘binlog_file‘;        从服务器：            SHOW SLAVE STATUS\G    3.判断主从节点的数据是否一致：        在创建表时，使用CHECKSUM=1选项，为此表开启校验和功能；        为了能够判断表的校验和，Percona提供了一个检测工具：pt_table_checksum；    4.主从节点数据不一致：        修复的方法通常有两种：            1.删除数据，重新复制；            2.将主服务器的数据进行完全备份，到从服务器上恢复；

MYSQL/MARIADB configuration of master-slave replication architecture and problems in the process