夜雨聆风
MySQL 8.0 部署文档
一、文档介绍
此文档旨在规范服务器上 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/srcwget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.45-linux-glibc2.17-x86_64.tar.xz
四、创建用户
cat /etc/passwd | grep mysql[ $? -ne 0 ] && useradd -s /sbin/nologin -M mysql
五、安装
cd /usr/local/srctar xf mysql-8.0.45-linux-glibc2.17-x86_64.tar.xzmv mysql-8.0.45-linux-glibc2.17-x86_64 /usr/local/mysql-8.0.45cd /usr/local/ln -s mysql-8.0.45 mysql80chown -R mysql.mysql mysql-8.0.45
六、配置环境变量
cat <<"EOF" | tee -a /etc/profile# MySQLexport MYSQL_HOME=/usr/local/mysql80export PATH=$MYSQL_HOME/bin:$PATHEOFsource /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/confcat <<EOF | teemy.cnf[client]socket = /data/logs/mysql/3306/mysql.sockport = 3306[mysqld]user = mysqlport = 3306server-id = 1pid-file = /data/logs/mysql/3306/mysql.pidsocket = /data/logs/mysql/3306/mysql.sockdatadir = /data/mysql_data/3306/datatmpdir = /data/mysql_data/3306/tmplog-error = /data/logs/mysql/3306/error.logsecure-file-priv = /data/mysql_data/3306/tmpgeneral_log_file = /data/logs/mysql/3306/general.logskip-mysqlx = 1character-set-server = utf8mb4collation_server = utf8mb4_general_cicharacter-set-client-handshake = FALSEdefault-time_zone = '+8:00'sql_mode = ''lower_case_table_names = 1skip_name_resolve = 1# authentication_policy = mysql_native_passworddefault-storage-engine = INNODBexplicit_defaults_for_timestamp = 1# read_only = 1# skip_slave_start = 1innodb_redo_log_capacity=1073741824innodb_log_buffer_size = 32Minnodb_max_dirty_pages_pct = 50innodb_online_alter_log_max_size = 1Ginnodb_open_files = 65535innodb_print_all_deadlocks = 1innodb_read_io_threads = 48innodb_spin_wait_delay = 30innodb_sync_spin_loops = 100innodb_thread_concurrency = 120innodb_thread_sleep_delay = 0innodb_write_io_threads = 48innodb_autoinc_lock_mode = 2innodb_flush_method = O_DIRECTinnodb_flush_sync = 0innodb_io_capacity = 10000innodb_io_capacity_max = 20000innodb_buffer_pool_size = 4Gconnect_timeout = 10wait_timeout = 28800interactive_timeout = 28800lock_wait_timeout = 86400innodb_lock_wait_timeout = 30net_read_timeout = 60net_write_timeout = 60replica_net_timeout = 60back_log = 1024open_files_limit = 65535max_allowed_packet = 1Greplica_max_allowed_packet = 1Greplica_pending_jobs_size_max = 256Mmax_prepared_stmt_count = 1048576min_examined_row_limit = 100join_buffer_size = 2Mkey_buffer_size = 16Mread_buffer_size = 256Kread_rnd_buffer_size = 2Msort_buffer_size = 4Mthread_cache_size = 800max_connections = 3000max_connect_errors = 99999999max_heap_table_size = 32Mtmp_table_size = 32Mslow_query_log = 1long_query_time = 1slow_query_log_file = /data/logs/mysql/3306/slow.loglog-bin = /data/logs/mysql/3306/mysql-binmax_binlog_size = 256Mbinlog_cache_size = 4Mbinlog_rows_query_log_events = 1binlog_expire_logs_seconds = 259200log_replica_updates = 1log_bin_trust_function_creators = 1log_statements_unsafe_for_binlog = 0log_throttle_queries_not_using_indexes = 60max_binlog_cache_size = 2Ginnodb_flush_log_at_trx_commit = 1sync_binlog = 1relay_log = /data/logs/mysql/3306/mysql-relaymax_relay_log_size = 256Mrelay_log_purge = 1relay_log_recovery = 1binlog-ignore-db = information_schemabinlog-ignore-db = performance_schemareplicate-ignore-db = information_schemareplicate-wild-ignore-table = information_schema.%replicate-ignore-db = performance_schemareplicate-wild-ignore-table = performance_schema.%gtid_mode = ONenforce_gtid_consistency = 1binlog_gtid_simple_recovery = 1transaction_isolation = READ-COMMITTED# expire_logs_days = 7[mysql]socket = /data/logs/mysql/3306/mysql.sockdefault-character-set = utf8mb4EOF
如果实际环境中使用的是不是3306端口，请执行以下命令进行替换：
sed -i 's/3306/实际端口/g' my.cnf九、修改目录权限
chown -R mysql.mysql /data/mysql_datachown -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 ServerDocumentation=man:mysqld(8)Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.htmlAfter=network-online.targetWants=network-online.targetAfter=syslog.target[Install]WantedBy=multi-user.target[Service]User=mysqlGroup=mysqlType=simpleTimeoutSec=0PermissionsStartOnly=trueExecStart=/usr/local/mysql80/bin/mysqld_safe --defaults-file=/data/mysql_data/3306/conf/my.cnfEnvironmentFile=-/etc/sysconfig/mysqlLimitNOFILE = 65535Restart=on-failureRestartPreventExitStatus=1Environment=MYSQLD_PARENT_PID=1PrivateTmp=falseEOF
十二、启动服务
systemctl daemon-reloadsystemctl start mysqldsystemctl 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"alias mysqldump="/usr/local/mysql80/bin/mysqldump -S /data/logs/mysql/3306/mysql.sock"' >> /etc/bashrcsource /etc/bashrc
十四、修改密码
mysql -uroot第一次登录不需要密码，登录成功后请修改 root 密码。
(一) 修改本地 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 参数后直接填写密码，会被历史记录保存，存在安全隐患。
(二) 创建所有权限 root 用户
-
默认的是 caching_sha2_password
create user root@'%' identified by '新密码';flush privileges;
-
支持旧版本的 mysql_native_password
create user root@'%' identified with mysql_native_password by '新密码';flush privileges;
分配权限
grant all privileges on *.* to root@'%' with grant option;
