数据库集群系列（一）：MySQL 5.7.44数据库安装及初始化配置、最全启动报错解决方案【测试成功】

一、安装MySQL并初始化配置    

1、配置本地yum源

[mysql]
name=mysql
baseurl=https://mirrors.cloud.tencent.com/mysql/yum/mysql-5.7-community-el7-x86_64/
enabled=1
gpgcheck=0                                                                                                                                                

2、执行yum安装，需先上车该rpm文件

yum -y install mysql-community-server-5.7.44-1.el7.x86_64.rpm

3、执行启动

systemctl start mysqld
systemctl stop mysqld
systemctl status mysqld

二、初始化密码并登陆MySQL    

 1、修改my.cnf配置文件

        添加配置内容：skip-grant-tables

 2、跳过权限授权表启动

whereis mysqld
/usr/sbin/mysqld --skip-grant-tables --skip-networking --user=root

 3、重置root密码



mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)


mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

以上命令报错，可使用：

mysql> UPDATE user SET authentication_string=PASSWORD('MEcTOdwlsA') WHERE User='root';
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MEcTOdwlsA';
Query OK, 0 rows affected (0.00 sec)
mysql>FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

 4、去除重置密码参数并重新启动

    启动失败出现报错，无法正常启动：

systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.


User
[root@JQ-Node1 etc]# systemctl status mysqld -l
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: activating (start-pre) since 五 2023-12-22 11:11:41 CST; 175ms ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 9997 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=1/FAILURE)
Main PID: 3743 (code=exited, status=0/SUCCESS); : 10044 (mysqld_pre_syst)
Tasks: 2
CGroup: system.slice/mysqld.service
└─control
├─10044 bin/bash usr/bin/mysqld_pre_systemd
└─10061 usr/bin/python -Es usr/sbin/semanage fcontext -a -e var/lib/mysql var/lib/mysql-files


12月 22 11:11:41 JQ-Node1 systemd[1]: Starting MySQL Server...

查看日志：tail -f var/log/mysqld.log

2023-12-22T11:12:56.546492+08:00 0 [Note] - '::' resolves to '::';
2023-12-22T11:12:56.546495+08:00 0 [Note] Server socket created on IP: '::'.
2023-12-22T11:12:56.546525+08:00 0 [ERROR] Could not open unix socket lock file var/lib/mysql/mysql.sock.lock.
2023-12-22T11:12:56.546528+08:00 0 [ERROR] Unable to setup unix socket lock file.
2023-12-22T11:12:56.546531+08:00 0 [ERROR] Aborting

分析：由于 MySQL 无法创建或打开 Unix 套接字锁文件 /var/lib/mysql/mysql.sock.lock
导致

解决：修改权限并重启

# ls -alh var/lib/mysql/mysql.sock
srwxrwxrwx 1 root root 0 12月 22 11:01 var/lib/mysql/mysql.sock
#修改权限
sudo chmod -R 755 var/lib/mysql
sudo chown -R mysql:mysql var/lib/mysql

再次连接，成功登陆：

# mysql -uroot -p123456
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 3
Server version: 5.7.44 MySQL Community Server (GPL)


Copyright (c) 2000, 2023, 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> 

三、MySQL自定义配置初始化启动常见问题

1、报错：no such file or directory

1月 08 10:13:22 WQ-MySQL mysqld[47817]: mysqld: Error on realpath() on '/data/mysql/mysql_files' (Error 2 - No such file or directory)
1月 08 10:13:22 WQ-MySQL mysqld[47817]: 2024-01-08T02:13:22.743894Z 0 [Warning] Could not increase number of max_open_files to more than 5000 (request: 102400)
1月 08 10:13:22 WQ-MySQL systemd[1]: mysqld.service: control process exited, code=exited status=1
1月 08 10:13:22 WQ-MySQL systemd[1]: Failed to start MySQL Server.
1月 08 10:13:22 WQ-MySQL systemd[1]: Unit mysqld.service entered failed state.
1月 08 10:13:22 WQ-MySQL systemd[1]: mysqld.service failed.

    分析：文件或目录缺失

    解决：创建对应目录

2、报错：Can't find error-message file

1月 08 10:13:24 WQ-MySQL mysqld_pre_systemd[47886]: 2024-01-08T10:13:24.996499+08:00 0
[ERROR] Can't find error-message file '/data/mysql/share/mysql/errmsg.sys'.
Check error-message file location and 'lc-messages-dir' configuration directive.
1月 08 10:13:24 WQ-MySQL mysqld_pre_systemd[47886]: 2024-01-08T10:13:24.997123+08:00 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
1月 08 10:13:24 WQ-MySQL mysqld_pre_systemd[47886]: 2024-01-08T10:13:24.997155+08:00 0 [ERROR] Aborting

分析：对应的errmsg.sys文件缺失

解决：复制安装文件其他路径到报错路径

find data/ -name errmsg.sys|grep english

find data/ -name errmsg.sys|grep english
cp /data/docker/overlay2/bd0699afc8adb5e646b2d211bfb2ed3814887130cce277e4872d93178624bdb3/diff/usr/share/mysql/english/errmsg.sys data/mysql/share/mysql/



3、报错：--initialize specified but the data directory has files in it

1月 08 10:21:50 WQ-MySQL mysqld_pre_systemd[49009]: 2024-01-08T10:21:50.981519+08:00 0 [ERROR] --
initialize specified but the data directory has files in it. Aborting.
1月 08 10:21:50 WQ-MySQL mysqld_pre_systemd[49009]: 2024-01-08T10:21:50.981556+08:00 0 [ERROR] Aborting

分析：初始化提示对应目录已有文件

解决：备份原目录，并新建空目录，重新初始化操作即可。

4、报错：for error logging: Permission denied

1月 08 10:21:53 WQ-MySQL mysqld[49136]: 2024-01-08T10:21:53.739614+08:00 0 [ERROR] Could not open file '/data/mysql/logs/mysqld.log' for error logging: Permission denied
1月 08 10:21:53 WQ-MySQL mysqld[49136]: 2024-01-08T10:21:53.739629+08:00 0 [ERROR] Aborting

分析：root启动提示权限问题

解决：调整mysql初始化路径的权限

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

5、报错：Table 'mysql.plugin' doesn't exist

2024-01-08T10:39:48.832606+08:00 0 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Table 'mysql.plugin' doesn't exist
2024-01-08T10:39:48.832823+08:00 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2024-01-08T10:39:48.849729+08:00 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
2024-01-08T10:39:48.849737+08:00 0 [ERROR] Fatal error: Failed to initialize ACL/grant/time zones structures or failed to remove temporary table files.
2024-01-08T10:39:48.849751+08:00 0 [ERROR] Aborting

分析：提示缺少 mysql.plugin 表，而且 'FEDERATED' 插件被禁用。这种情况下，可能需要运行 mysql_upgrade 来修复 MySQL 数据库。

解决： mysql_upgrade -u root -P33061 -p

[root@WQ-MySQL data]# mysql_upgrade -u root -P33061 -p
Enter password:
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
The sys schema is already up to date (version 1.5.2).
Checking databases.
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.



6、执行第五步无效果：Can't open and lock privilege tables

分析：初始化的时候选择root权限，后来更改目录为mysql拥有者，可能需要重新进行初始化。

解决：先停止mysql服务，再清除原data文件目录，重新执行初始化操作。

systemctl stop mysqld
rm -rf 安装目录
sudo mysqld --initialize --user=mysql --basedir=/home/mysql --datadir=/home/mysql/data
systemctl start mysqld
systemctl status mysqld

7、Selinux导致： [Warning] Can't create test file，日志中没有error信息，只有warning信息，但是无法启动

1月 08 13:27:14 App-WQ systemd[1]: Starting MySQL Server...
1月 08 13:27:15 App-WQ mysqld[29391]: 2024-01-08T05:27:14.973865Z 0 [Warning] Could not increase number of max_open_files to more than 5000 (request: 8000)
1月 08 13:27:15 App-WQ mysqld[29391]: 2024-01-08T13:27:15.094759+08:00 0 [Warning] Can't create test file /home/mysql/data/App-WQ.lower-test
1月 08 13:27:15 App-WQ mysqld[29391]: 2024-01-08T13:27:15.094845+08:00 0 [Note] /usr/sbin/mysqld (mysqld 5.7.34-log) starting as process 29394 ...
1月 08 13:27:15 App-WQ mysqld[29391]: 2024-01-08T13:27:15.096708+08:00 0 [Warning] Can't create test file /home/mysql/data/App-WQ.lower-test
1月 08 13:27:15 App-WQ mysqld[29391]: 2024-01-08T13:27:15.096741+08:00 0 [Warning] Can't create test file /home/mysql/data/App-WQ.lower-test
1月 08 13:27:15 App-WQ systemd[1]: mysqld.service: control process exited, code=exited status=1
1月 08 13:27:15 App-WQ systemd[1]: Failed to start MySQL Server.

分析：反复尝试各种权限设置后，发现由于linux服务器的安全策略selinux导致

解决：查看selinux状态、临时关闭、永久关闭、重新启动MySQL

[root@App-WQ data]# getenforce
Enforcing
[root@App-WQ data]# setenforce 0
[root@App-WQ data]# getenforce
Permissive


[root@App-WQ data]# vim /etc/selinux/config


SELINUX=disabled


[root@App-WQ data]# systemctl start mysqld
[root@App-WQ data]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 一 2024-01-08 13:32:27 CST; 7min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 29809 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 29781 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 29811 (mysqld)
Tasks: 42
CGroup: /system.slice/mysqld.service
└─29811 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid


1月 08 13:32:25 App-WQ systemd[1]: Starting MySQL Server...
1月 08 13:32:27 App-WQ systemd[1]: Started MySQL Server.



四、防火墙及授权特定访问配置

1、防火墙授权指定服务器进行访问，比如下方只授权2台服务器IP进行访问？

sudo firewall-cmd --zone=public --add-port=33061/tcp --permanent
sudo firewall-cmd --zone=public --add-rich-rule='rule family="ipv4" source address="10.18.10.63" port port="33061" protocol="tcp" accept' --permanent
sudo firewall-cmd --zone=public --add-rich-rule='rule family="ipv4" source address="192.168.207.22" port port="33061" protocol="tcp" accept' --permanent
sudo firewall-cmd --reload


firewall-cmd --list-ports



2、MySQL中的root账户只授权2台服务器IP进行访问？

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.18.10.63' IDENTIFIED BY 'MEcTOdwlsA' WITH GRANT OPTIO
ON;
Query OK, 0 rows affected, 1 warning (0.01 sec)


mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)


mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.207.22' IDENTIFIED BY 'MEcTOdwlsA' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.01 sec)


mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)