MySQL master-slave copy and read/write separation

Objective

Use MySQL proxy and MySQL replication for read-write separation

mysql Replication You can distribute master data replication across multiple slave, and then use slave to share the read pressure of master. For foreground applications, it is important to consider how to distribute the read pressure across multiple slave. If each application needs to implement a read-write separation algorithm, one cost is too high, and secondly, if slave adds more machines, the application will be modified. Obviously, the architecture of the entire system is more extensible if the application and database are added to a middle tier that is specifically designed to implement read-write separation. MySQL Proxy is such a middle-tier agent, simply said, MySQL proxy is a connection pool, is responsible for the foreground application connection request forwarded to the background database, and by using the lua script Enables complex connection control and filtering for read-write separation and load balancing. For the application, the MySQL proxy is completely transparent, and the application only needs to be connected to the listening port of MySQL proxy. Of course, this proxy machine can be can become a single point of failure, but can use more than one proxy machine as redundant, in the Application Server connection pool configuration to multiple proxy connection parameters can be .

The Gtid (Global Transaction IDs) introduced by MySQL 5.6 makes the configuration, monitoring, and management of its replication capabilities easier to implement and more robust.

To use the replication feature in MySQL 5.6, the following options should be defined in the service configuration segment [mysqld] for Less:

Binlog-format: Binary log format, there are several types of row, statement and mixed;

Note that when you set the isolation level to read-commited, you must set the binary log format to row, and now MySQL officially believes that statement this is no longer suitable for continued use, but the mixed type may result in inconsistent master-slave data under the default transaction isolation level;

Log-slave-updates, Gtid-mode, Enforce-gtid-consistency, Report-port and report-host: used to initiate gtid and meet other requirements of the subsidiary;

Master-info-repository and Relay-log-info-repository: Enable these two items, which can be used to ensure the security of the binary and slave servers in the crash;

Sync-master-info: Enable it to ensure that no information is lost;

Slave-paralles-workers: Set the number of SQL threads from the server; 0 to turn off the multithreading replication function;

Binlog-checksum, Master-verify-checksum, and slave-sql-verify-checksum: Enable all verification functions related to replication;

Binlog-rows-query-log-events: Enables the use of information related to logging events in binary logging to reduce the complexity of troubleshooting;

Log-bin: Enable the binary log, which is the basic premise to ensure the replication function;

Server-id: The ID number of all servers in the same replication topology must be unique;

Report-host:

The host name or IP address of the slave to is reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server.

Report-port:

The TCP/IP port number for connecting to the slave is reported to the master during slave registration.

Master-info-repository:

The setting of this variable determines whether the slave logs master status and connection information to a FILE (master. info), or to a TABLE (Mysql.slave_master_info)

Relay-log-info-repository:

This option causes the server to log it relay log info to a file or a table.

Log_slave_updates:

Whether updates received by a slave server from a master server should is logged to the slave ' s own binary log. Binary logging must is enabled on the slave for this variable to has any effect.

Enforce_gtid_consistency:

First, simple master-slave mode configuration steps

1, configure the master-slave node of the service configuration file

1.1. Configure the Master node:

[Mysqld]binlog-format=rowlog-bin=master-binlog-slave-updates=truegtid-mode=on enforce-gtid-consistency= truemaster-info-repository=tablerelay-log-info-repository=tablesync-master-info=1slave-parallel-workers= 2binlog-checksum=crc32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events= 1server-id=1report-port=3306port=3306datadir=/mydata/datasocket=/tmp/mysql.sockreport-host=master.xxx.com

1.2. Configure the slave node

[Mysqld]binlog-format=rowlog-slave-updates=truegtid-mode=on enforce-gtid-consistency=truemaster-info-repository =tablerelay-log-info-repository=tablesync-master-info=1slave-parallel-workers=2binlog-checksum= crc32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1server-id=11report-port= 3306port=3306log-bin=mysql-bin.logdatadir=/mydata/datasocket=/tmp/mysql.sockreport-host=slave.xxx.com

2. Create a replication user

Mysql>grant REPLICATION SLAVE On * * to [e-mail protected] identified by ' Replpass ';

Description: 172.16.100.7 is a slave node server; If you want to authorize more nodes at once, you can modify them as needed.

3. Provide the initial data set for the standby node

Lock the primary table, back up the data on the primary node, restore it to the slave node, and if Gtid is not enabled, you need to use the Show Master Status command in master to view the binary log file name and the event location when you back up, so that you can use it later when you start the slave node.

4. Start the replication thread from the node

If the Gtid feature is enabled, use the following command:

mysql> change MASTER to master_host= ' master.xxx.com ', master_user= ' repluser ', master_password= ' Replpass ', master_ Auto_position=1;

Gtid is not enabled, you need to use the following command:

slave> change MASTER to master_host= ' 172.16.100.6 ',-> master_user= ' repluser ',-> master_password= ' Replpass ', -master_log_file= ' master-bin.000003 ',-> master_log_pos=1174;

Second, semi-synchronous replication

1. Install the relevant plugins on the master and slave nodes respectively

master> Install PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so ';slave> install PLUGIN rpl_semi_sync_ Slave SONAME ' semisync_slave.so ';

2. Enable semi-synchronous replication

In the configuration file on master, add

Rpl_semi_sync_master_enabled=on

Add a configuration file in at least one slave node

Rpl_semi_sync_slave_enabled=on

Then restart the MySQL service to take effect.

Alternatively, you can dynamically start its related features on the MySQL service:

master> Set Global rpl_semi_sync_master_enabled = on;slave> set global rpl_semi_sync_slave_enabled = ON;slave> S TOP SLAVE Io_thread; START SLAVE Io_thread;

3. Confirm that the half-sync function is enabled

Master> CREATE DATABASE magedudb;master> show STATUS like ' Rpl_semi_sync_master_yes_tx ';slave> show DATABASES;

MySQL master-slave copy and read/write separation