ansible一键安装mysql8.0

Z时代
2024-01-10
分类：综合

database

ansbile安装:

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

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

我的版本如下：

[root@szwpldb1081 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 152019, 15:54:59) [GCC 4.8.520150623 (Red Hat 4.8.5-39.0.3)]

我的目录树如下：

[root@szwpldb1081 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 如下：

[root@szwpldb1081 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

[root@szwpldb1081 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

[root@szwpldb1081 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

[root@szwpldb1081 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

[root@szwpldb1081 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

[root@szwpldb1081 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

[root@szwpldb1081 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 ：

[root@szwpldb1081 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

[root@szwpldb1081 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

[root@szwpldb1081 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

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

[root@szwpldb1081 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对应上：

[root@szwpldb1081 ~]# 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:005 [Note] [MY-011061] [Server] Creating the system tables.
2020-05-01T12:51:17.506281+08:005 [Note] [MY-011061] [Server] Filling in the system tables, part 1.
2020-05-01T12:51:17.517417+08:005 [Note] [MY-011061] [Server] Filling in the system tables, part 2.
2020-05-01T12:51:17.776951+08:005 [Note] [MY-011061] [Server] Filling in the mysql.help table.
2020-05-01T12:51:17.858580+08:005 [Note] [MY-011061] [Server] Creating the system users forinternal usage.
2020-05-01T12:51:17.886104+08:005 [Note] [MY-011061] [Server] Creating the sys schema.
2020-05-01T12:51:18.387866+08:005 [Note] [MY-010456] [Server] Bootstrapping complete
2020-05-01T12:51:18.486722+08:005 [ERROR] [MY-000061] [Server] 1410  You are not allowed to create a user with GRANT.
2020-05-01T12:51:18.489065+08:000 [Note] [MY-010138] [Server] Execution of init_file "/tmp/mysql/users.sql" ended.
2020-05-01T12:51:18.489096+08:000 [ERROR] [MY-013455] [Server] The newly created data directory /database/mysql/node1/ by --initialize is unusable. You can remove it.

脚本如下：

[root@SZWPLDB1091 ~]# 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 有问题，修改以后然后执行：

[root@szwpldb1081 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