利用mysql5.6新版特性实现基于GTID的主从复制

1. 主从服务器之前分别作以下操作：
   • 确定主从关系（此处以192.168.1.140作主，192.168.1.50作从为例）
   • 版本一致（mysql-5.6.23）
   • 初始化系统表,停止mysql服务
2. 修改主服务器master:（以下配置以双核CPU，4G RAM为例）

    # vi /etc/my.cnf
    # Example MySQL config file for medium systems.
    #
    # This is for a system with little memory (32M - 64M) where MySQL plays
    # an important part, or systems up to 128M where MySQL is used together with
    # other programs (such as a web server)
    #
    # MySQL programs look for option files in a set of
    # locations which depend on the deployment platform.
    # You can copy this option file to one of those
    # locations. For information about these locations, see:
    # http://dev.mysql.com/doc/mysql/en/option-files.html
    #
    # In this file, you can use all long options that a program supports.
    # If you want to know which options a program supports, run the program
    # with the "--help" option.
   
    # The following options will be passed to all MySQL clients
    [client]
    #password   = your_password
    port        = 3306
    socket      = /tmp/mysql.sock
   
    # Here follows entries for some specific programs
   
    # The MySQL server
    [mysqld]
    report-port = 3306
    port = 3306
    report-host = 192.168.1.140                        //此参数可选，在统计主从数量时非常方便
    pid-file = /tmp/mysql.pid
    socket = /tmp/mysql.sock
    basedir = /usr/local/mysql
    datadir = /usr/local/mysql/var
    skip-external-locking
    max_connections = 1000                            //它指的是客户端连接的最大并发数
    key_buffer_size = 16M                            //它指的是用于缓存MyISAM表索引块的缓冲区大小
    max_allowed_packet = 64M                        //它指的是服务器与客户端之间通信时使用的缓冲区的最大大小
    table_open_cache = 2048                            //它指的是服务器能够同时打开的表的最大个数
    join_buffer_size = 1M                            //根据join语句。1M足矣
    sort_buffer_size = 2M                            //系统中对数据进行排序的时候用到的Buffer。2M-4M足够
    net_buffer_length = 16K                            //它指的是服务器与客户端程序进行通信时使用的连接和结果缓冲区的初始大小。
    read_buffer_size = 8M                            //指的是对表进行顺序扫描的那个线程所使用的缓存区的大小。缓冲区会根据每个客户端的需要进行分配
    read_rnd_buffer_size = 8M
    myisam_sort_buffer_size = 8M                    //在执行类似ALTER TABLE、CREATE INDEX和REPAIR TABLE这样的操作期间，需要分配缓冲区，以便对MyISAM表的索引进行排序。此变量指定的便是该缓冲区的大小。
   
    # Don't listen on a TCP/IP port at all. This can be a security enhancement,
    # if all processes that need to connect to mysqld run on the same host.
    # All interaction with mysqld must be made via Unix sockets or named pipes.
    # Note that using this option without enabling named pipes on Windows
    # (via the "enable-named-pipe" option) will render mysqld useless!
    #
    #skip-networking
   
    # Replication Master Server (default)
    # binary logging is required for replication
    log-bin = /var/log/mysql/master01                //开启binlog指定生成目录
    binlog-do-db = afmweb_news                        //以下设置指定需要生成binlog的数据库，即需要数据恢复同步的库
    binlog-do-db = gw_local
    binlog-do-db = umc_ah
    binlog-ignore-db = afmtimer                        //以下设置忽略生成binlog的数据库，一般系统库和不需要恢复同步的库
    binlog-ignore-db = afmweb_news_cg
    binlog-ignore-db = umc_ah_cg
    binlog-ignore-db = gearman
    binlog-ignore-db=mysql
    binlog-ignore-db=information_schema
    binlog-ignore-db=performance_schema
    relay-log=/var/log/mysql/relay_master01
    log-error=/var/log/mysql/error.log
    skip-name-resolve                                //重要参数，同一IDC ,IDC内部有DNS服务器，对各服务器的IP做了反向解析，但未对内网IP做反向解析，所以使用skip-name-resolve以后用内网地址向mysqlslap请求响应快了一半
    # binary logging format - mixed recommended
    binlog_format = ROW                                //注意此处严格限制binlog格式为ROW
   
    # required unique id between 1 and 2^32 - 1
    # defaults to 1 if master-host is not set
    # but will not function as a master if omitted
    server-id = 140                                    //唯一，为了防止冲突一般需ip尾段
   
    binlog-cache-size = 4M
    max-binlog-size = 1G                            //单个binlog文件大小
    #max-binlog-cache-size = 2G
    sync-binlog = 1
    log-slave-updates = true
    gtid-mode = on                                    //关键参数，on即开启主从模式为GTID模式，5.6版本以后新特性
    slave_parallel_workers=4                        //关键参数，新特性允许主从开启多线程，每个库单独一个线程
    enforce-gtid-consistency = true                    //关键参数，和gtidmode必须一起打开，否则报错
    master-info-repository = TABLE                    //relay.info明文存储不安全，把relay.info中的信息记录在table中相对安全。
    relay-log-info-repository = TABLE
    sync-master-info = 1                            //控制从属服务器更新 master info 的间隔,1为实时记录
    binlog-checksum = CRC32                        
    master-verify-checksum = 1                        //一下几个用于binlog日志验证
    slave-sql-verify-checksum = 1        
    binlog-rows-query-log_events = 1
   
    log-bin-trust-function-creators = 1
    slow-query-log = 1                                //慢日志相关参数
    long-query-time = 5
    slow-query-log-file = /var/log/mysql/slowquery.log
    log-queries-not-using-indexes = 1
    lower_case_table_names = 1
    #slave-skip-errors = 1062,1053
    #auto_increment_increment = 2
    #auto_increment_offset = 1
   
    #loose-innodb-trx=0
    #loose-innodb-locks=0
    #loose-innodb-lock-waits=0
    #loose-innodb-cmp=0
    #loose-innodb-cmp-per-index=0
    #loose-innodb-cmp-per-index-reset=0
    #loose-innodb-cmp-reset=0
    #loose-innodb-cmpmem=0
    #loose-innodb-cmpmem-reset=0
    #loose-innodb-buffer-page=0
    #loose-innodb-buffer-page-lru=0
    #loose-innodb-buffer-pool-stats=0
    #loose-innodb-metrics=0
    #loose-innodb-ft-default-stopword=0
    #loose-innodb-ft-inserted=0
    #loose-innodb-ft-deleted=0
    #loose-innodb-ft-being-deleted=0
    #loose-innodb-ft-config=0
    #loose-innodb-ft-index-cache=0
    #loose-innodb-ft-index-table=0
    #loose-innodb-sys-tables=0
    #loose-innodb-sys-tablestats=0
    #loose-innodb-sys-indexes=0
    #loose-innodb-sys-columns=0
    #loose-innodb-sys-fields=0
    #loose-innodb-sys-foreign=0
    #loose-innodb-sys-foreign-cols=0
   
    # Uncomment the following if you are using InnoDB tables
    innodb_data_home_dir = /usr/local/mysql/var
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_log_group_home_dir = /usr/local/mysql/var
    # You can set .._buffer_pool_size up to 50 - 80 %
    # of RAM but beware of setting memory usage too high
    innodb_buffer_pool_size = 2G                                //设置为内存的50-80%，根据RAM调整
    innodb_additional_mem_pool_size = 128M
    # Set .._log_file_size to 25 % of buffer pool size
    innodb_log_file_size = 500M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 1
    innodb_lock_wait_timeout = 50
    innodb_flush_method = O_DIRECT
    innodb_flush_neighbors = 0
    innodb_file_per_table = 1
   
    #other
    character-set-server=utf8
    collation-server=utf8_general_ci
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
    max-connect-errors=1000
    max-connections=1200
    transaction-isolation=read-committed
    event_scheduler = 1
   
    [mysqldump]
    quick
    max_allowed_packet = 64M
   
    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates
   
    [myisamchk]
    key_buffer_size = 20M
    sort_buffer_size = 20M
    read_buffer = 2M
    write_buffer = 2M
   
    [mysqlhotcopy]
    interactive-timeout
    expire_logs_days = 10
   

3. 修改从服务器slave:
   复制master的my.cnf一份，只修改增加以下几个参数

    server-id = 50            //修改
   
    //以下需要添加指定主从的表和忽略主从的表
    replicate_wild_do_table=afmweb_news.%
    replicate_wild_do_table=gw_local.%
    replicate_wild_do_table=umc_ah.%
    replicate_wild_ignore_table=mysql.%
    replicate_wild_ignore_table=information_schema.%
    replicate_wild_ignore_table=performance_schema.%
   

4. 重启两台服务器上的mysql

    $ service mysql restart
   

5. 在主服务器上建立帐户并授权slave:

    $ mysql -u root -p
    Enter password:
    mysql> GRANT REPLICATION SLAVE ON *.* to 'dbslave'@'192.168.1.%' identified by 'yourpassword';
    mysql> show master status\G
    *************************** 1. row ***************************
                 File: master01.000017
             Position: 477877
         Binlog_Do_DB: afmweb_news,gw_local,umc_ah
     Binlog_Ignore_DB: afmtimer,afmweb_news_cg,umc_ah_cg,gearman,mysql,information_schema,performance_schema
    Executed_Gtid_Set: a9b9b63d-6b63-11e6-8f20-3440b5860519:1-13531
    1 row in set (0.03 sec)
   

6. 配置从服务器Slave：

    $ mysql -u root -p
    Enter password:
    mysql> change master to master_host='192.168.1.140',master_user='dbslave',master_password='yourpassword',master_port=3306,master_auto_position=1;
    Query OK, 0 rows affected, 2 warnings (0.09 sec)
    mysql> start slave;
    Query OK, 0 rows affected, 1 warning (0.03 sec)
    mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.1.140
                      Master_User: dbslave
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: master01.000017
              Read_Master_Log_Pos: 436422
                   Relay_Log_File: relay_master01.000016
                    Relay_Log_Pos: 436630
            Relay_Master_Log_File: master01.000017
                 Slave_IO_Running: Yes                        //确保此处为Yes
                Slave_SQL_Running: Yes                        //确保此处为Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: afmweb_news.%,gw_local.%,umc_ah.%
      Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 436422
                  Relay_Log_Space: 437224
                  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: 140
                      Master_UUID: a9b9b63d-6b63-11e6-8f20-3440b5860519
                 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 the slave I/O thread to update it
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: a9b9b63d-6b63-11e6-8f20-3440b5860519:5160-13531
                Executed_Gtid_Set: a9b9b63d-6b63-11e6-8f20-3440b5860519:1-13531
                    Auto_Position: 1
    1 row in set (0.00 sec)
   

7. 主库创建数据库并还原数据

    $ cd /opt/databak
    $ mysql -u root -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 207584
    Server version: 5.6.23-log Source distribution
   
    Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
   
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
   
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
    mysql> create database afmweb_news;                    //创建需要还原的数据库，此处以afmweb_news为例
    mysql> source afmweb_news.sql                        //导入还原数据，其它库类似