通过Ansible部署mysql8单节点

# cat playbooks/Mysql8_Ubuntu2204_Single.yaml

- hosts: mysqlsingle
  roles:
    - ../roles/Mysql8_Ubuntu2204_Single

• 1.
• 2.
• 3.
• 4.
• 5.

# cat roles/Mysql8_Ubuntu2204_Single/files/mysql.server  mysql启动脚本

#!/bin/sh
basedir=
datadir=
service_startup_timeout=900
lockdir='/var/lock/subsys'
lock_file_path="$lockdir/mysql"
mysqld_pid_file_path=
if test -z "$basedir"
then
  basedir=/usr/local/mysql
  bindir=/usr/local/mysql/bin
  if test -z "$datadir"
  then
    datadir=/usr/local/mysql/data
  fi
  sbindir=/usr/local/mysql/bin
  libexecdir=/usr/local/mysql/bin
else
  bindir="$basedir/bin"
  if test -z "$datadir"
  then
    datadir="$basedir/data"
  fi
  sbindir="$basedir/sbin"
  libexecdir="$basedir/libexec"
fi

datadir_set=

lsb_functions="/lib/lsb/init-functions"
if test -f $lsb_functions ; then
  . $lsb_functions
else
  log_success_msg()
  {
    echo " SUCCESS! $@"
  }
  log_failure_msg()
  {
    echo " ERROR! $@"
  }
fi

PATH="/sbin:/usr/sbin:/bin:/usr/bin:$basedir/bin"
export PATH

mode=$1    # start or stop

[ $# -ge 1 ] && shift


other_args="$*"


case `echo "testing\c"`,`echo -n testing` in
    *c*,-n*) echo_n=   echo_c=     ;;
    *c*,*)   echo_n=-n echo_c=     ;;
    *)       echo_n=   echo_c='\c' ;;
esac

parse_server_arguments() {
  for arg do
    case "$arg" in
      --basedir=*)  basedir=`echo "$arg" | sed -e 's/^[^=]*=//'`
                    bindir="$basedir/bin"
            if test -z "$datadir_set"; then
              datadir="$basedir/data"
            fi
            sbindir="$basedir/sbin"
            libexecdir="$basedir/libexec"
        ;;
      --datadir=*)  datadir=`echo "$arg" | sed -e 's/^[^=]*=//'`
            datadir_set=1
    ;;
      --pid-file=*) mysqld_pid_file_path=`echo "$arg" | sed -e 's/^[^=]*=//'` ;;
      --service-startup-timeout=*) service_startup_timeout=`echo "$arg" | sed -e 's/^[^=]*=//'` ;;
    esac
  done
}

wait_for_pid () {
  verb="$1"           # created | removed
  pid="$2"            # process ID of the program operating on the pid-file
  pid_file_path="$3" # path to the PID file.

  i=0
  avoid_race_condition="by checking again"

  while test $i -ne $service_startup_timeout ; do

    case "$verb" in
      'created')
        # wait for a PID-file to pop into existence.
        test -s "$pid_file_path" && i='' && break
        ;;
      'removed')
        # wait for this PID-file to disappear
        test ! -s "$pid_file_path" && i='' && break
        ;;
      *)
        echo "wait_for_pid () usage: wait_for_pid created|removed pid pid_file_path"
        exit 1
        ;;
    esac

    # if server isn't running, then pid-file will never be updated
    if test -n "$pid"; then
      if kill -0 "$pid" 2>/dev/null; then
        :  # the server still runs
      else
        # The server may have exited between the last pid-file check and now.
        if test -n "$avoid_race_condition"; then
          avoid_race_condition=""
          continue  # Check again.
        fi

        # there's nothing that will affect the file.
        log_failure_msg "The server quit without updating PID file ($pid_file_path)."
        return 1  # not waiting any more.
      fi
    fi

    echo $echo_n ".$echo_c"
    i=`expr $i + 1`
    sleep 1

  done

  if test -z "$i" ; then
    log_success_msg
    return 0
  else
    log_failure_msg
    return 1
  fi
}

# Get arguments from the my.cnf file,
# the only group, which is read from now on is [mysqld]
if test -x "$bindir/my_print_defaults";  then
  print_defaults="$bindir/my_print_defaults"
else
  # Try to find basedir in /etc/my.cnf
  conf=/etc/my.cnf
  print_defaults=
  if test -r $conf
  then
    subpat='^[^=]*basedir[^=]*=\(.*\)$'
    dirs=`sed -e "/$subpat/!d" -e 's//\1/' $conf`
    for d in $dirs
    do
      d=`echo $d | sed -e 's/[     ]//g'`
      if test -x "$d/bin/my_print_defaults"
      then
        print_defaults="$d/bin/my_print_defaults"
        break
      fi
    done
  fi

  # Hope it's in the PATH ... but I doubt it
  test -z "$print_defaults" && print_defaults="my_print_defaults"
fi

#
# Read defaults file from 'basedir'.   If there is no defaults file there
# check if it's in the old (depricated) place (datadir) and read it from there
#

extra_args=""
if test -r "$basedir/my.cnf"
then
  extra_args="-e $basedir/my.cnf"
fi

parse_server_arguments `$print_defaults $extra_args mysqld server mysql_server mysql.server`

#
# Set pid file if not given
#
if test -z "$mysqld_pid_file_path"
then
  mysqld_pid_file_path=$datadir/`hostname`.pid
else
  case "$mysqld_pid_file_path" in
    /* ) ;;
    * )  mysqld_pid_file_path="$datadir/$mysqld_pid_file_path" ;;
  esac
fi

case "$mode" in
  'start')
    # Start daemon

    # Safeguard (relative paths, core dumps..)
    cd $basedir

    echo $echo_n "Starting MySQL"
    if test -x $bindir/mysqld_safe
    then
      # Give extra arguments to mysqld with the my.cnf file. This script
      # may be overwritten at next upgrade.
      $bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null &
      wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?

      # Make lock for RedHat / SuSE
      if test -w "$lockdir"
      then
        touch "$lock_file_path"
      fi

      exit $return_value
    else
      log_failure_msg "Couldn't find MySQL server ($bindir/mysqld_safe)"
    fi
    ;;

  'stop')
    # Stop daemon. We use a signal here to avoid having to know the
    # root password.

    if test -s "$mysqld_pid_file_path"
    then
      # signal mysqld_safe that it needs to stop
      touch "$mysqld_pid_file_path.shutdown"

      mysqld_pid=`cat "$mysqld_pid_file_path"`

      if (kill -0 $mysqld_pid 2>/dev/null)
      then
        echo $echo_n "Shutting down MySQL"
        kill $mysqld_pid
        # mysqld should remove the pid file when it exits, so wait for it.
        wait_for_pid removed "$mysqld_pid" "$mysqld_pid_file_path"; return_value=$?
      else
        log_failure_msg "MySQL server process #$mysqld_pid is not running!"
        rm "$mysqld_pid_file_path"
      fi

      # Delete lock for RedHat / SuSE
      if test -f "$lock_file_path"
      then
        rm -f "$lock_file_path"
      fi
      exit $return_value
    else
      log_failure_msg "MySQL server PID file could not be found!"
    fi
    ;;

  'restart')
    # Stop the service and regardless of whether it was
    # running or not, start it again.
    if $0 stop  $other_args; then
      $0 start $other_args
    else
      log_failure_msg "Failed to stop running server, so refusing to try to start."
      exit 1
    fi
    ;;

  'reload'|'force-reload')
    if test -s "$mysqld_pid_file_path" ; then
      read mysqld_pid <  "$mysqld_pid_file_path"
      kill -HUP $mysqld_pid && log_success_msg "Reloading service MySQL"
      touch "$mysqld_pid_file_path"
    else
      log_failure_msg "MySQL PID file could not be found!"
      exit 1
    fi
    ;;
  'status')
    # First, check to see if pid file exists
    if test -s "$mysqld_pid_file_path" ; then
      read mysqld_pid < "$mysqld_pid_file_path"
      if kill -0 $mysqld_pid 2>/dev/null ; then
        log_success_msg "MySQL running ($mysqld_pid)"
        exit 0
      else
        log_failure_msg "MySQL is not running, but PID file exists"
        exit 1
      fi
    else
      # Try to find appropriate mysqld process
      mysqld_pid=`pidof $libexecdir/mysqld`

      # test if multiple pids exist
      pid_count=`echo $mysqld_pid | wc -w`
      if test $pid_count -gt 1 ; then
        log_failure_msg "Multiple MySQL running but PID file could not be found ($mysqld_pid)"
        exit 5
      elif test -z $mysqld_pid ; then
        if test -f "$lock_file_path" ; then
          log_failure_msg "MySQL is not running, but lock file ($lock_file_path) exists"
          exit 2
        fi
        log_failure_msg "MySQL is not running"
        exit 3
      else
        log_failure_msg "MySQL is running but PID file could not be found"
        exit 4
      fi
    fi
    ;;
    *)
      # usage
      basename=`basename "$0"`
      echo "Usage: $basename  {start|stop|restart|reload|force-reload|status}  [ MySQL server options ]"
      exit 1
    ;;
esac

exit 0

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.
• 30.
• 31.
• 32.
• 33.
• 34.
• 35.
• 36.
• 37.
• 38.
• 39.
• 40.
• 41.
• 42.
• 43.
• 44.
• 45.
• 46.
• 47.
• 48.
• 49.
• 50.
• 51.
• 52.
• 53.
• 54.
• 55.
• 56.
• 57.
• 58.
• 59.
• 60.
• 61.
• 62.
• 63.
• 64.
• 65.
• 66.
• 67.
• 68.
• 69.
• 70.
• 71.
• 72.
• 73.
• 74.
• 75.
• 76.
• 77.
• 78.
• 79.
• 80.
• 81.
• 82.
• 83.
• 84.
• 85.
• 86.
• 87.
• 88.
• 89.
• 90.
• 91.
• 92.
• 93.
• 94.
• 95.
• 96.
• 97.
• 98.
• 99.
• 100.
• 101.
• 102.
• 103.
• 104.
• 105.
• 106.
• 107.
• 108.
• 109.
• 110.
• 111.
• 112.
• 113.
• 114.
• 115.
• 116.
• 117.
• 118.
• 119.
• 120.
• 121.
• 122.
• 123.
• 124.
• 125.
• 126.
• 127.
• 128.
• 129.
• 130.
• 131.
• 132.
• 133.
• 134.
• 135.
• 136.
• 137.
• 138.
• 139.
• 140.
• 141.
• 142.
• 143.
• 144.
• 145.
• 146.
• 147.
• 148.
• 149.
• 150.
• 151.
• 152.
• 153.
• 154.
• 155.
• 156.
• 157.
• 158.
• 159.
• 160.
• 161.
• 162.
• 163.
• 164.
• 165.
• 166.
• 167.
• 168.
• 169.
• 170.
• 171.
• 172.
• 173.
• 174.
• 175.
• 176.
• 177.
• 178.
• 179.
• 180.
• 181.
• 182.
• 183.
• 184.
• 185.
• 186.
• 187.
• 188.
• 189.
• 190.
• 191.
• 192.
• 193.
• 194.
• 195.
• 196.
• 197.
• 198.
• 199.
• 200.
• 201.
• 202.
• 203.
• 204.
• 205.
• 206.
• 207.
• 208.
• 209.
• 210.
• 211.
• 212.
• 213.
• 214.
• 215.
• 216.
• 217.
• 218.
• 219.
• 220.
• 221.
• 222.
• 223.
• 224.
• 225.
• 226.
• 227.
• 228.
• 229.
• 230.
• 231.
• 232.
• 233.
• 234.
• 235.
• 236.
• 237.
• 238.
• 239.
• 240.
• 241.
• 242.
• 243.
• 244.
• 245.
• 246.
• 247.
• 248.
• 249.
• 250.
• 251.
• 252.
• 253.
• 254.
• 255.
• 256.
• 257.
• 258.
• 259.
• 260.
• 261.
• 262.
• 263.
• 264.
• 265.
• 266.
• 267.
• 268.
• 269.
• 270.
• 271.
• 272.
• 273.
• 274.
• 275.
• 276.
• 277.
• 278.
• 279.
• 280.
• 281.
• 282.
• 283.
• 284.
• 285.
• 286.
• 287.
• 288.
• 289.
• 290.
• 291.
• 292.
• 293.
• 294.
• 295.
• 296.
• 297.
• 298.
• 299.
• 300.
• 301.
• 302.
• 303.
• 304.
• 305.
• 306.
• 307.
• 308.
• 309.
• 310.
• 311.
• 312.
• 313.
• 314.
• 315.

#-----------cat  roles/Mysql8_Ubuntu2204_Single/tasks/main.yml 定义了执行tasks中yml的顺序，是入口文件----------------#
- include_vars: vars/os_init_var.yml
- include_tasks: tasks/os_init.yml
- include_tasks: tasks/config_mycnf_init.yml
- include_tasks: tasks/mysql_init.yml

#----------------cat  roles/Mysql8_Ubuntu2204_Single/tasks/os_init.yml 对mysql系统进行初始化---------#
- name: Uninstall existing MySQL packages
  apt:
    name: "{{ item }}"
    state: absent
    purge: yes
  loop:
    - mysql-client
    - mysql-server
    - mysql-common
    - mysql-server-core-5.5
    - mysql-client-5.5
    - mariadb-client
    - mariadb-server
    - mariadb-common

- name: Clean up and update apt
  block:
  - apt:
      update_cache: yes
      autoremove: yes
  - command: apt-get -f install

- name: Install dependencies
  apt:
    name: "{{ item }}"
    state: latest
  loop:
    - libicu70
    - libglib2.0-0
    - libxml2-dev

- name: Download and extract MySQL tarball
  block:
  - name: Ensure /root/software directory exists
    file:
      path: /root/software
      state: directory
  - get_url:
      url: "{{ mysql_tarball_url }}"
      dest: "/root/software/mysql-8.2.0-linux-glibc2.28-x86_64.tar.xz"
  - unarchive:
      src: "/root/software/mysql-8.2.0-linux-glibc2.28-x86_64.tar.xz"
      dest: "{{ mysql_install_dir }}"
      remote_src: yes
  - command: mv "{{ mysql_install_dir }}/mysql-8.2.0-linux-glibc2.28-x86_64" "{{ mysql_install_dir }}/mysql-8.2.0"

- name: Add MySQL user
  user:
    name: mysql
    system: yes
    shell: /sbin/nologin


- name: Create data and logs directories
  file:
    path: "{{ item }}"
    state: directory
    owner: mysql
    group: mysql
  loop:
    - "{{ mysql_data_dir }}"
    - "{{ mysql_logs_dir }}"

- name: Set owner of MySQL directories
  file:
    path: "{{ item }}"
    state: directory
    owner: mysql
    group: mysql
    recurse: yes
  loop:
    - "{{ mysql_install_dir }}/mysql-8.2.0"
  
- name: Create new symlink as mysql user
  shell: 
    cmd: ln -s {{ mysql_install_dir }}/mysql-8.2.0 /usr/local/mysql
    creates: /usr/local/mysql
  become: yes
  become_user: root

- name: Copy MySQL server script to init.d
  copy:
    src: "mysql.server"
    dest: /etc/init.d/mysql
    mode: 0755

#------cat  roles/Mysql8_Ubuntu2204_Single/tasks/config_mycnf_init.yml----------#
#------对mysql配置文件模板进行初始化，mysql的环境变量我在添加的时候失败了这里可以自己在调整下----------#
- name: Get the last two segments of the IP address
  set_fact:
    server_id: "{{ ansible_default_ipv4.address.split('.')[-2:] | join('') }}"

- name: Generate my.cnf from template
  template:
    src: my.cnf.j2
    dest: /etc/my.cnf

- name: Check if MySQL bin path is in PATH in /etc/profile
  shell: grep -q "{{ mysql_bin_path }}" /etc/profile && echo "exists" || echo "not exists"
  register: path_in_profile
- name: Add MySQL bin to PATH
  lineinfile:
    path: /etc/profile
    backrefs: yes
    regexp: '^(export PATH=.*)$'
    line: '\1:{{ mysql_bin_path }}'
  when: "'not exists' in path_in_profile.stdout"
- name: Reload shell for the changes to take effect
  shell: . /etc/profile
  when: "'not exists' in path_in_profile.stdout"
  
#--cat  roles/Mysql8_Ubuntu2204_Single/tasks/mysql_init.yml 对mysql进行初始化(包括密码)，并启动--------#
- name: Initialize MySQL database
  command: >
    /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --user=mysql --initialize

- name: Ensure mysql is in system startup
  command:
    cmd: update-rc.d mysql defaults

- name: Reload systemd manager configuration
  command: systemctl daemon-reload
  become: yes

- name: Start mysql service
  service:
    name: mysql
    state: started

- name: Get initial MySQL root password from log
  shell: "grep 'temporary password' /usr/local/mysql/logs/mysqld-error.log | tail -1 | awk -F'root@localhost: ' '{print $2}'"
  register: mysql_init_passwd

- name: Print initial MySQL root password
  debug:
    msg: "Initial MySQL root password: {{ mysql_init_passwd.stdout }}"


- name: Change MySQL root password
  shell: |
    /usr/local/mysql/bin/mysql --connect-expired-password -u root -p'{{ mysql_init_passwd.stdout }}' -e "
      ALTER USER 'root'@'localhost' IDENTIFIED BY '{{ new_root_password }}';
      FLUSH PRIVILEGES;"
  args:
    executable: /bin/bash
  no_log: false

- name: Create root user for any host
  shell: |
    /usr/local/mysql/bin/mysql -u root -p'{{ new_root_password }}' -e "
      CREATE USER 'root'@'%' IDENTIFIED BY '{{ new_root_password }}';
      GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
      FLUSH PRIVILEGES;"
  args:
    executable: /bin/bash
  no_log: false

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.
• 30.
• 31.
• 32.
• 33.
• 34.
• 35.
• 36.
• 37.
• 38.
• 39.
• 40.
• 41.
• 42.
• 43.
• 44.
• 45.
• 46.
• 47.
• 48.
• 49.
• 50.
• 51.
• 52.
• 53.
• 54.
• 55.
• 56.
• 57.
• 58.
• 59.
• 60.
• 61.
• 62.
• 63.
• 64.
• 65.
• 66.
• 67.
• 68.
• 69.
• 70.
• 71.
• 72.
• 73.
• 74.
• 75.
• 76.
• 77.
• 78.
• 79.
• 80.
• 81.
• 82.
• 83.
• 84.
• 85.
• 86.
• 87.
• 88.
• 89.
• 90.
• 91.
• 92.
• 93.
• 94.
• 95.
• 96.
• 97.
• 98.
• 99.
• 100.
• 101.
• 102.
• 103.
• 104.
• 105.
• 106.
• 107.
• 108.
• 109.
• 110.
• 111.
• 112.
• 113.
• 114.
• 115.
• 116.
• 117.
• 118.
• 119.
• 120.
• 121.
• 122.
• 123.
• 124.
• 125.
• 126.
• 127.
• 128.
• 129.
• 130.
• 131.
• 132.
• 133.
• 134.
• 135.
• 136.
• 137.
• 138.
• 139.
• 140.
• 141.
• 142.
• 143.
• 144.
• 145.
• 146.
• 147.
• 148.
• 149.
• 150.
• 151.
• 152.
• 153.
• 154.
• 155.
• 156.
• 157.
• 158.
• 159.
• 160.
• 161.
• 162.
• 163.

#--cat  roles/Mysql8_Ubuntu2204_Single/templates/my.cnf.j2 mysql配置文件的模板，进行了适度优化--------#
[client]
port = 3306
socket = /usr/local/mysql/mysql.sock
#default-character-set = utf8mb4

[mysql]
#prompt = "\u@mysqldb \R:\m:\s [\d]> "
#no_auto_rehash
#loose-skip-binary-as-hex
#default-character-set = utf8mb4

[mysqld]
port = 3306
server_id = {{ server_id }}
bind-address = 0.0.0.0
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
socket  = /usr/local/mysql/mysql.sock
pid-file = /usr/local/mysql/mysqld.pid
user = mysql

#default_authentication_plugin = mysql_native_password
authentication_policy = MYSQL_NATIVE_PASSWORD

init-connect = 'SET NAMES utf8mb4'
character-set-server = utf8mb4
collation_server = utf8mb4_unicode_ci

skip_name_resolve = 1
# 不区分大小写
lower_case_table_names=1
#skip-networking
back_log = 300

max_connections = {{ max_connections }}
max_user_connections = 5120
max_connect_errors = 6000
open_files_limit = 65535
max_allowed_packet = 500M

log_timestamps = SYSTEM

log-error = /usr/local/mysql/logs/mysqld-error.log
log_error_verbosity = 3

slow_query_log = 1
slow_query_log_file = /usr/local/mysql/logs/mysqld-slow-query.log
long_query_time = 1
log_slow_extra = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
min_examined_row_limit = 30
log_throttle_queries_not_using_indexes = 60
log_queries_not_using_indexes = 1

log_bin = /usr/local/mysql/logs/mysql-binlog
binlog_format = ROW
#binlog_format = mixed
binlog_expire_logs_seconds = 604800
sync_binlog = 1 #MGR环境中由其他节点提供容错性，可不设置双1以提高本地节点性能
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G 
binlog_rows_query_log_events = 1 #配合ROW
gtid_mode = ON
enforce_gtid_consistency = TRUE
log_slave_updates=1 # 事务记录到binlog,否则无法开启并发复制

table_open_cache = {{ table_open_cache }}
max_heap_table_size = 8M
tmp_table_size = {{ tmp_table_size }}


read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 4M
thread_cache_size = {{ thread_cache_size }}
ft_min_word_len = 4

performance_schema = 0
explicit_defaults_for_timestamp
skip-external-locking

default_storage_engine = InnoDB
#default-storage-engine = MyISAM
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = {{ innodb_buffer_pool_size }}
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = {{ innodb_log_file_size }}
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = {{ myisam_sort_buffer_size }}
myisam_max_sort_file_size = 10G

interactive_timeout = 28800
wait_timeout = 28800

[mysqldump]
quick
max_allowed_packet = 500M

[myisamchk]
key_buffer_size = {{ key_buffer_size }}
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.
• 30.
• 31.
• 32.
• 33.
• 34.
• 35.
• 36.
• 37.
• 38.
• 39.
• 40.
• 41.
• 42.
• 43.
• 44.
• 45.
• 46.
• 47.
• 48.
• 49.
• 50.
• 51.
• 52.
• 53.
• 54.
• 55.
• 56.
• 57.
• 58.
• 59.
• 60.
• 61.
• 62.
• 63.
• 64.
• 65.
• 66.
• 67.
• 68.
• 69.
• 70.
• 71.
• 72.
• 73.
• 74.
• 75.
• 76.
• 77.
• 78.
• 79.
• 80.
• 81.
• 82.
• 83.
• 84.
• 85.
• 86.
• 87.
• 88.
• 89.
• 90.
• 91.
• 92.
• 93.
• 94.
• 95.
• 96.
• 97.
• 98.
• 99.
• 100.
• 101.
• 102.
• 103.
• 104.
• 105.
• 106.
• 107.
• 108.
• 109.
• 110.
• 111.
• 112.
• 113.
• 114.
• 115.
• 116.
• 117.
• 118.

#--cat  roles/Mysql8_Ubuntu2204_Single/vars/os_init_var.yml 部署过程中，ansible需要的变量，这些变量是自己定义的--------#
mysql_bin_path: /usr/local/mysql/bin
mysql_install_dir: /usr/local/disk_vdb
mysql_data_dir: "{{ mysql_install_dir }}/mysql-8.2.0/data"
mysql_logs_dir: "{{ mysql_install_dir }}/mysql-8.2.0/logs"
mysql_tarball_url: http://192.168.1.12:9999/work/os_init/mysql/mysql-8.2.0-linux-glibc2.28-x86_64.tar.xz
mysql_port: 3306
# 这一部分用在了mysql配置文件自动根据系统情况对参数优化并使用配置文件模板环境变量写入到配置文件中
mem: "{{ ansible_memtotal_mb }}"
max_connections: "{{ (mem / 3) | int }}"
innodb_buffer_pool_size: "{{ (mem / 3) | int }}M"
innodb_log_file_size: "{{ (mem / 12) | int }}M"
thread_cache_size: "{{ (mem > 1500 and mem <= 2500) | ternary('16', (mem > 2500 and mem <= 3500) | ternary('32', '64')) }}"
myisam_sort_buffer_size: "{{ (mem > 1500 and mem <= 2500) | ternary('16M', (mem > 2500 and mem <= 3500) | ternary('32M', '64M')) }}"
key_buffer_size: "{{ (mem > 1500 and mem <= 2500) | ternary('16M', (mem > 2500 and mem <= 3500) | ternary('64M', '256M')) }}"
tmp_table_size: "{{ (mem > 1500 and mem <= 2500) | ternary('32M', (mem > 2500 and mem <= 3500) | ternary('64M', '128M')) }}"
table_open_cache: "{{ (mem > 1500 and mem <= 2500) | ternary('256', (mem > 2500 and mem <= 3500) | ternary('512', '1024')) }}"
# mysql登录密码
new_root_password: "nicai123!!!A"

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.

# 查看运行状态
root@ubuntu2204-98:~/software# service mysql status
● mysql.service - LSB: start and stop MySQL
     Loaded: loaded (/etc/init.d/mysql; generated)
     Active: active (running) since Thu 2025-08-07 06:48:35 UTC; 1min 14s ago
       Docs: man:systemd-sysv-generator(8)
    Process: 4139 ExecStart=/etc/init.d/mysql start (code=exited, status=0/SUCCESS)
      Tasks: 39 (limit: 4557)
     Memory: 285.0M
        CPU: 2.611s
     CGroup: /system.slice/mysql.service
             ├─4155 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/mysqld.pid
             └─5178 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib>

Aug 07 06:48:32 ubuntu2204-98 systemd[1]: Starting LSB: start and stop MySQL...
Aug 07 06:48:32 ubuntu2204-98 mysql[4139]: Starting MySQL
Aug 07 06:48:35 ubuntu2204-98 mysql[4139]: ... *
Aug 07 06:48:35 ubuntu2204-98 systemd[1]: Started LSB: start and stop MySQL.
root@ubuntu2204-98:~/software# ss -tnlp
State       Recv-Q      Send-Q           Local Address:Port            Peer Address:Port     Process
LISTEN      0           300                    0.0.0.0:3306                 0.0.0.0:*         users:(("mysqld",pid=5178,fd=25))
LISTEN      0           4096             127.0.0.53%lo:53                   0.0.0.0:*         users:(("systemd-resolve",pid=649,fd=14))
LISTEN      0           128                    0.0.0.0:22                   0.0.0.0:*         users:(("sshd",pid=1759,fd=3))
LISTEN      0           128                       [::]:22                      [::]:*         users:(("sshd",pid=1759,fd=4))
LISTEN      0           70                           *:33060                      *:*         users:(("mysqld",pid=5178,fd=20))

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.