• 生活的道路一旦选定,就要勇敢地走到底,决不回头。——左拉
  • 坚强的信心,能使平凡的人做出惊人的事业。——马尔顿
  • 人不可有傲气,但不可无傲骨。 --徐悲鸿
  • 古之立大志者,不惟有超世之才,亦必有坚韧不拔之志。 --苏轼
  • 时间像海绵里的水,只要你愿意挤,总还是有的。 --鲁迅

ansible配置MHA脚本漂移与恢复binlog server与MHA+Atlas读写分离

MySQL zkinogg 2年前 (2020-07-29) 223次浏览 0个评论

一.查看ansible配置目录信息

[root@m01 roles]# ll
total 8
-rw-r--r--  1 root root 201 Jul  9 09:56 hosts
drwxr-xr-x 10 root root 154 Jul  9 08:58 mysql
-rw-r--r--  1 root root  79 Jul  9 09:40 site.yml

[root@m01 mysql]# cd files/
[root@m01 files]# ll
total 398752
-rw-r--r-- 1 root root       457 Jul 30 02:16 app1.cnf      # MHA主配置文件
-rw-r--r-- 1 root root   4963681 Jul 28 17:24 Atlas-2.2.1.el6.x86_64.rpm  # Atlas的rpm包
-rw-r--r-- 1 root root       481 Jul 27 15:57 lj.sh    # 免密连接脚本
-rwxr-xr-x 1 root root      2181 Jul 30 02:41 master_ip_failover    #vip漂移脚本
-rw-r--r-- 1 root root     87119 Jul  1 18:08 mha4mysql-manager-0.56-0.el6.noarch.rpm  # mha的manager节点安装包
-rw-r--r-- 1 root root     36326 Jul  1 18:10 mha4mysql-node-0.56-0.el6.noarch.rpm # mha的node节点安装包
-rw-r--r-- 1 root root 403177622 Jul  9 08:59 mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz #mysql5.6.46 二进制包
-rw-r--r-- 1 root root       325 Jul  9 09:49 mysqld.service  # mysql的systemd启动脚本 
-rwxr-xr-x 1 root root     10565 Jul  9 09:17 mysql.server    # mysql的启动脚本 
-rw-r--r-- 1 root root        39 Jul  9 09:45 mysql.sh        # mysql的环境变量文件
-rw-r--r-- 1 root root       448 Jul 30 03:57 start_bin.sh    # 恢复MHA及备份binlog的脚本
-rw-r--r-- 1 root root       650 Jul 30 03:58 start_mha.sh    # 恢复MHA及主从的脚本 
-rw-r--r-- 1 root root       974 Jul 30 03:21 switch_Atlas.sh  # Atlas实时同步库状态的脚本
-rw-r--r-- 1 root root       402 Jul 30 03:10 test.cnf        #Atlas 配置文件



# 恢复mha脚本(node节点)
[root@m01 files]# cat start_mha.sh 
#!/bin/bash
#判断数据库是否挂掉
mysql_pid=`ps -ef | grep [m]ysqld | wc -l`

#如果挂掉则重启,如果没挂则杀掉重启
if [ $mysql_pid -eq 0 ];then
    systemctl start mysqld
else
    systemctl restart mysqld
fi

sleep 3

#获取change master to语句
change=`ssh 172.16.1.53 "grep 'CHANGE MASTER TO' /service/mha/manager | tail -1 | sed 's#xxx#123#g'" | awk -F: '{print $4}'` &&\

#重启的数据库执行change master to
mysql -uroot -p123 -e "$change;start slave"

#修复MHA配置文件
ssh 172.16.1.53 "\cp /service/mha/app1.cnf.bak /service/mha/app1.cnf" &&\

#远程启动MHA
ssh 172.16.1.53 "/bin/bash /root/start_bin.sh"



# 恢复mysqlbin和mha脚本 (manager节点)
[root@m01 files]# cat start_bin.sh 
#!/bin/bash
#获取主机地址
mha_master_host=`grep 'as a new master' /service/mha/manager | tail -1 | awk -F "[ ,(]" '{print $2}'`

#启动保存binlog
cd /root/binlog/

nohup mysqlbinlog -R --host="$mha_master_host" --user=mha --password=mha --raw --stop-never mysql-bin.000001 2>&1 &

#启动MHA
nohup masterha_manager --conf=/service/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /service/mha/manager.log 2>&1 &



# Atlas实时同步库状态的脚本
  [root@m01 files]# cat switch_Atlas.sh 
#!/bin/bash
#1.获取新的主库IP
new_master=`grep "as a new master" /service/mha/manager | tail -1 | awk -F '[ ,(]' '{print $2}'`
#2.获取新的主库在Atlas中的ID
new_master_id=`mysql -uuser -ppwd -h127.0.0.1 -P 2345 -e "SELECT * FROM backends" | grep $new_master | awk '{print $1}'`
#3.移除提升为主库的从库
mysql -uuser -ppwd -h127.0.0.1 -P 2345 -e "REMOVE BACKEND $new_master_id" &> /dev/null
#4.找到挂掉的主机
down_server=`grep "Master .* is down" /service/mha/manager | tail -1 | awk -F '[ ,(]' '{print $2}'`
#5.远程连接执行恢复脚本
ssh $down_server "sh /root/start_mha.sh"
#6.获取挂掉的主机的IP加端口
down_server_port=`grep "Master .* is down" /service/mha/manager | tail -1 | awk -F '[ ,()]' '{print $3}'`
#7.添加down的主库为新的从库到Atlas
mysql -uuser -ppwd -h127.0.0.1 -P 2345 -e "add slave $down_server_port" &> /dev/null
#8.保存配置
mysql -uuser -ppwd -h127.0.0.1 -P 2345 -e "save config" &> /dev/null

二.ansible配置二进制安装和主从复制

[root@m01 tasks]# cat Push_mysql.yml 
- name: yum rely on     # 安装依赖
  yum:
    name: "{{ item }}"
    state: present
  loop:
    - ncurses-devel
    - libaio-devel
    - autoconf
  when: ansible_fqdn is match 'db*'

- name: useradd mysql   # 创建mysql用户
  user:
    name: mysql
    shell: /sbin/nologin
    create_home: false
    state: present
  when: ansible_fqdn is match 'db*'

- name: Unarchive mysql tgz   # 解压mysql二进制包
  unarchive:
    src: mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz
    dest: /usr/local/
    owner: mysql
    group: mysql
  tags: unarchive
  when: ansible_fqdn is match 'db*'

- name: Link mysql        # 做软链接
  file:
    src: /usr/local/mysql-5.6.46-linux-glibc2.12-x86_64
    dest: /usr/local/mysql
    owner: mysql
    group: mysql
    state: link
  when: ansible_fqdn is match 'db*'

- name: mysql config overwrite    # 推mysql主配置文件
  template:
    src: my-default.cnf.j2
    dest: /etc/my.cnf
  when: ansible_fqdn is match 'db*'

- name: Push sh mysql server    # 推mysql启动脚本 
  copy: 
    src: mysql.server
    dest: /etc/init.d/mysqld
  when: ansible_fqdn is match 'db*'

- name: initialization mysql    # 初始化mysql
  shell: 'cd /usr/local/mysql/scripts/ && ./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --socket=/usr/local/mysql/mysql.sock'
  when: ansible_fqdn is match 'db*'

- name: Push mysql sh   # 推mysql环境变量文件
  copy: 
    src: mysql.sh
    dest: /etc/profile.d/mysql.sh
  when: ansible_fqdn is match 'db*'
 
- name: source       # 刷新环境变量
  shell: 'source /etc/profile'
  when: ansible_fqdn is match 'db*'

- name: Push mysqld service     # 推mysql的systemd启动脚本
  copy:
    src: mysqld.service
    dest: /usr/lib/systemd/system/mysqld.service
  when: ansible_fqdn is match 'db*'

- name: daemon reload      # 重启systemd配置文件
  shell: 'systemctl daemon-reload'
  when: ansible_fqdn is match 'db*'

- name: start mysqld     # 开启mysqld
  service:
    name: mysqld
    state: started
    enabled: true
  when: ansible_fqdn is match 'db*'

- name: Yum Phthon       # 安装mysql-python
  yum:
    name: MySQL-python
    state: present
  when: ansible_fqdn is match 'db*'

- name: Update Root User Password    # 设置root用户及密码
  mysql_user:
    name: root
    password: '123'
    login_unix_socket: /usr/local/mysql/mysql.sock
    update_password: always
    host: 'localhost'
    priv: '*.*:ALL,GRANT'
    state: present
  when: ansible_fqdn is match 'db*'

- name: Create rep User     # 创建主从用户并授权
  mysql_user:
    login_user: root
    login_password: '123'
    login_unix_socket: /usr/local/mysql/mysql.sock
    login_host: 'localhost'
    name: rep
    password: '123'
    host: '172.16.1.%'
    priv: '*.*:replication slave'
    state: present

- name: Get master     # 获取主库信息
  mysql_replication:
    mode: getmaster
    login_user: root
    login_password: '123'
    login_unix_socket: /usr/local/mysql/mysql.sock
    login_host: 'localhost'
  register: master_status


- name: check slave for replication      # 获取从库信息
  mysql_replication:
    login_user: root
    login_password: '123'
    login_unix_socket: /usr/local/mysql/mysql.sock
    mode: getslave
  ignore_errors: true
  register: slave
  when: ansible_fqdn is match 'db*'

- name: change master to slave     # 从库配置主从
  mysql_replication:
    mode: changemaster
    login_user: root
    login_password: '123'
    login_unix_socket: /usr/local/mysql/mysql.sock
    login_host: 'localhost'
    master_host: '172.16.1.51'
    master_user: rep
    master_password: '123'
    master_log_file: "{{ hostvars['db01']['master_status']['File'] }}"
    master_log_pos: "{{ hostvars['db01']['master_status']['Position'] }}"
  when: mysql_role is match 'slave*'

- name: start slave   # 开启主从
  mysql_replication:
    mode: startslave
    login_user: root
    login_password: '123'
    login_unix_socket: /usr/local/mysql/mysql.sock
    login_host: 'localhost'
  when: mysql_role is match 'slave*'

三.ansible配置MHA和VIP漂移

[root@m01 tasks]# cat Mha.yml 
- name: yum DBD mysql    # 安装依赖
  yum:
    name: perl-DBD-MySQL
    state: present

- name: yum manager packages    # 安装主节点依赖包
  yum:
    name: "{{ item }}"
    state: present
  loop:
    - perl-Config-Tiny
    - epel-release
    - perl-Log-Dispatch
    - perl-Parallel-ForkManager
    - perl-Time-HiRes
  when: ansible_fqdn == 'db03'

- name: Push node     # 推node节点包
  copy:
    src: mha4mysql-node-0.56-0.el6.noarch.rpm
    dest: /root/mha4mysql-node-0.56-0.el6.noarch.rpm

- name: yum node    # 安装node节点包
  yum:
    name: mha4mysql-node-0.56-0.el6.noarch.rpm
    state: present

- name: Push manager   # 推manager节点包
  copy:
    src: mha4mysql-manager-0.56-0.el6.noarch.rpm
    dest: /root/mha4mysql-manager-0.56-0.el6.noarch.rpm
  when: ansible_fqdn == 'db03'

- name: yum manager   # 安装manager节点包
  yum:
    name: mha4mysql-manager-0.56-0.el6.noarch.rpm
    state: present
  when: ansible_fqdn == 'db03'

- name: mkdir mha directory   #创建mha目录和mha备份binlog目录
  file:
    path: "{{ item }}"
    state: directory
  with_items:
    - /service/mha
    - /root/binlog
  when: ansible_fqdn == 'db03'

- name: Push mha config    #推mha配置文件
  copy:
    src: app1.cnf
    dest: /service/mha/app1.cnf
  when: ansible_fqdn == 'db03'

- name: Create mha User   #创建mha用户并授权
  mysql_user:
    login_user: root
    login_password: 123
    login_unix_socket: /usr/local/mysql/mysql.sock
    login_host: 'localhost'
    name: mha
    password: 'mha'
    host: '172.16.1.%'
    priv: '*.*:ALL'
  when: ansible_fqdn is match 'db01'

- name: Push lj sh    # 推免密连接脚本
  copy:
    src: lj.sh
    dest: /root/
    mode: 0755
  when: ansible_fqdn is match 'db*'

- name: sh lj sh     # 启动脚本 
  shell: '/bin/sh lj.sh'
  when: ansible_fqdn is match 'db*'

- name: Push failover sh   #推vip漂移脚本
  copy:
    src: master_ip_failover
    dest: /service/mha/master_ip_failover
    mode: 0755
  when: ansible_fqdn == 'db03'

- name: ifconfig eth1    #开启vip
  shell: 'ifconfig eth1:1 172.16.1.50/24'
  when: ansible_fqdn == 'db01'

- name: test ssh   #测试ssh
  shell: 'masterha_check_ssh --conf=/service/mha/app1.cnf'
  when: ansible_fqdn == 'db03'

- name: test master slave   #测试主从状态
  shell: 'masterha_check_repl --conf=/service/mha/app1.cnf'
  when: ansible_fqdn == 'db03'

- name: start mha  # 开启mha
  shell: 'nohup masterha_manager --conf=/service/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /service/mha/manager 2>&1 &'
  when: ansible_fqdn == 'db03'

- name: Push node sh   # 推node节点恢复mha脚本
  copy:
    src: start_mha.sh
    dest: /root/start_mha.sh
    mode: 0755
  when: ansible_fqdn is match 'db*'

- name: Push manager sh   # 推manager节点恢复mha脚本
  copy:
    src: start_bin.sh
    dest: /root/start_bin.sh
    mode: 0755
  when: ansible_fqdn == 'db03'

- name: Push Mha conf backup  # 推mha备份恢复的配置文件
  copy:
    src: app1.cnf
    dest: /service/mha/app1.cnf.bak
  when: ansible_fqdn == 'db03'

四.ansible配置Atlas与MHA恢复

[root@m01 tasks]# cat Atlas.yml 
- name: Push Atlas rpm   # 推Atlas 的rpm 包
  copy:
    src: Atlas-2.2.1.el6.x86_64.rpm
    dest: /root/Atlas-2.2.1.el6.x86_64.rpm
  when: ansible_fqdn == 'db03'

- name: Yum Atlas  #安装Atlas
  yum:
    name: /root/Atlas-2.2.1.el6.x86_64.rpm
    state: present
  when: ansible_fqdn == 'db03'

- name: Push Atlas config  #推Atlas配置文件
  copy:
    src: test.cnf
    dest: /usr/local/mysql-proxy/conf/test.cnf
  when: ansible_fqdn == 'db03'

- name: start Atlas  # 开启Atlas
  shell: '/usr/local/mysql-proxy/bin/mysql-proxyd test start'
  when: ansible_fqdn == 'db03'

- name: Push Atlas sh  #推Atlas实时同步库信息脚本
  copy:
    src: switch_Atlas.sh
    dest: /root/switch_Atlas.sh
  when: ansible_fqdn == 'db03'
喜欢 (0)
[17551054905]
分享 (0)

您必须 登录 才能发表评论!