MySQL 5.6 基于GTID 进行主从复制

摘要

GITD是唯一标示符是由服务器的UUID(全局唯一标示对于任何一个服务器都是128位随机符)并结合事务ID号来组合成一个唯一的标示,某一个主机上某一个事务的标识码 就叫做GTID

MySQL 5.6 基于GTID 进行主从复制

mysql


一、什么是GTID?

GTID全称GTIDs are unique identifiers comprising the server UUID (of the original master) and a transaction number

GITD是唯一标示符是由服务器的UUID(全局唯一标示对于任何一个服务器都是128位随机符)并结合事务ID号来组合成一个唯一的标示,某一个主机上某一个事务的标识码 就叫做GTID

自从引入GTID之后 MySQL5.6的binlog在每一个事务的首部都会写上GTID的标示,在二进制文件中,每一个事务的语句记录下来的时候在这个事件首部会把相关联的GTID给记录下来。

因此GTID使得追踪和比较复制事务变得非常简单,而且能够实现从崩溃中快速进行恢复。

尤其是innodb引擎,要想使用高可用功能,必须要依靠GTID实现


二、GTID工作方式

Mysql5.6引入了一个新特性多线程复制,有了gtid相关的功能之后,我们Slave服务器上所谓的多线程复制是我们Master服务器上有多颗cpu,多个事务同时执行的时候,会比Slave服务器读取事务的速度要快,从库存放的只有一个线程。这样在多线程复制的时候Slave服务器会比Master服务器落后很多.
从服务器可以开启多个进程,并行的读取数据存放在本地。但是比如说我们bbs库,2个线程互相读取数据,会造成问题。

Mysql5.6 多线程复制每个数据库也仅能使用一个线程,这样在我们多线程复制的时候只有多个库进行复制,我们的多线程复制才有意义

三、MySQL主从复制原理

这个主从复制原理基本上是mysql的基础知识,我这里在给大家介绍一遍

 MySQL的主从复制是一个异步的复制过程(虽然一般情况下感觉是实时的),数据将从一个Mysql数据库(我们称之为Master)复制到另一个Mysql数据库(我们称之为Slave),在Master与Slave之间实现整个主从复制的过程是由三个线程参与完成的。其中有两个线程(SQL线程和IO线程)在Slave端,另一个线程(I/O线程)在Master端。

  要实现MySQL的主从复制,首先必须打开Master端的binlog记录功能,否则就无法实现。因为整个复制过程实际上就是Slave从aster端获取binlog日志,然后再在Slave上以相同顺序执行获取的binlog日志中的记录的各种SQL操作

image_1bs4ms8nq106l1c401ujg1cor1ond9.png-61.5kB

  • 从库生成两个线程,一个I/O线程,一个SQL线程;
  • i/o线程去请求主库 的binlog,并将得到的binlog日志写到relay log(中继日志) 文件中;
  • 主库会生成一个 log dump 线程,用来给从库 i/o线程传binlog;
  • SQL 线程,会读取relay log文件中的日志,并解析成具体操作,来实现主从的操作一致,而最终数据一致;

四、主从节点数据库配置

1.1环境如下

关于MySQL 5.6的安装我就不介绍了,网上太多了,大家自行百度吧!

IP地址 mysql版本 系统版本 作用 端口
192.168.11.11 MySQL5.6 CentOS Linux release 7.2.1511 Master 3306
192.168.11.12 MySQL5.6 CentOS Linux release 7.2.1511 Slave 3306

关闭防火墙、Selinux、调整字符集 (2台都请执行,当然一台使用多实例也可以)

  1. systemctl stop firewalld
  2. sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
  3. grep SELINUX=disabled /etc/selinux/config
  4. setenforce 0
  5. echo '* - nofile 100000 ' >>/etc/security/limits.conf

1.2配置Master节点my.cnf

  1. [client]
  2. port = 3306
  3. socket = /usr/local/mysql/tmp/mysql.sock
  4. [mysqld]
  5. ########mysql master GTID conf##############
  6. binlog-format=ROW
  7. log-bin=master-bin
  8. log-slave-updates=true
  9. gtid-mode=on
  10. enforce-gtid-consistency=true
  11. master-info-repository=TABLE
  12. relay-log-info-repository=TABLE
  13. sync-master-info=1
  14. slave-parallel-workers=2
  15. binlog-checksum=CRC32
  16. master-verify-checksum=1
  17. slave-sql-verify-checksum=1
  18. binlog-rows-query-log_events=1
  19. server-id=1
  20. report-port=3306
  21. report-host=master.abcdocker.com
  22. #######################################
  23. port = 3306
  24. basedir = /usr/local/mysql/
  25. socket = /usr/local/mysql/tmp/mysql.sock
  26. pid-file = /usr/local/mysql/var/mysql.pid
  27. datadir = /usr/local/mysql/var/
  28. tmpdir = /usr/local/mysql/tmp/
  29. slave-load-tmpdir = /usr/local/mysql/tmp/
  30. !include /usr/local/mysql/etc/mysqld.cnf
  31. # skip lever
  32. skip-name-resolve
  33. skip-symbolic-links
  34. skip-external-locking
  35. skip-slave-start
  36. #thread level
  37. table_open_cache = 2048
  38. #############connect############
  39. back_log = 50
  40. max_connections = 1000
  41. max_connect_errors = 10000
  42. #open_files_limit = 10240
  43. ##############timeout###########
  44. connect-timeout = 10
  45. wait-timeout = 800
  46. interactive-timeout = 800
  47. slave-net-timeout = 60
  48. net_read_timeout = 30
  49. net_write_timeout = 60
  50. net_retry_count = 10
  51. net_buffer_length = 16384
  52. max_allowed_packet = 64M
  53. ################# cache #############
  54. table_open_cache = 2048
  55. thread_stack = 192K
  56. thread_cache_size = 100
  57. thread_concurrency = 16
  58. # qcache settings
  59. query_cache_size = 20m
  60. query_cache_limit = 2M
  61. query_cache_min_res_unit = 2K
  62. # default settings
  63. # time zone
  64. default-time-zone = system
  65. character-set-server = utf8
  66. default-storage-engine = InnoDB
  67. # tmp & heap
  68. tmp_table_size = 512M
  69. max_heap_table_size = 512M
  70. log-bin = mysql-bin
  71. log-bin-index = mysql-bin.index
  72. relay-log = relay-log
  73. relay_log_index = relay-log.index
  74. # warning & error log
  75. log-warnings = 1
  76. log-error = /usr/local/mysql/log/mysql.err
  77. log-output = FILE
  78. # slow query log
  79. slow_query_log = 1
  80. long-query-time = 1
  81. slow_query_log_file = /usr/local/mysql/log/slow.log
  82. #log-queries-not-using-indexes
  83. #log-slow-slave-statements
  84. general_log = 1
  85. general_log_file = /usr/local/mysql/log/mysql.log
  86. max_binlog_size = 1G
  87. max_relay_log_size = 1G
  88. # if use auto-ex, set to 0
  89. relay-log-purge = 1
  90. # max binlog keeps days
  91. expire_logs_days = 7
  92. binlog_cache_size = 1M
  93. # replication
  94. replicate-wild-ignore-table = mysql.%
  95. replicate-wild-ignore-table = test.%
  96. # slave_skip_errors=all
  97. key_buffer_size = 30M
  98. sort_buffer_size = 2M
  99. read_buffer_size = 2M
  100. join_buffer_size = 8M
  101. read_rnd_buffer_size = 8M
  102. bulk_insert_buffer_size = 64M
  103. myisam_sort_buffer_size = 64M
  104. myisam_max_sort_file_size = 10G
  105. myisam_repair_threads = 1
  106. myisam_recover
  107. group_concat_max_len = 64K
  108. transaction_isolation = REPEATABLE-READ
  109. innodb_file_per_table
  110. #############mysql5.5new################
  111. innodb_file_format = Barracuda
  112. ########################################
  113. innodb_additional_mem_pool_size = 10M
  114. innodb_buffer_pool_size = 200M
  115. innodb_data_home_dir = /usr/local/mysql/var/
  116. innodb_data_file_path = ibdata1:100M:autoextend
  117. ################mysql5.5new############
  118. innodb_read_io_threads = 8
  119. innodb_write_io_threads = 8
  120. innodb_purge_threads = 1
  121. ########################################
  122. innodb_thread_concurrency = 16
  123. innodb_flush_log_at_trx_commit = 1
  124. innodb_log_buffer_size = 16M
  125. innodb_log_file_size = 50M
  126. innodb_log_files_in_group = 2
  127. innodb_log_group_home_dir = /usr/local/mysql/var/
  128. innodb_max_dirty_pages_pct = 90
  129. innodb_lock_wait_timeout = 50
  130. #innodb_flush_method = O_DSYNC
  131. ################mysql5.6 new#####################
  132. innodb_buffer_pool_instances = 16
  133. innodb_change_buffering = all
  134. innodb_adaptive_flushing = 1
  135. innodb_io_capacity = 2000
  136. innodb_old_blocks_time = 1000
  137. innodb_stats_on_metadata = 0
  138. #################################################
  139. old-passwords = 0
  140. [mysqldump]
  141. quick
  142. max_allowed_packet = 64M
  143. [mysql]
  144. no-auto-rehash
  145. default-character-set = utf8
  146. connect-timeout = 3
  147. [myisamchk]
  148. key_buffer_size = 256M
  149. sort_buffer_size = 256M
  150. read_buffer = 2M
  151. write_buffer = 2M
  152. [mysqlhotcopy]
  153. interactive-timeout
  154. #提示:配置文件除了上面mysql master GTID conf注释下的是我们GTID需要用的,其他的大家可以用默认就可以。我这个配置比较全

1.3配置Master详解

  1. [mysqld]
  2. binlog-format=ROW #行记录
  3. log-bin=master-bin #二进制文件
  4. log-slave-updates=true #表示当从服务器从中继日志中读取事件在本地应用的时候,是否把操作记录到本地的二进制文件中(为了GTID复制的安全,我们启用这项) 主服务器必须启用,从服务器可以不启用
  5. gtid-mode=on #gtid-mode=是否启用GTID功能
  6. enforce-gtid-consistency=true #是否强制GTID一致性(所谓GTID一致性指的是在记录到二进制的时候有些特殊的语句能够设计数据库修改的语句它是不记录的,但是可以靠其他功能保证数据一致性。主要和临时创建的表语句有关)
  7. MySQL 5.6.2之前,slave记录的master信息以及slave应用binlog的信息存放在文件中,即master.inforelay-log.info。在5.6.2版本之后,允许记录到table
  8. master-info-repository=TABLE #主要用于主服务器记录从服务连接信息和每一个从服务器的二进制文件及其相关事件位置等信息记录到一个文件中(master.info)或一个表
  9. relay-log-info-repository=TABLE #让从服务器自己记录连接的主服务器是谁,主服务器哪一个二进制文件,及二进制文件的位置等等
  10. 对应的表分别为mysql.slave_master_infomysql.slave_relay_log_info,且这两个表均为innodb引擎表
  11. master inforelay info还有3个参数控制刷新:
  12. sync_relay_log:默认为10000,即每10000sync_relay_log事件会刷新到磁盘。为0则表示不刷新,交由OScache控制。
  13. sync_master_info:若master-info-repositoryFILE,当设置为0,则每次sync_master_info事件都会刷新到磁盘,默认为10000次刷新到磁盘;若master-info-repositoryTABLE,当设置为0,则表不做任何更新,设置为1,则每次事件会更新表 #默认为10000
  14. sync_relay_log_info:若relay_log_info_repositoryFILE,当设置为0,交由OS刷新磁盘,默认为10000次刷新到磁盘;若relay_log_info_repositoryTABLE,且为INNODB存储,则无论为任何值,则都每次evnet都会更新表。
  15. 建议参数设置如下:
  16. sync_relay_log = 1
  17. sync_master_info = 1
  18. sync_relay_log_info = 1
  19. GTID模式下,每一个从服务器在连入主服务器的时候,它必须要告诉主服务器自己的主机名和IP地址and端口号这样我们在主服务器使用show slave hosts可以看到当前主服务器连接的从服务器信息
  20. report-port= 从服务器的端口
  21. report-host= 从服务器的主机名
  22. report-host=master.abcdocker.com
  23. report-port=3306
  24. server-id=1 #同一个复制拓扑中的所有服务器的ID号必须唯一
  25. 从服务器并行的线程数
  26. slave-parallel-workers=2
  27. workers指的是进程
  28. 后面的进程数尽量等于数据库的个数或者小于,大于数据库的数量是没有意义的
  29. 如果=0 表示禁用多线程功能
  30. binlog-checksummaster-verify-checksumslave-sql-verify-checksum:启用复制有关的所有校验功能
  31. binlog-checksum=CRC32 #主服务器端在启动的时候要不要校验binlog本身的校验码
  32. master-verify-checksum=1
  33. slave-sql-verify-checksum=1
  34. binlog-rows-query-log_events=1 #用于在二进制日志详细记录事件相关的信息,可降低故障排除的复杂度;
  35. datadir"/mydata/data
  36. socket=/tmp/mysql.sock #mysql.sock的作用是server和client在同一台服务器,并且使用localhost进行链接的时候,就会使用socket来进行连接
  37. log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host:用于启动GTID及满足附属的其他需求;

1.4配置Slave详解

  1. [mysqld]
  2. binlog-format=ROW
  3. log-slave-updates=true
  4. gtid-mode=on
  5. enforce-gtid-consistency=true
  6. master-info-repository=TABLE
  7. relay-log-info-repository=TABLE
  8. sync-master-info=1
  9. slave-parallel-workers=2
  10. binlog-checksum=CRC32
  11. master-verify-checksum=1
  12. slave-sql-verify-checksum=1
  13. binlog-rows-query-log_events=1
  14. server-id=11 #需要和master不一样
  15. report-port=3306
  16. port=3306
  17. log-bin=mysql.bin.log
  18. report-host=slave.abcdocker.com #名字也要和master不一样
  19. #从节点不需要定义二进制日志,也可以不定义中继日志

1.5配置Slave节点配置my.cnf

  1. [client]
  2. port = 3306
  3. socket = /usr/local/mysql/tmp/mysql.sock
  4. [mysqld]
  5. #############slave GTID conf#################################
  6. binlog-format=ROW
  7. log-slave-updates=true
  8. gtid-mode=on
  9. enforce-gtid-consistency=true
  10. master-info-repository=TABLE
  11. relay-log-info-repository=TABLE
  12. sync-master-info=1
  13. slave-parallel-workers=2
  14. binlog-checksum=CRC32
  15. master-verify-checksum=1
  16. slave-sql-verify-checksum=1
  17. binlog-rows-query-log_events=1
  18. server-id=11
  19. report-port=3306
  20. report-host=slave.abcdocker.com
  21. ########################################################
  22. port = 3306
  23. basedir = /usr/local/mysql/
  24. socket = /usr/local/mysql/tmp/mysql.sock
  25. pid-file = /usr/local/mysql/var/mysql.pid
  26. datadir = /usr/local/mysql/var/
  27. tmpdir = /usr/local/mysql/tmp/
  28. slave-load-tmpdir = /usr/local/mysql/tmp/
  29. !include /usr/local/mysql/etc/mysqld.cnf
  30. # skip lever
  31. skip-name-resolve
  32. skip-symbolic-links
  33. skip-external-locking
  34. skip-slave-start
  35. #thread level
  36. table_open_cache = 2048
  37. #############connect############
  38. back_log = 50
  39. max_connections = 1000
  40. max_connect_errors = 10000
  41. #open_files_limit = 10240
  42. ##############timeout###########
  43. connect-timeout = 10
  44. wait-timeout = 800
  45. interactive-timeout = 800
  46. slave-net-timeout = 60
  47. net_read_timeout = 30
  48. net_write_timeout = 60
  49. net_retry_count = 10
  50. net_buffer_length = 16384
  51. max_allowed_packet = 64M
  52. ################# cache #############
  53. table_open_cache = 2048
  54. thread_stack = 192K
  55. thread_cache_size = 100
  56. thread_concurrency = 16
  57. # qcache settings
  58. query_cache_size = 20m
  59. query_cache_limit = 2M
  60. query_cache_min_res_unit = 2K
  61. # default settings
  62. # time zone
  63. default-time-zone = system
  64. character-set-server = utf8
  65. default-storage-engine = InnoDB
  66. # tmp & heap
  67. tmp_table_size = 512M
  68. max_heap_table_size = 512M
  69. log-bin = mysql-bin
  70. log-bin-index = mysql-bin.index
  71. relay-log = relay-log
  72. relay_log_index = relay-log.index
  73. # warning & error log
  74. log-warnings = 1
  75. log-error = /usr/local/mysql/log/mysql.err
  76. log-output = FILE
  77. # slow query log
  78. slow_query_log = 1
  79. long-query-time = 1
  80. slow_query_log_file = /usr/local/mysql/log/slow.log
  81. #log-queries-not-using-indexes
  82. #log-slow-slave-statements
  83. general_log = 1
  84. general_log_file = /usr/local/mysql/log/mysql.log
  85. max_binlog_size = 1G
  86. max_relay_log_size = 1G
  87. # if use auto-ex, set to 0
  88. relay-log-purge = 1
  89. # max binlog keeps days
  90. expire_logs_days = 7
  91. binlog_cache_size = 1M
  92. # replication
  93. replicate-wild-ignore-table = mysql.%
  94. replicate-wild-ignore-table = test.%
  95. # slave_skip_errors=all
  96. key_buffer_size = 30M
  97. sort_buffer_size = 2M
  98. read_buffer_size = 2M
  99. join_buffer_size = 8M
  100. read_rnd_buffer_size = 8M
  101. bulk_insert_buffer_size = 64M
  102. myisam_sort_buffer_size = 64M
  103. myisam_max_sort_file_size = 10G
  104. myisam_repair_threads = 1
  105. myisam_recover
  106. group_concat_max_len = 64K
  107. transaction_isolation = REPEATABLE-READ
  108. innodb_file_per_table
  109. #############mysql5.5new################
  110. innodb_file_format = Barracuda
  111. ########################################
  112. innodb_additional_mem_pool_size = 10M
  113. innodb_buffer_pool_size = 200M
  114. innodb_data_home_dir = /usr/local/mysql/var/
  115. innodb_data_file_path = ibdata1:100M:autoextend
  116. ################mysql5.5new############
  117. innodb_read_io_threads = 8
  118. innodb_write_io_threads = 8
  119. innodb_purge_threads = 1
  120. ########################################
  121. innodb_thread_concurrency = 16
  122. innodb_flush_log_at_trx_commit = 1
  123. innodb_log_buffer_size = 16M
  124. innodb_log_file_size = 50M
  125. innodb_log_files_in_group = 2
  126. innodb_log_group_home_dir = /usr/local/mysql/var/
  127. innodb_max_dirty_pages_pct = 90
  128. innodb_lock_wait_timeout = 50
  129. #innodb_flush_method = O_DSYNC
  130. ################mysql5.6 new#####################
  131. innodb_buffer_pool_instances = 16
  132. innodb_change_buffering = all
  133. innodb_adaptive_flushing = 1
  134. innodb_io_capacity = 2000
  135. innodb_old_blocks_time = 1000
  136. innodb_stats_on_metadata = 0
  137. #################################################
  138. old-passwords = 0
  139. [mysqldump]
  140. quick
  141. max_allowed_packet = 64M
  142. [mysql]
  143. no-auto-rehash
  144. default-character-set = utf8
  145. connect-timeout = 3
  146. [myisamchk]
  147. key_buffer_size = 256M
  148. sort_buffer_size = 256M
  149. read_buffer = 2M
  150. write_buffer = 2M
  151. [mysqlhotcopy]
  152. interactive-timeout

1.6 Master 上创建复制用户

  1. grant replication slave on *.* to repl@192.168.11.12 identified by '123456';

说明:192.168.11.12是从节点服务器,如果想一次性授权更多的节点,可以自行根据需要修改。


1.7 为备节点提供初始数据集

锁定主表,备份主节点上的数据,将其还原至从节点;如果没有GTID,在备份时需要在master上使用show master status命令查看二进制文件名称及事件位置,以便后面启动slave节点时使用。

1.8 启动Slave节点的复制线程

如果启动了GTID功能,则使用如下命令;

  1. CHANGE MASTER TO MASTER_HOST='master.abcdocker.com',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1;
  • MASTER_AUTO_POSITION 该参数在mysql5.6.5版本引入,如果进行change master to时使用MASTER_AUTO_POSITION = 1,slave连接master将使用基于GTID的复制协议。

  • 使用基于GTID协议的复制,slave会告诉master它已经接收到或执行了哪些事务。计算这个集,slave需要读取全局参数gtid_executed以及通过show slave status获取的参数Retrieved_gtid_set。

  • 结果集作为初次握手的一部分,发送到master,master发回它已经执行的且不在结果集这部分的所有事务。如果这些事务在master的binlog文件中已经被清除,master将会发送一个

提示:主从服务器节点时间要同步,主机名最好可以解析。

启动从库

  1. mysql> start slave;

查看主从状态

  1. mysql> show slave status\G
  2. *************************** 1. row ***************************
  3. Slave_IO_State: Waiting for master to send event
  4. Master_Host: master.abcdocker.com
  5. Master_User: repl
  6. Master_Port: 3306
  7. Connect_Retry: 60
  8. Master_Log_File: mysql-bin.000005
  9. Read_Master_Log_Pos: 814
  10. Relay_Log_File: relay-log.000002
  11. Relay_Log_Pos: 1024
  12. Relay_Master_Log_File: mysql-bin.000005
  13. Slave_IO_Running: Yes
  14. Slave_SQL_Running: Yes
  15. Replicate_Do_DB:
  16. Replicate_Ignore_DB:
  17. Replicate_Do_Table:
  18. Replicate_Ignore_Table:
  19. Replicate_Wild_Do_Table:
  20. Replicate_Wild_Ignore_Table: mysql.%,test.%
  21. Last_Errno: 0
  22. Last_Error:
  23. Skip_Counter: 0
  24. Exec_Master_Log_Pos: 814
  25. Relay_Log_Space: 1222
  26. Until_Condition: None
  27. Until_Log_File:
  28. Until_Log_Pos: 0
  29. Master_SSL_Allowed: No
  30. Master_SSL_CA_File:
  31. Master_SSL_CA_Path:
  32. Master_SSL_Cert:
  33. Master_SSL_Cipher:
  34. Master_SSL_Key:
  35. Seconds_Behind_Master: 0
  36. Master_SSL_Verify_Server_Cert: No
  37. Last_IO_Errno: 0
  38. Last_IO_Error:
  39. Last_SQL_Errno: 0
  40. Last_SQL_Error:
  41. Replicate_Ignore_Server_Ids:
  42. Master_Server_Id: 1
  43. Master_UUID: a961eee5-ae7a-11e7-89f1-000c29020966
  44. Master_Info_File: mysql.slave_master_info
  45. SQL_Delay: 0
  46. SQL_Remaining_Delay: NULL
  47. Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
  48. Master_Retry_Count: 86400
  49. Master_Bind:
  50. Last_IO_Error_Timestamp:
  51. Last_SQL_Error_Timestamp:
  52. Master_SSL_Crl:
  53. Master_SSL_Crlpath:
  54. Retrieved_Gtid_Set: a961eee5-ae7a-11e7-89f1-000c29020966:1-3
  55. Executed_Gtid_Set: a961eee5-ae7a-11e7-89f1-000c29020966:1-3
  56. Auto_Position: 1
  57. 1 row in set (0.00 sec)

五、演示

1.1 主库创建库

  1. master-mysql> create database shopping;
  2. Query OK, 1 row affected (0.00 sec)

从库查看

  1. slave-mysql> show databases;
  2. +--------------------+
  3. | Database |
  4. +--------------------+
  5. | information_schema |
  6. | mysql |
  7. | performance_schema |
  8. | shopping |
  9. | test |
  10. +--------------------+
  11. 5 rows in set (0.01 sec)

这里已经出来了,我们导入点数据

1.2 导出一份数据

  1. [root@abcdocker ~]# mysqldump -uroot -p --hex-blob --routines --triggers --single-transaction --default-character-set=utf8 shopping >slave.sql
  2. Enter password:
  3. 关于mysqldump相关参数请前往:https://www.abcdocker.com/abcdocker/20

现在我们已经将数据导入到主库,现在去从库看一下

  1. master-mysql> show tables;
  2. +------------------------------+
  3. | Tables_in_shopping |
  4. +------------------------------+
  5. | aaaaaa_activity |
  6. | aaaaaa_activity_detail |
  7. | aaaaa_address |
  8. | aaaaa_admin |
  9. 这时候主库和从库看到的数据应该和我们这里面显示的相同

常用命令

停止从库:
mysql> stop slave;
开启从库
mysql> start slave;
清除主从信息:(关闭从库才可以执行)
mysql> reset slave;

MySQL 复制过滤功能介绍

数据库过滤,数据同步的时候只复制某一个库或者某一个表或某些表的操作

1.1 配置介绍
主 [Master]

  • binlog-do-db-d   代表白名单(仅将指定数据库的相关修改操作记入二进制日志) 缺点:如果主服务器宕了,恢复的时候只可以恢复记录到二进制文件日志里面的数据量。

  • binlog-ignore-db   代表黑名单(只要和这里面设置相关的库,都不记录到二进制文件 )

  • 不管是do-db还是ignore-db我们都不建议在主端操作,一旦主库挂了,任何不被记录的数据都将无法还原。
    我们不开启do和ignore这个功能,这时候我们主库的binlog是完整的。但是在从库会有很多的二进制可以进行过滤

从[Slave]

  • replicate-do-db 白名单(只应用哪个数据库到本地 )
  • replicate-ignore-db 黑名单(忽略哪些数据库到本地)

  • 过滤的功能还可以在表级别实现
    replicate-do-table=    应用那些表到本地
    replicate-ignore-table=  忽略哪些表到本地

  • 在实现表同步的时候还可以使用通配符 replicate-wild-do-table= (abcdocker.tb%)abcdocker下面的所有tb开头的表 replicate-wild-ignore-table=

  • 通配符%,_ 我们可以一个指令使用多次,只要每次使用的值不同就可以

1.2 例子

例子:例如我们在mysql主从上,只复制abcdocker上的所有数据
从库设置如下

  1. vim /etc/my.conf
  2. relay-log = relay-log
  3. relay-log-index =relay-log-index
  4. replicate-do-db = abcdocker [库]

重启mysqld
重启从库之后我们需要开启主从连接,在查看就可以看到我们这里只同步abcdocker库
image_1bs59gqgd1r461fq5jloltf176qm.png-56.6kB

主库设置如下

  1. 我们在主库创建2databases
  2. create database abcdocker
  3. create database discuz
  4. 因为我们同步discuz库,而不同步abc库。

主库有如下库

  1. master-mysql> show databases;
  2. +--------------------+
  3. | Database |
  4. +--------------------+
  5. | information_schema |
  6. | abcdocker |
  7. | discuz |
  8. | mysql |
  9. | performance_schema |
  10. | shopping |
  11. | test |
  12. +--------------------+
  13. 7 rows in set (0.00 sec)

我们在去从库使用show databases;查看库是否有discuz和abcdocker库就可以
此时只有abcdocker库一个,因为我们授权的只有一个

  1. mysql> show databases;
  2. +--------------------+
  3. | Database |
  4. +--------------------+
  5. | information_schema |
  6. | abcdocker |
  7. | mysql |
  8. | performance_schema |
  9. | shopping |
  10. | test |
  11. +--------------------+
  12. 6 rows in set (0.00 sec)

相关文章
MySQL主从原理


历史上的今天:

  1. 2016:  Zabbix 3.0 分布式监控 [九](0)
  2. 2016:  Zabbix 3.0 主备模式 [八](0)
    A+
发布日期:2017年10月11日  所属分类:MySQL
标签: