2.1. 主机设置

2.1.1. 禁用 THP（Transparent Huge Page）、NUMA

THP（Transparent Huge Page，透明大页）的内存分配方式为按需动态分配，不适合内存密集型的数据库服务器，因此需要禁用该特性。

针对专用 MySQL 服务器，建议禁用 NUMA 特性。

查看透明大页

~]# cat /sys/kernel/mm/transparent_hugepage/defrag
[always] madvise never

~]# cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never

never 表示已禁用 THP。

禁用 THP、NUMA

将 numa=off transparent_hugepage=never 追加至 /etc/default/grub 文件的 GRUB_CMDLINE_LINUX 末尾。

# 1. 修改启动文件
~]# cp /etc/default/grub /etc/default/grub.bak
~]# vi /etc/default/grub
GRUB_CMDLINE_LINUX="crashkernel=auto rd.lvm.lv=ol/root rd.lvm.lv=ol/swap rhgb quiet numa=off transparent_hugepage=never"

# 2. 生成新启动文件
~]# cp /boot/grub2/grub.cfg /boot/grub2/grub.cfg.bak
~]# cp /boot/efi/EFI/redhat/grub.cfg /boot/efi/EFI/redhat/grub.cfg.bak

~]# grub2-mkconfig -o /boot/efi/EFI/redhat/grub.cfg
Generating grub configuration file ...
Found linux image: /boot/vmlinuz-3.10.0-1127.el7.x86_64
Found initrd image: /boot/initramfs-3.10.0-1127.el7.x86_64.img
Found linux image: /boot/vmlinuz-0-rescue-2b736cc499234bc4953ba55d0dd9288c
Found initrd image: /boot/initramfs-0-rescue-2b736cc499234bc4953ba55d0dd9288c.img
done

2.1.2. 禁用 SE Linux

# 1. 查看 SELinux
[root@localhost mysql]# getenforce
Disabled

# 2. 禁用 SELinux
[root@localhost ~]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/sysconfig/selinux

2.1.3. 资源限制

[root@localhost ~]# cat >> /etc/security/limits.conf << EOF
# for mysql
mysql   soft    nproc   2047
mysql   hard    nproc   16384
mysql   soft    nofile  65535
mysql   hard    nofile  65535
EOF

2.1.4. 创建用户及目录

root 用户执行

groupadd -g 1001 mysql && useradd -g mysql -u 1001 mysql

mkdir -p /mysql/{database,socket,pid,mysql-tmp,mysql-log,redo-log,undo-log,bin-log,relay-log}

chown -R mysql:mysql /mysql
chmod -R 755 /mysql

[root@localhost local]# tree -L 2 /mysql/
/mysql/
├── bin-log
├── database
├── mysql-log
├── mysql-tmp
├── pid
├── redo-log
├── relay-log
├── socket
└── undo-log

9 directories, 0 files

2.1.5. 安装系统依赖包

yum install -y gcc gcc-c++ kernel-devel ntp vim-enhanced flex bison autoconf make automake bzip2-devel ncurses-devel zlib-devel libjpeg-devel libpng-devel libtiff-devel freetype-devel libXpm-devel gettext-devel  pam-devel libtool libtool-ltdl openssl openssl-devel fontconfig-devel libxml2-devel curl-devel  libicu libicu-devel libmcrypt libmcrypt-devel libmhash libmhash-devel pcre-devel libtool-libs gd file patch mlocate diffutils readline-devel glibc-devel glib2-devel libcap-devel libaio

2.2. MySQL 安装

2.2.1. 安装 MySQL 二进制包

root 用户执行

rpm -qa |grep -E 'mysql|mariadb'
 # 卸载
rpm -e --nodeps $(rpm -qa |grep -E 'mysql|mariadb')

安装包下载地址：https://dev.mysql.com/downloads/mysql/，选择 RHEL7 预编译的安装包。 ![](vx_images/67153518220843.png =600x)

上传安装包：

[root@localhost ~]# export VERSION=5.7.22
[root@localhost ~]# wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-${VERSION}-el7-x86_64.tar.gz

[root@localhost ~]# export VERSION=8.0.30
[root@localhost ~]# wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-${VERSION}-el7-x86_64.tar.gz

tar -xzvf mysql-${VERSION}-el7-x86_64.tar.gz

mv mysql-${VERSION}-el7-x86_64 /usr/local/
chown -R mysql:mysql /usr/local/mysql-${VERSION}-el7-x86_64
chmod -R 755 /usr/local/mysql-${VERSION}-el7-x86_64

ln -s /usr/local/mysql-${VERSION}-el7-x86_64 /usr/local/mysql
chown -R mysql:mysql /usr/local/mysql
chmod -R 755 /usr/local/mysql

ls -l /usr/local/|grep mysql

lrwxr-xr-x. 1 mysql mysql   34 Aug  2 18:49 mysql -> /usr/local/mysql-8.0.28-el7-x86_64
drwxr-xr-x. 9 mysql mysql 4096 Aug  2 18:45 mysql-8.0.28-el7-x86_64

2.2.2. 准备配置文件

创建 MySQL 配置文件：

cp /usr/local/mysql/support-files/my-medium.cnf  /etc/my.cnf

MEMTOTAL=$(grep MemTotal /proc/meminfo|awk '{print $2}')
INNODB_BUFFER=$(( MEMTOTAL * 1024 * 8 / 10 ))

2.2.2.1. MySQL 8.0 配置文件

主要关注的重点参数如下：

innodb_buffer_pool_chunk_size 默认为 128M。
innodb_buffer_pool_size 的值，设置为物理内存的 80%，同时应为 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的整数倍。若不等于整数倍的值，则 MySQL 自动将 innodb_buffer_pool_size 调整为 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的整数倍。
若应用类型为并发较高的 OLTP，可适当调高 innodb_buffer_pool_instances（默认为 8）到 16 或 32 甚至 64，减少会话间内存争用；
若应用类型为并发较低的 OLAP，可适当调高 innodb_buffer_pool_chunk_size（默认为 128MB），以提高内存分配效率。

cat >  /etc/my.cnf << EOF
[mysqld]
bind-address=0.0.0.0
port= 3306
user= mysql
basedir=/usr/local/mysql
datadir=/mysql/database/
socket=/mysql/socket/mysql.sock
default_storage_engine=InnoDB

tmpdir=/mysql/mysql-tmp/
log-error=/mysql/mysql-log/mysql.err
pid-file=/mysql/pid/mysql.pid
authentication_policy = mysql_native_password   # MySQL 5.7 无此参数

###  Server Config  ###
server-id= 100
autocommit = 1
character_set_server= utf8mb4
collation-server = utf8mb4_general_ci
skip_name_resolve = 1
open_files_limit = 65535
max_connections = 3000
max_connect_errors = 100
interactive_timeout = 18800
wait_timeout = 18800
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp=true
sql_mode ="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"
table_open_cache = 4000
max_allowed_packet = 32M
join_buffer_size = 128M
tmp_table_size = 128M
max_heap_table_size = 128M
read_buffer_size = 16M          # 适用 MyISAM 存储引擎
read_rnd_buffer_size = 32M
sort_buffer_size = 32M
lower_case_table_names= 1


### slow query config ###
slow_query_log = on
long_query_time = 600
slow_query_log_file =/mysql/mysql-log/slowquery.log
# log_queries_not_using_indexes = true    # 将不使用索引的查询记录到慢查询日志中
# min_examined_row_limit = 100            # 检索行数低于参数值的SQL，不记录到慢查询日志中。

###  binlog config ###
# gtid_mode = on
# enforce_gtid_consistency = on
innodb-file-per-table = 1
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

log-bin=/mysql/bin-log/mysql-bin
binlog_format = row
binlog_expire_logs_seconds = 172800
max_binlog_size = 1G

# binlog_ignore_db = mysql
# binlog_ignore_db = information_schema
# binlog_ignore_db = performation_schema
# binlog_ignore_db = sys

### InnoDB Redo Log Config ###
innodb_log_group_home_dir = /mysql/redo-log/
innodb_log_files_in_group = 5
innodb_log_file_size = 1G

innodb_undo_directory =  /mysql/undo-log/
#innodb_page_size = 16K

# 0.8*Physical Memory for the dedicated MySQL Server，be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances
innodb_buffer_pool_size = 2560M
innodb_buffer_pool_instances = 16

innodb_log_buffer_size = 100M
#innodb_thread_concurrency = 64

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

[client]
default-character-set=utf8mb4
socket=/mysql/socket/mysql.sock
### client user config ###
#host = localhost
#user = root
#password = 'syzx123'
[mysqld_safe]
socket=/mysql/socket/mysql.sock
log-error=/mysql/mysql-log/mysql.err
pid-file=/mysql/pid/mysql.pid
EOF

2.2.2.2. MySQL 5.7 配置文件

export MEMTOTAL=$(grep MemTotal /proc/meminfo|awk '{print $2}')
export INNODB_BUFFER=$(( MEMTOTAL * 1024 * 8 / 10 ))
echo ${INNODB_BUFFER}


cat >  /etc/my.cnf << EOF
[mysqld]
bind-address=0.0.0.0
port= 5306
user= mysql
basedir=/usr/local/mysql
datadir=/mysql/database/
socket=/mysql/socket/mysql.sock
tmpdir=/mysql/mysql-tmp/
log-error=/mysql/mysql-log/mysql.err
pid-file=/mysql/pid/mysql.pid
default_authentication_plugin= mysql_native_password

###  Server Config  ###
server-id= 100
autocommit = 1
character_set_server= utf8mb4
collation-server = utf8mb4_general_ci
skip_name_resolve = 1
open_files_limit = 65535
max_connections = 3000
max_connect_errors = 100
interactive_timeout = 18800
wait_timeout = 18800
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"
table_open_cache = 4000
max_allowed_packet = 16M
join_buffer_size = 128M
tmp_table_size = 128M
max_heap_table_size = 128M
read_buffer_size = 16M
read_rnd_buffer_size = 32M
sort_buffer_size = 32M
lower_case_table_names= 1
explicit_defaults_for_timestamp=true

### slow query config ###
slow_query_log = on
long_query_time = 600
slow_query_log_file =/mysql/mysql-log/slowquery.log

###  binlog config ###
#gtid_mode = on
#enforce_gtid_consistency = on
innodb-file-per-table = 1
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

log-bin=/mysql/bin-log/mysql-bin
binlog_format = row
expire_logs_days = 1           # binlog 保留 1 天
max_binlog_size = 1G
binlog_ignore_db = mysql
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys

### innodb config ###
innodb_log_files_in_group = 5
innodb_log_group_home_dir = /mysql/redo-log/
innodb_undo_directory =  /mysql/undo-log/
#innodb_page_size = 16K

# 0.8*Physical Memory for the dedicated MySQL Server，be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances
innodb_buffer_pool_size = 2560M
innodb_buffer_pool_instances = 20
innodb_log_file_size = 1G
innodb_log_buffer_size = 100M
#innodb_thread_concurrency = 64

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

[client]
default-character-set=utf8mb4
socket=/mysql/socket/mysql.sock
### client user config ###
#host = localhost
#user = root
#password = 'syzx123'
[mysqld_safe]
socket=/mysql/socket/mysql.sock
log-error=/mysql/mysql-log/mysql.err
pid-file=/mysql/pid/mysql.pid
EOF

chown -R mysql:mysql /etc/my.cnf
chmod -R 644 /etc/my.cnf

2.2.3. 数据库初始化

初始化 MySQL 数据库（mysql 操作）：

## mysql_install_db             # 5.6 初始化命令
## mysqld --initialize          # 5.7 8.0 初始化命令，需要看错入日志查密码
## mysqld --initialize-insecure # 5.7 8.0 初始化命令，空密码

su - mysql
cd /usr/local/mysql/bin
./mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/ --datadir=/mysql/database/ --user=mysql --initialize

## 查看 root 密码：
~]$ more /mysql/mysql-log/mysql.err
2022-08-02T11:44:27.644104Z 0 [Warning] [MY-010140] [Server] Could not increase number of max_open_files to more than 1024 (request: 65535)
2022-08-02T11:44:27.644122Z 0 [Warning] [MY-010141] [Server] Changed limits: max_connections: 214 (requested 3000)
2022-08-02T11:44:27.644140Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 400 (requested 4000)
2022-08-02T11:44:27.648255Z 0 [System] [MY-013169] [Server] /usr/local/mysql-8.0.30-el7-x86_64/bin/mysqld (mysqld 8.0.30) initializing of server in progress as process 9687
2022-08-02T11:44:27.660286Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute in
nodb_redo_log_capacity=5368709120. Please use innodb_redo_log_capacity instead.
2022-08-02T11:44:27.661900Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-08-02T11:44:28.825955Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-08-02T11:44:29.947660Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: f9l=t,8jyGvM

lUaf/?t8#%mJ 配置环境变量（opsuser 操作）

sudo vi /etc/profile
# 在文件尾追加：
export MYSQL_HOME=/usr/local/mysql
export MYSQL_PATH=${MYSQL_HOME}/bin:${MYSQL_HOME}/lib
export PATH=$PATH:${MYSQL_HOME}/bin

2.2.4. 开启自启

配置 mysql 数据库开机自启动（opsuser 操作）

sudo cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
sudo chmod +x /etc/init.d/mysqld
sudo vi /etc/init.d/mysqld

#修改basedir和datadir(指定到实际位置如果不是/usr/local/mysql/和/usr/local/mysql/data)
....
# If you change base dir, you must also change datadir. These may get
# overwritten by settings in the MySQL configuration files.

basedir=/usr/local/mysql
datadir=/mysql/database/
....

sudo chkconfig --add mysqld
sudo chkconfig --list

2.2.5. 启停数据库

mysql 数据库启停测试（opsuser 操作）：

sudo systemctl start mysqld
sudo systemctl stop mysqld
sudo systemctl status mysqld
sudo systemctl enable mysqld  # 开机自启

# 或
/etc/init.d/mysqld start
/etc/init.d/mysqld stop
/etc/init.d/mysqld start

[root@sjzx ~]# ps -ef|grep mysql
root     22910     1  0 08:46 ?        00:00:00 /bin/sh /usr/local/mysql8.0.28/bin/mysqld_safe --datadir=/mysql/database/ --pid-file=/mysql/pid/mysql.pid
mysql    23572 22910  3 08:46 ?        00:29:00 /usr/local/mysql8.0.28/bin/mysqld --basedir=/usr/local/mysql8.0.28 --datadir=/mysql/database --plugin-dir=/usr/local/mysql8.0.28/lib/plugin --usermysql --log-error=/mysql/mysql-log/mysql.err --open-files-limit=65535 --pid-file=/mysql/pid/mysql.pid --socket=/mysql/socket/mysql.sock --port=5306
root     25472 25450  0 21:12 pts/0    00:00:00 grep --color=auto mysql

2.2.6. 修改初始密码

连接 MySQL 数据库并修改 root 用户密码（mysql 操作）：

[mysql@sjzx ~]$ mysql -uroot -p'f9l=t,8jyGvM' -P5306
mysql> use mysql
No connection. Trying to reconnect...
Connection id:    10
Current database: *** NONE ***

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> alter user user() identified by "root";
Query OK, 0 rows affected (0.01 sec)

mysql> alter user 'root'@'localhost' identified by 'root' password expire never;
mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

mysql> update mysql.user set host = '%' where user = 'root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

2.2.7. 用户管理

CREATE USER 'system'@'%' IDENTIFIED WITH mysql_native_password BY 'system';

CREATE USER 'admin'@'192.168.3.22_' IDENTIFIED WITH mysql_native_password by 'admin';

flush privileges;

2.2.8. 忘记密码

停止数据库，并以“绕过授权”方式启动数据库

systemctl stop mysqld

su - mysql
cd /usr/local/mysql/bin
mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables --skip-networking &

连接数据库，重置密码

su - mysql
mysql -uroot
mysql> flush privileges;
mysql> alter user root identified with mysql_native_password by "root";
mysql> flush privileges;

重启数据库

su - mysql
cd /usr/local/mysql/bin
mysqladmin -uroot -p shutdown
su - root
systemctl start mysqld

2.3. 安装 FAQ

2.3.1. 华为 openEuler 22.03 踩坑

openEuler 22.03 建议部署通用版的二进制包 mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz。部署完毕在通过 mysql cli 连接服务器的时候，可能会错找不到 libtinfo.so.5。

解决方案：

ln -s /usr/lib64/libtinfo.so.6.3  /usr/lib64/libtinfo.so.5