文章目录
  1. 1. MySQL自动failover介绍
    1. 1.1. 环境准备
      1. 1.1.1. 第一步安装mysql DB
      2. 1.1.2. 第二步 初始化mysql DB
      3. 1.1.3. 第三步构建复制结构
    2. 1.2. mysql utilities的几个常用命令
    3. 1.3. 自动和手动 failover
      1. 1.3.1. 自动 failover
      2. 1.3.2. 手动 failover
  2. 2. 总结

MySQL自动failover介绍

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                                [pass]
Are there binlog exceptions?                                         [pass]
Replication user exists?                                             [pass]
Checking server_id values                                            [pass]
Checking server_uuid values                                          [pass]
Is slave connected to master?                                        [pass]
Check master information file                                        [pass]
Checking InnoDB compatibility                                        [pass]
Checking storage engines compatibility                               [pass]
Checking lower_case_table_names settings                             [pass]
Checking slave delay (seconds behind master)                         [pass]

检查复制健康状态:

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, 一个好数据架构师 ...
文章目录
  1. 1. MySQL自动failover介绍
    1. 1.1. 环境准备
      1. 1.1.1. 第一步安装mysql DB
      2. 1.1.2. 第二步 初始化mysql DB
      3. 1.1.3. 第三步构建复制结构
    2. 1.2. mysql utilities的几个常用命令
    3. 1.3. 自动和手动 failover
      1. 1.3.1. 自动 failover
      2. 1.3.2. 手动 failover
  2. 2. 总结