MySQL5.6基于GTID的主从复制
http://www.jb51.net/article/79718.htm
MySQL 5.6 的新特性之一,是加入了全局事务 ID (GTID) 来强化数据库的主备一致性,故障恢复,以及容错能力。
什么是GTID?
官方文档:http://dev.mysql.com/doc/refman/5.6/en/replication-gtids.html在这篇文档里,我们可以知道全局事务 ID 的官方定义是:GTID = source_id:transaction_id
MySQL 5.6 中,每一个 GTID 代表一个数据库事务。在上面的定义中,source_id 表示执行事务的主库 uuid(server_uuid),transaction_id 是一个从 1 开始的自增计数,表示在这个主库上执行的第 n 个事务。MySQL 会保证事务与 GTID 之间的 1 : 1 映射。
一、环境准备
操作系统:CentOS6.5 64位
数据库版本:MySQL5.6.23
拓扑如下:
三、安装主数据库(masterdb.example.com)
1、准备数据存放目录、创建用户
123456 | [root@masterdb ~]#mkdir /data/mysqldata -p #创建数据存放目录 [root@masterdb ~]#mkdir /data/mysqlLog/logs -p #创建日志存放目录 [root@masterdb ~]#groupadd -r mysql [root@masterdb ~]#useradd -g mysql -r -s /sbin/nologin -M -d /data/mysqldata mysql [root@masterdb ~]#chown -R mysql:mysql /data/mysqldata [root@masterdb ~]#chown -R mysql:mysql /data/mysqlLog/logs |
2、安装并初始化mysql5.6.23
1234567891011 | [root@masterdb ~]# tar xf mysql-advanced-5.6.23-linux-glibc2.5-x86_64.tar.gz -C /usr/ local / [root@masterdb ~]# cd /usr/ local / [root@masterdb ~]# ln -sv mysql-advanced-5.6.23-linux-glibc2.5-x86_64 mysql [root@masterdb ~]# chown -R root.mysql mysql [root@masterdb ~]# cd mysql [root@masterdb ~]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld [root@masterdb ~]# cp support-files/my- default .cnf /etc/my.cnf [root@masterdb ~]# chmod +x /etc/rc.d/init.d/mysqld [root@masterdb ~]# chkconfig --add mysqld [root@masterdb ~]# chkconfig mysqld on [root@masterdb ~]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysqldata/ |
3、输出mysql的man手册至man命令的查找路径:
编辑/etc/man.config,添加如下行即可:
MANPATH /usr/local/mysql/man
4、输出mysql的头文件至系统头文件路径/usr/include:
这可以通过简单的创建链接实现:
1 | [root@masterdb ~]#ln -sv /usr/ local /mysql/include /usr/include/mysql |
5、输出mysql的库文件给系统库查找路径:
1 | [root@masterdb ~]#echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf |
而后让系统重新载入系统库:
1 | [root@masterdb ~]# ldconfig |
6、修改PATH环境变量,让系统可以直接使用mysql的相关命令:
123 | [root@masterdb ~]# vim /etc/profile.d/mysql.sh export PATH=$PATH:/usr/ local /mysql/bin [root@masterdb ~]#source /etc/profile.d/mysql.sh |
从数据库安装同上,具体过程略过。
四、分别为主从数据库提供配置文件/etc/my.cnf
要在MySQL 5.6中使用复制功能,其服务配置段[mysqld]中于少应该定义如下选项:
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线程数;0表示关闭多线程复制功能;
binlog-checksum、master-verify-checksum和slave-sql-verify-checksum:启用复制有关的所有校验功能;
binlog-rows-query-log-events:启用之可用于在二进制日志记录事件相关的信息,可降低故障排除的复杂度;
log-bin:启用二进制日志,这是保证复制功能的基本前提;
server-id:同一个复制拓扑中的所有服务器的id号必须惟一;
主数据库上:
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748 | [client] port = 3306 socket = /tmp/mysql.sock default - character - set = utf8 [mysql] no -auto-rehash default - character - set = utf8
[mysqld] server-id = 1 port = 3306 user = mysql basedir = /usr/ local /mysql datadir = /data/mysqldata socket = /tmp/mysql.sock default -storage-engine = INNODB character - set -server = utf8 connect_timeout = 60 interactive_timeout = 28800 wait_timeout = 28800 back_log = 500 event_scheduler = ON skip_name_resolve = ON ;
###########binlog########## log-bin = /data/mysqlLog/logs/mysql-bin binlog_format = row max_binlog_size = 128M binlog_cache_size = 2M expire-logs-days = 5 log-slave-updates= true gtid-mode= on enforce-gtid-consistency= true master-info-repository= TABLE relay-log-info-repository= TABLE sync-master-info=1 slave-parallel-workers=4 #rpl_semi_sync_master_enabled = 1
slow_query_log = 1 slow_query_log_file = /data/mysqlLog/logs/mysql.slow long_query_time = 1
log_error = /data/mysqlLog/logs/error.log max_connections = 3000 max_connect_errors = 32767 log_bin_trust_function_creators = 1 transaction_isolation = READ - COMMITTED |
从数据库上:
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748 | [client] port = 3306 socket = /tmp/mysql.sock default - character - set = utf8
[mysql] no -auto-rehash default - character - set = utf8
[mysqld] server-id = 205 port = 3306 user = mysql basedir = /usr/ local /mysql datadir = /data/mysqldata socket = /tmp/mysql.sock default -storage-engine = INNODB character - set -server = utf8 connect_timeout = 60 wait_timeout = 18000 back_log = 500 event_scheduler = ON
###########binlog########## log-bin = /data/mysqlLog/logs/mysql-bin binlog_format = row max_binlog_size = 128M binlog_cache_size = 2M expire-logs-days = 5 log-slave-updates= true gtid-mode= on enforce-gtid-consistency= true master-info-repository= TABLE relay-log-info-repository= TABLE sync-master-info=1 slave-parallel-workers=4 #rpl_semi_sync_slave_enabled = 1 skip-slave-start
slow_query_log = 1 slow_query_log_file = /data/mysqlLog/logs/mysql.slow long_query_time = 2
log-error = /data/mysqlLog/logs/error.log max_connections = 3000 max_connect_errors = 10000 log_bin_trust_function_creators = 1 transaction_isolation = READ - COMMITTED |
五、分别在主从数据库上启动mysqld服务
12345678 | [root@masterdb ~]# service mysqld start Starting MySQL...... [ OK ] [root@masterdb ~]#
[root@slavedb ~]# service mysqld start Starting MySQL...... [ OK ] [root@slavedb ~]# |
六、在主数据库上创建复制用户
说明:172.16.88.205是从节点服务器;如果想一次性授权更多的节点,可以自行根据需要修改;
七、启动从数据库上的复制线程
12 | mysql> CHANGE MASTER TO MASTER_HOST= 'masterdb.example.com' , MASTER_USER= 'repluser' , MASTER_PASSWORD= 'replpassword' , MASTER_AUTO_POSITION=1; mysql>start slave; |
八、在从数据库上查看复制状态
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263 | mysql> show slave status\G; *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: masterdb.56xyl.com
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 191
Relay_Log_File: slavedb-relay-bin.000003
Relay_Log_Pos: 401
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes #IO线程已正常运行
Slave_SQL_Running: Yes #SQL线程已正常运行
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: 191
Relay_Log_Space: 1899
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: 971d7245-c3f8-11e5-8b6b-000c2999e5a5
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: 971d7245-c3f8-11e5-8b6b-000c2999e5a5:1-6
Executed_Gtid_Set: 89e78301-c3f4-11e5-8b51-00505624d26a:1-3, 971d7245-c3f8-11e5-8b6b-000c2999e5a5:1-6
Auto_Position: 1 1 row in set (0.00 sec)
ERROR: No query specified
mysql> |
九、测试
在主库上创建数据库:
123456789101112131415161718 | mysql> create database log_statics; Query OK, 1 row affected (0.11 sec)
mysql> use log_statics; Database changed 到从数据库上查看log_statics是否已经复制过去 mysql> show databases; + --------------------+ | Database | + --------------------+ | information_schema | | log_statics | | mysql | | performance_schema | + --------------------+ 4 rows in set (0.01 sec)
mysql> |
可以看到log_statics数据库已经存在于从数据库上。
更多相关文章
- vmware12下的centos7 linux 安装 mysql5.17数据库
- ASP.NET GridView 绑定 MySql数据库
- 如何向mysql数据库添加多个映像?
- 获取项目列表的更好方法:缓存序列化数据与数据库查询或其他?
- MySQL数据库相关开发入门
- MySQL数据库导入或者同步大量数据时数据丢失解决方案
- php 连接数据库 Warning: mysqli_connect(): (HY000/2002): No s
- mysql进阶(十一)外键在数据库中的作用
- 我需要介绍MongoDB / NoSQL数据库