使用ansible-playbook自动化安装MySQL主从
【使用自动化安装MySQL主从架构】
说明：使用ansible-playbook 自动化安装 MySQL主从 + mysqld-exporter的采集数据 + xtrabackup备份
【剧本说明】
以下文件在roles目录下
tree roles/mysql_ms/
mysql_ms/
├── files
│ ├── mysql.service MySQL服务
│ └── mysql_slave_config.sh 创建主从脚本
├── tasks
│ ├── install_backup.yml 安装备份的剧本
│ ├── install_exporter.yml 安装监控采集的剧本
│ └── main.yml 主剧本
├── templates
│ ├── my.cnf MySQL的配置文件，可以根据自己实际情况修改配置
│ ├── mysqld-exporter.service 监控采集的服务配置
│ ├── mysql_fullbackup_xtrabackup.sh 备份的脚本
│ ├── readme.md
└── vars
└── main.yml 全局的环境变量
【对应目录创建脚本】
files目录
vim mysql.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 65536
Environment=MYSQLD_PARENT_PID=1
vim mysql_slave_config.sh
#!/bin/bash
REPLUSER=$1
REPLPASS=$2
MYSQL_MASTER=$3
MYSQL_PASS=$4
MYSQLBIN=$(/usr/local/mysql/bin/mysql -u ${REPLUSER} -p${REPLPASS} -h${MYSQL_MASTER} -e "show master status\G;"|egrep 'File: .*+'|awk '{print $2}')
BINPOSITION=$(/usr/local/mysql/bin/mysql -uroot -p${MYSQL_PASS} -e "show master status\G;"|egrep 'Position: .*+'|awk '{print $2}')
/usr/local/mysql/bin/mysql -uroot -p${MYSQL_PASS} -e "CHANGE MASTER TO MASTER_HOST='${MYSQL_MASTER}', MASTER_USER='${REPLUSER}', MASTER_PASSWORD='${REPLPASS}', MASTER_LOG_FILE='${MYSQLBIN}', MASTER_LOG_POS=${BINPOSITION};start slave;select sleep(10);show slave status\G;"
task目录
vim install_backup.yml --- - name: Download mysqld xtrabackup soft copy: src={{download_target}}/{{mysqlxtrabackup_binary}} dest=/tmp/{{mysqlxtrabackup_binary}} mode=0755 - name: tar xtrabackup binary command: tar -zxvf /tmp/percona-xtrabackup-2.4.12-Linux-x86_64.libgcrypt145.tar.gz -C /usr/local/ - name: ln xtrabackup binary command: ln -sf /usr/local/percona-xtrabackup-2.4.12-Linux-x86_64/bin/xtrabackup /usr/bin/ - name: Download mysqld qpress soft copy: src={{download_target}}/{{mysqlqpress_binary}} dest=/tmp/{{mysqlqpress_binary}} mode=0755 - name: install qpress binary command: tar -xf /tmp/qpress-11-linux-x64.tar -C /usr/local/bin - name: create data directory file: path: '{{backup_dir}}/{{item.0}}/{{item.1}}' state: directory owner: root group: root recurse: yes with_nested: - ['backup'] - ['scripts', 'logs', 'innobackupex'] when: cluster_role == "master" - name: Add mysqld backup keyfile scripts shell: echo -n GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs > '{{backup_dir}}/backup/scripts/keyfile' when: cluster_role == "master" - name: Add mysqld backup shell scripts template: dest: '{{backup_dir}}/backup/scripts/mysql_fullbackup_xtrabackup.sh' src: mysql_fullbackup_xtrabackup.sh mode: 755 when: cluster_role == "master" - name: Mysql xtrabackup ansible.builtin.cron: name: "Mysql xtrabackup" minute: "0" hour: "2" job: "{{backup_dir}}/backup/scripts/mysql_fullbackup_xtrabackup.sh > /dev/null 2>&1 &" disabled: false when: cluster_role == "master"
vim install_exporter.yml
---
- name: Download mysqld Exporter
copy: src={{download_target}}/{{exporter_binary}} dest=/usr/local/bin/{{exporter_binary}} mode=0755
- name: Add mysqld exporter system server
template:
dest: /etc/systemd/system/mysqld-exporter.service
src: mysqld-exporter.service
- name: Ensure mysqld exporter is enabled
systemd:
daemon_reload: yes
name: mysqld-exporter
enabled: yes
- name: Start mysqld exporter
service:
name: mysqld-exporter
state: restarted
enabled: yes
vim main.yml
---
- name: Gathering info
action: setup
- name: create mysql user and group
user:
name: mysql
shell: /sbin/nologin
- name: download installation media
copy:
src: "{{download_target}}/{{mysql_tgz}}"
dest: /tmp/mysql.tar.gz
- name: install binary
command: tar -xzf /tmp/mysql.tar.gz -C /usr/local/
- template:
src: my.cnf
dest: /etc/my.cnf
owner: mysql
group: mysql
- file:
src: /usr/local/{{tgz_extracted}}
dest: /usr/local/mysql
owner: mysql
group: mysql
state: link
- file:
path: /data/mysql
state: directory
owner: mysql
group: mysql
recurse: True
- file:
path: /var/run/mysqld/
owner: mysql
group: mysql
state: directory
recurse: True
- file:
path: /var/log/mysql
owner: mysql
group: mysql
state: directory
recurse: True
- file:
path: /etc/my.cnf
owner: mysql
group: mysql
state: touch
- file:
path: /usr/local/{{tgz_extracted}}
owner: mysql
group: mysql
state: directory
recurse: True
- name: mysql initialization and reset password
copy:
src: mysql.service
dest: /etc/systemd/system/mysql.service
- command: /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql
- command: systemctl daemon-reload
- service:
name: mysql
state: started
enabled: yes
- command: sleep 10s
- command: /usr/local/mysql/bin/mysqladmin -uroot password {{mysql_pass}}
- name: repl configuration on master side
command: /usr/local/mysql/bin/mysql -uroot -p{{mysql_pass}} -e "CREATE USER {{repl_user}}@'%' IDENTIFIED BY '{{repl_pass}}';GRANT REPLICATION SLAVE ON *.* TO {{repl_user}}@'%';GRANT REPLICATION CLIENT ON *.* TO {{repl_user}}@'%';flush privileges;"
when: cluster_role == "master"
- name: repl configuration on slave side
copy:
src: mysql_slave_config.sh
dest: /tmp/mysql_slave_config.sh
mode: 755
when: cluster_role == "slave"
- command: /tmp/mysql_slave_config.sh {{repl_user}} {{repl_pass}} {{groups['mysql_ms'][0]}} {{mysql_pass}}
when: cluster_role == "slave"
- name: repl configuration read_only
command: /usr/local/mysql/bin/mysql -uroot -p{{mysql_pass}} -e "set global read_only=on;"
when: cluster_role == "slave"
- name: install Exporter
include: install_exporter.yml
tags:
- mysqld_exporter
- name: install backup files
include: install_backup.yml
tags:
- mysqld_backup
templates目录
vim my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[client]
port = 3306
socket = /data/mysql/mysql.sock
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
pid-file=/var/run/mysqld/mysqld.pid
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
character_set_server=utf8
gtid_mode = ON
enforce-gtid-consistency = ON
lower_case_table_names = 1
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 = ON
log_timestamps = SYSTEM
#####################innodata engine########################
innodata_buffer_pool_size = {{ (ansible_memtotal_mb * 0.6 // 128 ) | int * 128 }}M
innodata_buffer_pool_instances = 4
innodata_log_file_size = 2G
max_connections = 3000
#####################bin log##############################
server_id = {{server_id}}
log-bin = mysqlbin
expire_logs_days = 7
max_binlog_size = 1024M
binlog_format = row
sync_binlog = 1
max_allowed_packet = 1024M
log_slave_updates = on
#####################replication conf##############################
master_info_repository =table
relay_log_info_repository =table
slave_parallel_type =logical_clock
slave_parallel_workers =4
rpl_semi_sync_master_enabled =1
rpl_semi_sync_slave_enabled =1
rpl_semi_sync_master_timeout =5000
plugin_load_add =semisync_master.so
plugin_load_add =semisync_slave.so
binlog_group_commit_sync_delay =500
binlog_group_commit_sync_no_delay_count = 13
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64
#####################readonly conf##############################
#read_only = on
#super_read_only=on
#####################slow log##############################
slow_query_log = ON
long_query_time = 2
slow_query_log_file = /data/mysql/mysql01_slow.log
#####################error log##############################
log-error = /var/log/mysql/mysqld.log
explicit_defaults_for_timestamp = OFF
[mysql]
auto-rehash
prompt = "\u@\h:3306(production)\\d \\R:\\m:\\s>"
default-character-set = utf8
vim mysqld-exporter.service
[Unit]
Description=https://prometheus_exporter.io
[Service]
Environment=DATA_SOURCE_NAME=monitor:monitor@(localhost:3306)/
ExecStart=/usr/local/bin/mysqld-exporter --web.listen-address=0.0.0.0:9104
Restart=on-failure
[Install]
WantedBy=multi-user.target
var 目录
vim main.yml
---
mysql_tgz: mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz
tgz_extracted: mysql-5.7.36-linux-glibc2.12-x86_64
mysql_pass: Root_1234
repl_user: repl
repl_pass: Repl_1234
download_target: /tmp/soft
exporter_binary: mysqld-exporter
mysqlxtrabackup_binary: percona-xtrabackup-2.4.12-Linux-x86_64.libgcrypt145.tar.gz
mysqlqpress_binary: qpress-11-linux-x64.tar
backup_dir: /backup
【安装包及配置】
环境变量说明,这里默认数据目录为/data：可以直接修改my.cnf修改目录，后续使用环境变量替换 sed -i 's/\data/\你的目录名称/g' templates/my.cnf sed -i 's/\db/\data/g' tasks/main.yml
cat var/main.yml
---
mysql_tgz: mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz
tgz_extracted: mysql-5.7.36-linux-glibc2.12-x86_64
mysql_pass: Root_1234
repl_user: repl
repl_pass: Repl_1234
download_target: /tmp/soft
exporter_binary: mysqld-exporter
mysqlxtrabackup_binary: percona-xtrabackup-2.4.12-Linux-x86_64.libgcrypt145.tar.gz
mysqlqpress_binary: qpress-11-linux-x64.tar
backup_dir: /backup
将安装包放在download_target目录下，以上变量可以自己定义：
mysql，mysqld-exporter，percona-xtrabackup，qpress
创建ansible的hosts文件,前面换成你的IP,root用户root密码，cluster_role后面定义了master节点,server_id不重复即可
vim /etc/ansible/hosts
[mysql_ms]
IPXX1 ansible_user=root ansible_ssh_pass=xxx cluster_role=master server_id=90
IPXX2 ansible_user=root ansible_ssh_pass=xxx cluster_role=slave server_id=91
IPXX3 ansible_user=root ansible_ssh_pass=xxx cluster_role=slave server_id=92
创建playbook文件
vim mysql_ms.yaml
---
- hosts: mysql_ms
gather_facts: no
roles:
- mysql_ms
然后执行自动化安装,等待安装完成即可
ansible-playbook mysql_ms.yaml
【检查】
查看数据库服务：systemctl status mysql
查看监控服务：systemctl status mysqld-exporter
查看备份信息：crontab -l
