myslq-5.6 Gtid-based master-slave replication implementation

Mysql-5.6:gtid mechanism

slave-parallel-workes= the number of threads started equals the number of libraries less than the database
0 means disable

using the Gtid-based replication feature in mysql-5.6



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=row
Log-bin=master-bin
Log-slave-updates=true
Gtid-mode=on
Enforce-gtid-consistency=true
master-info-repository=table (Let the master record a few links from the server, and copy the files to that,)
Relay-log-info-repository=table (Let yourself record from the server who the Blessing weapon is, and where the binaries are）
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=192.168.122.1(replace the host IP address to avoid parsing)

1.2. Configure the slave node:
[Mysqld]
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(can be enabled or not, enabling is the slave can be promoted to the master at any time when master hangs off)
Datadir=/mydata/data
Socket=/tmp/mysql.sock
report-host=192.168.122.10(replace the host IP address to avoid parsing)

2. Create a replication user

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

Description: 172.16.100.7 is from the 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, or, if Gtid is not enabled, use the Show Master Status command on 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.magedu.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;


Example:

New Data directory, user decompression

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "alt=" File:///tmp/ct_tmp/1.png "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "/>650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/70/80/wKioL1W47dDCQGklAADYe4EFLL8847.jpg "title=" 21.png "alt = "Wkiol1w47ddcqgklaadye4efll8847.jpg"/>

Create a soft link

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "alt=" File:///tmp/ct_tmp/2.png "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "/>650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/70/83/wKiom1W46_nD039CAAEEJh51wec201.jpg "title=" 22.png "alt = "Wkiom1w46_nd039caaeejh51wec201.jpg"/>

Change of owner and genus Group

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "alt=" File:///tmp/ct_tmp/3.png "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "/>

Initialization

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "alt=" File:///tmp/ct_tmp/4.png "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "/>650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/70/83/wKiom1W47ODST5XYAAIKgfXXI0g924.jpg "title=" 24.png "alt = "Wkiom1w47odst5xyaaikgfxxi0g924.jpg"/>

Generate a configuration file under the current directory

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/70/80/wKioL1W472_xoP2yAAB1h_jRrkc603.jpg "title=" 25.png "alt=" Wkiol1w472_xop2yaab1h_jrrkc603.jpg "/>650) this.width=650; src="/e/u261/themes/default/images/ Spacer.gif "alt=" File:///tmp/ct_tmp/5.png "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") No-repeat center;border:1px solid #ddd; "/>

Master-side configuration file modification vim my.cnf

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/70/83/wKiom1W47aqDk-jeAAFpm1tFsSM438.jpg "title=" 31.png "alt=" Wkiom1w47aqdk-jeaafpm1tfssm438.jpg "/>650) this.width=650; src="/e/u261/themes/default/images/ Spacer.gif "alt=" File:///tmp/ct_tmp/6.png "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") No-repeat center;border:1px solid #ddd; "/>

Copy Startup scripts

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "alt=" File:///tmp/ct_tmp/7.png "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "/>650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/70/80/wKioL1W477ug0grMAABRgIfnrPE413.jpg "title=" 26.png "alt = "Wkiol1w477ug0grmaabrgifnrpe413.jpg"/>

Edit environment variable File

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/70/83/wKiom1W47eTSVYGLAAAxQe5uX00400.jpg "title=" 27.png "alt=" Wkiom1w47etsvyglaaaxqe5ux00400.jpg "/>

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "alt=" File:///tmp/ct_tmp/9.png "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "/>650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/70/80/wKioL1W479_hJ8DPAABPKnZ_Nqo666.jpg "title=" 28.png "alt = "Wkiol1w479_hj8dpaabpknz_nqo666.jpg"/>

Start MySQL and view binary log files

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/70/83/wKiom1W47hHCstHDAAEyetp3CUY271.jpg "title=" 29.png "alt=" Wkiom1w47hhcsthdaaeyetp3cuy271.jpg "/>650) this.width=650; src="/e/u261/themes/default/images/ Spacer.gif "alt=" File:///tmp/ct_tmp/10.png "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") No-repeat center;border:1px solid #ddd; "/>

Inquire

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "alt=" File:///tmp/ct_tmp/11.png "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "/>650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/70/80/wKioL1W48BKCscUCAAGJYr2FGGg474.jpg "title=" 30.png "alt = "Wkiol1w48bkcscucaagjyr2fggg474.jpg"/>

Slave-side configuration



Mkdir-pv/mydata /data
useradd-r mysql
chown-r mysql.mysql/mydata/data
CD in unzip directory
LN-SV mysql-5.6.10  mysql Create a linked file
CD  mysql

Chown-r mysql.mysql./*

initialize

scriptes/ mysql_install_db--user=mysql  --datadir=/mydata/data/

Vim my.cnf

CP support-files/mysql.server /etc/init.d/mysqld
Chkconfig  --add mysqld
Service mysqld start

Slave modification of the configuration file

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "alt=" File:///tmp/ct_tmp/12.png "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "/>650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/70/83/wKiom1W47krhaMycAAE4biYhWxo138.jpg "title=" 33.png "alt = "Wkiom1w47krhamycaae4biyhwxo138.jpg"/>

Service mysqld Start




Queries on the master side

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "alt=" File:///tmp/ct_tmp/13.png "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "/>650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/70/80/wKioL1W48GTjqfI7AAFOQdgUPQw439.jpg "title=" 34.png "alt = "Wkiol1w48gtjqfi7aafoqdgupqw439.jpg"/>

From the end of the query, (at this point is not divided between the master and slave in fact, who can be the boss)

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/70/83/wKiom1W47pLz1BtCAAGDOfhdACM321.jpg "title=" 35.png "alt=" Wkiom1w47plz1btcaagdofhdacm321.jpg "/>

Authorize a Slave on the master side


mysql> grant replication Slave on * * to ' repluser ' @ ' 172.16.%.% ' identified by ' replpass ';

mysql> FLUSH privileges;


Slave End link Master side

mysql> Change Master to master_host= ' 192.16.100.106 ', master_user= ' repluser ', master_password= ' Replpass ', master_ Auto_position=1;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/70/80/wKioL1W48KSTTkd-AABiqxCWdHQ128.jpg "title=" 36.png "alt=" Wkiol1w48ksttkd-aabiqxcwdhq128.jpg "/>650) this.width=650; src="/e/u261/themes/default/images/ Spacer.gif "alt=" File:///tmp/ct_tmp/15.png "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") No-repeat center;border:1px solid #ddd; "/>

mysql> Start slave startup



Testing: Creating a database on the master side

Mysql> CREATE DATABASE MyDB

Viewing the slave server from the master side

Mysql> show Slave hosts;

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "alt=" File:///tmp/ct_tmp/16.png "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "/>650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/70/83/wKiom1W47sCTvO-wAACbKw3ydcs250.jpg "title=" 37.png "alt = "Wkiom1w47sctvo-waacbkw3ydcs250.jpg"/>

This article from the "Learning Log" blog, reproduced please contact the author!

myslq-5.6 Gtid-based master-slave replication implementation