CentOS7下安装配置mysql
一.卸载
安装之前需要先卸载，CentOS7精简版默认是有残留的MySQL，所以开始时要先卸载掉原来的MySQL。
1.卸载原有程序
yum remove mysql mysql-server mysql-libs compat-mysql51
2.查看残留的目录
whereis mysql #应该会输出 /usr/lib64/mysql
3.删除mysql目录
rm -rf /usr/lib64/mysql
4.删除其他文件
rm -rf /usr/my.cnf #有则删除
rm -rf /root/.mysql_sercret #有则删除
rm -rf /var/lib/mysql #有则删除,存在的话需要删除，不然密码不会重新初始化
二.安装
1.安装mysql5.7
1 下载并安装MySQL官方的 Yum Repository
[root@localhost ~]# wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
使用上面的命令就直接下载了安装用的Yum Repository，大概25KB的样子，然后就可以直接yum安装了。
[root@localhost ~]# yum -y install mysql57-community-release-el7-10.noarch.rpm
之后就开始安装MySQL服务器。
[root@localhost ~]# yum -y install mysql-community-server
1.添加MySQL Yum 仓库
从MySQL Yum 仓库dev.mysql.com/downloads/r…下载适合电脑版本的rpm包。 查看电脑版本
[root@localhost ~]# uname -a
Linux localhost.localdomain 3.10.0-693.el7.x86_64 #1 SMP Tue Aug 22 21:09:27 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
网站(dev.mysql.com/downloads/r…)上找到对应el7的版本
Red Hat Enterprise Linux 7 / Oracle Linux 7 (Architecture Independent), RPM Package 25.2K
Download
(mysql80-community-release-el7-1.noarch.rpm) MD5: 739dc44566d739c5d7b893de96ee6848
下载rpm文件(该文件非离线安装包)
格式：wget dev.mysql.com/get/ + mysql80-community-release-el7-1.noarch.rpm
wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
下载后执行(mysql80-community-release-el7-1.noarch.rpm是上一步下载的rpm包)
sudo rpm -Uvh mysql80-community-release-el7-1.noarch.rpm
2.安装mysql
sudo yum install mysql-community-server
一路输入y,网速快慢决定了这个步骤的快慢.
三.启动/关闭
重新配置参数文件my.cnf，在MySQL启动之前将参数lower_case_table_names设置为不区分大小写：
[root@localhost home]# vi /etc/my.cnf
# 在[mysqld]后新增一行:lower_case_table_names=1
[mysqld]
lower_case_table_names=1
sudo service mysqld start #启动服务
[root@localhost home]# service mysqld start
Redirecting to /bin/systemctl start mysqld.service
sudo service mysqld status #查看状态
[root@localhost home]# service mysqld status
Redirecting to /bin/systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2018-09-14 16:42:31 CST; 7s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 2591 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 2659 (mysqld)
Status: "SERVER_OPERATING"
CGroup: /system.slice/mysqld.service
└─2659 /usr/sbin/mysqld
Sep 14 16:42:12 localhost.localdomain systemd[1]: Starting MySQL Server...
Sep 14 16:42:31 localhost.localdomain systemd[1]: Started MySQL Server.
启动成功后， 本地超级用户root被创建，root用户的密码在日志文件中，使用下面的命令查看密码(找到temporary password)
cat /var/log/mysqld.log
[root@localhost home]# cat /var/log/mysqld.log
2018-09-14T08:42:13.302248Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.12) initializing of server in progress as process 2612
2018-09-14T08:42:21.039447Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: v9EU-<j9>+T>
2018-09-14T08:42:27.139278Z 0 [System] [MY-013170] [Server] /usr/sbin/mysqld (mysqld 8.0.12) initializing of server has completed
2018-09-14T08:42:30.782576Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.12) starting as process 2659
2018-09-14T08:42:31.549366Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2018-09-14T08:42:31.613617Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.12' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
修改密码，密码需要8位且需要符合要求，如下例子
mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY ‘新密码’; #注意标点符号， 新密码必须是8位及以上的数字、字母、特殊字符
#如下：
mysql -u root -p
然后在Enter password: 输入上一步查询到的密码：如： v9EU-<j9>+T>
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Root1234!';
[root@localhost home]# 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.12
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root1234!';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Root1234!';
Query OK, 0 rows affected (0.15 sec)
四.修改配置
1.修改编码格式
show variables like '%character_set%' ##查看编码
mysql> show variables like '%character_set%';
+--------------------------+--------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.01 sec)
mysql>
2.修改
在[mysqld]之前添加 [client] default-character-set=utf8 在[mysqld]之后添加 character-set-server=utf8
vi /etc/my.cnf
mysql> exit;
Bye
[root@localhost home]# vi /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
3.重启mysql服务
sudo systemctl restart mysqld.service
[root@localhost home]# sudo systemctl restart mysqld.service
[root@localhost home]# 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.12 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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 variables like '%character_set%';
+--------------------------+--------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.01 sec)
mysql>
4.创建用户并允许远程访问
[root@localhost home]# mysql -u root -p
Enter password:
## Mysql8后密码要求比较严格，需要包含大小写字母、数字、特殊字符
## 1.创建用户
mysql> CREATE USER 'panda'@'%' IDENTIFIED BY 'Panda123!';
Query OK, 0 rows affected (0.06 sec)
## 2.授权 及允许远程访问
mysql> grant all on *.* to 'panda'@'%';
Query OK, 0 rows affected (0.05 sec)
## 3.MySql8.0 版本和 5.0 的加密规则不一样，而现在的可视化工具只支持旧的加密方式
## 需要将 MySQL 用户登录的加密规则修改为 mysql_native_password
mysql> ALTER USER 'panda'@'%' IDENTIFIED WITH mysql_native_password BY 'Panda123!';
...
## 4.查看修改后信息
mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+-------------------------------------+-----------------------+
| host | user | authentication_string | plugin |
+-----------+------------------+-------------------------------------+-----------------------+
| % | panda | DDDD...(手动修改过)
至此，可以通过客户端远程访问。
5.开放端口
如果4还不能访问需要关闭防火墙或者开发端口
//查看已开放的端口(默认不开放任何端口)
firewall-cmd --list-ports
//开启3306端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
//重启防火墙
firewall-cmd --reload
//再查看端口是否开通，如下说明端口开启了
[root@localhost rabbitmq]# firewall-cmd --list-ports
3306/tcp
6.修改表名忽略大小写
如果已经启动(初始化)了mysql服务。则需要先删除数据，配置之后重新初始化：
1.停掉运行的MySQL进程删除默认datadir下的文件,清空错误日志文件便于重启查看：
[root@localhost home]# systemctl stop mysqld
[root@localhost home]# rm -rf /var/lib/mysql/*
[root@localhost home]# echo '' > /var/log/mysqld.log
2.重启MySQL 查看临时密码 登录数据库:
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# cat /var/log/mysqld.log | grep -i password
2018-10-16T14:14:33.531160Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: WpmR>F1)HsP/
##使用上面的密码登录
[root@localhost ~]# mysql -p -S /var/lib/mysql/mysql.sock
Enter password:
##其他的操作必须在修改root的密码之后
mysql> alter user root@'localhost' identified by 'Root$1234';
mysql> show variables like '%lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 1 |
+------------------------+-------+
2 rows in set (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
看到 lower_case_table_names=1说明设置成功.
