ray的窝

raysuen

  • 博客访问: 16090
  • 博文数量: 81
  • 用 户 组: 普通用户
  • 注册时间: 2016-08-23 17:45
  • 认证徽章:
个人简介

一个不是很靠谱的DBA,一个不是很靠谱的系统工程师

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(81)

文章存档

2017年(48)

2016年(33)

我的朋友
微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题
mysql 5.7 GTID主从配置 2017-04-11 11:20:25

分类: MySQL

binlog-format:二进制日志的格式,有row、statement和mixed几种类型;需要注意的是:当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW,现在MySQL官方认为STATEMENT这个已经不再适合继续使用;但mixed类型在默认的事务隔离级别下,可能会导致主从数据不一致;

log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host:用于启动GTID及满足附属的其它需求;
master-info-repository和relay-log-info-repository:启用此两项,可用于实现在崩溃时保证二进制及从服务器安全的功能;
sync-master-info:启用之可确保无信息丢失;
slave-paralles-workers:设定从服务器的SQL线程数,根据cpu核数设定;0表示关闭多线程复制功能;
binlog-checksum、master-verify-checksum和slave-sql-verify-checksum:启用复制有关的所有校验功能;
binlog-rows-query-log-events:启用之可用于在二进制日志记录事件相关的信息,可降低故障排除的复杂度;
log-bin:启用二进制日志,这是保证复制功能的基本前提;
server-id:同一个复制拓扑中的所有服务器的id号必须惟一。

report-host:
The host name or IP address of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server.

report-port:
The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration.

master-info-repository:
The setting of this variable determines whether the slave logs master status and connection information to a FILE (master.info), or to a TABLE (mysql.slave_master_info)

relay-log-info-repository:
This option causes the server to log its relay log info to a file or a table.

log_slave_updates:
Whether updates received by a slave server from a master server should be logged to the slave's own binary log. Binary logging must be enabled on the slave for this variable to have any effect.

master服务器配置
编辑master的参数文件

#GTID parameter
gtid-mode=on
enforce-gtid-consistency=true
slave-parallel-workers=10
binlog-checksum=CRC32
binlog-format=ROW
log-slave-updates=true
report-port=3306
report-host=192.168.56.212
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1

重启master的mysql数据库
[root@ray ~]# /data/3306/mysqld restart              
Stoping MySQL...
Warning: Using a password on the command line interface can be insecure.
Starting MySQL...


mysql> show global variables like '%gtid%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON    |
| gtid_executed            |       |
| gtid_mode                | ON    |   #说明gti功能已启动
| gtid_owned               |       |
| gtid_purged              |       |
+--------------------------+-------+
5 rows in set (0.01 sec)

创建同步用户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.78 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)



从服务器slave配置
my.cnf参数文件配置
#GTID parameter
gtid-mode=on
enforce-gtid-consistency=true
slave-parallel-workers=10
binlog-checksum=CRC32
relay-log = /data/3307/logs/relay-log
relay-log-index = /data/3307/logs/relay-log.index
binlog-format=ROW
log-slave-updates=true
report-port=3307
report-host=192.168.56.212
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
sync_relay_log = 1
sync_relay_log_info = 1
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
relay_log_recovery = ON

重启mysql数据库
[root@ray ~]# /data/3307/mysqld restart             
Stoping MySQL...
Warning: Using a password on the command line interface can be insecure.
Starting MySQL...


mysql> show global variables like '%gtid%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON    |
| gtid_executed            |       |
| gtid_mode                | ON    |
| gtid_owned               |       |
| gtid_purged              |       |
+--------------------------+-------+
5 rows in set (0.56 sec)



change master to  
master_host='192.168.56.212',  
master_user='rep',  
master_password='123456',  
master_port=3306,  
master_auto_position = 1;  

mysql> change master to  
    ->  master_host='192.168.56.212',  
    ->  master_user='rep',  
    ->  master_password='123456',  
    ->  master_port=3306,  
    ->  master_auto_position = 1;  
Query OK, 0 rows affected, 2 warnings (0.59 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.212
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: ray-bin.000009
          Read_Master_Log_Pos: 588
               Relay_Log_File: relay-log.000003
                Relay_Log_Pos: 797
        Relay_Master_Log_File: ray-bin.000009
             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: 588
              Relay_Log_Space: 1175
              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: 1
                  Master_UUID: 97e8847a-ffdf-11e6-87ed-08002736c224
             Master_Info_File: mysql.slave_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: 97e8847a-ffdf-11e6-87ed-08002736c224:1-2
            Executed_Gtid_Set: 97e8847a-ffdf-11e6-87ed-08002736c224:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

参数:
master-info-repository=TABLE
relay-log-info-repository=TABLE
把master.info 和relay.info 保存在表中,默认是myisam引擎,官方建议修改为innodb
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> alter table slave_master_info engine=innodb;
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table slave_relay_log_info engine=innodb;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table slave_worker_info engine=innodb;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0



忽略过滤表:
配置文件,需要重启
replicate-ignore-table=test.t1

在线动态修改,无需重启
CHANGE REPLICATION FILETER REPLICATE_DO_DB=(DB1,DB2);
CHANGE REPLICATION FILETER REPLICATE_IGNORE_DB=(DB1,DB2);
CHANGE REPLICATION FILETER REPLICATE_DO_TABLE=(DB1.T1);
CHANGE REPLICATION FILETER REPLICATE_IGNORE_TABLE=(DB1.T1);
CHANGE REPLICATION FILETER REPLICATE_WILD_DO_TABLE=(DB1.T%);
CHANGE REPLICATION FILETER REPLICATE_WILD_IGNORE_TABLE=(DB%.T%);
CHANGE REPLICATION FILETER REPLICATE_REWRITE_DB=(FROM_DB,TO_DB);











阅读(403) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册