O
OnTheRoad
V1
2022/09/25阅读:31主题:默认主题
MySQL5.7/8.0 Install for Linux
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 预编译的安装包。 
上传安装包:
[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
作者介绍
O
OnTheRoad
V1