linux 安装mysql与常见问题

1.安装mysql

1.1 检查本地是否已经安装和卸载

检查命令
rpm -qa | grep mysql

删除命令
rpm -e mysql
rpm -e --nodeps mysql

 rpm -e mysql：普通删除模式
 rpm -e --nodeps mysql： 强力删除模式，如果使用上面命令删除时，提示有依赖的其它文件，则用该命令可以对其进行强力删除

1.2 下载和安装

建议使用5.7 || 5.8 版本，在8.0版本会有几个小坑

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz

cp mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz /usr/local/.

tar -zxvf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz

mv mysql-5.7.24-linux-glibc2.12-x86_64 mysql

配置和重新加载环境变量

vim /etc/profile

export MYSQL_HOME=/usr/local/mysql
export CLASSPATH=$:CLASSPATH:$MYSQL_HOME/lib
export PATH=$PATH:$MYSQL_HOME/bin

source /etc/profile

创建用户和组

groupadd mysql
useradd -r -g mysql mysql

目录所有者及所属组改为mysql

chown -R mysql.mysql /usr/local/mysql

进入mysql，安装依赖

cd /usr/local/mysql/

yum install libaio

1.3 进行初始化

mysqld --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --initialize

注意，在安装8.0的时候，如果需要表名为大小写不敏感的，需要增加 --lower-case-table-names=1
注意，在安装8.0的时候，如果需要表名为大小写不敏感的，需要增加 --lower-case-table-names=1
注意，在安装8.0的时候，如果需要表名为大小写不敏感的，需要增加 --lower-case-table-names=1


2020-09-14T03:05:57.891529Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-09-14T03:05:58.068364Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-09-14T03:05:58.104669Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-09-14T03:05:58.166008Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 363a8382-f637-11ea-9a0b-fa163e506dc6.
2020-09-14T03:05:58.168162Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-09-14T03:05:58.168515Z 1 [Note] A temporary password is generated for root@localhost: nkp%yupmI8s?

nkp%yupmI8s?就是分配的随机密码，需要保存好，后期登陆时会用

编辑配置文件

vim /etc/my.cnf

[mysqld]
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
socket=/tmp/mysql.sock
user=mysql
port=3306
character-set-server=utf8
symbolic-links=0

# skip-grant-tables #跳过权限验证
# skip-name-resolve 禁止DNS解析
# lower-case-table-names=1 #大小写敏感值 0敏感 1不敏感
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

1.4 启动mysql

配置开机启动

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

chkconfig mysql on

启动mysql

#启动
service mysql start

#停止
service mysql stop

#重启
service mysql restart

1.5 重置密码

必须要重置，否则，不能做任何操作 

mysql -u root -p
输入分配的随机密码：nkp%yupmI8s?

set password for root@localhost = password('123456');

安装完成

2.常见问题总结

2.1Starting MySQL..The server quit without updating PID file ([FAILED]al/mysql/data/sjc-ecs-0015.pid)

一般是进程里已经存在mysql进程了

ps -ef | grep mysql

kill -9 [pid]

如果还不可以的话，建议参考：https://javawind.net/p141

2.2 远程连接MySQL数据库报错：is not allowed to connect to this MYSQL server的解决办法

首先排除mysql端口是否对外开放，（如果是使用云服务器，还需确认是否配置安全规则，端口是否开放）

方案1：允许所有机器能访问root用户

mysql -u root -p

mysql>use mysql;

mysql>update user set host = '%' where user = 'root';

mysql>select host, user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| localhost | mysql.session |
| localhost | mysql.sys     |
+-----------+---------------+
3 rows in set (0.00 sec)

方案2：禁用DNS解析

找到my.cnf文件，在[mysqld]项，在其后加入一句：skip-name-resolve，

保存，重启mysql服务即可 service mysql restart

2.3 大小写不敏感

如果是5.7/5.8的版本，找到my.cnf文件，在[mysqld]项，在其后加入一句：lower-case-table-names=1，

保存，重启mysql服务即可 service mysql restart

如果 8.0 的版本，那就没有办法了，只能重装，在初始化的时候加上 --lower-case-table-names=1

原因：

While I can understand the concern for screwing things up by changing this mid-stream, simply preventing someone from changing it on an "out-of-the-box" install using a linux package manager(IE Apt) is really going too far. You've essentially forced anyone who wants to install mysql 8 and use lower_case_table_names=1 to download source, compile and initialize the system themselves. In my opinion, if someone is stupid enough to change this setting on a running server with active databases, well... they get what they deserve, you shouldn't punish those of us who set it RIGHT after install before anything else has been done to the server. I tried re-running mysqld --initialize --lower-case-table-names=1 with no success. I for one find this "protection" that has been added to be unecessary and annoying, PLEASE REMOVE IT!!!

原文地址：https://bugs.mysql.com/bug.php?id=90695