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

风的影子007
1,495 阅读2分钟

上一篇文章简单介绍了使用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主从自动化部署的方式有很多种，以上只是其中的一种实现。

一些思考

  • 如何实现一主多从？

  • 如何实现主主复制？

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

avatar
文章
阅读
粉丝