ansible部署二进制mysql 8

1、配置文件

ll /ansible
-rw-r--r-- 1 root root        836 Sep 22 12:09 my.cnf
-rw-r--r-- 1 root root        810 Sep 22 07:21 mysql.service
-rw-r--r-- 1 root root       2731 Sep 22 12:32 mysql.yam

2、my.cnf内容

root@bole:/ansible# cat my.cnf 
[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8mb4
[mysqld]
skip-name-resolve
user=mysql
ngram_token_size=2
server-id=1
default_password_lifetime=0
port=3306
#设置安装目录
basedir=/app/mysql
#数据存放目录
datadir=/app/mysql/data
log-error=/app/mysql/logs/err.log
#允许最大连接数
max_connections=1000
#服务端默认使用的字符集
character-set-server=utf8mb4
#创捷新表时默认的储存引擎
default-storage-engine=INNODB
#忘记密码时使用
#skip-grant-tables
#不区分大小写
lower_case_table_names=1
#认证方式
default_authentication_plugin=mysql_native_password
max_allowed_packet=500M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
wait_timeout=28800
interactive_timeout=28800
max_connect_errors=100
max_user_connections=0
#日志文件大小
max_binlog_size=100M

3、mysql.service内容

root@bole:/ansible# cat 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

# Have mysqld write its state to the systemd notify socket
Type=notify

# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0

# Start main service
ExecStart=/app/mysql/bin/mysqld --defaults-file=/app/mysql/conf/my.cnf $MYSQLD_OPTS

# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit
LimitNOFILE=10000

Restart=on-failure
RestartPreventExitStatus=1

# Set environment variable MYSQLD_PARENT_PID. This is required for restart.
Environment=MYSQLD_PARENT_PID=1

PrivateTmp=false

4、mysql.yaml的内容

root@bole:/ansible# cat mysql.yaml 
- name: Install MySQL
  hosts: 192.168.10.102
  gather_facts: no
 #创建用户组
  tasks:
    - name: Create MySQL group
      group:
        name: mysql
        system: yes
        gid: 306

    - name: Create MySQL user
      user:
        name: mysql
        shell: /sbin/nologin
        system: yes
        group: mysql
        uid: 306
        home: /data/mysql
        create_home: no
    #将shell的标准输出赋予 id_output
    - name: Check creation of user and group
      shell: id mysql
      register: id_output
   #将标准输出打印，加stdout即使捕获register模块的标准输出
    - name: Print output of 'id mysql'
      debug:
        var: id_output.stdout
   #创建app目录
    - name: Create directory for MySQL installation
      file:
        path: /app
        state: directory
        mode: 0755
   #下载mysql的tar包并将其解压
    - name: Unzip MySQL tarball
      block:
        - name: Download MySQL tarball
          get_url:
            url: https://cdn.mysql.com/Downloads/MySQL-8.0/mysql-8.0.39-linux-glibc2.28-x86_64.tar
            dest: /app

        - name: Unzip MySQL tarball to temporary directory
          unarchive:
            src: /app/mysql-8.0.39-linux-glibc2.28-x86_64.tar
            dest: /app
            copy: no

        - name: Tar.xz Mysql
          unarchive:
            src: /app/mysql-8.0.39-linux-glibc2.28-x86_64.tar.xz
            dest: /app
            copy: no

        - name: Move MySQL directory to final location
          shell: mv /app/mysql-8.0.39-linux-glibc2.28-x86_64 /app/mysql
          args:
            creates: /app/mysql
  
        - name: Set ownership and permissions for MySQL directory
          file:
            path: /app/mysql
            state: directory
            owner: mysql
            group: mysql
            mode: 0755
    #创建所需的目录
    - name: Create MySQL directories
      file:
        path: "{{ item }}"
        state: directory
        owner: mysql
        group: mysql
        mode: 0755
      with_items:
        - /app/mysql
        - /app/mysql/logs
        - /app/mysql/conf
        - /app/mysql/data
    #安装依赖
    - name: Install dependencies
      apt:
        name:
          - libaio1
          - libaio-dev
          - libncurses5-dev
        update_cache: yes
    #拷贝配置文件及service文件
    - name: Copy MySQL configuration files
      copy:
        src: /ansible/my.cnf
        dest: /app/mysql/conf
        owner: mysql
        group: mysql
        mode: 0644

    - name: Copy Mysql.service
      copy:
        src: /ansible/mysql.service
        dest: /usr/lib/systemd/system
        owner: root
        group: root
        mode: 0644
   #安装mysql并启动
    - name: Initialize MySQL
      shell: /app/mysql/bin/mysqld --defaults-file=/app/mysql/conf/my.cnf --initialize
      environment:
        MYSQL_ROOT_PASSWORD: your_root_password_here

    - name: Start MySQL service
      systemd:
        name: mysql
        state: started
        enabled: yes

5、验证查看

root@ab2:~# systemctl status mysql
● mysql.service - MySQL Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2024-09-22 12:33:40 UTC; 18min ago
       Docs: man:mysqld(8)
             http://dev.mysql.com/doc/refman/en/using-systemd.html
   Main PID: 4001 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 4513)
     Memory: 361.7M
        CPU: 8.028s
     CGroup: /system.slice/mysql.service
             └─4001 /app/mysql/bin/mysqld --defaults-file=/app/mysql/conf/my.cnf

Sep 22 12:33:40 ab2 systemd[1]: Starting MySQL Server...
Sep 22 12:33:40 ab2 systemd[1]: Started MySQL Server.

6、登录验证

#获取密码
root@ab2:~# cat /app/mysql/logs/err.log |grep 'temporary password'
2024-09-22T12:33:37.597706Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: QEJw-p,=:8(.
#登录
mysql -uroot -p 'QEJw-p,=:8(.'

#修改密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)