系统介质准备：
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>复制
