MySQL master-slave replication principle, master-slave replication (asynchronous), semi-synchronous replication, SSL-based Replication

Overview replication is usually used to create a copy of the master node to ensure high availability by adding redundant nodes. Of course, replication can also be used for other purposes, such as reading and analyzing data on the slave node. In horizontally Scalable businesses, replication is easy to implement, mainly in the use of the master node for write operations, multiple slave nodes for read operations, in mysql5.5 by default

Overview replication is usually used to create a copy of the master node to ensure high availability by adding redundant nodes. Of course, replication can also be used for other purposes, such as reading and analyzing data on the slave node. In horizontally Scalable businesses, replication is easy to implement, mainly in the use of the master node for write operations, multiple slave nodes for read operations, in mysql5.5 by default

Overview

Replication is usually used to create a copy of the master node. It ensures high availability by adding redundant nodes. Of course, replication can also be used for other purposes, such as reading and analyzing data from the slave node. In horizontally Scalable businesses, replication is easy to implement, mainly in the use of the master node for write operations, multiple slave nodes for read operations, in mysql5.5, the default is asynchronous replication.

The Asynchronization of mysql replication means that a transaction is first committed on the master node, then copied to the slave node and applied on the slave node, this means that the data on the master and slave nodes may be inconsistent at the same time point. The advantage of asynchronous replication is that it is faster than synchronous replication. If there is a high requirement on data consistency, it is better to adopt synchronous replication.

The mysql-5.5 began to support semi-synchronous replication, also known as semi-synchronous replication, to maintain master-slave consistency in the transaction environment

The mysql-5.6 starts to support delayed replication.

The principles of mysql replication are the same at present. The master records the operation to the bin-log, and a slave thread reads the bin-log from the master and saves them to the relay-log, another slave thread replays the operations in relay-log to synchronize data with the master.

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151214/100I52C6-0.jpg "title =" QQ20140610115311.jpg "alt =" wKioL1OWgmrSKROTAAEnELMIXRI329.jpg "/>

The first part of this process is that the master records binary logs. Before each Transaction Completes data update, the master will record these changes in the second log. MySQL writes transactions into binary logs in sequence, even if the statements in the transaction are executed in a crossover manner. After the event is written to the binary log, the master notifies the storage engine to submit the transaction.
The next step is to copy the binary log of the master to its own relay log. First, slave starts a working thread-I/O thread. The I/O thread opens a normal connection on the master and starts the binlog dump process. The Binlog dump process reads the event from the binary log of the master. If it has already followed the master, it will sleep and wait for the master to generate a new event. The I/O thread writes these events to the relay log.
The last step of SQL slave thread processing this process. The SQL thread reads the event from the relay log, replays the event, and updates the slave data so that it is consistent with the data in the master. As long as the thread is consistent with the I/O thread, the relay log is usually located in the OS cache, so the overhead of the relay log is very small.
In addition, there is also a working thread in the master: Like other MySQL connections, opening a connection in the master will also enable the master to start a thread. There is an important limitation in the replication process-replication is serialized on the slave, that is, the parallel update operations on the master cannot be performed in parallel on the slave.

Asynchronous master-slave replication Configuration

Preparation:

OS: rhel5.8 _ i386

SoftWare: mysql-5.5.28-linux2.6-i686.tar.gz

1. Install mysql

Tar xf mysql-5.5.28-linux2.6-i686.tar.gz-C/usr/local/# cd/usr/local/# ln-s mysql-5.5.28-linux2.6-i686/mysql # groupadd-r mysql # useradd-r-g mysql-s/sbin /nologin mysql # mkdir/mydata/data-p # chown-R mysql. mysql/mydata/data/# chown-R root. mysql/usr/local/mysql/* # cp support-files/my-large.cnf/etc/my. cnf # cp support-files/mysql. server/etc/init. d/mysqld [mysqld] innodb_file_per_table = 1 datadir =/mydata/data # because mysql is installed in binary mode, you must specify the database directory location # vim/etc/profile. d/mysqld. sh export PAHT = $ PATH:/usr/local/mysql/bin #. /etc/profile. d/mysqld. sh

2. master server configuration

    # vim /etc/my.cnf        [mysqld]        log-bin = master-bin        log-bin-index = master-bin.index                server-id = 1    # scripts/mysql_install_db --user=mysql --datadir=/mydata/data/    # service mysqld start    # mysql    mysql> grant replication slave on *.* to repl@'192.168.100.12' identified by 'asdasd';    mysql> flush privileges;    mysql> flush logs;    mysql> show master logs;    +-------------------+-----------+    | Log_name          | File_size |    +-------------------+-----------+    | master-bin.000001 |     27326 |    | master-bin.000002 |   1038693 |    | master-bin.000003 |       379 |    | master-bin.000004 |       107 |    +-------------------+-----------+    mysql> purge binary logs to 'master-bin.000004';

3. slave server configuration

    # vim /etc/my.cnf        [mysqld]        relay-log = relay-log        relay-log-index = relay-log.index        read-only = 1        #innodb_file_per_table = 1        #binlog_format=mixed        server-id = 10    # scripts/mysql_install_db --user=mysql --datadir=/mydata/data/    # service mysqld start    mysql> change master to master_host='192.168.100.11',master_user='repl',master_password='asdasd',master_log_file='master-bin.000004',master_log_pos=107;    mysql> show slave status\G    *************************** 1. row ***************************                   Slave_IO_State:                       Master_Host: 192.168.100.11                      Master_User: repl                      Master_Port: 3306                    Connect_Retry: 60                  Master_Log_File: master-bin.000004              Read_Master_Log_Pos: 107                   Relay_Log_File: relay-log.000001                    Relay_Log_Pos: 4            Relay_Master_Log_File: master-bin.000004                 Slave_IO_Running: No                Slave_SQL_Running: No                  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: 107                  Relay_Log_Space: 107                  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: 0                    Last_IO_Error:                    Last_SQL_Errno: 0                   Last_SQL_Error:       Replicate_Ignore_Server_Ids:                  Master_Server_Id: 0    1 row in set (0.00 sec)    mysql> start slave;    mysql> show slave status\G             Slave_IO_Running: Yes            Slave_SQL_Running: Yes            ..............

Now the master-slave asynchronous replication is complete.

Note:

Slave_id must be unique

Slave does not need to enable binary logs, but must be set in some cases, such as mysql cascade. slave is the master of other slave instances. Therefore, bin_log must be set. The default value is hostname. However, if the hostname is changed, a problem may occur.

Some may have enabled the slave binary log, but have not set log_slave_updates. Check whether the slave Data has changed. This is an incorrect configuration. Therefore, try to use read_only = 1 to prevent data changes (except the SQL _thread process ).

Start slave: start the IO_Thread and SQL _Thread threads on the slave server. You can also start them separately.

Set sync-binlog = 1 on the master server for transaction security.

Reset the change master parameter:

            mysql> slave stop;            mysql> reset slave;            mysql> change master to master_host='192.168.100.11',master_user='repl',master_password='asdasd',master_log_file='master-bin.000005',master_log_pos=107;

Because slave will automatically connect to the master, when we sometimes need to manually adjust it, we can move the master under the slave Data Directory before starting. ino and relay.info files, or check whether the variable "skip-slave-start" exists in variables.

Semi-synchronous Replication

/Usr/local/mysql/lib/plugin/semisync_master.so
/Usr/local/mysql/lib/plugin/semisync_slave.so

1. master server configuration

    mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';    mysql> show 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    |    +------------------------------------+-------+    mysql> set global rpl_semi_sync_master_enabled=1;    mysql> set global rpl_semi_sync_master_timeout=1000;

2. Slave Configuration

    mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';    mysql> show variables like '%semi%';    +---------------------------------+-------+    | Variable_name                   | Value |    +---------------------------------+-------+    | rpl_semi_sync_slave_enabled     | OFF   |    | rpl_semi_sync_slave_trace_level | 32    |    +---------------------------------+-------+    mysql> set global rpl_semi_sync_slave_enabled=1;

To take effect permanently, write the above variables to the mysqld fields in the master and slave respectively.

MySQL replication Filtering

MySQL replication filtering can be performed on the Master or Slave
Because the Master-based filtering operation affects the integrity of binary logs, it will affect our future point recovery in a timely manner. Therefore, we generally do not recommend performing copy filtering on the Maser.

1. Database-based

Binlog-do-db // binlog-do-db indicates which database-related write class and modify class commands will be written into binlog-ignore-db // binlog-ignore-db indicates ignore (blacklist)

2. Table-based
Replicate-do-table =
Replicate-ignore-table =

3. For tables, you can also configure and filter using wildcards.
Replicate-wild-do-table =
Replicate-wild-ignore-table =

SSL Replication

Both the master and slave servers must have certificates and private keys. By default, the SSL function of the master and slave servers is disabled and must be enabled first.

    mysql> show variables like '%ssl%';    +---------------+----------+    | Variable_name | Value    |    +---------------+----------+    | have_openssl  | DISABLED |    | have_ssl      | DISABLED |    | ssl_ca        |          |    | ssl_capath    |          |    | ssl_cert      |          |    | ssl_cipher    |          |    | ssl_key       |          |    +---------------+----------+

1. Configure Master as the CA Certificate Server

# vim /etc/pki/tls/openssl.cnf# cd /etc/pki/CA/# (umask 077; openssl genrsa 1024 >private/cakey.pem)# openssl req -new -x509 -key private/cakey.pem -out cacert.pem    Country Name (2 letter code) [GB]:CN     State or Province Name (full name) [Berkshire]:GD    Locality Name (eg, city) [Newbury]:ZS    Organization Name (eg, company) [My Company Ltd]:NEO    Organizational Unit Name (eg, section) []:tech    Common Name (eg, your name or your server's hostname) []:station01.neo.com# mkdir newcerts certs crl# touch index.txt# echo 01 >serial

2. Prepare a private key and issue a certificate for MySQL on the Master node.

# mkdir /usr/local/mysql/ssl# cd /usr/local/mysql/ssl/#(umask 077; openssl genrsa 1024 > mysql.key)# openssl req -new -key mysql.key -out mysql.csr -days 3650    Country Name (2 letter code) [GB]:CN    State or Province Name (full name) [Berkshire]:GD    Locality Name (eg, city) [Newbury]:ZS    Organization Name (eg, company) [My Company Ltd]:NEO    Organizational Unit Name (eg, section) []:tech    Common Name (eg, your name or your server's hostname) []:station01.neo.com    Email Address []:        Please enter the following 'extra' attributes    to be sent with your certificate request    A challenge password []:    An optional company name []:# openssl ca -in mysql.csr -out mysql.crtUsing configuration from /etc/pki/tls/openssl.cnfCheck that the request matches the signatureSignature okCertificate Details:        Serial Number: 1 (0x1)        Validity            Not Before: May 28 02:26:17 2014 GMT            Not After : May 28 02:26:17 2015 GMT        Subject:            countryName               = CN            stateOrProvinceName       = GD            organizationName          = NEO            organizationalUnitName    = tech            commonName                = station01.neo.com        X509v3 extensions:            X509v3 Basic Constraints:                 CA:FALSE            Netscape Comment:                 OpenSSL Generated Certificate            X509v3 Subject Key Identifier:                 A4:B7:A6:98:9F:60:08:BE:86:87:65:5F:B6:13:BC:4A:5B:D4:44:3A            X509v3 Authority Key Identifier:                 keyid:4F:D8:57:42:D9:39:17:7D:39:44:91:01:A4:01:DE:32:92:D6:F9:DFCertificate is to be certified until May 28 02:26:17 2015 GMT (365 days)Sign the certificate? [y/n]:y1 out of 1 certificate requests certified, commit? [y/n]yWrite out database with 1 new entriesData Base Updated# chown mysql.mysql *

3. Apply for a certificate on Slave

# mkdir /usr/local/mysql/ssl# (umask 077; openssl genrsa 1024 >mysql.key)# openssl req -new -key mysql.key -out mysql.csr -days 3650    Country Name (2 letter code) [GB]:CN    State or Province Name (full name) [Berkshire]:GD    Locality Name (eg, city) [Newbury]:ZS    Organization Name (eg, company) [My Company Ltd]:NEO    Organizational Unit Name (eg, section) []:tech    Common Name (eg, your name or your server's hostname) []:station02.neo.com    Email Address []:        Please enter the following 'extra' attributes    to be sent with your certificate request    A challenge password []:    An optional company name []:# scp mysql.csr 192.168.100.11:/root/

4. The Master node issues a certificate for the Slave.

# openssl ca -in mysql.csr -out mysql.crtUsing configuration from /etc/pki/tls/openssl.cnfCheck that the request matches the signatureSignature okCertificate Details:        Serial Number: 2 (0x2)        Validity            Not Before: May 28 02:36:24 2014 GMT            Not After : May 28 02:36:24 2015 GMT        Subject:            countryName               = CN            stateOrProvinceName       = GD            organizationName          = NEO            organizationalUnitName    = tech            commonName                = station02.neo.com        X509v3 extensions:            X509v3 Basic Constraints:                 CA:FALSE            Netscape Comment:                 OpenSSL Generated Certificate            X509v3 Subject Key Identifier:                 81:9F:5B:E7:06:D0:64:B7:E6:81:3F:98:95:71:D4:DF:C6:B8:CE:3D            X509v3 Authority Key Identifier:                 keyid:4F:D8:57:42:D9:39:17:7D:39:44:91:01:A4:01:DE:32:92:D6:F9:DFCertificate is to be certified until May 28 02:36:24 2015 GMT (365 days)Sign the certificate? [y/n]:yes1 out of 1 certificate requests certified, commit? [y/n]yesWrite out database with 1 new entriesData Base Updated# scp mysql.crt 192.168.100.12:/usr/local/mysql/ssl/# scp /etc/pki/CA/cacert.pem 192.168.100.12:/usr/local/mysql/ssl/

5. Run/etc/my. cnf on the Master node to enable ssl and set Master/Slave nodes.

# Vim/etc/my. cnf [mysqld] log-bin = mysql-bin sync_binlog = 1 ## binary log server-id = 1 ## this id must be globally unique innodb_flush_log_at_trx_commit = 1 ## immediately add transaction logs per second flushing to disk ssl # enabling ssl is disabled by default, show variables like '% ssl %' in mysql to view ssl_ca =/usr/local/mysql/ssl/cacert. pem # ca File Location ssl_cert =/usr/local/mysql/ssl/mysql. crt # Certificate file location ssl_key =/usr/local/mysql/ssl/mysql. key ## location of the private key file

6. Start mysql and view ssl information

# service mysqld start # mysql mysql> show variables like '%ssl%'; +---------------+---------------------------------+ | Variable_name | Value                           | +---------------+---------------------------------+ | have_openssl  | YES                             | | have_ssl      | YES                             | | ssl_ca        | /usr/local/mysql/ssl/cacert.pem | | ssl_capath    |                                 | | ssl_cert      | /usr/local/mysql/ssl/mysql.crt  | | ssl_cipher    |                                 | | ssl_key       | /usr/local/mysql/ssl/mysql.key  | +---------------+---------------------------------+

7. Create a minimum permission account for synchronization and require ssl

mysql> create user 'backup_ssl'@'192.168.100.12' identified by 'redhat'; mysql> revoke all privileges,grant option from 'backup_ssl'@'192.168.100.12'; mysql> grant replication slave,replication client on *.* to 'backup_ssl'@'192.168.100.12' require ssl; mysql> flush privileges;mysql> flush logs;

8. Compile/etc/my. cnf on Slave, enable ssl, and set Master/Slave

# Vim/etc/my. cnf [mysqld] server-id = 2 # This id must be globally unique # log-bin = mysql-bin # comment out, slave server does not need binary log relay-log = mysql-relay # relay log relay-log-index = mysql-ralay.index # relay directory read-only = 1 # slave server read-only ssl # # ssl is disabled by default, show variables like '% ssl %' in mysql to view ssl_ca =/usr/local/mysql/ssl/cacert. pem # ca File Location ssl_cert =/usr/local/mysql/ssl/mysql. crt # Certificate file location ssl_key =/usr/local/mysql/ssl/mysql. key ## location of the private key file

9. Enable mysqld and view ssl information

# servie mysqld start mysql> show variables like '%ssl%'; +---------------+---------------------------------+ | Variable_name | Value                           | +---------------+---------------------------------+ | have_openssl  | YES                             | | have_ssl      | YES                             | | ssl_ca        | /usr/local/mysql/ssl/cacert.pem | | ssl_capath    |                                 | | ssl_cert      | /usr/local/mysql/ssl/mysql.crt  | | ssl_cipher    |                                 | | ssl_key       | /usr/local/mysql/ssl/mysql.key  | +---------------+---------------------------------+

10. Start the slave synchronization process and connect to the master server

Mysql> change master to-> master_host = '2017. 168.100.11 ',-> master_user = 'backup _ ssl',-> master_password = 'redhat ',-> master_log_file = 'mysql-bin.000004',-> master_ssl = 1, -> master_ssl_ca = '/usr/local/mysql/ssl/cacert. pem',-> master_ssl_cert = '/usr/local/mysql/ssl/mysql. crt ',-> master_ssl_key ='/usr/local/mysql/ssl/mysql. key'; mysql> start slave mysql> show slave status \ G; # view the slave status

11. Pay attention to the following parameters:

Slave_IO_Running: Yes # Whether IOthread is running. If No is used, it indicates that slave is not running properly. If No is used, it indicates that slave is running abnormally. If No is used, it indicates that Master_SSL_CA_File is not running properly: /usr/local/mysql/ssl/cacert. pem # whether ssl Master_SSL_Cert is Enabled:/usr/local/mysql/ssl/mysql. crt Master_SSL_Key:/usr/local/mysql/ssl/mysql. key Master_Log_File: mysql-bin.00005 # The last received master server binary Exec_Master_Log_Pos: 338 # The Last executed location, check whether the location in the master is Last_IO_Errno: 0 # The Last IOthread has no error