运维首页 > 数据运维 > mysql 自动部署 for ansible

mysql 自动部署 for ansible

2023年 9月 2日 44.1k 0

刚学ansible不久，想着可以利用ansible自动部署mysql，提高部署效率。本文主要介绍怎么通过 ansible 自动部署 mysql。不介绍 ansible 中语法作用，只是贴出 ansible 的脚本及部署过程，直接上干货。 

什么是 Ansible

Ansible 是一个 IT 自动化的 “配置管理工具”，自动化主要体现在 Ansible 集成了丰富模块，以及强大的功能组件，可以通过一个命令行完成一系列的操作。进而能减少我们重复性的工作，以提高工作的效率。

Ansible 主要功能

  • 批量执行远程命令，可以对N多台主机同时进行命令的执行。

  • 批量配置软件服务，可以进行自动化的方式配置和管理服务。

  • 实现软件开发功能，jumpserver 底层使用 ansible 来实现的自动化管理。

  • 编排高级的 IT 任务， Ansible 的 Playbook 是一门编程语言，可以用来描绘一套 IT 架构。

Ansbile 安装与配置

Ansible 安装方式

rpm 安装

[root@manager ~]# wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
[root@manager ~]# yum install ansible -y

pip 安装

[root@manager ~]# yum install python3 python3-devel python3-pip -y
[root@manager ~]# pip3 install --upgrade pip -i https://pypi.douban.com/simple/
[root@manager ~]# pip3 install ansible -i https://pypi.douban.com/simple/
[root@manager ~]# /usr/local/bin/ansible --version

离线安装

- 上传离线安装包
- cd 离线安装包目录
- yum install -y *

配置

配置文件

位置

[root@tydb010 ~]# rpm -qc ansible
/etc/ansible/ansible.cfg # ansible 配置文件
/etc/ansible/hosts # 主机配置文件

ansible.cfg 配置文件解析

#inventory = /etc/ansible/hosts # 主机配置文件
#library = /usr/share/my_modules/
#module_utils = /usr/share/my_module_utils/
#remote_tmp = ~/.ansible/tmp
#local_tmp = ~/.ansible/tmp
#plugin_filters_cfg = /etc/ansible/plugin_filters.yml
#forks = 5 # 调整并行的主机数量
#host_key_checking = False
#roles_path = /etc/ansible/roles

配置文件的优先级

# nearly all parameters can be overridden in ansible-playbook
# or with command line flags. ansible will read ANSIBLE_CONFIG，
# ansible.cfg in the current working directory， .ansible.cfg in
# the home directory or /etc/ansible/ansible.cfg， whichever it
# finds first

第一步读取：ANSIBLE_CONFIG 变量
第二步读取：当前项目目录下的 ansible.cfg
第三步读取：当前用户家目录下的 .ansible.cfg
第四步读取：/etc/ansible/ansible.cfg

测试环境

os：redhat 7.9
mysql version：5.7

使用方法

1. 通过 ansbile 服务器执行安装脚本
2. ansible 脚本说明：
　　　　a.new_auto_mysql.yml # 新服务器安装
　　　　b.add_mysql_instance.yml # 旧服务器新增实例
　　　　c.clear_mysql_by_port.yml # 清理指定实例
# 以上脚本使用的参数均设置在 /etc/ansible/hosts 文件中

编辑hosts

[root@tydb010 ansible_install_mysql]# cat /etc/ansible/hosts
[dblist]
192.168.10.22 ansible_ssh_port=22 ansible_ssh_user=root ansible_ssh_pass='oracle'
#192.168.10.23 ansible_ssh_port=22 ansible_ssh_user=root ansible_ssh_pass='oracle'
#192.168.10.24 ansible_ssh_port=22 ansible_ssh_user=root ansible_ssh_pass='oracle'

[dblist:vars]
port=3307
soft_path=/root/soft
version=mysql-5.7.35-el7-x86_64
my_cnf=my.cnf
page_size=16384
character_set=utf8mb4

new_auto_mysql.yml

[root@tydb010 ansible_install_mysql]# cat new_auto_mysql.yml
- hosts: dblist
tasks:

- name: if directory exist
shell: ls /data/"{{ port }}"
ignore_errors: True
register: result

- name: if directory exist 2
fail:
msg: " /data/{{ port }} diretory exist! "
when: result.failed == false

- name: Install Package
yum:
name:
#- perl-IO
- perl-Digest
- perl
- perl-devel
- perl-Time-HiRes
- perl-DBI
- perl-DBD-MySQL
- perl-IO-Socket-SSL
- perl-TermReadKey
#- perl-perl-Digest-MD5
- sysstat
- net-tools
state: present

- name: add soft nproc
pam_limits:
domain: mysql
limit_type: soft
limit_item: nproc
value: 16384

- name: add hard nproc
pam_limits:
domain: mysql
limit_type: hard
limit_item: nproc
value: 16384

- name: add soft nofile
pam_limits:
domain: mysql
limit_type: soft
limit_item: nofile
value: 65536

- name: add hard nofile
pam_limits:
domain: mysql
limit_type: hard
limit_item: nofile
value: 65536

- name: add soft memlock
pam_limits:
domain: mysql
limit_type: soft
limit_item: memlock
value: unlimited

- name: add edit hard memlock
pam_limits:
domain: mysql
limit_type: hard
limit_item: memlock
value: unlimited

- name: add soft stack
pam_limits:
domain: mysql
limit_type: soft
limit_item: stack
value: 32768

- name: add hard stack
pam_limits:
domain: mysql
limit_type: hard
limit_item: stack
value: 32768

- name: Disable SELinux
selinux:
state: disabled

- name: stop firewalld and disable
systemd:
name: firewalld
enabled: no
state: stopped

- name: add mysql group
group:
name: mysql
state: present

- name: add mysql user
user:
name: mysql
shell: /bin/bash
group: mysql
state: present
password: $6$salt$FWu89.xN80SeC7uXhlrGbRpOgEWgXRlBz6jFDhLo0eLyQWDnQ1EptqvNGyx3N0aUcV7rbuLgGBuGxZWUy.yeo0
create_home: yes

#- name: if directory exist
# shell: ls /data/"{{ port }}"
# ignore_errors: True
# register: result

#- name: if directory exist 2
# fail:
# msg: " /data/{{ port }} diretory exist! "
# when: result.failed == false

- name: create directory
shell: mkdir /data/"{{ port }}"/{data,logs,tmp} -p

- name: create directory1
shell: mkdir /data/"{{ port }}"/logs/{error,slow,redo,undo,binlog,relay} -p

#- name: cp mysql software
# copy:
# src: /root/soft/mysql-5.7.35-el7-x86_64.tar.gz
# dest: /usr/local/
# owner: root
# group: root
# backup: yes

- name: cp my.cnf
copy:
#src: /root/soft/my.cnf
src: "{{ soft_path }}/{{ my_cnf }}"
dest: "/data/{{ port }}/"
owner: root
group: root
backup: yes

#- name: set buffer_pool_size
# lineinfile:
# path: "/data/{{ port }}/my.cnf"
# regexp: '^innodb_buffer_pool_size'
# line: 'innodb_buffer_pool_size = {{ (ansible_memtotal_mb * 0.65 / 1024) | int }}GB'

- name: unzip mysql software
unarchive:
#src: /root/soft/mysql-5.7.35-el7-x86_64.tar.gz
src: "{{ soft_path }}/{{ version }}.tar.gz"
dest: /usr/local/
remote_src: no

- name: link
file:
#src: /usr/local/mysql-5.7.35-el7-x86_64
src: /usr/local/{{ version }}
dest: /usr/local/mysql
owner: root
group: root
state: link

- name: set buffer_pool_size
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^innodb_buffer_pool_size'
line: 'innodb_buffer_pool_size = {{ (ansible_memtotal_mb * 0.65 / 1024) | int }}GB'

- name: set datadir
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^datadir'
line: 'datadir = /data/{{ port }}/data/'

- name: set log_bin
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^log_bin ='
line: 'log_bin = /data/{{ port }}/logs/binlog/bin.log'

- name: set undo path
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^innodb_undo_directory'
line: 'innodb_undo_directory = /data/{{ port }}/logs/undo/'

- name: set redo path
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^innodb_log_group_home_dir'
line: 'innodb_log_group_home_dir = /data/{{ port }}/logs/redo/'

- name: set report-host
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^report-host'
line: 'report-host = {{ ansible_default_ipv4.address }}'

- name: set report-port
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^report-port'
line: 'report-port = {{ port }}'

- name: set tmpdir
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^tmpdir'
line: 'tmpdir = /data/{{ port }}/tmp/'

- name: set port
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^port'
line: 'port = {{ port }}'

- name: set log_error
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^log_error'
line: 'log_error = /data/{{ port }}/logs/error/error.log'

- name: set slow_query_log_file
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^slow_query_log_file'
line: 'slow_query_log_file = /data/{{ port }}/logs/slow/slow.log'

- name: set relay_log
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^relay_log ='
line: 'relay_log = /data/{{ port }}/logs/relay/relay.log'

- name: set buffer poll page size
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^innodb_page_size ='
line: 'innodb_page_size = {{ page_size }}'

- name: set character_set
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^character-set-server ='
line: 'character-set-server = {{ character_set }}'

- name: get random id
#shell: echo $RANDOM |md5sum | cut -c 2-10
shell: date +%s%N | cut -c6-14
register: random_id

- name: set server-id
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^server-id'
line: 'server-id = {{ random_id.stdout }}'

- name: set socket
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^socket'
line: 'socket = /data/{{ port }}/mysql{{ port }}.sock'

- name: chown
file:
path: /data/
owner: mysql
group: mysql
recurse: yes

- name: chown
file:
path: /usr/local/mysql
owner: mysql
group: mysql
#recurse: yes

- name: chown
file:
path: /usr/local/{{ version }}
owner: mysql
group: mysql
recurse: yes

- name: init mysql
shell: /usr/local/mysql/bin/mysqld --defaults-file=/data/{{ port }}/my.cnf --initialize-insecure --basedir=/usr/local/mysql --datadir=/data/{{ port }}/data/ --user=mysql

- name: SSL setup
shell: /usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/data/{{ port }}/data/ --user=mysql &> /dev/null

- name: chown
file:
path: /data/
owner: mysql
group: mysql
recurse: yes

#- name: start mysql services
# shell: /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/{{ port }}/my.cnf &

- name: set env
lineinfile:
path: /home/mysql/.bash_profile
insertafter: "export PATH"
line: "export PATH=/usr/local/mysql/bin/:$PATH"

- name: add services
copy:
#src: /root/soft/mysqld.service
src: "{{ soft_path }}/mysqld.service"
dest: /etc/systemd/system/mysqld{{ port }}.service
owner: root
group: root
backup: yes

- name: edit service file
lineinfile:
path: /etc/systemd/system/mysqld{{ port }}.service
regexp: '^ExecStart='
line: 'ExecStart=/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/{{ port }}/my.cnf'

- name: reload service
shell: systemctl daemon-reload

- name: start mysql service
systemd:
name: mysqld{{ port }}.service
state: started
enabled: no

- name: add sudo for mysql
lineinfile:
path: /etc/sudoers
insertafter: root ALL=(ALL) ALL
line: "mysql ALL=(ALL) NOPASSWD:ALL"

add_mysql_instance.yml 内容

[root@tydb010 ansible_install_mysql]# cat add_mysql_instance.yml
- hosts: dblist
tasks:

- name: if directory exist
shell: ls /data/"{{ port }}"
ignore_errors: True
register: result

- name: if directory exist 2
fail:
msg: " /data/{{ port }} diretory exist! "
when: result.failed == false

- name: create directory
shell: mkdir /data/"{{ port }}"/{data,logs,tmp} -p

- name: create directory1
shell: mkdir /data/"{{ port }}"/logs/{error,slow,redo,undo,binlog,relay} -p

- name: cp my.cnf
copy:
#src: /root/soft/my.cnf
src: "{{ soft_path }}/{{ my_cnf }}"
dest: "/data/{{ port }}/"
owner: root
group: root
backup: yes

#- name: cp my.cnf
# copy:
# src: /root/soft/my.cnf
# dest: /data/{{ port }}/
# owner: root
# group: root
# backup: yes

- name: set buffer_pool_size
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^innodb_buffer_pool_size'
line: 'innodb_buffer_pool_size = {{ (ansible_memtotal_mb * 0.65 / 1024) | int }}GB'

- name: set datadir
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^datadir'
line: 'datadir = /data/{{ port }}/data/'

- name: set log_bin
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^log_bin ='
line: 'log_bin = /data/{{ port }}/logs/binlog/bin.log'

- name: set undo path
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^innodb_undo_directory'
line: 'innodb_undo_directory = /data/{{ port }}/logs/undo/'

- name: set redo path
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^innodb_log_group_home_dir'
line: 'innodb_log_group_home_dir = /data/{{ port }}/logs/redo/'

- name: set report-host
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^report-host'
line: 'report-host = {{ ansible_default_ipv4.address }}'

- name: set report-port
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^report-port'
line: 'report-port = {{ port }}'

- name: set tmpdir
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^tmpdir'
line: 'tmpdir = /data/{{ port }}/tmp/'

- name: set port
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^port'
line: 'port = {{ port }}'

- name: set log_error
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^log_error'
line: 'log_error = /data/{{ port }}/logs/error/error.log'

- name: set slow_query_log_file
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^slow_query_log_file'
line: 'slow_query_log_file = /data/{{ port }}/logs/slow/slow.log'

- name: set relay_log
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^relay_log ='
line: 'relay_log = /data/{{ port }}/logs/relay/relay.log'

- name: get random id
#shell: echo $RANDOM |md5sum | cut -c 2-10
shell: date +%s%N | cut -c6-14
register: random_id

- name: set server-id
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^server-id'
line: 'server-id = {{ random_id.stdout }}'

- name: set socket
lineinfile:
path: /data/{{ port }}/my.cnf
regexp: '^socket'
line: 'socket = /data/{{ port }}/mysql{{ port }}.sock'

- name: chown
file:
path: /data/
owner: mysql
group: mysql
recurse: yes

- name: init mysql
shell: /usr/local/mysql/bin/mysqld --defaults-file=/data/{{ port }}/my.cnf --initialize-insecure --basedir=/usr/local/mysql --datadir=/data/{{ port }}/data/ --user=mysql

- name: SSL setup
shell: /usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/data/{{ port }}/data/ --user=mysql &> /dev/null

- name: chown
file:
path: /data/
owner: mysql
group: mysql
recurse: yes

#- name: start mysql services
# shell: /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/{{ port }}/my.cnf &

- name: scp services file
copy:
src: /root/soft/mysqld.service
dest: /etc/systemd/system/mysqld{{ port }}.service
owner: root
group: root
backup: yes

- name: edit services file
lineinfile:
path: /etc/systemd/system/mysqld{{ port }}.service
regexp: '^ExecStart='
line: 'ExecStart=/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/{{ port }}/my.cnf'

- name: reload service
shell: systemctl daemon-reload

- name: start mysql service
systemd:
name: mysqld{{ port }}.service
state: started
enabled: no

- name: set env
lineinfile:
path: /home/mysql/.bash_profile
insertafter: "export PATH"
line: "export PATH=/usr/local/mysql/bin/:$PATH"

clear_mysql_by_port.yml 内容

[root@tydb010 ansible_install_mysql]# cat clear_mysql_by_port.yml
- hosts: dblist
tasks:

- name: close services
systemd:
name: mysqld{{ port }}
state: stopped
enabled: no

- name: drop /data/{{ port }}
shell: rm -rf /data/{{ port }}

共饮一杯
数据运维
0 0

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF，云和恩墨技术通讯（2024年7月刊）
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论

共饮一杯

这个人很懒，什么都没有留下～

文章8405 评论0