- A+
所属分类：数据库
mysql5.6支持以全局统一事务ID（GTID)为基础的复制，当在主库上提交事务或者被从库应用时， 可以定位和追踪每一个事务，可以通过使用–gtid-mode 和–enforce-gtid-consistency 参数启动复制可以开启GTIDs 这也是mysql官方提供的工具集utilities中的自动failover的一个基础，今天我们一步一步的来使用mysql utilities来搭建一套 MySQL自动failover结构
环境准备
我准备在我自己的电脑上面搭建四台mysql实例，一个master和三个slave
localhost：3306 （ master ）
localhost：3307 （ slave ）
localhost：3308 （ slave ）
localhost：3309 （ slave ）
第一步安装mysql DB
从官方网站下载 >= 5.6.10 的mysql dmg版本。安装到系统中之后，配置好path 和 mysql_base
export MYSQL_BASE=/usr/local/mysql
export DYLD_LIBRARY_PATH="$DYLD_LIBRARY_PATH:/usr/local/mysql/lib" #for python
export PATH=$MYSQL_BASE/bin:$PATH
第二步 初始化mysql DB
配置my.cnf文件
[client]
port=3309
socket=/data/mysql3309/run/mysql.sock
[mysql]
port=3309
prompt=\\u@\\d \\r:\\m:\\s>
default-character-set=gbk
[mysqld]
report_host=localhost
explicit_defaults_for_timestamp=true
default-storage-engine=INNODB
master-info-repository=TABLE
#dir
innodb_log_group_home_dir=/logs/mysql3309/iblog
innodb_data_home_dir=/logs/mysql3309/iblog
basedir=/usr/local/mysql/
datadir=/data/mysql3309/data
tmpdir=/data/mysql3309/tmp
slave_load_tmpdir=/data/mysql3309/tmp
log-error=/data/mysql3309/log/alert.log
slow_query_log_file=/data/mysql3309/log/slow.log
relay_log_info_file=/logs/mysql3309/binlog/relay-log.info
master-info-file=/logs/mysql3309/binlog/master.info
socket=/data/mysql3309/run/mysql.sock
log-bin=/logs/mysql3309/binlog/binlog
relay-log=/logs/mysql3309/binlog/relaylog
lower_case_table_names=2
#innodb
innodb_log_files_in_group=4
innodb_log_file_size=20M
innodb_buffer_pool_size=256m
innodb_open_files=65535
innodb_flush_log_at_trx_commit=2
innodb_max_dirty_pages_pct=50
innodb_io_capacity=100
innodb_read_io_threads=2
innodb_write_io_threads=4
innodb_file_format=Barracuda
innodb_file_per_table=1
innodb_thread_concurrency=10
innodb_change_buffering=inserts
innodb_adaptive_flushing=1
innodb_stats_on_metadata=0
innodb_additional_mem_pool_size=10M
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=10M
transaction-isolation = READ-COMMITTED
query_cache_type=0
sync_binlog=100
max_binlog_size =50M
binlog_cache_size=5M
binlog-format=ROW
expire_logs_days=7
#gtid
gtid-mode=on
log-slave-updates=true
enforce-gtid-consistency=true
long_query_time=1
slow_query_log=1
skip-slave-start
server_id=1
#timeout
connect_timeout=30
delayed_insert_timeout =300
innodb_lock_wait_timeout=50
innodb_rollback_on_timeout=OFF
net_read_timeout=30
net_write_timeout=60
slave_net_timeout=30
port=3309
skip-name-resolve
max_connect_errors=1500
connect_timeout=30
max_allowed_packet=24M
#myisam
concurrent_insert=2
key_buffer_size=8M
sort_buffer_size=4M
join_buffer_size=4M
read_buffer_size=4M
myisam_sort_buffer_size=20M
#common
character-set-server=gbk
skip-external-locking
read_rnd_buffer_size=5M
safe-user-create
local-infile=0
[mysqld_safe]
pid-file=/data/mysql3309/run/mysqld.pid
其中： report_host=localhost
explicit_defaults_for_timestamp=true
gtid-mode=on log-slave-updates=true
enforce-gtid-consistency=true
是必须的；注意四个实例的server_id 不能一样。
配置每个实例的环境变量
alias my3306='export MYSQL_HOME=/data/mysql3306/'
alias my3307='export MYSQL_HOME=/data/mysql3307/'
alias my3308='export MYSQL_HOME=/data/mysql3308/'
alias my3309='export MYSQL_HOME=/data/mysql3309/'
alias dbasql='mysql -uroot -proot'
alias nsql='mysql '
alias alert='tail -100f $MYSQL_HOME/log/alert.log'
初始化每一个实例(以3309为例子) 创建目录，赋权
mkdir -p /data/mysql3309/{data,tmp,run,log}
mkdir -p /log/mysql3309/{iblog,binlog}
chmod -R 777 /data/mysql3309/
chmod -R 777 /logs/mysql3309/
chmod 644 /data/mysql3309/my.cnf
create database
/usr/local/mysql/scripts/mysql_install_db --defaults-file=/data/mysql3309/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql3309/data --user=root
启动实例
export MYSQL_HOME=/data/mysql3309/
mysqld_safe &
第三步构建复制结构
mysqlreplicate --master=root:root@127.0.0.1:3306 --slave=root:root@127.0.0.1:3307 --rpl-user=repl:repl -vv
mysqlreplicate --master=root:root@127.0.0.1:3306 --slave=root:root@127.0.0.1:3308 --rpl-user=repl:repl -vv
mysqlreplicate --master=root:root@127.0.0.1:3306 --slave=root:root@127.0.0.1:3309 --rpl-user=repl:repl -vv
执行日志如下：
$ mysqlreplicate --master=root:root@127.0.0.1:3306 --slave=root:root@127.0.0.1:3307 --rpl-user=repl:repl -vv
# master on 127.0.0.1: ... connected.
# slave on 127.0.0.1: ... connected.
# master id = 2
# slave id = 3
# master uuid = c23c028a-dc35-11e3-9ecf-fa53af763fd8
# slave uuid = c2350336-dc35-11e3-9ecf-c98b5f3b0a82
# Checking InnoDB statistics for type and version conflicts.
# Checking storage engines...
# Checking for binary logging on master...
# Setting up replication...
# Connecting slave to master...
# CHANGE MASTER TO MASTER_HOST = '127.0.0.1', MASTER_USER = 'repl', MASTER_PASSWORD = 'repl', MASTER_PORT = 3306, MASTER_AUTO_POSITION=1
# Starting slave from master's last position...
# IO status: Waiting for master to send event
# IO thread running: Yes
# IO error: None
# SQL thread running: Yes
# SQL error: None
# ...done.
查看复制结构
ruiayLinSunnydeMacBook-Pro-2:manages root# mysqlrplshow --master=root:root@localhost:3306 --discover-slaves-login=root:root -v
# master on localhost: ... connected.
# Finding slaves for master: localhost:3306
# Replication Topology Graph
localhost:3306 (MASTER)
|
+--- localhost:3307 [IO: Yes, SQL: Yes] - (SLAVE)
|
+--- localhost:3308 [IO: Yes, SQL: Yes] - (SLAVE)
|
+--- localhost:3309 [IO: Yes, SQL: Yes] - (SLAVE)
至此我们的环境已经搭建完成。
mysql utilities的几个常用命令
检查复制环境：
mysqlrplcheck --master=root:root@127.0.0.1:3306 --slave=root:root@127.0.0.1:3307 -s
—>:检查日志
ruiayLinSunny at ruiayLinSunnydeMacBook-Pro-2 in ~
$ mysqlrplcheck --master=root:root@127.0.0.1:3306 --slave=root:root@127.0.0.1:3307 -s
# master on 127.0.0.1: ... connected.
# slave on 127.0.0.1: ... connected.
Test Description Status
---------------------------------------------------------------------------
Checking for binary logging on master 下载密码：发表评论并刷新可见！
Are there binlog exceptions? 下载密码：发表评论并刷新可见！
Replication user exists? 下载密码：发表评论并刷新可见！
Checking server_id values 下载密码：发表评论并刷新可见！
Checking server_uuid values 下载密码：发表评论并刷新可见！
Is slave connected to master? 下载密码：发表评论并刷新可见！
Check master information file 下载密码：发表评论并刷新可见！
Checking InnoDB compatibility 下载密码：发表评论并刷新可见！
Checking storage engines compatibility 下载密码：发表评论并刷新可见！
Checking lower_case_table_names settings 下载密码：发表评论并刷新可见！
Checking slave delay (seconds behind master) 下载密码：发表评论并刷新可见！
检查复制健康状态：
mysqlrpladmin --master=root:root@127.0.0.1:3306 --slave=root:root@127.0.0.1:3307 health
—>: 检查日志
$ mysqlrpladmin --master=root:root@127.0.0.1:3306 --slave=root:root@127.0.0.1:3307 health
# Checking privileges.
#
# Replication Topology Health:
+------------+-------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+------------+-------+---------+--------+------------+---------+
| 127.0.0.1 | 3306 | MASTER | UP | ON | OK |
| 127.0.0.1 | 3307 | SLAVE | UP | ON | OK |
+------------+-------+---------+--------+------------+---------+
显示整体拓扑结构：
mysqlrplshow --master=root:root@127.0.0.1:3306 --discover-slaves-login=root:root -v
—>: 检查结果
ruiaylinydembp2:manages root# mysqlrplshow --master=root:root@localhost:3306 --discover-slaves-login=root:root
# master on localhost: ... connected.
# Finding slaves for master: localhost:3306
# Replication Topology Graph
localhost:3306 (MASTER)
|
+--- localhost:3307 - (SLAVE)
|
+--- localhost:3308 - (SLAVE)
|
+--- localhost:3309 - (SLAVE)
通过 mysqlrpladmin 来打开和关闭复制
mysqlrpladmin --master=root:root@127.0.0.1:3306 --slave=root:root@127.0.0.1:3308 start
mysqlrpladmin --master=root:root@127.0.0.1:3306 --slave=root:root@127.0.0.1:3308 stop
以上是几个比较常用的工具，每一个具体工具详细内容，请自行看文档，呵呵
自动和手动 failover
自动 failover
mysqlfailover --master=root:root@127.0.0.1:3306 --candidates=root:root@127.0.0.1:3307 --discover-slaves-login=root:root
执行这个命令之后，会在当前终端打开failover check的终端， like this ：
### mysqlfailover --master=root:root@127.0.0.1:3306 --candidates=root:root@127.0.0.1:3307 --discover-slaves-login=root:root
MySQL Replication Failover Utility
Failover Mode = auto Next Interval = Sat May 31 16:05:59 2014
Master Information
------------------
Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB
binlog.000009 191
GTID Executed Set
dd1f65aa-e2d0-11e3-89e3-7f8d49c2f173:1-14
Replication Health Status
+------------+-------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+------------+-------+---------+--------+------------+---------+
| 127.0.0.1 | 3306 | MASTER | UP | ON | OK |
| localhost | 3307 | SLAVE | UP | ON | OK |
| localhost | 3308 | SLAVE | UP | ON | OK |
| localhost | 3309 | SLAVE | UP | ON | OK |
+------------+-------+---------+--------+------------+---------+
Q-quit R-refresh H-health G-GTID Lists U-UUIDs
另外开一个终端
执行 ：
mysqladmin -uroot -proot shutdown
failover终端：
### mysqlfailover --master=root:root@127.0.0.1:3306 --candidates=root:root@127.0.0.1:3307 --discover-slaves-login=root:root
Master Information
------------------
Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB
binlog.000008 191
GTID Executed Set
dd1f65aa-e2d0-11e3-89e3-7f8d49c2f173:1-14
Master GTID Executed Set
+------------+-------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+------------+-------+---------+--------+------------+---------+
| 127.0.0.1 | 3306 | MASTER | UP | ON | OK |
| localhost | 3307 | SLAVE | UP | ON | OK |
| localhost | 3308 | SLAVE | UP | ON | OK |
| localhost | 3309 | SLAVE | UP | ON | OK |
+------------+-------+---------+--------+------------+---------+
Q-quit R-refresh H-health G-GTID Lists U-UUIDs
Failed to reconnect to the master after 3 attemps.
Failover starting in 'auto' mode...
# Candidate slave 127.0.0.1:3307 will become the new master.
# Checking slaves status (before failover).
# Preparing candidate for failover.
# Creating replication user if it does not exist.
# Stopping slaves.
# Performing STOP on all slaves.
# Switching slaves to new master.
# Disconnecting new master as slave.
# Starting slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Failover complete.
# Discovering slaves for master at 127.0.0.1:3307
Failover console will restart in 5 seconds.
Q-quit R-refresh H-health G-GTID Lists U-UUIDs
# Discovering slaves for master at 127.0.0.1:3307
MySQL Replication Failover Utility
Failover Mode = auto Next Interval = Sat May 31 15:01:00 2014
Master Information
------------------
Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB
binlog.000005 191
GTID Executed Set
dd1f65aa-e2d0-11e3-89e3-7f8d49c2f173:1-14
Master GTID Executed Set
+------------+-------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+------------+-------+---------+--------+------------+---------+
| 127.0.0.1 | 3307 | MASTER | UP | ON | OK |
| localhost | 3308 | SLAVE | UP | ON | OK |
| localhost | 3309 | SLAVE | UP | ON | OK |
+------------+-------+---------+--------+------------+---------+
master down 了之后，3307 实例接管成为了master
切换收尾：
启动 3306 实例，也就是老的master ； 调整复制链路 ，使 3306 成为新的slave；
mysqld_safe &
mysqlreplicate --master=root:root@127.0.0.1:3307 --slave=root:root@127.0.0.1:3306 --rpl-user=repl:repl -vv
手动 failover
此时我们的链路是如下样子：
GTID Executed Set
dd1f65aa-e2d0-11e3-89e3-7f8d49c2f173:1-14
Master GTID Executed Set
+------------+-------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+------------+-------+---------+--------+------------+---------+
| 127.0.0.1 | 3307 | MASTER | UP | ON | OK |
| localhost | 3306 | SLAVE | UP | ON | OK |
| localhost | 3308 | SLAVE | UP | ON | OK |
| localhost | 3309 | SLAVE | UP | ON | OK |
+------------+-------+---------+--------+------------+---------+
我们执行手动切换：
mysqlrpladmin --master=root:root@127.0.0.1:3307 --new-master=root:root@127.0.0.1:3306 --discover-slaves-login=root:root --demote-master switchover
—>：日志输出
ruiayLinSunnydeMacBook-Pro-2:~ root# mysqlrpladmin --master=root:root@127.0.0.1:3307 --new-master=root:root@127.0.0.1:3306 --discover-slaves-login=root:root --demote-master switchover
# Discovering slaves for master at 127.0.0.1:3307
# Discovering slave at localhost:3306
# Found slave: localhost:3306
# Discovering slave at localhost:3308
# Found slave: localhost:3308
# Discovering slave at localhost:3309
# Found slave: localhost:3309
# Checking privileges.
# WARNING: You may be mixing host names and IP addresses. This may result in negative status reporting if your DNS services do not support reverse name lookup.
# Performing switchover from master at 127.0.0.1:3307 to slave at 127.0.0.1:3306.
# Checking candidate slave prerequisites.
# Checking slaves configuration to master.
# Waiting for slaves to catch up to old master.
# Stopping slaves.
# Performing STOP on all slaves.
# Demoting old master to be a slave to the new master.
# Switching slaves to new master.
# Starting all slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Switchover complete.
#
# Replication Topology Health:
+------------+-------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+------------+-------+---------+--------+------------+---------+
| 127.0.0.1 | 3306 | MASTER | UP | ON | OK |
| 127.0.0.1 | 3307 | SLAVE | UP | ON | OK |
| localhost | 3308 | SLAVE | UP | ON | OK |
| localhost | 3309 | SLAVE | UP | ON | OK |
+------------+-------+---------+--------+------------+---------+
# ...done.
执行切换成功之后，重新check 链路：
ruiayLinSunnydeMacBook-Pro-2:~ root# mysqlrplshow --master=root:root@127.0.0.1:3306 --discover-slaves-login=root:root -v
# master on 127.0.0.1: ... connected.
# Finding slaves for master: 127.0.0.1:3306
# Replication Topology Graph
127.0.0.1:3306 (MASTER)
|
+--- localhost:3307 [IO: Yes, SQL: Yes] - (SLAVE)
|
+--- localhost:3308 [IO: Yes, SQL: Yes] - (SLAVE)
|
+--- localhost:3309 [IO: Yes, SQL: Yes] - (SLAVE)
手动切换完成 ， end
mysqlfailover 可以以Deamon的方式启动，还可以指定 –log=LOG_FILE 打印到日志文件中
总结
mysql新版本的功能真的很让人鼓舞兴奋，作为一个dba尤然，继续学习，继续进步，才是硬道理 ...
持续研究和学习 mysql 数据库，做一个好dba， 一个好数据架构师 ...
