1.服务目录及外挂站
外挂站点(Ansible会自动从本网站下载需要的软件包)
http://192.168.1.12:9999/work/os_init/jd_move_yd/
--------------------------------------------------
目录结构
root@privateserver-12:~/Nginx_Software/down/work/os_init/jd_move_yd# tree -L 1
.
├── acme.sh
├── kafka_2.12-3.5.0.tgz
├── libaio1_0.3.112-13build1_amd64.deb
├── libmecab2_0.996-14build9_amd64.deb
├── libstdc++6_8.3.0-6_amd64.deb
├── mydumper_0.17.1-1.jammy_amd64.deb
├── mysql-8.2.0-linux-glibc2.28-x86_64.tar.xz
├── Nginx
├── os_ubuntu2204_init_software.tar.gz
└── redis-7.0.0.tar.gz2.部署服务
2.1 Ansible部署Mysql
Ansible目录结构
Ploybooks
# cat playbooks/Mysql8_Ubuntu2204_Single.yaml
- hosts: mysqlsingle
roles:
- ../roles/Mysql8_Ubuntu2204_Singlefiles
# 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[^=]*=\(.*\)#39;
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 0tasks(有4个)
#-----------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=.*)#39;
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: falsetemplates
#--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 = 4Mvars
#--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"3.开始部署
4.测试部署
# 查看运行状态
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))