MySQL5.6 is based on Gtid synchronous replication, and how to achieve MySQL load balancing, read/write separation.

MySQL Presumably everyone is not unfamiliar, before the article also introduced synchronous replication and semi-synchronous replication, today first to understand what is gtid.

GTID (Global transaction ID) global transaction ID, which is a random number transaction ID by the server's uuid+.

features : transactions that are replicated from the server from the primary serverGTID unchanged, that is, the ID of a transaction in the global replication schema does not change.

What is the use of:

In a MySQL cluster, when master fails, you need to pick a promotion from slave to master to keep the data consistent based on Gtid compared to other slave.

MySQL Master-Slave synchronization How to configure data filtering

Primary server configuration:

B inlog-do-db// Specifies the database to log binary logs

B inlog-ignore-db// Specifies the database to ignore logging binary logs

From the server configuration:

R eplicate-do-table=// Specifies the table to log binary logs

R eplicate-ignore-table=// Specifies the table to ignore logging binary logs

R eplicate-do-db=// Specifies the database to log binary logs

R eplicate-ignore-db=// Specifies the database to ignore logging binary logs

R eplicate-wild-do-table=// Specify a table to log binary logs (wildcard characters are supported)

R eplicate-wild-ignore-table=// specifies a table to ignore logging binary logs (wildcard characters are supported)

Experiment one, configure a MySQL master-slave replication architecture based on Gtid.

Lab Environment: rhel6.4+mysql5.6

master:192.168.18.201 admin1.tuchao.com

slave:192.168.18.202 admin2.tuchao.com

Installing and starting the MySQL process is the same as before (slightly)

Master configuration:

[Mysqld]
Port = 3306
Socket =/tmp/mysql.sock
Skip-external-locking
Key_buffer_size = 256M
Max_allowed_packet = 1M
Table_open_cache = 256
Sort_buffer_size = 1M
Read_buffer_size = 1M
Read_rnd_buffer_size = 4M
Myisam_sort_buffer_size = 64M
Thread_cache_size = 8
Query_cache_size= 16M
Thread_concurrency = 2
Datadir=/mydata
Log-bin=mysql-bin
innodb_file_per_table = 1

Binlog-format=row//Set binary log format
Log-slave-updates=true // whether to record synchronization of data from the server action

Gtid-mode=on // enable gtid mode

Enforce-gtid-consistency=true // whether to enforce gtid consistency

Master-info-repository=table where//master information is recorded

Relay-log-info-repository=table // relay log information record location

Sync-master-info=1
slave-parallel-workers=2 // Set Number of replication threads from server

BINLOG-CHECKSUM=CRC32 // set binlog Check algorithm (cyclic redundancy check code)

Master-verify-checksum=1 // Sets whether the master server verifies

Slave-sql-verify-checksum=1 // set check from server

Binlog-rows-query-log_events=1
Server-id = 10
report-port=3306
report-host=admin1.tuchao.com // settings report to which server, generally set to the host name of the native.

Slave configuration:

[Mysqld]
Port = 3306
Socket =/tmp/mysql.sock
Skip-external-locking
Key_buffer_size = 256M
Max_allowed_packet = 1M
Table_open_cache = 256
Sort_buffer_size = 1M
Read_buffer_size = 1M
Read_rnd_buffer_size = 4M
Myisam_sort_buffer_size = 64M
Thread_cache_size = 8
Query_cache_size= 16M
Thread_concurrency = 2
Datadir=/data
Log-bin=mysql-bin
innodb_file_per_table = 1

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 =
report-port=3306
report-host=admin2.tuchao.com

In fact, the master and slave configuration is almost the same, only need to modify a few parameters I used to mark the above in red font, such as Server-id, Report-host and so on.

Users who are authorized to copy permissions on Master

• Grant replication Slave,replication Client on * * to ' repluser ' @ ' 192.168.18.202 ' identified by ' 123456 ';

Start slave replication thread

• Change Master to master_host= ' admin1.tuchao.com ', master_user= ' repluser ', master_password= ' 123456 ', master_auto_ Position=1;

• Start slave;

Viewing warning messages

• Show warnings

View slave status

• Show Slave Status\g

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/46/B9/wKiom1P0HVHiLPJBAAIUhlZFM00896.jpg "title=" _6xyd} _C75DYC8LJ@3YM7M5.jpg "alt=" Wkiom1p0hvhilpjbaaiuhlzfm00896.jpg "/>

At this point we create a Tuchao database on master

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/46/BC/wKioL1P0JW3g02VTAAEQnJ5-JXE645.jpg "title=" 1.jpg " alt= "Wkiol1p0jw3g02vtaaeqnj5-jxe645.jpg"/>

Come to Slave view

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/46/BA/wKiom1P0JHbSxLPqAADMQtFQX8w645.jpg "title=" 2.jpg " alt= "Wkiom1p0jhbsxlpqaadmqtfqx8w645.jpg"/>

Sync succeeded!

Experiment two, using amoeba to build MySQL cluster to achieve load balancing, read and write separation.

This article is from the "Bad Guys blog" blog, make sure to keep this source http://tchuairen.blog.51cto.com/3848118/1542454