a tool for automatic offline/online unusable slave node in Atlas open source software
此脚本配合360 Atlas中间件的使用， 检测slave状况(延迟或slavethread错误)，自动上线或下线存在于Atlas admin接口里的slave节点；
- 不对master做改动，仅检测slave信息；
- 支持多个slave, 详见 perldoc atlas_auto_setline说明;
- 多个atlas端口必须是同一实例下的;
- 新加循环检测, 默认每10s检测一次, 在上下线过程中忽略kill的INT和TERM两个信号;
需要安装的依赖:
DBI
DBD::mysql
Config::Auto
db.conf文件配置(单实例下的多个库)举例,:
#slave host and atlas admin host info.
slave_host:172.30.0.15,172.30.0.16 #多台slave以','分隔
slave_port:3306 #slave 服务端口
slave_user:slave_user #可以检测slave 延迟状态的用户
slave_pass:xxxxxx #slave_user口令
atlas_host:172.30.0.18 #atlas对外服务的ip, 建议是虚ip
atlas_port:5012 #atlas对外服务的管理端口, 一个atlas的mysql-proxyd占用一个端口, 如果起了多个, 以','分隔指定多个端口
atlas_user:admin #atlas的管理账户
atlas_pass:xxxxxxx #atlas管理账户的口令信息
mail:chenzhe07@gmail.com
atlas_port和atlas_user和atlas_pass三个参数应该指定atlas的管理端口和管理的用户信息， 用于读取 atlas 的后端状态backend.
可添加到任务计划循环检测, 如下:
#!/bin/bash
(
flock -x -n 200
if [[ $? -ne 0 ]]; then
echo "Failed acquiring lock"
exit 1
fi
perl atlas_auto_setline.pl --conf=db.conf --verbose --setline --interval=10 >>setline.log 2>&1
) 200>/web/scripts/atlas_auto/atlas.lock
mysql> select * from backends;
+-------------+-------------------+-------+------+
| backend_ndx | address | state | type |
+-------------+-------------------+-------+------+
| 1 | 172.30.0.14:3306 | up | rw |
| 2 | 172.30.0.14:3306 | up | ro |
| 3 | 172.30.0.15:3306 | up | ro |
| 4 | 172.30.0.16:3306 | up | ro |
+-------------+-------------------+-------+------+
4 rows in set (0.00 sec)
停止ip为16的slave的复制线程后(多个端口，多个offline操作):
# perl atlas_auto_setline.pl --conf=db.conf --verbose --setline --threshold=30
+---2014-04-15 11:53:01, 172.30.0.15, Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 13
+---2014-04-15 11:53:01, 172.30.0.16, Slave_IO_Running: No, Slave_SQL_Running: No, Seconds_Behind_Master: NULL
+-- 2014-04-15 11:53:01 OK SET offline node 172.30.0.16:3306
atlas下线:
mysql> select * from backends;
+-------------+-------------------+-------+------+
| backend_ndx | address | state | type |
+-------------+-------------------+-------+------+
| 1 | 172.30.0.14:3306 | up | rw |
| 2 | 172.30.0.14:3306 | up | ro |
| 3 | 172.30.0.15:3306 | up | ro |
| 4 | 172.30.0.16:3306 | offline| ro |
+-------------+-------------------+-------+------+
4 rows in set (0.00 sec)
# perl atlas_auto_setline.pl --conf=db.conf --verbose --setline --threshold=30
+---2014-04-15 11:54:01, 172.30.0.15, Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0
+---2014-04-15 11:54:01, 172.30.0.16, Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0
+-- 2014-04-15 11:54:01 OK SET online node 172.30.0.16:5012
mysql> set offline 4;
+-------------+------------------+---------+------+
| backend_ndx | address | state | type |
+-------------+------------------+---------+------+
| 3 | 172.30.0.16:3306 | offline | ro |
+-------------+------------------+---------+------+
1 row in set (0.00 sec)
mysql> select * from backends;
+-------------+-------------------+-------+------+
| backend_ndx | address | state | type |
+-------------+-------------------+-------+------+
| 1 | 172.30.0.14:3306 | up | rw |
| 2 | 172.30.0.14:3306 | up | ro |
| 3 | 172.30.0.15:3306 | up | ro |
| 4 | 172.30.0.16:3306 | offline| ro |
+-------------+-------------------+-------+------+
4 rows in set (0.00 sec)
# perl atlas_auto_setline.pl --conf=db.conf --verbose --setline --threshold=30
+---2014-04-15 11:56:01, 172.30.0.15, Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0
+---2014-04-15 11:56:01, 172.30.0.16, Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0
+-- 2014-04-15 11:56:01 OK SET online node 172.30.0.16:5012
上线成功:
# perl atlas_auto_setline.pl --conf=db.conf --verbose --setline --threshold=30 --interval=10
+---2014-09-22 16:22:42, 172.30.0.154, Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0
+---2014-09-22 16:22:42, 172.30.0.133, Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0
+---2014-09-22 16:22:52, 172.30.0.154, Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0
+---2014-09-22 16:22:52, 172.30.0.133, Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0
+---2014-09-22 16:23:02, 172.30.0.154, Slave_IO_Running: No, Slave_SQL_Running: No, Seconds_Behind_Master: NULL
+-- 2014-09-22 16:23:02 OK SET offline node 172.30.0.154:5012
+---2014-09-22 16:23:02, 172.30.0.133, Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0
+---2014-09-22 16:23:12, 172.30.0.154, Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0
+-- 2014-09-22 16:23:12 OK SET online node 172.30.0.154:5012
+---2014-09-22 16:23:12, 172.30.0.133, Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0
+---2014-09-22 16:23:22, 172.30.0.154, Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0
+---2014-09-22 16:23:22, 172.30.0.133, Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0
+---2014-09-22 16:23:32, 172.30.0.154, Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0
MySQL Server 版本为 5.6, 运行脚本的时候会有安全提示 Warning: Using a password on the command line interface can be insecure., 这个错误信息可以忽略, 不影响脚本的执行. perl DBI驱动不兼容atlas的管理端口, 只能通过 mysql -h 的方式调用取到结果, 5.6 的警告信息可以忽略, 也可以在my.cnf 配置里[client]设置密码信息, 然后脚本里去掉 -p$pass这部分就可以避免出现错误信息, 或使用 2>/dev/null 将错误重定向.
增加隐藏连接 MySQL 的密码信息功能, 参考文章 safe-bash-with-mysql