一、文档介绍

此文档旨在规范服务器上 MySQL 8.0 部署步骤，标准化操作步骤，为后续标准运维提供支撑。

二、部署环境

• 操作系统：Linux（CentOS 7.6）
• MySQL版本：8.0.40

注意：

（1）由于官方不再推出针对 CentOS 非 RPM 包，所以本文档中使用的是官方根据 glibc 版本编译的安装包。

（2）本文档中 CentOS 7.6 glibc 版本默认为 2.7，如果是国产操作系统，或者有重新适配过 glibc 的操作系统，需根据 glibc 版本选择对应的安装包。

三、下载

官网下载地址：https://dev.mysql.com/downloads/mysql/

cd /usr/local/src
wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.40-linux-glibc2.17-x86_64.tar.xz

四、创建用户

cat /etc/passwd | grep mysql
[ $? -ne 0 ] && useradd -s /sbin/nologin -M mysql

五、安装

cd /usr/local/src
tar xf mysql-8.0.40-linux-glibc2.17-x86_64.tar.xz
mv mysql-8.0.40-linux-glibc2.17-x86_64 /usr/local/mysql-8.0.40
cd /usr/local/
ln -s mysql-8.0.40 mysql80
chown -R mysql.mysql mysql-8.0.40

六、配置环境变量

cat <<"EOF" | tee -a /etc/profile
# MySQL
export MYSQL_HOME=/usr/local/mysql80
export PATH=$MYSQL_HOME/bin:$PATH
EOF

source /etc/profile

七、创建目录

默认端口为 3306，请根据实际情况进行替换。

mkdir -p /data/mysql_data/3306/{conf,data,tmp}
mkdir -p /data/logs/mysql/3306

八、配置my.cnf

cd /data/mysql_data/3306/conf
cat <<EOF | tee my.cnf
[client]
socket = /data/logs/mysql/3306/mysql.sock
port = 3306

[mysqld]
user = mysql
port = 3306
server-id = 1

pid-file = /data/logs/mysql/3306/mysql.pid
socket = /data/logs/mysql/3306/mysql.sock
datadir = /data/mysql_data/3306/data
tmpdir = /data/mysql_data/3306/tmp
log-error = /data/logs/mysql/3306/error.log
secure-file-priv = /data/mysql_data/3306/tmp
general_log_file = /data/logs/mysql/3306/general.log

skip-mysqlx = 1
character-set-server = utf8mb4
collation_server = utf8mb4_general_ci
character-set-client-handshake = FALSE
default-time_zone = '+8:00'
sql_mode = ''
lower_case_table_names = 1
skip_name_resolve = 1
# authentication_policy = mysql_native_password
default-storage-engine = INNODB
explicit_defaults_for_timestamp = 1

# read_only = 1
# skip_slave_start = 1
innodb_redo_log_capacity=1073741824
innodb_log_buffer_size = 32M
innodb_max_dirty_pages_pct = 50
innodb_online_alter_log_max_size = 1G
innodb_open_files = 65535
innodb_print_all_deadlocks = 1
innodb_read_io_threads = 48
innodb_spin_wait_delay = 30
innodb_sync_spin_loops = 100
innodb_thread_concurrency = 120
innodb_thread_sleep_delay = 0
innodb_write_io_threads = 48
innodb_autoinc_lock_mode = 2
innodb_flush_method = O_DIRECT
innodb_flush_sync = 0
innodb_io_capacity = 10000
innodb_io_capacity_max = 20000
innodb_buffer_pool_size = 4G

connect_timeout = 10
wait_timeout = 3600
interactive_timeout = 600
lock_wait_timeout = 86400
innodb_lock_wait_timeout = 30
net_read_timeout = 60
net_write_timeout = 60
replica_net_timeout = 60

back_log = 1024
open_files_limit = 65535
max_allowed_packet = 1G
replica_max_allowed_packet = 1G
replica_pending_jobs_size_max = 256M
max_prepared_stmt_count = 1048576
min_examined_row_limit = 100

join_buffer_size = 2M
key_buffer_size = 16M
read_buffer_size = 256K
read_rnd_buffer_size = 2M
sort_buffer_size = 4M
thread_cache_size = 800
max_connections = 3000
max_connect_errors = 99999999
max_heap_table_size = 32M
tmp_table_size = 32M

slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/logs/mysql/3306/slow.log

log-bin = /data/logs/mysql/3306/mysql-bin
max_binlog_size = 256M
binlog_cache_size = 4M
binlog_rows_query_log_events = 1
binlog_expire_logs_seconds = 259200
log_replica_updates = 1
log_bin_trust_function_creators = 1
log_statements_unsafe_for_binlog = 0
log_throttle_queries_not_using_indexes = 60
max_binlog_cache_size = 2G

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

relay_log = /data/logs/mysql/3306/mysql-relay
max_relay_log_size = 256M
relay_log_purge = 1
relay_log_recovery = 1

binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema

replicate-ignore-db = information_schema
replicate-wild-ignore-table = information_schema.%
replicate-ignore-db = performance_schema
replicate-wild-ignore-table = performance_schema.%

gtid_mode = ON
enforce_gtid_consistency = 1
binlog_gtid_simple_recovery = 1

transaction_isolation = READ-COMMITTED

# expire_logs_days = 7

[mysql]
socket = /data/logs/mysql/3306/mysql.sock
default-character-set = utf8mb4

EOF

如果实际环境中使用的是不是3306端口，请执行以下命令进行替换：

sed -i 's/3306/实际端口/g' my.cnf

九、修改目录权限

chown -R mysql.mysql /data/mysql_data
chown -R mysql.mysql /data/logs/mysql

十、初始化数据库

/usr/local/mysql80/bin/mysqld --defaults-file=/data/mysql_data/3306/conf/my.cnf  --initialize-insecure  --user=mysql

十一、创建服务

cat <<EOF | tee /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network-online.target
Wants=network-online.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
Type=simple
TimeoutSec=0
PermissionsStartOnly=true
ExecStart=/usr/local/mysql80/bin/mysqld_safe --defaults-file=/data/mysql_data/3306/conf/my.cnf
EnvironmentFile=-/etc/sysconfig/mysql
LimitNOFILE = 65535
Restart=on-failure
RestartPreventExitStatus=1
Environment=MYSQLD_PARENT_PID=1
PrivateTmp=false
EOF

十二、启动服务

systemctl daemon-reload
systemctl start mysqld
systemctl status mysqld
# 开机启动
systemctl enable mysqld

十三、修改 mysql 命令

由于本文档使用非默认 mysql socket（/tmp/mysql.sock），每次执行 mysql 命令时都需要带上 -S 或 --socket 参数。为简化命令操作，需要配置别名。

echo '
alias mysql="/usr/local/mysql80/bin/mysql -S /data/logs/mysql/3306/mysql.sock"
' >> /etc/bashrc

source /etc/bashrc

十四、修改密码

mysql -uroot

第一次登录不需要密码，登录成功后请修改 root 密码。

MySQL 8.0 的密码插件有变化：

• 默认的是caching_sha2_password

alter user root@'localhost' identified by '新密码';
flush privileges;

• 支持旧版本的 mysql_native_password

alter user root@'localhost' identified with mysql_native_password by '新密码';
flush privileges;

密码修改完成后退出重新登录，需要使用新密码登录

mysql -uroot -p

注意：不建议在 -p 参数后直接填写密码，会被历史记录保存，存在安全隐患。