利用mysql5.6新版特性实现基于GTID的主从复制
主从服务器之前分别作以下操作：
- 确定主从关系（此处以192.168.1.140作主，192.168.1.50作从为例）
- 版本一致（mysql-5.6.23）
- 初始化系统表,停止mysql服务
修改主服务器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
修改从服务器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.%
重启两台服务器上的mysql
$ service mysql restart
在主服务器上建立帐户并授权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)配置从服务器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)主库创建数据库并还原数据
$ 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 //导入还原数据，其它库类似