mysql5.6 Gtid-based master-slave replication

Source: Internet
Author: User

Mysql5.6gtid mode master-slave replication:

1. Configure the Master MY.CNF file:
################# #master
[Mysqld]
Basedir =/usr/local/mysql
DataDir =/home/data/mysqldb
Socket =/home/data/mysqldb/mysql.sock
Port = 3306
server_id = 1
User=mysql

Binlog-format=row
Log-bin=master-bin.log-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-worker=2
slave_allow_batching = 1
Master-verify-checksum=1
Slave-sql-verify-checksum=1
Binlog-rows-query-log-events=1
Server-id=1
port=3306
report-port=3306
report-host=192.168.100.3
Lower_case_table_names=1
#安装完半同步插件后即可在配置文件中打开下面的选项
#rpl_semi_sync_slave_enabled =1
#rpl_semi_sync_master_enabled =1
#rpl_semi_sync_master_timeout =2000

2. Configure the Master MY.CNF file:
################# #slave
[Mysqld]
Basedir =/usr/local/mysql
DataDir =/home/data/mysqldb
Socket =/home/data/mysqldb/mysql.sock
Port = 3306
server_id = 1
User=mysql
Binlog-format=row
Log-bin=master-bin.log-bin
Enforce-gtid-consistency=true
Master-info_repository=table
Relay-log-info-repository=table
Sync-master-info=1
slave-parallel-worker=2
slave_allow_batching = 1
Master-verify-checksum=1
Slave-sql-verify-checksum=1
Binlog-rows-query-log-events=1
server-id=2
report-port=3306
port=3306
report-host=192.168.100.5
#安装完半同步插件后即可在配置文件中打开下面的选项
#rpl_semi_sync_slave_enabled =1
#rpl_semi_sync_master_enabled =1
#rpl_semi_sync_master_timeout =1000
3. Back up the primary database (Perconna-xtrabackup software must be installed):
Innobackupex--user=backupuser--password=backupuser--NO-TIMESTAMP/TMP/BAK/FULLBK
Transfer the backup file under/TMP/BAK/FULLBK to the/TMP/BAK/FULLBK directory from the library and execute the following statement:
Innobackupex--APPLY-LOG/TMP/BAK/FULLBK
Copy the files in the/TMP/BAK/FULLBK directory to the DataDir directory;

4. Install the Sync plugin and authorize:
Install the semi-synchronous plugin in the MySQL master/slave database:
Install plugin rpl_semi_sync_master soname ' semisync_master.so ';
Install plugin rpl_semi_sync_slave soname ' semisync_slave.so ';

Authorize in MySQL's master-slave database and turn on the semi-sync:
GRANT REPLICATION SLAVE on * * to ' repl ' @ ' 192.168.100.3 ' identified by ' repl ';
GRANT REPLICATION SLAVE on * * to ' repl ' @ ' 192.168.100.5 ' identified by ' repl ';
Set global rpl_semi_sync_slave_enabled=1;
Set global rpl_semi_sync_master_enabled=1;
Set global rpl_semi_sync_master_timeout=1000;

5. Start the synchronization:
Change Master to Master host= ', master_user= ', master_password= ', master_auto_position=1;
Start slave;
6. Check:
View from the client in the main MySQL database:
Show slave hosts;

Check the sync status from the server in MySQL:
Mysql>show slave Status\g
Slave_io_running:yes
Slave_sql_running:yes
exec_master_log_pos:34810
Seconds_behind_master:4
Master_server_id:1
Auto_position:1
7. Now, complete the master-slave configuration based on Gtid

This article is from the "Webseven" blog, make sure to keep this source http://webseven.blog.51cto.com/4388012/1657759

mysql5.6 Gtid-based master-slave replication

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.