mysql 自动部署 for ansible

发表于： 2023-09-01 09:36:06
分类：网络资源
浏览：浏览(33)

刚学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)

免责声明：

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源，请您支持作者，购买正版内容。

6、资源失效，请下方留言，欢迎分享资源链接

文章评论

共0条评论