Start to prepare

  • Mysql 8.0.20 and two centos7 servers.
  • For Linux mysql installation, you can use Baidu, you can also use Pagoda tool to build mysql, or you can use Docker to build mysql image.

Master-slave theory

  • The master/slave cluster setup of MySQL can solve the data storage and access pressure of MySQL.
  • Ensure data security: MySQL master/slave configuration is equivalent to an extra backup data.
  • Achieve read/write separation: In Internet projects, many scenarios are read more and write less, so you can do read/write separation, write data to the primary database, read data from the database. Some middleware, such as ShardingSphere, is required for read-write separation.
  • Ensure high availability: When the primary database is down, a new primary database can be elected from the primary database to ensure service availability. Implementing high availability also requires some middleware, such as MMM, MHA, MGR, etc.

Synchronization principle

  • MySQL master/slave schema data synchronization is achieved through the binlog log file. Open binlog log on the primary server to record each step of the database operation, and then the secondary server will have an I/O thread to establish a TCP connection with the primary server to request the binlog from the primary server. The dump thread on the master server transfers the binlog log file contents to the slave server through this TCP connection, and the slave library writes the binlog data read to its relay log file. Another thread from the library then reads the relay file contents and repeats the operation to restore the data. The following figure

  • MySQL’s binlog log is used for master/slave data synchronization, and can also be used for cache data synchronization. For example, the cache data in Redis can simulate a binlog data synchronization request from the node to MySQL, and then write the data to Redis to achieve the consistency between the cache and the database.
  • In the master/slave architecture, the versions of the MySQL servers must be the same, or the version of the master server must be earlier than that of the slave server. There are two more nodes that need time synchronization.

Master-slave structures,

Master database setup

  • First, open the configuration file /etc/my.cnf on the primary node. Configure to open binLog and specify serverId. The following figure
[mysqld]
server-id=47Enable binlog log_bin=master-bin
log_bin-index=master-bin.index
skip-name-Resolve # Set connection port=3306Set mysql installation directory basedir=/usr/local/Mysql > select * from 'datadir' where 'datadir' = 'datadir'=/usr/local/mysql/mysql-Files # Max_connections Max=200# Number of connections allowed to fail. max_connect_errors=10The default server character set is UTF8character-set-server=Utf8 # Default storage engine that will be used when new tables are createddefault-storage-engine=INNODB # uses the "mysql_native_password" plug-in for authentication #mysql_native_password default_authentication_plugin by default=mysql_native_password
Copy the code
  • The following configurations need attention:
    • Server-id: uniquely identifies a service node. You need to configure a separate ID for each cluster node.
    • Log_bin: Turns on binLog logging and specifies the file name
    • Log_bin-index: indicates the binlog file
  • To restart the mysql service, run the service mysqld restart command
  • You can run the following command to view the synchronization status of the primary node:
show master status;
Copy the code

  • File: indicates the binlog file of the current log.
  • Position: indicates the index in the file.
  • Binlog_do_db and binlog_ignore_db refer to libraries that record binlog files and libraries that do not need to record binlog files. (Empty indicates no configuration.)

Build from the database

  • Also open the mysql configuration file: my.cnf
[mysqld] # Master and slave need different servers-id=48Enable MySQL relay log relay-log-index=slave-relay-bin.index
relay-log=slave-relay-Bin # Open binary log log from service-bin=mysql-Bin # causes updated data to be written to log in binary log-slave-updates=1# set3306Port to port=3306Set mysql installation directory basedir=/usr/local/Mysql > select * from 'datadir' where 'datadir' = 'datadir'=/usr/local/mysql/mysql-Files # Max_connections Max=200# Number of connections allowed to fail. max_connect_errors=10The default server character set is UTF8character-set-server=Utf8 # Default storage engine that will be used when new tables are createddefault-storage-engine=INNODB # uses the "mysql_native_password" plug-in for authentication #mysql_native_password default_authentication_plugin by default=mysql_native_password
Copy the code
  • The following configurations need attention:
    • Server-id: unique ID of a service node.
    • Relay-log: enables relay logging on the slave server.
    • Log-bin: enables binlog logging.
  • Then start the MySQL slave server and set its master synchronization status:
# Log in from service mysql-u root -p; # Set synchronization to MASTER: CHANGE MASTERTO
MASTER_HOST='192.168.232.128',
MASTER_PORT=3306,
MASTER_USER='root',
MASTER_PASSWORD='root',
MASTER_LOG_FILE='master-bin.000004',
MASTER_LOG_POS=156,
GET_MASTER_PUBLIC_KEY=1; # open slavestartslave; Check the primary/secondary synchronization statusshowslave status; Or useshowslave status \G; This is a neat way to view itCopy the code
  • Note: CHANGE MASTER must specify MASTER_LOG_FILE and MASTER_LOG_POS to be consistent with the MASTER database. Follow-up check whether the primary and secondary schemas are configured successfully also requires the consistency of the file and position attributes.

  • Pay attention to the two circled attributes in the figure above. If they are consistent with the primary node, the master/slave synchronization is established.

Master/slave synchronization test

  • Run the show databases command to view the databases on the two mysql servers. The following figure

  • Then add a database on the master with the following command, and find the corresponding database on the slave. The following figure
mysql> create database syncdemo;
Query OK, 1 row affected (0.00 sec)
Copy the code

  • Then add a table and a piece of data to the SyncDemo database and find corresponding tables and data on the slave library. The following figure
mysql> use syncdemo;
Database changed
mysql> create table demoTable(id int not null);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into demoTable value(1);
Query OK, 1 row affected (0.01 sec)
Copy the code

  • So all writes on the primary are synchronized to the secondary.
  • If Slave_sql_runing=no is found when viewing the status on the slave library, the primary/secondary synchronization has failed. This could be because a write operation on the slave library conflicted with a synchronized SQL operation, or a transaction was rolled back after the slave library was restarted.
  • Data cannot be synchronized from the slave to the master. If you want TO synchronize the slave database TO the MASTER database, you only need TO configure the CHANGE MASTER TO… Then this is the two-master mode. This also allows you to configure the intermaster cluster mode.

Master/slave synchronous extension

  • All of these are full database synchronization operations for master and slave, whereas in practice you might be synchronizing only one database or table. That, too, is configurable.
  • My.cnf = my.cnf = my.cnf = my.cnf
# Binary database name to be synchronized binlog-do-db=Masterdemo # is reserved only7Binary logs for days in case the disk is full of logs (optional) EXPIRE-logs-days = 7# Not backing up database binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys
Copy the code
  • The configuration file my.cnf in the slave library is as follows:
If the salve library name is the same as the Master library name, use this configuration replicate-do-db =Masterdemo # If the name of the master library [MastDemo] is different from the name of the Salve library [mastDemo01], use the following configuration-rewrite-db = masterdemo ->Masterdemo01 # Specifies which table replicates you want to synchronize, if not all-wild-do-table=masterdemo01.t_dict
replicate-wild-do-table=masterdemo01.t_num
Copy the code
  • The binlog_do_db and binlog_lgnore_db parameters can be used by using the shou master status command.
  • In this case, to ensure data consistency, data is usually written to the primary library and data is read from the secondary library. This is called MySQL read/write separation. If you want to implement read/write separation for MySQL, then you need to rely on the third-party plug-in ShardingSphere.
  • To limit data written to the slave, you can configure the read_only parameter (set global read_only=1;). This restricts the user from writing data directly, but does not affect the synchronization of data from the secondary library reading the binlog operation of the primary library. In addition, the read_only command can only restrict the write data of common users. Users with the super permission can still write data. To restrict the write data of the super user, you can run the super_read_only=0 command.

GTID Cluster synchronization mode

  • GTID essentially synchronizes data using a binlog log, but it identifies the slave synchronization progress based on a global transaction ID. Therefore, GTID is a global transaction ID, which is globally unique and increasing, ensuring that each transaction committed on the primary database generates a unique ID in the replication cluster.
  • In gtid-based synchronization, the slave tells the master that the transaction has completed at the slave’s GTID. The master then sends all transactions that have not been executed on the slave to the slave for execution. Using GTID synchronous replication ensures that the same transaction will only be executed on the specified slave once. This avoids data inconsistency due to offsets.
  • The configuration of GTID is similar to the preceding configuration. You only need to modify the configuration in the my.cnf configuration file:
# Configure gtid_mode for the primary database=on
enforce_gtid_consistency=on
log_bin=on
server_id=Set a separate binlog_format=row
Copy the code
# Configure gtid_mode for the slave library=on
enforce_gtid_consistency=on
log_slave_updates=1
server_id=Set one separatelyCopy the code
  • Then start the secondary and primary libraries respectively to enable GTID synchronous replication.

The cluster expansion

  • In the above one master one slave cluster pattern, if you want to scale to one master many slaves, you just need to add the binlog copy of the slave library.
  • If the previous cluster has been running for some time and the slave server needs to be extended, the binlog files of the previous master library cannot be copied to the new slave server. Add a data replication operation for the new slave server. MySQL data backup and recovery operation is relatively simple, you can master the original database SQL file, and then put into the new slave server execution, and then repeat the above configuration synchronization configuration of the slave library can be.

Semi-synchronous replication

  • Asynchronous replication is used when master/slave data is synchronized in MySQL. The master can be written to the binlog file after the user commits the transaction, and then returned to the client. Another thread dump from the master sends binlog data to the slave. This asynchronism may cause the primary library to break down after inserting data and returning data. At this time, the dump thread has not sent data to the secondary library, resulting in data inconsistency.
  • To solve this problem, MySQL has a semi-synchronous replication mode to ensure data security, which is to send binlog data asynchronously instead of synchronously.

  • When the user commits the transaction to the binlog file, the user does not return the transaction immediately. Instead, the user waits for the slave to receive the data and writes the data to the relay log file and then returns the successful response to the client. The master server has a timeout mechanism when waiting for the slave SERVER’s ACK response. If you do not have an ACK from the slave, the data will be degraded to asynchronous replication.
  • This synchronous data replication improves data consistency compared with asynchronous data replication. However, it is not absolutely consistent, because it is guaranteed that the binlog will be transferred to at least one slave library after the transaction is committed, but it is not guaranteed that the slave library will execute the transaction successfully. And because there are synchronous waits, there will be some delays and service performance will degrade.

Set up the semi-synchronous replication mode

  • The semisync_master.so and semisync_salve.so files are in the lib/plugin directory of the MySQL installation directory. You need to install the Semisync_master module on the primary server and semisync_salve module on the secondary server.

  • First install the modules for the primary server
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)
mysql> show global variables like 'rpl_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_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set.1 warning (0.02 sec)
mysql> set global rpl_semi_sync_master_enabled=ON;
Query OK, 0 rows affected (0.00 sec)
Copy the code
  • The first command: to install a semi-synchronous replication module through an extension library, specify the name of the extension library.
  • Rpl_semi_sync_master_timeout indicates the timeout waiting time of semi-synchronous replication. Self-configurable.
  • The third command is to enable semi-synchronous replication
  • Then install the slave server module
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)
mysql> show global variables like 'rpl_semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set.1 warning (0.01 sec)
mysql> set global rpl_semi_sync_slave_enabled = on;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'rpl_semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set.1 warning (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
Copy the code
  • The installation process for the secondary server is similar to that for the primary server. You need to restart the secondary server after the installation.

Master/slave schema data latency

  • In the master-slave architecture, there is a delay between synchronous replication of data and MySQL read/write separation. Data is being read from the database while the master data is being inserted. The delay in replicating the master data to the slave data causes data inconsistency.
  • The problem is that when the user writes the data to commit the transaction, the Java backend, for example, has multiple threads writing concurrently and then pulling the binlog file from the server with a single thread, it is inefficient in the middle.
  • MySQL supports server parallel replication after version 5.7. You can set slave_parallel_workers to a number greater than 0 on the slave server and set the slave_parallel_type parameter to LOGLCAL_CLOCK.

Mysql high availability solution

  • The MySQL cluster above is built based on its own functions and does not have high availability functions. That is, if the master server fails, the slave server cannot automatically switch over to the master server. If you want to use MySQL’s high availability, you need some third-party tools to do it.
  • Common ones are: MMM, MHA, MGR. Some things they have in common:
    • Monitor the Mater master node of the master-slave cluster architecture.
    • Automatically migrates the master node.
    • Configure the secondary node to synchronize data with the new primary node.
  • Mention that MHA high availability scheme is used more in large Internet companies at present. MMM is about to become obsolete, while MGR is a new tool, lacking authoritative certification.
  • In the end, I will not go into detail about the implementation principles of three of them, but those who are interested can study on their own.

The last

  • Study with an open mind and make progress together