Gtid and Mysql-proxy for mysql5.6 reading and writing separation

Gtid Description :

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;

For starting Gtid and meeting other needs of the subsidiary:

Log-slave-updates: Whether to enable binary logging when copying from the server

Gtid-mode: Whether the Gtid feature is enabled

Enforce-gtid-consistency: Whether to force Gtid consistency feature

Report-port: Whether to enable logging of ports from the server when connecting to the master server in Gtid mode

Report-host: Whether to enable logging from the server's IP or primary when connecting to the master server in Gtid mode

Machine name

Enable these two items, can be used to implement in the crash to ensure that the binary and security from the server function;

Master-info-repository: Define binary Log Master to log replication-related information from the server

Record in file Master.info or record in table Mysql.slave_master_info

Relay-log-info-repository: records from the server

Enable all the checksum functions for replication:

Binlog-checksum: Whether the primary server verifies Gtid checksum at startup

Master-verify-checksum

Slave-sql-verify-checksum

Binlog-rows-query-log-events: Enable it to be used for information related to logging events in the binary

The complexity of troubleshooting can be reduced;

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;

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;

Description of the building service Content :

The three server addresses are 202.207.178.6 (master), 202.207.178.7 (from node (Slave), 202.207.178.8 (front node), and master-slave replication based on gtid between the primary node and the slave node. Install Mysql-proxy on the front-end node to achieve read and write separation of master and slave nodes. (To avoid impact, turn off firewalls and SELinux)

First, install MySQL (I am here to install MySQL-5.6.33 (Universal binary format) by compiling

Master:

1. Unzip the downloaded package to/usr/local and enter this directory

# Tar XF mysql-5.6.33-linux-glibc2.5-i686.tar.gz-c/usr/local/

# cd/usr/local/

2. Create a link to the extracted directory and enter this directory

# LN-SV mysql-5.6.33-linux-glibc2.5-i686 MySQL

# CD MySQL

3. Create a MySQL user (make it a system user) and MySQL group

# groupadd-r-G 306 MySQL

# useradd-g 306-r-u 306 mysql

4. Make all files under MySQL belong to MySQL user and MySQL group

# Chown-r mysql.mysql/usr/local/mysql/*

5. Create a data directory and make it a MySQL user and MySQL group, no one else has permission

# mkdir-p/mydata/data

# Chown-r mysql:mysql/mydata/data/

# chmod o-rw/mydata/data/

6. Ready to start Installation

# scripts/mysql_install_db--user=mysql--datadir=/mydata/data

7. After the installation is complete, change the permissions of all files under/usr/local/mysql for security

#chown-R Root.

8. Prepare the startup script and enable it to boot automatically

# CP Support-files/mysql.server/etc/init.d/mysqld

# chkconfig--add mysqld

# chkconfig--list mysqld

9. Edit the database configuration file

# CP SUPPORT-FILES/MY-DEFAULT.CNF/ETC/MY.CNF

#vim/etc/my.cnf, modify and add the following:

Binlog-format=row

Log-bin=master-bin

Log-slave-updates=true

Gtid-mode=on

Enforce-gtid-consistency=true

Master-info-repository=table

Relay-log-info-repository=table

Sync-master-info=1

slave-parallel-workers=2

Binlog-checksum=crc32

Master-verify-checksum=1

Slave-sql-verify-checksum=1

Binlog-rows-query-log_events=1

Server-id=1

report-port=3306

port=3306

Datadir=/mydata/data

Socket=/tmp/mysql.sock

report-host=202.207.178.6

10. Provide the environment variables required to execute the relevant commands

# vim/etc/profile.d/mysql.sh

Add the following content:

Export path= $PATH:/usr/local/mysql/bin

11. Now that the MySQL service configuration is complete, you can start the test

# service Mysqld Start

Slave: (The method is the same as the master, except that the configuration file differs)

Configuration file:

Binlog-format=row

Log-slave-updates=true

Gtid-mode=on

Enforce-gtid-consistency=true

Master-info-repository=table

Relay-log-info-repository=table

Sync-master-info=1

slave-parallel-workers=2

Binlog-checksum=crc32

Master-verify-checksum=1

Slave-sql-verify-checksum=1

Binlog-rows-query-log_events=1

server-id=11

report-port=3306

port=3306

Log-bin=mysql-bin.log #如果用到高可用功能, at any time to promote a from the main, need to start the two-step

System log

Datadir=/mydata/data

Socket=/tmp/mysql.sock

report-host=202.207.178.7

Second, configure master-slave replication

Master:

To create a replication user:

Mysql> GRANT REPLICATION SLAVE on * * to ' repluser ' @ ' 202.207.178.% ' identified by ' replpass ';

mysql> FLUSH privileges;

Slave:

To start a replication thread from a node:

mysql> change MASTER to master_host= ' 202.207.178.6 ', master_user= ' Repluser ',

Master_password= ' Replpass ', master_auto_position=1;

mysql> SHOW SLAVE status\g;

Mysql> START SLAVE;

Test everything OK!

Third, install mysql-proxy (I use mysql-proxy-0.8.4-linux-glibc2.3-x86-32bit.tar.gz here)

1. Create a proxy user

# Useradd-r Mysql-proxy

2. Unzip the appropriate package and create a link

# Tar XF mysql-proxy-0.8.4-linux-glibc2.3-x86-32bit.tar.gz-c/usr/local/

# cd/usr/local/

# LN-SV Mysql-proxy-0.8.4-linux-glibc2.3-x86-32bit Mysql-proxy

3. Export environment variables

# CD Mysql-proxy

# vim/etc/profile.d/mysql-proxy.sh

Export path= $PATH:/usr/local/mysql-proxy/bin

4. Start Mysql-proxy

# Mysql-proxy--daemon--log-level=debug

--log-file=/var/log/mysql-proxy.log--plugins= "proxy"

--proxy-backend-addresses= "202.207.178.6:3306"

--proxy-read-only-backend-addresses= "202.207.178.7:3306"

# Tail/var/log/mysql-proxy.log

The discovery is now started!

5. Create a root user on the master server

Mysql>grant all on * * to ' root ' @ ' 202.207.178.% ' identified by ' Redhat ';

mysql> FLUSH privileges;

6. Connect the front-end mysql-proxy node test on the slave node

# mysql-uroot-p-h202.207.178.8--port=4040

When you create a database or a table, you may find that the master-slave database is there, which may be a coincidence, because a LUA script is needed to implement read-write separation

7. Restart the Mysql-proxy and specify the Lua script for it

# Killall Mysql-proxy

# Mysql-proxy--daemon--log-level=debug

--log-file=/var/log/mysql-proxy.log--plugins= "proxy"

--proxy-backend-addresses= "202.207.178.6:3306"

--proxy-read-only-backend-addresses= "202.207.178.7:3306"

--proxy-lua-script= "/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"

# Tail/var/log/mysql-proxy.log

At this point, the true meaning of the read and write separation is complete!

Welcome to criticize and correct!

This article is from the "10917734" blog, please be sure to keep this source http://10927734.blog.51cto.com/10917734/1869205

Gtid and Mysql-proxy for mysql5.6 reading and writing separation