MySQL master-slave architecture, replication, semi-synchronization, and SSL encrypted Replication

The image function of csdn is too painful. You need to manually upload images. When Can Windows Live writer be opened? You are welcome to visit my personal website: www.deanlinux.com to write only some technical articles.

MySQL Master/Slave architecture:

In enterprise applications, MySQL often uses a master-slave architecture or a master-master architecture, which can achieve load balancing on the MySQL server and improve the performance of the MySQL server. The key is that Mysql Data Synchronization and replication can be implemented to provide a convenient mechanism for enterprise data backup.

I. first understand the MySQL replication function:

1) supports the one-master-multiple-slave mechanism. Data is synchronized from the master server and replicated from the server.

2) multi-level structure, master-slave master (master-slave)

3) supports filtering (only part of the data of the master service can be copied, not all)

2. For MySQL of the master-slave architecture, modification data can only be executed on the master server for Data Consistency purposes. Let's take a look at its replication type.

1) Statement-based replication: Execute the statements executed on the master service once on the slave server.

This method has a defect. If it is like updata T1 set salris = salaris + 1000, it will execute 1000 times for each data to be changed, instead of based on rows, which wastes a lot of system resources.

2) Row-based copy row: after changing the content, copy the row instead of executing it again.

Update T1 set salaris = salaris + 1000

3) hybrid replication: Statement + row

Statement replication is preferred. If the content and data of statement replication are accurate, row replication is enabled.

However, imagine that the replication of a single master server in this architecture may become a System Bottleneck if there are many write operations. The excessive number of slave servers will also result in a waste of storage space. Therefore, this architecture can only optimize read operations and write data on the master server.

The main functions of replication:

1) implement data distribution, but this distribution means that the master and slave servers provide the same data.

2) Load Balancing of data (mainly refers to reading and writing needs to be implemented by other mechanisms), mainly through the LVS cluster or MySQL-proxy.

3) backup sends a copy request to the slave server. The master server transfers the data to the slave server for hot backup or warm backup, or stop the service for cold backup when backup is required.

4) provides high availability and Failover functions to ensure that the service is not interrupted.

5) test MySQL upgrade: Back up data on the primary service to the slave server. If you can use the upgrade service, upgrade the data to the primary service (if the performance is poor, it is only a temporary replacement ).

Iii. Important Notes:

1) the MySQL replication process is in plain text during network upload and transmission, but supports SSL encryption.

2) MySQL replication requires that the server IDs of the master-slave architecture cannot be the same.

= ================================

Next we will implement the MySQL server and replication process of the master-slave architecture. We need to prepare two virtual machines, one as the master MySQL and the other as the slave MySQL, for example:

1) set a server as the master server

# The master server must be set to a static address.

# In versions earlier than 5.5, set the default engine to InnoDB. The setting method is as follows: in/etc/My. add default-engine = InnoDB to CF and restart the service. The installation process is no longer cumbersome. I use the Binary Package mysql5.5

(1) configure the IP address,

ifconfig eth0 172.16.14.11vim /etc/sysconfig/network-scripts/ifcfg-eth0DEVICE=eth0BOOTPROTO=staticONBOOT=yesHWADDR=00:0c:29:f5:6d:47IPADDR=172.16.14.11NETMASK=255.255.0.0GATEWAY=172.16.0.1

(2) modify the configuration file to become the master server

Vim/etc/My. CNF # Add or modify the following line # If the version is 5.0, add: default-engine = innodblog-bin = mysql-BINLOG-bin-Index = mysql-bin.indexbinlog_format = mixdserver_id = 1 service MySQL restart

(3) create a user and grant permissions

Mysqlmysql> grant replication client, replication slave on *. * To repl @ '2017. 16. %. % 'identified by 'redhat'; # replication client: the user has the right to ask where the submitter/controller is. Replication slave: required to reply to the submitter. Mysql> flush privileges;

2) set a server as a slave server

Vim/etc/My. CNF # The slave server does not need binary logs, only need to start relay log comment out log-bin and binlog_formatrelay-log = relay-bin # slave server need to enable relay log relay-log-Index = relay-bin.indexserver-id = 11 # id = 1 is the master server, others indicate the relay-related variable settings from the server service mysqd startmysql #. The server_id values are mysql> show variables like 'relay % '; mysql> show variables like 'server _ id '; # Set the connection account and password (you can set SSL, etc.) mysql> change master to master_host = '2017. 16.14.11 ', mysql> master_user = 'repl', # note that No. mysql> master_password = 'redhat'; # Check the slave service status, whether to enable mysql> show slave status \ G # enable slave server mysql> Start slave # after a while, run the show command to view the status. Mysql> show slave status \ G # if an error occurs, view the error log mysql> show variables like '% erro %' Vim/etc/My. cnfslave_skip_erros = on

========================================================== ==========

# If MySQL always reports an error during the above process, we can ignore it using the following options:

Add replicate-ignore-DB = dB in my. CNF to ignore a database during slave Server replication.

# If an error is reported for the MySQL database, replicate-ignore-DB = MySQL and restart the service.

Stop slave;

Start slave;

Show slave status \ G # make sure there is no error before performing the following copy process

========================================================== ================

3) Implementation of Database Replication

(1) # create a database on the master server

mysql> CREATE DATABASE MYDB； mysql> USE MYDB；mysql> CREATE TABLE t1 ( name varchar(20) );

(2) set the slave server to start the replication thread

Mysql> Start slave;

Query OK, 0 rows affected (0.00 Sec)

Mydb is displayed. Copied successfully!

========================================================= =

Detailed description of the replication process:

Assume that we have four MySQL servers: 1 master: A 3 slave: B c d

After the slave server B c d initiates a copy request at the same time, master server a transmits data to slave server B c d. This process is asynchronous. After a receives the request, no matter whether B c d is synchronized or not, if data is modified, it cannot be controlled during use, this may cause data inconsistency (synchronization to half is interrupted, but the master server does not care), and it takes a long time. Later, Goole introduced a plug-in for MySQL to achieve semi-synchronization, which greatly reduced the synchronization time, that is, semi-sync.

Concept of semi-sync: During data synchronization, only one of the slave servers (such as B) is required. In this way, the slave server synchronizes data to other servers (c d)

However, semi-synchronization also causes problems: Network reasons (such as slow network speed), resulting in delayed synchronization.

Solution:After a slave server cannot be synchronized, it is automatically downgraded to asynchronous.

About semi-sync:

Semi-sync was first implemented by Google. The code was mainly contributed by Mark Callaghan, Wei Li (@ Google), and others. Google originally raised the requirement to mongoki, but then it couldn't wait to implement it by itself ....... (Now Mark callaghan has jumped to Facebook. In addition to Google, he has also worked in Informix and Oracle)

After 5.5, the MySQL replication team transplanted the code as plugin and independently converted semi-sync into a MySQL plug-in. The main code transplant is Zhenxing he (reference ).

The following describes how to implement the semi-synchronous replication process (based on the above ):

(1) load the plug-in

If MySQL version 5.5 is used, related plug-ins are saved in/usr/local/MySQL/lib/plugin/, and semisync_master.so semisync_slave.so must be installed.

CD/usr/local/MySQL/lib/plugin/LS-A semisync _ * semisync_master.so semisync_slave.so # Run the following command to load the plug-in # Install plugin rpl_semi_sync_master soname 'semisync _ on the master server _ master. so '; # mysql> install plugin rpl_semi_sync_slave soname' semisync _ slave on the slave server. so '; after the plug-in is loaded for the first time, MySQL records the plug-in to the system table MySQL. in plugin, the system is automatically loaded next time. However, we still need my. in CNF, add the following record to open the semi-syncrpl_semi_sync_master_enable = 1rpl_semi_sync_master_timeout = 1000 my. add the following record to CNF: rpl_semi_sync_slave_enable = 1. Restart the MySQL service on the Master/Slave server.

========================================================== ======

# You can also set global variables as follows:

Set global rpl_semi_sync_master_enabled = 1

# Cancel plug-in loading

Mysql> uninstall plugin rpl_semi_sync_master;

========================================================== ======

Check whether semi_sync on the slave server is enabled

Check whether semi_sync is enabled on the master server. Note that the clients value is 1, which indicates that the master-slave semi-sync replication connection is successful.

(2) create a database tests on the master server, enable replication on the slave server, and observe the changes.

# From the server

mysql> stop slave;mysql> start slave;

# On the master server

The preceding important status values are:

Rpl_semi_sync_master_tx_avg_wait_time: the average waiting time for a transaction because semi_sync is enabled.

Rpl_semi_sync_master_net_avg_wait_time: average wait time from network to network after the transaction enters the waiting queue

Based on the above two status values, we can know how much the network consumption of semi-sync is and how much extra consumption is incurred for a transaction.

Rpl_semi_sync_master_status indicates whether the current semi-sync works properly.

From the rpl_semi_sync_master_no_times variable, you can know whether semi-Sync has timed out and failed for a period of time. This counter records the number of such failures.

========================================================== = Cut-in line ================================================== ============================

Use SSL to encrypt the replication process (very simple ):

See Official Documents: http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-ssl.html

Supplement:

Set the slave server to read-only to prevent users from modifying data on the slave server:

Show variables like '% read %'

Set global read_only = on;

--------------------------------------------------

Filtering implementation during replication: filtering can be implemented on the master server or slave server.

BINLOG-do-DB whitelist specifies the database to be copied

Database to be ignored in the BINLOG-ignore-DB blacklist

Note: Do not use both options. If both options are used, only the whitelist takes effect.

It is generally not recommended to filter data on the master server, which may result in incomplete binary files and cannot be restored at a time point.

Usage: Filter Databases

Replicate-do-DB = mydb1

Replicate-do-DB = mydb2

Replicate-do-table = table

# Symbol matching

Replicate-wild-do-table = db_parttern.tbl_pattern

If the following is defined, will the statements modified by the MySQL database on the master server be copied?

Replicate-ignore-DB = MySQL

It will still be copied, but it will not be saved, occupying the relevant bandwidth.