Ansible自动化部署MySQL5.7.33主从复制

上一篇文章简单介绍了使用ansible自动化不是zk集群，衔接上文，本篇文章简单介绍使用ansible自动化部署MySQL主从复制实现过程。还是那句话：“工欲善其事，必先利其器”。

ansible自动化部署zookeeper: 博文链接

目录结构

liheng@liheng-ThinkPad:~/桌面$ tree playbook 
playbook
├── host
├── mysql.yml
├── roles
│   ├── mysql
│   │   ├── files
│   │   │   └── mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
│   │   ├── handlers
│   │   ├── tasks
│   │   │   ├── add_user.yml
│   │   │   ├── change_slave_to_master.yml
│   │   │   ├── install_mysql.yml
│   │   │   ├── main.yml
│   │   │   └── pre_install.yml
│   │   ├── templates
│   │   │   ├── my.cnf.j2
│   │   │   └── mysql.service.j2
│   │   └── vars
│   │       └── main.yml

部署文件

1、资产清单

liheng@liheng-ThinkPad:~/桌面/playbook$ cat host
[mysql]
172.17.252.57 master=true
172.17.252.58 slave=true
[mysql:vars]
master_ip=172.17.252.57
slave_ip=172.17.252.58

2、剧本入口

liheng@liheng-ThinkPad:~/桌面/playbook$ cat mysql.yml 
---
- hosts: mysql
  gather_facts: yes
  roles:
    - mysql

3、自定义变量

liheng@liheng-ThinkPad:~/桌面/playbook$ cat roles/mysql/vars/main.yml 
---
mysql_version: mysql-5.7.33-linux-glibc2.12-x86_64
mysql_install_path: /opt/data/mysql_data
mysql_link: mysql
mysql_sock: /tmp/mysql.sock
mysql_port: 33306
mysql_root_passwd: "Root_123^"
#master_ip: 172.17.252.1
#slave_ip: 172.17.252.2
repl_user: repl
repl_passwd: "Repl_123^"
user: mysql
group: mysql

4、配置摸版文件

liheng@liheng-ThinkPad:~/桌面/playbook$ cat roles/mysql/templates/my.cnf.j2 
[client]
port = {{ mysql_port }}
socket = {{ mysql_sock }}
default-character-set=utf8mb4

[mysqldump]
single-transaction

[mysqld]
port = {{ mysql_port }}
socket = {{ mysql_sock }}
character-set-server=utf8mb4
#skip-grant-tables
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
log_bin_trust_function_creators=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

gtid-mode = on
enforce_gtid_consistency
log-bin = on
log-slave-updates = on

#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=1000
#rpl_semi_sync_slave_enabled=1


master_info_repository = TABLE
relay_log_info_repository = TABLE


replicate-ignore-table=mysql.failover_console

datadir={{ mysql_install_path }}/{{ mysql_link }}/data

{% if master is defined %}
server-id=1                              
{% else %}
server-id=2
{% endif %}

read-only=0
#relay_log_purge=0
log_timestamps=SYSTEM
lower_case_table_names=1
log_slave_updates=on

skip-name-resolve
#skip-networking
back_log = 600

slave_parallel_workers = 16
slave-parallel-type = LOGICAL_CLOCK
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON
slave_preserve_commit_order = 1

innodb_undo_directory={{ mysql_install_path }}/{{ mysql_link }}/undolog
innodb_undo_tablespaces=4
innodb_undo_logs=128
innodb_max_undo_log_size=1024M
innodb_purge_rseg_truncate_frequency
innodb_undo_log_truncate=1

max_connections = 4000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 4096
table_open_cache_instances = 64
max_allowed_packet = 128M
binlog_cache_size = 32M
max_heap_table_size = 128M
tmp_table_size = 32M
read_buffer_size = 8M  
read_rnd_buffer_size = 8M  
sort_buffer_size = 8M  
join_buffer_size = 8M  
key_buffer_size = 8M  
thread_cache_size = 64
query_cache_type = 0
query_cache_size = 0
#query_cache_size = 16M  
#query_cache_limit = 8M
ft_min_word_len = 4
log_bin = mysql-bin
binlog_format = row
expire_logs_days = 15
log_error ={{ mysql_install_path }}/{{ mysql_link }}/error.log
slow_query_log = 1
long_query_time = 3
performance_schema = 0
explicit_defaults_for_timestamp
#lower_case_table_names = 1
skip-external-locking
default_storage_engine = InnoDB
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_stats_persistent_sample_pages = 64
innodb_open_files = 10000
innodb_buffer_pool_size = 90G
innodb_write_io_threads = 24
innodb_read_io_threads = 24
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_log_buffer_size = 64M
innodb_sort_buffer_size = 64M
innodb_log_file_size = 1024M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 75
innodb_lock_wait_timeout = 120
log_warnings=1
#local-infile=0
#connection-control-failed-connections-threshold=10
#connection-control-min-connection-delay=10800
wait_timeout = 3600
interactive_timeout = 3600
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:5G

以上MySQL配置文件，可根据业务场景及服务器硬件配置灵活调整

5、MySQL服务脚本

liheng@liheng-ThinkPad:~/桌面/playbook$ cat roles/mysql/templates/mysql.service.j2 
#!/bin/sh
# Copyright Abandoned 1996 TCX DataKonsult AB & Monty Program KB & Detron HB
# This file is public domain and comes with NO WARRANTY of any kind

# MySQL daemon start/stop script.

# Usually this is put in /etc/init.d (at least on machines SYSV R4 based
# systems) and linked to /etc/rc3.d/S99mysql and /etc/rc0.d/K01mysql.
# When this is done the mysql server will be started when the machine is
# started and shut down when the systems goes down.

# Comments to support chkconfig on RedHat Linux
# chkconfig: 2345 64 36
# description: A very fast and reliable SQL database engine.

# Comments to support LSB init script conventions
### BEGIN INIT INFO
# Provides: mysql
# Required-Start: $local_fs $network $remote_fs
# Should-Start: ypbind nscd ldap ntpd xntpd
# Required-Stop: $local_fs $network $remote_fs
# Default-Start:  2 3 4 5
# Default-Stop: 0 1 6
# Short-Description: start and stop MySQL
# Description: MySQL is a very fast and reliable SQL database engine.
### END INIT INFO
 
# If you install MySQL on some other places than /usr/local/mysql, then you
# have to do one of the following things for this script to work:
#
# - Run this script from within the MySQL installation directory
# - Create a /etc/my.cnf file with the following information:
#   [mysqld]
#   basedir=<path-to-mysql-installation-directory>
# - Add the above to any other configuration file (for example ~/.my.ini)
#   and copy my_print_defaults to /usr/bin
# - Add the path to the mysql-installation-directory to the basedir variable
#   below.
#
# If you want to affect other MySQL variables, you should make your changes
# in the /etc/my.cnf, ~/.my.cnf or other MySQL configuration files.

# If you change base dir, you must also change datadir. These may get
# overwritten by settings in the MySQL configuration files.

basedir={{ mysql_install_path }}/{{ mysql_link }}
datadir={{ mysql_install_path }}/{{ mysql_link }}/data

...
...

以上配置为MySQL启动脚本，只需配置好basedir(数据库部署目录)和datadir(数据库数据目录)，其余配置保持默认即可。

6、部署主程序入口

liheng@liheng-ThinkPad:~/桌面/playbook$ cat roles/mysql/tasks/main.yml 
---
 - import_tasks: add_user.yml
 - import_tasks: install_mysql.yml
 - import_tasks: change_slave_to_master.yml

剧本会按照主程序中定义的任务(task)，自上而下执行。

7、剧本任务

liheng@liheng-ThinkPad:~/桌面/playbook$ cat roles/mysql/tasks/add_user.yml 
---
- name: add_mysql_user
  user:
    name: "{{ user }}"
    shell: /bin/bash
  tags:
    - add_mysql_user            
liheng@liheng-ThinkPad:~/桌面/playbook$ cat roles/mysql/tasks/install_mysql.yml 
---
- name: create workdir
  file:
    path: "{{ mysql_install_path }}"
    state: directory
    owner: "{{ user }}"
    group: "{{ group }}"
    recurse: yes

- name: copy_mysql_tar
  copy:
    src: "{{ mysql_version }}.tar.gz"
    dest: "{{ mysql_install_path }}"
    owner: "{{ user }}"
    group: "{{ group }}"
  tags:
    - copy_mysql_tar

- name:  unarchive_mysql_tar
  unarchive:
    src:  "{{ mysql_install_path }}/{{ mysql_version }}.tar.gz"
    dest: "{{ mysql_install_path }}"
    copy: no
    owner: "{{ user }}"
    group: "{{ group }}"
  tags:
    - unarchive_mysql_tar

- name: chown_mysql
  file: 
    dest: "{{ mysql_install_path }}/{{mysql_version}}"
    owner: "{{ user }}"
    group: "{{ group }}"
    recurse: yes
  tags:
    - chown_mysql

- name: link_mysql
  file:
    src: "{{ mysql_install_path }}/{{mysql_version}}"
    dest: "{{ mysql_install_path }}/{{ mysql_link }}"
    owner: "{{ user }}"
    group: "{{ group }}"
    state: link
  tags:
    - link_mysql

- name: create undologdir
  file:
    path: "{{ mysql_install_path }}/{{ mysql_link }}/undolog"
    state: directory
    owner: "{{ user }}"
    group: "{{ group }}"

- name: create datadir
  file:
    path: "{{ mysql_install_path }}/{{ mysql_link }}/data"
    state: directory
    owner: "{{ user }}"
    group: "{{ group }}"

- name: copy_my.cnf
  template:
    src: my.cnf.j2
    dest: /etc/my.cnf
  tags:
    - copy_my.cnf

- name: init_mysql
  shell: ./bin/mysqld --initialize  --user=mysql
  args:
    chdir: "{{ mysql_install_path }}/{{ mysql_link }}"
  tags: 
    - init_mysql
    
- name: get_mysql_passwd
  shell: cat ./error.log |grep localhost|grep "temporary password"|awk '{print $NF}'
  register: mysql_init_passwd
  args:  
    chdir: "{{ mysql_install_path }}/{{ mysql_link }}"
  tags: 
    - get_mysql_passwd

- name: dispaly_passwd
  debug: 
    msg: "{{ mysql_init_passwd.stdout }}"
  tags:
    - dispaly_passwd

- name: copy_mysql.server
  template: 
    src: mysql.service.j2
    dest: "/etc/init.d/mysql.server"
    mode: 0755
  tags:
     - copy_mysql.server

- name: add_mysql_systemd
  template:
    src: mysql.service.j2
    dest: /etc/systemd/system/mysql-{{ mysql_port }}.service
  tags:
    - add_mysql_systemd

- name: start_mysql_service
  command: /etc/init.d/mysql.server start
  tags:
    - start_mysql_service

- name: alter_passwd
  shell: ./bin/mysqladmin -u root -p'{{mysql_init_passwd.stdout}}' password '{{ mysql_root_passwd }}'
  args:
    chdir: "{{ mysql_install_path }}/{{ mysql_link }}"
  tags:
    - alter_passwd
          
liheng@liheng-ThinkPad:~/桌面/playbook$ cat roles/mysql/tasks/change_slave_to_master.yml 
---
- name: create_user
  mysql_user: 
    login_host: localhost
    login_port: "{{ mysql_port }}"
    login_user: root
    login_unix_socket: "{{ mysql_sock }}" 
    login_password: "{{ mysql_root_passwd }}"
    name: "{{ repl_user }}"
    password: "{{ repl_passwd }}"
    priv: "*.*:ALL"
    state: present 
    host: "%"
  when: master is defined
  tags:
    - create_user

- name: change_slave_to_master
  mysql_replication:
    login_unix_socket: "{{ mysql_sock }}"
    login_host: localhost
    login_port: "{{ mysql_port }}"
    login_user: root     
    login_password: "{{ mysql_root_passwd }}"
    master_host: "{{ master_ip }}" 
    master_user: "{{ repl_user }}" 
    master_password: "{{ repl_passwd }}"
    master_port: "{{ mysql_port }}"
    master_auto_position: 1
    mode: changemaster
  when: slave is defined
  tags:
    - change_slave_to_master 

- name: start_slave
  mysql_replication: 
    login_unix_socket: "{{ mysql_sock }}"
    login_user: root 
    login_host: localhost
    login_port: "{{ mysql_port }}"
    login_password: "{{ mysql_root_passwd }}"
    mode: startslave
  when: slave is defined
  tags: 
    - start_slave

- name: get_slave_info
  mysql_replication:
    login_host: localhost
    login_user: root
    login_port: "{{ mysql_port }}"
    login_password: "{{ mysql_root_passwd }}"
    login_unix_socket: "{{ mysql_sock }}"
    mode: getslave
  when: slave is defined
  register: info
  tags:
    - get_slave_info

- name: dispaly_slave
  debug:
    msg: "Slave_IO_Running={{ info.Slave_IO_Running }}       Slave_SQL_Running={{ info.Slave_SQL_Running }}"
  when: slave is defined
  tags:
    - dispaly_slave

- name: clean_pkgs
  file:
    path: '/opt/{{ mysql_version }}.tar.gz'
    state: absent

以上通过ansible部署MySQL主从复制所用到的模块，可以通过'ansible-doc 模块名'查看其具体含义和使用方法。

部署

现在已经准备好部署MySQL主从复制所需的所有清单及mysql5.7.33的二进制文件，只需要通过一条命令即可完成MySQL主从复制的部署工作：

liheng@liheng-ThinkPad:~/桌面/playbook$ ansible-playbook -i host mysql.yml

其实实现MySQL主从自动化部署的方式有很多种，以上只是其中的一种实现。

一些思考

• 如何实现一主多从？
• 如何实现主主复制？

实际要实现以上需求也是非常容易的，只需要在以上配置清单中稍作调整即可实现，这里就不展开说明了。有兴趣的同学，可以自行研究，也可留言交流。

如果文章对您有帮助，还想了解更过关于k8s相关的实战经验，请关注“IT运维图谱”公众号或着通过微信搜一搜关注公众号。