系统介质准备：
centos7.9
mysql8.0.31（oracle每3个月会发布一次新补丁，月份在1/4/7/10）
https://dev.mysql.com/downloads/mysql/
[root@mysql1 mysql]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)
[root@mysql1 mysql]# free -g
total used free shared buff/cache available
Mem: 3 0 0 0 2 2
Swap: 3 0 3
[root@mysql1 mysql]# lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 2
On-line CPU(s) list: 0,1
Thread(s) per core: 1
Core(s) per socket: 2
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 15
Model: 6
Model name: Common KVM processor
Stepping: 1
CPU MHz: 1699.998
BogoMIPS: 3399.99
Hypervisor vendor: KVM
Virtualization type: full
L1d cache: 32K
L1i cache: 32K
L2 cache: 4096K
L3 cache: 16384K
NUMA node0 CPU(s): 0,1
Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx lm constant_tsc nopl xtopology eagerfpu pni cx16 x2apic hypervisor lahf_lm
[root@mysql1 mysql]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 32G 0 disk
├─sda1 8:1 0 1G 0 part /boot
└─sda2 8:2 0 31G 0 part
├─centos-root 253:0 0 27.8G 0 lvm /
└─centos-swap 253:1 0 3.2G 0 lvm [SWAP]
sr0 11:0 1 9.5G 0 rom
系统参数优化准备：
卸载自带mariadb
rpm -aq|grep mari
mariadb-libs-5.5.68-1.el7.x86_64
marisa-0.2.4-4.el7.x86_64
rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64
rpm -e --nodeps marisa-0.2.4-4.el7.x86_64
rpm -aq|grep mari
systemctl stop firewalld
systemctl disable firewalld
/etc/selinux/config修改disabled
在/etc/sysctl.conf中增加如下行,(内存的60%左右，稍微大于mysql配置的内存)
vm.nr_hugepages = 40960
验证大页内存开启
cat /proc/meminfo | grep -i huge
AnonHugePages: 268288 kB
ShmemHugePages: 0 kB
FileHugePages: 0 kB
HugePages_Total: 40960
HugePages_Free: 40960
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
Hugetlb: 83886080 kB
mysql 用户的内存限制
配置MySQL 的内存无限制.请编辑/etc/security/limits.conf 文件.添加以下命令.
mysql hard memlock unlimited
mysql soft memlock unlimited
reboot系统
free -g检查大页内存占用
安装步骤：
1）解压下载好的mysql8.0安装包
tar xvf mysql-8.0.31-linux-glibc2.12-x86_64.tar.xz
2）重命名解压出来的文件夹，移动到目标目录，这里改成mysql
mv ./mysql-8.0.31-linux-glibc2.12-x86_64 /mysql
3)/mysql文件夹下创建data文件夹 存储文件
cd /mysql
mkdir data
mkdir log
4）分别创建用户组以及用户和密码（如果提示已存在说明之前有创建过了）
groupadd mysql
useradd -g mysql mysql
5）、授权刚刚新建的用户
chown -R mysql.mysql /mysql
chmod -R 750 /mysql/data
6）、配置环境，编辑/etc/profile文件
vim /etc/profile
加入环境变量
export PATH=$PATH:/mysql/bin:/mysql/lib
source /etc/profile
7）、编辑my.cnf文件
vi /etc/my.cnf
按下面复制进去就好了，#号开头的不用管，一样，添加完之后，保存文件并退出
[client]
port=3306
socket=/mysql/mysql.sock
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
port=3306
user=mysql
socket=/mysql/mysql.sock
basedir=/mysql
datadir=/mysql/data
lower-case-table-names=1
default_authentication_plugin=mysql_native_password
innodb_buffer_pool_size=2G #注意这个大小建议为50%-70%的物理内存即可，总之建议给操作系统留出30%以上的剩余
slow_query_log=ON
slow_query_log_file=/mysql/log/slow.log
long_query_time=2
log_queries_not_using_indexes=off
max_connections=1000
innodb_flush_log_at_trx_commit=2
character_set_server=utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect=‘SET NAMES utf8mb4’
skip-character-set-client-handshake = true
skip-name-resolve
log-bin=/mysql/data/mysql-bin
#general_log = 1
#general_log_file= /mysql/log/mysql.log
#skip-grant-tables
#skip-log-bin
8）初始化基础信息,得到数据库的初始密码（命令在/mysql/bin下）
mysqld --user=mysql --basedir=/mysql --datadir=/mysql/data/ --initialize
复制出初始密码，保存到本地，后面需要使用到。
9）复制 mysql.server 文件，在/mysql目录下执行
cp ./support-files/mysql.server /etc/init.d/mysql
编辑：vi /etc/init.d/mysql修改
basedir=/mysql
datadir=/mysql/data
10）、赋予权限
chmod +x /etc/init.d/mysql
11）启动数据库，有SUCCESS字眼说明MySQL安装完成
[root@mysql1 mysql]# service mysql start
Starting MySQL.Logging to ‘/mysql/data/mysql1.err’.
. SUCCESS!
12）连接数据库
[root@mysql1 mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.31
Copyright © 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'abcd1234';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye
13.建库建用户
[root@mysql1 mysql]# mysql -h 192.168.207.131 -P 3306 -u root -pabcd1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.31 MySQL Community Server - GPL
Copyright © 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> CREATE user 'mysql'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> alter user 'mysql'@'%' identified with mysql_native_password by 'abcd1234';
Query OK, 0 rows affected (0.00 sec)
mysql> create database jycdb default character set utf8mb4 collate utf8mb4_unicode_ci;
Query OK, 1 row affected (0.01 sec)
mysql> grant all privileges on jycdb.* to "mysql"@"%";
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select user,password_expired from user;
±-----------------±-----------------+
| user | password_expired |
±-----------------±-----------------+
| mysql | N |
| root | N |
| mysql.infoschema | N |
| mysql.session | N |
| mysql.sys | N |
±-----------------±-----------------+
5 rows in set (0.00 sec)
mysql> \q
Bye
14)建表：
[root@mysql1 mysql]# mysql -h 192.168.207.131 -P 3306 -u mysql -pabcd1234 -Djycdb
或
[root@mysql1 mysql]# mysql -h 192.168.207.131 -P 3306 -u mysql -pabcd1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.31 MySQL Community Server - GPL
Copyright © 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| jycdb |
| performance_schema |
±-------------------+
3 rows in set (0.00 sec)
mysql> use jycdb;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table test(id int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test values(1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test;
±-----+
| id |
±-----+
| 1 |
±-----+
1 row in set (0.00 sec)
查看mysql的通用和错误日志：
mysql> show variables like '%general%';
+------------------+------------------------+
| Variable_name | Value |
+------------------+------------------------+
| general_log | OFF |
| general_log_file | /mysql/data/mysql2.log |
+------------------+------------------------+
2 rows in set (0.01 sec)
mysql> SHOW VARIABLES LIKE 'log_error';
+---------------+--------------+
| Variable_name | Value |
+---------------+--------------+
| log_error | ./mysql2.err |
+---------------+--------------+
1 row in set (0.00 sec)
mysql> set @@global.general_log = 1;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like '%general%';
+------------------+------------------------+
| Variable_name | Value |
+------------------+------------------------+
| general_log | ON |
| general_log_file | /mysql/data/mysql2.log |
+------------------+------------------------+
2 rows in set (0.01 sec)
mysql> set @@global.general_log = 0;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like '%general%';
+------------------+------------------------+
| Variable_name | Value |
+------------------+------------------------+
| general_log | OFF |
| general_log_file | /mysql/data/mysql2.log |
+------------------+------------------------+
2 rows in set (0.00 sec)
[root@mysql2 data]# ll mysql2.err
-rw-r-----. 1 mysql mysql 5599 Dec 15 15:36 mysql2.err
[root@mysql2 data]# pwd
/mysql/data
[root@mysql2 data]# ll mysql2.err
-rw-r-----. 1 mysql mysql 5599 Dec 15 15:36 mysql2.err
[root@mysql2 data]# more mysql2.err
2022-12-09T05:32:18.980351Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authe
ntication_policy instead.
2022-12-09T05:32:18.980396Z 0 [System] [MY-010116] [Server] /mysql/bin/mysqld (mysqld 8.0.30) starting as process 10434
2022-12-09T05:32:18.995371Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-12-09T05:32:19.575385Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-12-09T05:32:20.232569Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-12-09T05:32:20.232646Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-12-09T05:32:20.275828Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2022-12-09T05:32:20.275868Z 0 [System] [MY-010931] [Server] /mysql/bin/mysqld: ready for connections. Version: '8.0.30' socket: '/mysql/mysql.sock' port: 3306 My
SQL Community Server - GPL.
2022-12-09T05:36:33.509284Z 12 [Warning] [MY-013130] [Server] Aborted connection 12 to db: 'unconnected' user: 'mysql' host: '192.168.207.132' (init_connect command
failed; diagnostics area: MY-001064 - You have an error in your SQL syntax; check the manual that corr)
2022-12-09T05:36:41.800260Z 13 [Warning] [MY-013130] [Server] Aborted connection 13 to db: 'unconnected' user: 'mysql' host: '192.168.207.132' (init_connect command
failed; diagnostics area: MY-001064 - You have an error in your SQL syntax; check the manual that corr)
2022-12-09T05:39:13.740221Z 15 [Warning] [MY-013130] [Server] Aborted connection 15 to db: 'unconnected' user: 'mysql' host: '192.168.207.132' (init_connect command
failed; diagnostics area: MY-001064 - You have an error in your SQL syntax; check the manual that corr)
2022-12-09T05:39:17.257238Z 16 [Warning] [MY-013130] [Server] Aborted connection 16 to db: 'unconnected' user: 'mysql' host: '192.168.207.132' (init_connect command
failed; diagnostics area: MY-001064 - You have an error in your SQL syntax; check the manual that corr)
2022-12-15T07:08:35.312005Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.30).
2022-12-15T07:08:36.285610Z 0 [System] [MY-010910] [Server] /mysql/bin/mysqld: Shutdown complete (mysqld 8.0.30) MySQL Community Server - GPL.
2022-12-15T07:29:15.481122Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authe
ntication_policy instead.
2022-12-15T07:29:15.481179Z 0 [System] [MY-010116] [Server] /mysql/bin/mysqld (mysqld 8.0.31) starting as process 22919
2022-12-15T07:29:15.504650Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-12-15T07:29:16.191966Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-12-15T07:29:20.321752Z 4 [System] [MY-013381] [Server] Server upgrade from '80030' to '80031' started.
2022-12-15T07:29:29.257460Z 4 [System] [MY-013381] [Server] Server upgrade from '80030' to '80031' completed.
mysqld: File '/mysql/log/slow.log' not found (OS errno 2 - No such file or directory)
2022-12-15T07:29:29.331017Z 0 [ERROR] [MY-011263] [Server] Could not use /mysql/log/slow.log for logging (error 2 - No such file or directory). Turning logging off
for the server process. To turn it on again: fix the cause, then either restart the query logging by using "SET GLOBAL SLOW_QUERY_LOG=ON" or restart the MySQL serve
r.
2022-12-15T07:29:29.413390Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-12-15T07:29:29.413462Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-12-15T07:29:29.448487Z 0 [System] [MY-010931] [Server] /mysql/bin/mysqld: ready for connections. Version: '8.0.31' socket: '/mysql/mysql.sock' port: 3306 My
SQL Community Server - GPL.
2022-12-15T07:29:29.448436Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2022-12-15T07:36:37.058928Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.31).
2022-12-15T07:36:37.951491Z 0 [System] [MY-010910] [Server] /mysql/bin/mysqld: Shutdown complete (mysqld 8.0.31) MySQL Community Server - GPL.
2022-12-15T07:36:38.868210Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authe
ntication_policy instead.
2022-12-15T07:36:38.868267Z 0 [System] [MY-010116] [Server] /mysql/bin/mysqld (mysqld 8.0.31) starting as process 23689
2022-12-15T07:36:38.886615Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-12-15T07:36:39.549292Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-12-15T07:36:40.080015Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-12-15T07:36:40.080125Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-12-15T07:36:40.156571Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2022-12-15T07:36:40.156921Z 0 [System] [MY-010931] [Server] /mysql/bin/mysqld: ready for connections. Version: '8.0.31' socket: '/mysql/mysql.sock' port: 3306 My
SQL Community Server - GPL.
遇到的问题问题：
当mysql版本为8.0.30时，创建用户并赋权之后出现如下报错：
ERROR 2013 (HY000): Lost connection to MySQL server during query
解决办法：
grant super on *.* to 'mysql'@'%';
问题记录如下：
[root@mysql2 data]# mysql -h 192.168.207.132 -P 3306 -u mysql -pabcd1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.30
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id: 16
Current database: *** NONE ***
ERROR 1184 (08S01): Aborted connection 16 to db: 'unconnected' user: 'mysql' host: '192.168.207.132' (init_connect command failed)
mysql> \q
Bye
[root@mysql2 data]# mysql -u root -pabcd1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.30 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant super on *.* to 'mysql'@'%';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> \q
Bye
[root@mysql2 data]# mysql -h 192.168.207.132 -P 3306 -u mysql -pabcd1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.30 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| jycdb |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> use jycdb;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table test (id int);
Query OK, 0 rows affected (0.03 sec)
mysql>
