• 51270

    文章

  • 499

    评论

  • 40

    友链

  • 最近新加了换肤功能,大家多来逛逛吧~~~~
  • 喜欢这个网站的朋友可以加一下QQ群,我们一起交流技术。

How To Setup GTID Replication In MySQL 5.6

695856371Web网页设计师②群 | 172816590(满)Web网页设计师①群, 喜欢本站的朋友可以收藏本站,或者加入我们大家一起来交流技术!

欢迎来到梁钟霖个人博客网站。本个人博客网站提供最新的站长新闻,各种互联网资讯。 还提供个人博客模板,最新最全的java教程,java面试题。在此我将尽我最大所能将此个人博客网站做的最好! 谢谢大家,愿大家一起进步!

Database

How To Setup GTID Replication In MySQL 5.6

mysql-master-slave-replication
mm
Written by Santosh Prasad

What is GTID?

GTID (Global Transaction Identifier) is a unique identifier created and associated with each transaction committed on the server of origin. It consists of two parts separated by a column:

GTID = source_id:transaction_id

 

Where,

source_id : Server’s UUID
transaction_id : Sequence number

GTID Benefits

  • Easy to setup MySQL replication.
  • Consistency is guaranteed between master and salves.
  • Fail-over process become easy.
  • Automatic fail over script is not a pain now.
  • Simple to determine inconsistency.

Setting Up Replication Using GTIDs

MySQL master side configurations
MySQL slave side configurations

Master Side Configuration

Add the following variables to /etc/my.cnf.

# vim /etc/my.cnf
[mysqld]
log-bin = mysql-bin
server-id = 1
relay-log = relay-log-slave
gtid-mode =ON
enforce-gtid-consistency
binlog_format = MIXED
log_slave_updates

Save and close file.

Restart MySQL to apply the configuration changes:

# service mysql restart

Now create a MySQL user to be used by the slave.

mysql> GRANT REPLICATION SLAVE ON *.* TO
'slave_user_name'@'slave_ip'
IDENTIFIED BY 'password';

Slave Side Configuration

Add the following variables to /etc/my.cnf.

# vim /etc/my.cnf
[mysqld]
log_bin = mysql-bin
server_id = 2
binlog_format = ROW
skip_slave_start
gtid_mode = on
enforce_gtid_consistency
log_slave_updates

Save and close file.

Restart MySQL to apply the configuration changes:

# service mysql restart

Now run the CHANGE MASTER TO command:

mysql> CHANGE MASTER TO
MASTER_HOST='master_ip',
MASTER_PORT=3306,
MASTER_USER='slave_user_name',
MASTER_PASSWORD='password',
MASTER_AUTO_POSITION=1;

Start the replication

mysql> start slave;

Check The Replication

Now check the replicatin status by running below command:

mysql> show slave status\G;
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.5
Master_User: repleuser
Master_Port: 3306
. . .
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
. . .
Retrieved_Gtid_Set: b9b4712a-df64-11e3-b391-60672090eb04:1-2
Executed_Gtid_Set: b9b4712a-df64-11e3-b391-60672090eb04:1-2
Auto_Position: 1

Add New Slave To A GTID Replication

Follow the below steps to add a new slave to GTID replication:

1. Teka master server backup using mysqldump.

mysql> mysqldump -u root -p --all-databases –flush-privileges --single-transaction --flush-logs --triggers --routines --events –hex-blob > /path/to/backupdir/full_backup-$TIMESTAMP.sql

2. Edit the new slave’s /etc/my.cnf file as described above.
3. Now restore the master backup file on the slave server.
4. Execute the CHANGE MASTER TO command with MASTER_AUTO_POSITION=1
5. Start MySQL slave.

mysqldump knows about GTID

--
-- GTID state at the beginning of the backup
--
SET @@GLOBAL.GTID_PURGED='b9b4712a-df64-11e3-b391-60672090eb04:1-7';

Thanks:)


 转载至链接:https://my.oschina.net/rootliu/blog/3007773。


转载原创文章请注明出处,转载至: 梁钟霖个人博客www.liangzl.com

您觉喜欢本网站,或者觉得本文章对您有帮助,那么可以选择打赏。
打赏多少,您高兴就行,谢谢您对梁钟霖这小子的支持! ~(@^_^@)~

  • 微信扫一扫

  • 支付宝扫一扫

    支付宝打赏

0条评论

Loading...


发表评论

电子邮件地址不会被公开。 必填项已用*标注

自定义皮肤
注册梁钟霖个人博客