崬城衞 发表于 2018-10-10 07:32:52

Mysql 主从复制GTID-zhuang

  --------------------------------------------
  安装准备
  配置/etc/my.cnf
  主master grant 分配复制帐号
  从slavechange ;开启slave;查看状态;
  验证一致性
  --------------------------------------------
  当一个事务在主库端执行并提交时,产生GTID,一同记录到binlog日志中。
  binlog传输到slave,并存储到slave的relaylog后,读取这个GTID值设置gtid_next变量,告诉slave,下一个要执行的GTID值。sql线程从relay log中获取GTID,然后对比slave端的binlog是否有该GTID。如果有记录,说明该GTID的事务已经执行过,slave就会忽略。如果没有记录,slave就会执行该GTID事务,记录该GTID到自身的binlog,再读取执行事务前会先检查其他session持有该GTID,确保不被重复执行。在解析过程中会判断是否有主键,如果没有就用二级索引,如果没有就用全部扫面。
  --------------------------------------------
  mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
  tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
  yum install -y mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm
  /etc/init.d/mysqld start             ##开启服务,初始化,得到一个uuid和临时秘密
  2017-03-28T00:46:21.597691Z 0 No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: f6701f68-134f-11e7-94e7-52540022095a.
  2017-03-28T00:46:21.664060Z 1 A temporary password is generated for root@localhost: I-S>yshos7JT
  # mysql -uroot -p      ##修改密码
  Enter password:
  mysql> alter user root@localhost identified by 'Redhat-1024';
  Query OK, 0 rows affected (0.09 sec)
  vim /etc/my.cnf                  ##编辑文件,进行配置
  server-id=3
  gtid-mode=ON
  enforce-gtid-consistency=1                ##开启gtid的一些安全限制
  log-bin=mysql-bin
  binlog-do-db=test
  binlog-ignore-db=mysql
  log-slave-updates
  /etc/init.d/mysqld restart            ##更改配置,重启服务
  mysql> show master status;            ##查看主节点状态
  +------------------+----------+--------------+------------------+-------------------+
  | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  +------------------+----------+--------------+------------------+-------------------+
  | mysql-bin.000004 |      154 | test         | mysql            |                   |
  +------
  mysql> grant replication slave on *.* to ly@'172.25.38.%' identified by 'Redhat-1024';##分配复制帐号
  Query OK, 0 rows affected, 1 warning (0.10 sec)
  --------------------------------------------
  # /etc/init.d/mysqld start
  2017-03-28T00:47:40.538466Z 0 No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 257d874a-1350-11e7-b4ab-525400e717cb.
  2017-03-28T00:47:40.661188Z 1 A temporary password is generated for root@localhost: Aozj4uC:4iff
  # mysql -uroot -p
  Enter password:
  mysql> alter user root@localhost identified by 'Redhat-1024';
  Query OK, 0 rows affected (0.40 sec)
  # /etc/init.d/mysqld restart
  # mysql -uroot -p
  Enter password:
  mysql> change master to master_host='172.25.38.3',master_user='ly',master_password='Redhat-1024',master_auto_position=1;##
  mysql> show slave status\G;
  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes
  Retrieved_Gtid_Set: f6701f68-134f-11e7-94e7-52540022095a:1
  Executed_Gtid_Set: f6701f68-134f-11e7-94e7-52540022095a:1
  # mysql -p
  Enter password:
  # cat auto.cnf
  
  server-uuid=f6701f68-134f-11e7-94e7-52540022095a
  mysql> show global variables like '%gtid%';
  +----------------------------------+------------------------------------------+
  | Variable_name                  | Value                                    |
  +----------------------------------+------------------------------------------+
  | binlog_gtid_simple_recovery      | ON                                       |
  | enforce_gtid_consistency         | ON                                       |
  | gtid_executed                  | f6701f68-134f-11e7-94e7-52540022095a:1-7 |
  | gtid_executed_compression_period | 1000                                     |
  | gtid_mode                        | ON                                       |
  | gtid_owned                     |                                          |
  | gtid_purged                      |                                          |
  | session_track_gtids            | OFF                                    |
  +----------------------------------+------------------------------------------+
  # cat auto.cnf
  
  server-uuid=257d874a-1350-11e7-b4ab-525400e717cb
  mysql> show global variables like '%gtid%';
  +----------------------------------+------------------------------------------+
  | Variable_name                  | Value                                    |
  +----------------------------------+------------------------------------------+
  | binlog_gtid_simple_recovery      | ON                                       |
  | enforce_gtid_consistency         | ON                                       |
  | gtid_executed                  | f6701f68-134f-11e7-94e7-52540022095a:1-7 |
  | gtid_executed_compression_period | 1000                                     |
  | gtid_mode                        | ON                                       |
  | gtid_owned                     |                                          |
  | gtid_purged                      | f6701f68-134f-11e7-94e7-52540022095a:1-6 |
  | session_track_gtids            | OFF                                    |
  +----------------------------------+------------------------------------------+
  mysql> grant replication slave on *.* to ly@'172.25.38.%' identified by 'Redhat-1024';
  mysql> change master to master_host='172.25.38.3',master_user='ly',master_password='Redhat-1024',master_auto_position=1;
  mysql> start slave;
  mysql> show slave status\G;
  *************************** 1. row ***************************
  Slave_IO_State: Waiting for master to send event
  mysql> insert into usertb values(0004,'xiao');
  Query OK, 1 row affected (0.13 sec)
  Master_Host: 172.25.38.3
  Master_User: ly
  Master_Port: 3306
  Connect_Retry: 60
  Master_Log_File: mysql-bin.000004
  Read_Master_Log_Pos: 445
  Relay_Log_File: server4-relay-bin.000002
  Relay_Log_Pos: 658
  Relay_Master_Log_File: mysql-bin.000004
  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes
  Replicate_Do_DB:
  Replicate_Ignore_DB:
  Replicate_Do_Table:
  Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
  Last_Errno: 0
  Last_Error:
  Skip_Counter: 0
  Exec_Master_Log_Pos: 445
  Relay_Log_Space: 867
  Until_Condition: None
  Until_Log_File:
  Until_Log_Pos: 0
  Master_SSL_Allowed: No
  Master_SSL_CA_File:
  Master_SSL_CA_Path:
  Master_SSL_Cert:
  Master_SSL_Cipher:
  Master_SSL_Key:
  Seconds_Behind_Master: 0
  Master_SSL_Verify_Server_Cert: No
  Last_IO_Errno: 0
  Last_IO_Error:
  Last_SQL_Errno: 0
  Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
  Master_Server_Id: 3
  Master_UUID: f6701f68-134f-11e7-94e7-52540022095a
  Master_Info_File: /var/lib/mysql/master.info
  SQL_Delay: 0
  SQL_Remaining_Delay: NULL
  Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
  Master_Retry_Count: 86400
  Master_Bind:
  Last_IO_Error_Timestamp:
  Last_SQL_Error_Timestamp:
  Master_SSL_Crl:
  Master_SSL_Crlpath:
  Retrieved_Gtid_Set: f6701f68-134f-11e7-94e7-52540022095a:1
  Executed_Gtid_Set: f6701f68-134f-11e7-94e7-52540022095a:1
  Auto_Position: 1
  Replicate_Rewrite_DB:
  Channel_Name:
  Master_TLS_Version:
  1 row in set (0.00 sec)
  mysql> select * from test.usertb
  -> ;
  +----+----------+
  | id | name   |
  +----+----------+
  |1 | zhangsan |
  |2 | wang   |
  |3 | lisi   |
  +----+----------+
  mysql> insert into usertb values(0004,'xiao');
  Query OK, 1 row affected (0.13 sec)
  mysql> select * from test.usertb;
  +----+----------+
  | id | name   |
  +----+----------+
  |1 | zhangsan |
  |2 | wang   |
  |3 | lisi   |
  |4 | xiao   |
  +----+----------+

页: [1]
查看完整版本: Mysql 主从复制GTID-zhuang