Gtid-based master-slave mysql replication and mysql-proxy read/write Separation

Mysql5.6 gtid (GlobalTransactionIDs) introduced in MySQL5.6 Based on GTID master-slave replication and mysql-proxy read/write separation makes the configuration, monitoring and management of its replication function easier to implement and more healthy ..

Mysql5.6 gtid (GlobalTransactionIDs) introduced in MySQL5.6 Based on GTID master-slave replication and mysql-proxy read/write separation makes the configuration, monitoring and management of its replication function easier to implement and more healthy ..

IP Address Allocation for master-slave replication and master-slave relationship:

Precautions for configuring master-slave replication:

1. The time of the two servers needs to be synchronized

2. Communication and ping

3. Disable SElinux.

Mysql version: mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz

System Version: redhat 6.4 32-bit

Configuration steps:

Reminder: the configuration process in this article is preceded by a (0-9) number table name. Please configure it in this order.

Mysql installation Configuration

Mysql installation and configuration 1, # mkdir/mydata/data-pv2, # useradd-r mysql # chown-R mysql. mysql/mydata/data/3, # tar xvf mysql-5.6.10-linux-glibc2.5-i686.tar.gz-C/usr/local4, # cd/usr/local/5, # ln-sv mysql-5.6.10-linux-glibc2.5-i686 mysql6, # cd mysql7, # chown-R root. mysql. /* 8, # scripts/mysql_install_db -- user = mysql -- datadir =/mydata/data9, # cp support-files/mysql. server/etc/init. d/mysqld10, # chkconfig -- add mysqld

The following is the configuration file. It is worth noting that the value of server-id in the following configuration file cannot be repeated. The master and slave cannot be the same.

11, # vim/etc/local/mysql/my. cnf Add the following items datadir =/mydata/datainnodb_file_per_table = ONserver-id = 1 the slave server cannot repeat socket =/tmp/mysql with this id. socklog-bin = master-bin12, # service mysqld start13, # vim/etc/profile. d/mysqld add export PATH = $ PATH:/usr/local/mysql/bin14 ,#. /etc/profile. d/mysqld repeat the configuration file

Next, configure mysql. Configure the slave server. The server-id value in/etc/local/mysql/my. cnf cannot be repeated !!!

Configure Master/Slave mysql
Edit the configuration file to add configuration parameters for the master in master-slave replication.

Master15, # vim/usr/local/mysql/my. cnf: binlog-format = ROWlog-slave-updates = truegtid-mode = onenforce-gtid-consistency = truemaster-info-repository = TABLErelay-log-info-repository = TABLEsync -master-info = 1slave-parallel-workers = 2binlog-checksum = CRC32master-verify-checksum = 1slave-sql-verify-checksum = protocol = 1report-port = 3306 port = 3306report-host = 172.16.66.1

16. Start the mysql service.

16, # service mysqld restart

17. Go to the mysql database to view gtid and other related information.

# Mysql> show global variables like '% gtid % ';

18. Create a copy user and grant permissions

Mysql> grant replication slave on *. * TO laogen@172.16. %. % identified by 'lastgen '; mysql> flush privileges; mysql> SELECT @ autocommit

The following is the mysql slave service configuration !!!

Slave19: edit the configuration file from mysql # vim/usr/local/mysql/my. add binlog-format = ROWlog-slave-updates = truegtid-mode = onenforce-gtid-consistency = truemaster-info-repository = TABLErelay-log-info-repository = TABLEsync- master-info = 1slave-parallel-workers = 2binlog-checksum = CRC32master-verify-checksum = 1slave-sql-verify-checksum = protocol = 1report-port = 3306 port = 3306report-host = 172.16.66.2

20. Start the mysql service.

# Service mysqld restart

21. Use the account and password created on the primary mysql instance to log in and copy.

# Msyql> change master to master_host = '2017. 16.66.1 ', master_user = 'lastgen', master_password = 'lastgen ', master_auto_position = 1; # mysql> start slave; # mysql> show slave status \ G view copy STATUS information

Next, go to the debugging and verification steps.

Create a database on the primary mysql instance to monitor whether the slave database can be copied

22. log on to the primary mysql database and check whether the synchronization of the created database is successful.

# Mysql> create database laogen;

23. log on to the mysql-based database for verification.

# Mysql> show database;

Check whether the synchronization location of the binary file is the same !!!!

24. Run the command in the primary mysql database.

Mysql> show master status \ G

25. Execute this command from the mysql database.

Mysql> show master status \ G

So far, Gtid-based mysql master-slave replication configuration has been successful !!!

######################################## ###############################

######################################## ###############################

Mysql-proxy master-slave Separation

Next we will configure the mysql-proxy read/write splitting based on the above mysql master-slave replication. We need to add another server outside the master-slave database. Note that this mysql-proxy should have a network segment as far as possible with the master-slave database, ping and time synchronization are guaranteed.

The IP configuration information is as follows:

First, let's briefly introduce mysql-proxy: