CentOS安装Mysql8及配置优化

发表于 | 分类于
CentOS安装Mysql8及配置优化

查看版本

1
SELECT VERSION();

使用Yum安装

添加源

1
2
sudo yum localinstall https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
yum list mysql

安装

1
sudo yum install -y --nogpgcheck mysql-community-server

启动MySQL服务：

1
sudo systemctl start mysqld

查找临时生成的root密码：

1
sudo grep 'temporary password' /var/log/mysqld.log

可以看到生成的临时密码

A temporary password is generated for root@localhost: Vd10lgqN5m;G

安全配置MySQL（设置root密码等）：

1
sudo mysql_secure_installation

会让先输入旧密码，再输入两次新密码。

密码要求必须有大小写长度至少为8位。

登录

1
mysql -uroot -p

输入密码登录后

重启

1
sudo systemctl restart mysqld

启动MySQL服务，并设置开机自启：

1
2
sudo systemctl enable mysqld
sudo systemctl start mysqld

修改密码

如果想设置简单一点的密码，可以设置密码策略，但是这个密码策略必须修改过一次密码之后才能设置。

推荐用此方式

1
mysql -uroot -p

连接上以后

1
2
3
4
5
6
7
8
SET GLOBAL validate_password.policy = 0; -- 设置为较低的密码策略
SET GLOBAL validate_password.length = 6;  -- 设置密码长度为6
alter user user() identified by "Cxz1qaz2wsx";

-- 或者
alter user root identified by "Cxz1qaz2wsx";

flush privileges;

注意

上面的user()为当前Mysql登录的用户 也可以换成其它要修改的用户

配置

配置修改

表名/编码/连接数/数据包大小

设置表名不区分大小写/字符编码/连接数

创建文件

1
2
mkdir -p  /var/log/mysql/
vi /var/log/mysql/mysql-slow.log

修改 /etc/my.cnf 

1
vi /etc/my.cnf

添加以下的几行行

1
2
3
4
5
6
7
8
9
[mysqld]
character-set-server=utf8mb4
max_connections = 1000
max_allowed_packet = 256M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

slow_query_log = ON
slow-query-log-file = /var/log/mysql/mysql-slow.log
long_query_time = 2

如果报错可以查看错误

1
vi /var/log/mysqld.log

重启 

1
service mysqld restart

允许远程登录

1
2
3
CREATE USER 'root'@'%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES; 
quit

设置密码永不过期

1
2
ALTER USER 'root'@'%' PASSWORD EXPIRE NEVER;
flush privileges;

赋权限

查看权限

1
SHOW GRANTS FOR 'root'@'%';

赋予所有权限

1
2
3
4
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

密码丢失

按如下处理

1
vi /etc/my.cnf

在[mysqld]的段中加上一句：skip-grant-tables
例如：

1
2
[mysqld]
skip-grant-tables

保存并且退出vi。

重启

1
service mysqld restart

这时候重新连接数据库就不用输入密码了。

卸载

1
2
3
chkconfig mysqld off
service mysqld stop
yum remove mysql-community-server

配置优化

创建日志目录

1
2
mkdir -p  /var/log/mysql/
vi /var/log/mysql/mysql-slow.log

添加的配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[mysqld]
# base config
character-set-server=utf8mb4
max_connections = 1000
max_allowed_packet = 256M
lower_case_table_names=1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
default_authentication_plugin=mysql_native_password

# slow_query
slow_query_log = ON
slow-query-log-file = /var/log/mysql/mysql-slow.log
long_query_time = 2

# performance optimization
innodb_flush_log_at_trx_commit=2
sort_buffer_size = 4M
thread_cache_size = 128
innodb_buffer_pool_size = 5000M
group_concat_max_len = 102400

[client]
default-character-set=utf8mb4

基本配置

  • max_connections=1000:设置 MySQL 允许的最大连接数为 1000。这意味着最多可以同时有 1000 个客户端连接到 MySQL 服务器。
  • max_allowed_packet=256M:设置 MySQL 服务器允许的最大数据包大小为 256MB。这一配置决定了单个 SQL 语句、数据行或结果集允许的最大字节数。
  • lower_case_table_names=1:表名不区分大小写。
  • sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES:放宽了SQL限制，在SQL报错时配置。
  • default_authentication_plugin=mysql_native_password:使用旧版本默认的身份验证机制，兼容性更好。

字符集配置

1
2
3
4
5
[mysqld]
character-set-server=utf8mb4

[client]
default-character-set=utf8mb4

这些配置都是用于设置 MySQL 数据库的字符集，具体作用如下：

  • [mysqld] 下的 character-set-server=utf8mb4：

    这个配置项用于设置 MySQL 服务器的默认字符集为 utf8mb4。

    utf8mb4 是一种支持所有 Unicode 字符的字符集，尤其适合存储多语言内容和表情符号（emoji）。

    通过设置这个选项，所有新创建的数据库、表和字段的默认字符集都将采用 utf8mb4。

  • [client] 下的 default-character-set=utf8mb4：

    用于设置 MySQL 客户端的默认字符集为 utf8mb4。

    确保在命令行工具（如 mysql 命令行客户端）中输入和输出数据时使用相同的字符集。

这样做的好处是可以避免因为字符集不匹配而导致的数据出错或乱码。

这两个配置的作用

1
2
3
4
5
[mysql]
default-character-set=utf8mb4

[client]
default-character-set=utf8mb4

具体来说：

  • [mysql]：这个配置主要影响具体的 MySQL 命令行客户端和相关工具在连接到 MySQL 服务器时所使用的字符集。
  • [client]：这个配置适用于所有 MySQL 客户端程序，它也指定了连接时使用的默认字符集。

如果你希望在连接 MySQL 时都使用 utf8mb4，只需要在 [client] 中设置一次就足够了

性能优化配置

1
2
3
4
5
innodb_flush_log_at_trx_commit=2
sort_buffer_size = 4M
thread_cache_size = 128
innodb_buffer_pool_size = 5000M
group_concat_max_len = 102400

作用

  1. innodb_flush_log_at_trx_commit=2
  • 作用：此设置控制事务日志的刷新策略。值为 2 表示每次事务提交时，日志只会写到日志文件，而不会立即刷新到磁盘。这提高了性能，但在崩溃情况下可能会丢失最近的部分事务。
  • 用途：适合性能要求较高的场景，但要注意可能的数据丢失风险。
  1. sort_buffer_size=4M
  • 作用：这个参数指定每个线程用于排序操作的缓冲区大小，默认设置为 4MB。在执行 ORDER BY 或 GROUP BY 查询时，MySQL 会使用这个缓冲区。
  • 用途：增大该值可以提高大型数据集的排序效率，特别是在频繁进行排序操作时。需要根据实际需求和系统内存来调整该值。
  1. thread_cache_size=128
  • 作用：控制 MySQL 为新连接缓存的线程数量。该设置表明最多可以缓存 128 条线程。如果当前活跃线程少于这个数量，MySQL 将重用这些线程，而不是每次都创建新线程。
  • 用途：提高连接处理的效率，减少频繁创建和销毁线程的开销，适合高并发的应用场景。
  1. innodb_buffer_pool_size=5000M
  • 作用：指定 InnoDB 存储引擎用于缓冲数据和索引的内存池大小，总共 5000MB（5GB）。这是提升 InnoDB 表性能的关键参数。
  • 用途：一个较大的缓冲池可以显著提高读写性能，能够缓存更多的数据和索引，从而减少磁盘 I/O。recommended to set this value to approximately 70-80% of your available memory if the server is dedicated to MySQL.
  1. group_concat_max_len=102400
  • 作用：该参数定义了 GROUP_CONCAT 函数返回的最大长度，设置为 102400 字节。如果结果超过该值，将会被截断。
  • 用途：适合需要处理大数据量结果的查询，增加这个值可以避免在拼接字符串时信息丢失。

防火墙配置

添加信任规则

打开文件

1
vim /etc/sysconfig/iptables

添加规则

1
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

重启防火墙

1
service iptables restart

常见问题

默认文件存储位置

数据库文件存储位置

1
show global variables like "%datadir%";

默认路径

1
cd /var/lib/mysql

日志文件位置

1
vi /var/log/mysqld.log

修改默认位置请参看https://www.psvmc.cn/article/2019-02-13-mysql-datadir.html

启动失败

1
2
[root@root ~]# /etc/init.d/mysqld start
Starting mysqld (via systemctl):  Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

解决方式

1
2
mkdir -p /var/run/mysqld/
chown mysql.mysql /var/run/mysqld/

长时间未访问 断开

mysql有一个连接超时时间的概念。。。查询此项目的数据库的连接超时时间为28800秒，即为8小时。。

1
2
3
4
5
6
7
mysql> show global variables like 'wait_timeout';
 +---------------+-------+
 | Variable_name | Value |
 +---------------+-------+
 | wait_timeout  | 28800 |
 +---------------+-------+
 1 row in set

于是不难得知，肯定是由于项目长时间没有请求数据库，数据库过了8小时和这个连接超时时间之后，就会断开连接。。

而我们的项目使用的是c3p0的连接池，，过了8小时后，连接池中的连接已经被mysql断开了，即连接失效。。

但是c3p0认为此连接却依然有效，此时当我们发请求请求数据库中的数据时,由于连接失效，并不能去连接数据库操纵数据，所以服务器会抛出一个500的错误

问题的原因已经找到，那我们该怎么解决呢？？

其实解决起来很简单的。。

先说第一种办法吧，就是将数据库的连接超时时间设置大一点，

1
2
msyql> set global wait_timeout=1814400;
msyql> set global interactive_timeout=1814400;

当然这种办法我并不推荐，，这个办法不太好，弊端太多了 比如占用数据库资源，关键是这种办法并不能彻底根治mysql连接断开这种情况

所以我推荐第二种办法:设置c3p0隔多少时间自动检测与数据库的连接，如果断开则自动重连

1
2
3
4
5
6
7
8
9
<bean id="pooledDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">  
    <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>  
    <property name="driverClass" value="${jdbc.driverClass}"></property>  
    <property name="user" value="${jdbc.user}"></property>  
    <property name="password" value="${jdbc.password}"></property>  
	//下面两个属性就是设置c3p0隔28800秒自动检测与数据库的连接（28800也是mysql的默认的连接超时时间）  
    <property name="testConnectionOnCheckin" value="true"></property>  
    <property name="idleConnectionTestPeriod" value="28800"></property>  
</bean>