ansible一键安装mysql8.0

ansbile安装:

# ansible在CentOS7中需要安装epel仓库
yum install -y epel-release
yum install -y ansible

安装有好几种方法，yum安装是最简单的，安装ansible不是重点。

我的版本如下：

[ mysqltools]# ansible --version
ansible 2.7.10
  config file = /etc/ansible/ansible.cfg
  configured module search path = [‘/root/.ansible/plugins/modules‘, ‘/usr/share/ansible/plugins/modules‘]
  ansible python module location = /usr/local/python-3.7.3/lib/python3.7/site-packages/ansible
  executable location = /usr/local/python/bin/ansible
  python version = 3.7.3 (default, Dec 15 2019, 15:54:59) [GCC 4.8.5 20150623 (Red Hat 4.8.5-39.0.3)]

我的目录树如下：

[ mysql]# tree
.
├── common
│   ├── config_systemd.yaml
│   ├── create_my_cnf.yaml
│   ├── create_user.yaml
│   ├── export_path.yaml
│   ├── init_mysql_data_dir.yaml
│   ├── install_mysql.yaml
│   └── mysql_dependencies.yaml
├── install_group_replication.yaml
├── install_master_slaves.yaml
├── install_multi_source_replication.yaml
├── install_single.yaml
├── templates
│   ├── 5.7
│   │   └── my.cnf.jinja
│   ├── 8.0
│   │   ├── my.cnf
│   │   └── my.cnf.jinja
│   ├── init_mysql_data_dir.sh
│   ├── ld.so.conf
│   ├── mysqld.service
│   ├── mysqldump_backup.sh
│   ├── untar_mysql_pkg.sh
│   ├── users.sql
│   └── users.sql.jinja
├── upgrad_single_mysql.yaml
└── vars
    ├── group_replication.yaml
    ├── master_slaves.yaml
    ├── multi_source_replication.yaml
    └── mysql.yaml

以安装单机版为例， install_single.yaml 如下：

[ mysql]# cat install_single.yaml 
---
  - hosts: uat
    remote_user: root
    become_user: root
    become: yes
    vars_files:
      - ../../config.yaml
      - ./vars/mysql.yaml
    tasks:
      - name: create mysql user
        import_tasks: common/create_user.yaml
      
      - name: install dependencies
        import_tasks: common/mysql_dependencies.yaml
      
      - name: "create /etc/my-{{mysql_port}}.cnf"
        import_tasks: common/create_my_cnf.yaml

      - name: "install {{mysql_binary_pkg}}"
        import_tasks: common/install_mysql.yaml

      - name: "init data dir"
        import_tasks: common/init_mysql_data_dir.yaml

      - name: "config path"
        import_tasks: common/export_path.yaml

      - name: "config systemd and start mysqld"
        import_tasks: common/config_systemd.yaml

 create_user.yaml 

[ mysql]# cat common/create_user.yaml 
---
 - name: create mysql group
   group:
     name: "{{mysql_group}}"
     state: present
     gid: "{{mysql_gid}}"
    
 - name: create user "{{mysql_user}}"
   user:
     name: "{{mysql_user}}"
     state: present
     uid: "{{mysql_uid}}"
     group: mysql

 mysql_dependencies.yaml 

[ mysql]# cat common/mysql_dependencies.yaml
---
 - name: install libaio
   yum:
    name: libaio-devel
    state: present

 - name: install numactl
   yum:
    name: numactl-devel
    state: present

 - name: install perl-Data-Dumper 
   yum:
    name: perl-Data-Dumper 
    state: present

 install_mysql.yaml 

[ mysql]# cat common/install_mysql.yaml
---
  - name: "transfer {{mysql_binary_pkg}} to target host(s)."
    copy:
      src: "../../../sps/mysql/{{mysql_binary_pkg}}"
      dest: "/tmp/mysql/"
      owner: "{{mysql_user}}"
  
  - name: "generate untar script /tmp/untar_mysql_pkg.sh"
    template:
      src: "../templates/untar_mysql_pkg.sh"
      dest: "/tmp/mysql/untar_mysql_pkg.sh"
  
  - name: "untar {{mysql_binary_pkg}} "
    shell: "bash /tmp/mysql/untar_mysql_pkg.sh > /tmp/untar_mysql_pkg.log"
  
  - name: "rm /tmp/untar_mysql_pkg.sh"
    file:
      path: "/tmp/mysql/untar_mysql_pkg.sh"
      state: absent

  - name: "create libmysqlclient_r.so"
    file:
      src: "{{mysql_base_dir}}/lib/libmysqlclient.so"
      dest: "{{mysql_base_dir}}/lib/libmysqlclient_r.so"
      state: link
  
  - name: "update file privileges"
    file:
      path: "{{mysql_base_dir}}"
      owner: "{{mysql_user}}"
      group: "{{mysql_group}}"
      recurse: yes
  
  - name: "config ldconfig"
    template:
      src: "../templates/ld.so.conf"
      dest: "/etc/ld.so.conf.d/{{mysql_version}}.conf"
  
  - name: "load so"
    shell: ldconfig
  
  - name: "conifg header file"
    file:
      src: "{{mysql_base_dir}}/include"
      dest: "/usr/include/{{mysql_version}}"
      state: link

 init_mysql_data_dir.yaml 

[ mysql]# cat common/init_mysql_data_dir.yaml
---
  - name: "transfer users.sql to target host(s)."
    template:
      src: "../templates/users.sql"
      dest: "/tmp/mysql/users.sql"

  - name: "transfer init_mysql_data_dir.sh to target host(s)."
    template:
      src: "../templates/init_mysql_data_dir.sh"
      dest: /tmp/mysql/init_mysql_data_dir.sh
  
  - name: "init data dir"
    shell: bash /tmp/mysql/init_mysql_data_dir.sh

  - name: "rm mysql_pkg_scripts"
    file:
      path: "/tmp/mysql/"
      state: absent

 export_path.yaml 

[ mysql]# cat common/export_path.yaml
---
  - name: /etc/profile
    lineinfile:
      path: /etc/profile
      line: "export PATH={{mysql_base_dir}}/bin/:$PATH"
      insertafter: EOF
      state: present
  
  - name: ~/.bash_profile
    lineinfile:
      path: "/home/{{mysql_user}}/.bash_profile"
      line: "export PATH={{mysql_base_dir}}/bin/:$PATH"
      insertafter: EOF
      state: present   

  - name: ~/.bashrc
    lineinfile:
      path: "/home/{{mysql_user}}/.bashrc"
      line: "export PATH={{mysql_base_dir}}/bin/:$PATH"
      insertafter: EOF
      state: present

 config_systemd.yaml 

[ mysql]# cat common/config_systemd.yaml
---
  - name: "config mysqld-{{mysql_port}}.service"
    template:
      src: "../templates/mysqld.service"
      dest: "/usr/lib/systemd/system/mysqld-{{mysql_port}}.service"
  
  - name: "conifg mysqld-{{mysql_port}} auto start"
    systemd:
      name: "mysqld-{{mysql_port}}"
      enabled: yes
      daemon_reload: yes
  
  - name: "start mysqld-{{mysql_port}}"
    systemd:
      name: "mysqld-{{mysql_port}}"
      state: started
      daemon_reload: yes

定义一些变量配置参数 config.yaml ：

[ mysql]# cat ../../config.yaml
max_memory_size_mb: "{{ 1024 * 512 }}" # 512G内存
mysql_port: 33601
mysql_binary_pkg: "mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz"

 mysql.yaml 

[ mysql]# cat ./vars/mysql.yaml
#mysql configure
mysql_user: "mysql"
mysql_group: "mysql"
mysql_uid: "{{mysql_port}}"
mysql_gid: "{{mysql_port}}"
mysql_packages_dir: "/usr/local/mysqltools/sps/mysql/"
mysql_base_dir: "/usr/local/{{ mysql_binary_pkg | replace(‘.tar.gz‘,‘‘) | replace(‘.tar.xz‘,‘‘) }}"
mysql_data_dir: "/database/mysql/node1"
mysql_version: "{{ mysql_binary_pkg | replace(‘.tar.gz‘,‘‘) | replace(‘.tar.xz‘,‘‘) }}"
mysql_root_pwd: ‘xxxx‘
mysql_monitor_user: ‘xxx‘
mysql_monitor_pwd: ‘xxxx‘
mysql_binlog_format: "row"
mysql_xport: "{{ mysql_port + 100 }}"
mysql_mgrport: "{{ mysql_port + 101 }}"
mysql_admin_port: "{{ mysql_port + 102 }}"

 create_my_cnf.yaml 

[ mysql]# cat common/create_my_cnf.yaml
---
  - name: "/etc/my-{{mysql_port}}.cnf for mysql-8.0.x "
    when: mysql_binary_pkg.find(‘8.0‘) != -1
    template:
      src: ../templates/8.0/my.cnf
      dest: "/etc/my.cnf"
      owner: "{{mysql_user}}"
      group: "{{mysql_group}}"
      backup: yes

  - name: "/etc/my-{{mysql_port}}.cnf for mysql-5.7.x "
    when: mysql_binary_pkg.find(‘5.7‘) != -1
    template:
      src: ../templates/5.7/my.cnf
      dest: "/etc/my.cnf"
      owner: "{{mysql_user}}"
      group: "{{mysql_group}}"
      backup: yes

文件目录设置的比较多，主要是为了配置方便灵活，可以适当的增加，看每个人自己实际的情况来操作就好。

[ mysql]# cat /etc/ansible/ansible.cfg 
[defaults]
host_key_checking = False    #不检测host key
stdout_callback = debug
ANSIBLE_STDOUT_CALLBACK=debug

修改ansible配置文件hosts主机里面的主机，然后与 install_single.yaml：里面的host对应上：

[ ~]# cat /etc/ansible/hosts
[uat]
x.x.x.x ansible_host=x.x.x.x ansible_user=root ansible_ssh_pass=xxxxx ansible_su_pass=xxxxx

执行过程报错：

TASK [init data dir] *********************************************************************************************************************
fatal: [172.18.1.192]: FAILED! => {
    "changed": true,
    "cmd": "bash /tmp/mysql/init_mysql_data_dir.sh",
    "delta": "0:00:14.202501",
    "end": "2020-05-01 12:36:32.514086",
    "rc": 1,
    "start": "2020-05-01 12:36:18.311585"
}

MSG:

non-zero return code

    to retry, use: --limit @/usr/local/mysqltools/ansible/mysql/install_single.retry

PLAY RECAP *******************************************************************************************************************************
172.18.1.192               : ok=18   changed=7    unreachable=0    failed=1

手动执行提示：

hing off the --initialize-insecure option.
2020-05-01T12:51:17.339226+08:00 5 [Note] [MY-011061] [Server] Creating the system tables.
2020-05-01T12:51:17.506281+08:00 5 [Note] [MY-011061] [Server] Filling in the system tables, part 1.
2020-05-01T12:51:17.517417+08:00 5 [Note] [MY-011061] [Server] Filling in the system tables, part 2.
2020-05-01T12:51:17.776951+08:00 5 [Note] [MY-011061] [Server] Filling in the mysql.help table.
2020-05-01T12:51:17.858580+08:00 5 [Note] [MY-011061] [Server] Creating the system users for internal usage.
2020-05-01T12:51:17.886104+08:00 5 [Note] [MY-011061] [Server] Creating the sys schema.
2020-05-01T12:51:18.387866+08:00 5 [Note] [MY-010456] [Server] Bootstrapping complete
2020-05-01T12:51:18.486722+08:00 5 [ERROR] [MY-000061] [Server] 1410  You are not allowed to create a user with GRANT.
2020-05-01T12:51:18.489065+08:00 0 [Note] [MY-010138] [Server] Execution of init_file ‘/tmp/mysql/users.sql‘ ended.
2020-05-01T12:51:18.489096+08:00 0 [ERROR] [MY-013455] [Server] The newly created data directory /database/mysql/node1/ by --initialize is unusable. You can remove it.

脚本如下：

[ ~]# cat /tmp/mysql/init_mysql_data_dir.sh 
#!/bin/bash

cd /usr/local/mysql-8.0.18-linux-glibc2.12-x86_64

if [ -d /database/mysql/node1 ]
then
    echo "`date`  | datadir has been inited" >> /tmp/mtls8.log
    exit 1;
else
    mkdir -p /database/mysql/33601
    mkdir -p /database/mysql/33601/binlogs
    mkdir -p /database/mysql/33601/undolog
    mkdir -p /database/mysql/33601/relaylogs    
    mkdir -p /database/mysql/33601/tmpdir 
    chown mysql:mysql -R /database/mysql
    chmod 600 /root/.mylogin.cnf
    chmod 777 -R /database/mysql
    ./bin/mysqld --defaults-file=/etc/my.cnf --user=mysql --initialize-insecure --init-file=/tmp/mysql/users.sql
fi

最后排查发现是 --init-file=/tmp/mysql/users.sql 有问题，修改以后然后执行：

[ mysql]# ansible-playbook install_single.yaml 

PLAY [uat] *******************************************************************************************************************************

TASK [Gathering Facts] *******************************************************************************************************************
ok: [172.18.1.192]

TASK [create mysql group] ****************************************************************************************************************
ok: [172.18.1.192]

TASK [create user "mysql"] ***************************************************************************************************************
ok: [172.18.1.192]

TASK [install libaio] ********************************************************************************************************************
ok: [172.18.1.192]

TASK [install numactl] *******************************************************************************************************************
ok: [172.18.1.192]

TASK [install perl-Data-Dumper] **********************************************************************************************************
ok: [172.18.1.192]

TASK [/etc/my-33601.cnf for mysql-8.0.x] *************************************************************************************************
changed: [172.18.1.192]

TASK [/etc/my-33601.cnf for mysql-5.7.x] *************************************************************************************************
skipping: [172.18.1.192]

TASK [transfer mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz to target host(s).] ************************************************************
ok: [172.18.1.192]

TASK [generate untar script /tmp/untar_mysql_pkg.sh] *************************************************************************************
changed: [172.18.1.192]

TASK [untar mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz] **********************************************************************************
changed: [172.18.1.192]

TASK [rm /tmp/untar_mysql_pkg.sh] ********************************************************************************************************
changed: [172.18.1.192]

TASK [create libmysqlclient_r.so] ********************************************************************************************************
ok: [172.18.1.192]

TASK [update file privileges] ************************************************************************************************************
ok: [172.18.1.192]

TASK [config ldconfig] *******************************************************************************************************************
ok: [172.18.1.192]

TASK [load so] ***************************************************************************************************************************
changed: [172.18.1.192]

TASK [conifg header file] ****************************************************************************************************************
ok: [172.18.1.192]

TASK [transfer users.sql to target host(s).] *********************************************************************************************
changed: [172.18.1.192]

TASK [transfer init_mysql_data_dir.sh to target host(s).] ********************************************************************************
ok: [172.18.1.192]

TASK [init data dir] *********************************************************************************************************************
changed: [172.18.1.192]

TASK [rm mysql_pkg_scripts] **************************************************************************************************************
changed: [172.18.1.192]

TASK [/etc/profile] **********************************************************************************************************************
changed: [172.18.1.192]

TASK [~/.bash_profile] *******************************************************************************************************************
changed: [172.18.1.192]

TASK [~/.bashrc] *************************************************************************************************************************
changed: [172.18.1.192]

TASK [config mysqld-33601.service] *******************************************************************************************************
changed: [172.18.1.192]

TASK [conifg mysqld-33601 auto start] ****************************************************************************************************
changed: [172.18.1.192]

TASK [start mysqld-33601] ****************************************************************************************************************
changed: [172.18.1.192]

TASK [create backup dir] *****************************************************************************************************************
changed: [172.18.1.192]

TASK [create backup script dir] **********************************************************************************************************
changed: [172.18.1.192]

TASK [transfer backup script to target host (mysqldump)] *********************************************************************************
changed: [172.18.1.192]

TASK [config backup job (mysqldump)] *****************************************************************************************************
changed: [172.18.1.192]

PLAY RECAP *******************************************************************************************************************************
172.18.1.192               : ok=30   changed=18   unreachable=0    failed=0

至此单机mysql一键安装算是完毕，借鉴了https://galaxy.ansible.com/home，看什么时候能自己整合一个平台，把ansible集成到界面，鼠标点记下，就完成一台机器安装，就好了。