卸载mariaDb
mysql被oracle收购后为了防止mysql有可能变成闭源,因此mysql创始人maria就开源做了一个mariaDb, centos7是自带使用了这个数据库的.因此安装mysql之前,应当首先卸载mariadb数据库:
$ sudo yum list installed | grep mariadb #检查mariadb是否已安装
$ sudo yum list installed | grep mariadb
mariadb-libs.x86_64 1:5.5.56-2.el7 @anaconda
$ sudo yum -y remove mariadb* #全部卸载
同理如果是已经安装过其他版本的MySQL，安装新的版本之前也需要按照如上方法进行卸载。
安装MySQL
下载mysql的YUM源： https://dev.mysql.com/downloads/repo/yum/
YUM源的官方安装说明：https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/
下载源mysql的YUM源，官网提供的是最新的MySQL8.0的源，内含其他较低版本的源
wget -P /home/hadoop/softwares https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm --no-check-certificate
由于我们是下载到/home/hadoop/softwares目录下，所以先切换到该目录下：
cd /home/hadoop/softwares
安装mysql的YUM源：
sudo rpm -ivh mysql80-community-release-el7-3.noarch.rpm
检查mysql的YUM源是否安装成功：
sudo yum repolist enabled | grep mysql
mysql-connectors-community/x86_64 MySQL Connectors Community 175
mysql-tools-community/x86_64 MySQL Tools Community 120
mysql80-community/x86_64 MySQL 8.0 Community Server 211
选择要启用的mysql版本
查看源里包含的mysql版本,执行：
sudo yum repolist all | grep mysql
mysql-cluster-7.5-community/x86_64 MySQL Cluster 7.5 Community 禁用
mysql-cluster-7.5-community-source MySQL Cluster 7.5 Community - So 禁用
mysql-cluster-7.6-community/x86_64 MySQL Cluster 7.6 Community 禁用
mysql-cluster-7.6-community-source MySQL Cluster 7.6 Community - So 禁用
mysql-cluster-8.0-community/x86_64 MySQL Cluster 8.0 Community 禁用
mysql-cluster-8.0-community-source MySQL Cluster 8.0 Community - So 禁用
mysql-connectors-community/x86_64 MySQL Connectors Community 启用: 175
mysql-connectors-community-source MySQL Connectors Community - Sou 禁用
mysql-tools-community/x86_64 MySQL Tools Community 启用: 120
mysql-tools-community-source MySQL Tools Community - Source 禁用
mysql-tools-preview/x86_64 MySQL Tools Preview 禁用
mysql-tools-preview-source MySQL Tools Preview - Source 禁用
mysql55-community/x86_64 MySQL 5.5 Community Server 禁用
mysql55-community-source MySQL 5.5 Community Server - Sou 禁用
mysql56-community/x86_64 MySQL 5.6 Community Server 禁用
mysql56-community-source MySQL 5.6 Community Server - Sou 禁用
mysql57-community/x86_64 MySQL 5.7 Community Server 禁用
mysql57-community-source MySQL 5.7 Community Server - Sou 禁用
mysql80-community/x86_64 MySQL 8.0 Community Server 启用: 211
mysql80-community-source MySQL 8.0 Community Server - Sou 禁用
可以通过类似下面的语句来启动和禁用某些版本，比如这里是禁用默认启用的MySQL8.0的源，转而启用我们需要安装的MySQL5.7的源。
或者通过修改/etc/yum.repos.d/mysql-community.repo文件，改变默认安装的mysql版本。比如要安装5.7版本，将8.0源的enabled=1改成enabled=0，然后再将5.7源的enabled=0改成enabled=1即可。
sudo yum-config-manager --enable mysql57-community
sudo yum-config-manager --disable mysql80-community
要使用yum-config-manager命令, 需要先安装yum-utils: sudo yum install yum-utils
注意： 同一时间只允许enable一个MySQL版本。
如果只需要安装MySQL5.7的版本，也可以不看上面，直接本地安装指定的远程MySQL5.7源
yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
查看当前的启用的 MySQL 版本：
yum repolist enabled | grep mysql
mysql-connectors-community/x86_64 MySQL Connectors Community 175
mysql-tools-community/x86_64 MySQL Tools Community 120
mysql57-community/x86_64 MySQL 5.7 Community Server 464
安装MySQL
sudo yum install -y mysql-community-server
已加载插件：fastestmirror
mysql-connectors-community | 2.6 kB 00:00:00
mysql-tools-community | 2.6 kB 00:00:00
mysql57-community | 2.6 kB 00:00:00
mysql57-community/x86_64/prima FAILED
http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/repodata/cae8c564a11e5fca11dbe958c273358f206f1bba-primary.sqlite.bz2: [Errno 14] curl#7 - "Failed connect to repo.mysql.com:80; Connection refused"
正在尝试其它镜像。
mysql57-community/x86_64/primary_db | 247 kB 00:00:00
Loading mirror speeds from cached hostfile
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
正在解决依赖关系
--> 正在检查事务
---> 软件包 mysql-community-server.x86_64.0.5.7.32-1.el7 将被 安装
--> 正在处理依赖关系 mysql-community-common(x86-64) = 5.7.32-1.el7，它被软件包 mysql-community-server-5.7.32-1.el7.x86_64 需要
--> 正在处理依赖关系 mysql-community-client(x86-64) >= 5.7.9，它被软件包 mysql-community-server-5.7.32-1.el7.x86_64 需要
--> 正在检查事务
---> 软件包 mysql-community-client.x86_64.0.5.7.32-1.el7 将被 安装
--> 正在处理依赖关系 mysql-community-libs(x86-64) >= 5.7.9，它被软件包 mysql-community-client-5.7.32-1.el7.x86_64 需要
---> 软件包 mysql-community-common.x86_64.0.5.7.32-1.el7 将被 安装
--> 正在检查事务
---> 软件包 mysql-community-libs.x86_64.0.5.7.32-1.el7 将被 安装
--> 解决依赖关系完成
依赖关系解决
==============================================================================================
Package 架构 版本 源 大小
==============================================================================================
正在安装:
mysql-community-server x86_64 5.7.32-1.el7 mysql57-community 173 M
为依赖而安装:
mysql-community-client x86_64 5.7.32-1.el7 mysql57-community 25 M
mysql-community-common x86_64 5.7.32-1.el7 mysql57-community 308 k
mysql-community-libs x86_64 5.7.32-1.el7 mysql57-community 2.3 M
事务概要
==============================================================================================
安装 1 软件包 (+3 依赖软件包)
总下载量：201 M
安装大小：875 M
....
已安装:
mysql-community-server.x86_64 0:5.7.32-1.el7
作为依赖被安装:
mysql-community-client.x86_64 0:5.7.32-1.el7
mysql-community-common.x86_64 0:5.7.32-1.el7
mysql-community-libs.x86_64 0:5.7.32-1.el7
启动MySQL服务
systemctl start mysqld.service # 或 service mysqld start
配置开机启动
systemctl enable mysqld.service
查看运行状态
systemctl status mysqld.service
mysql 2574 1 1 23:49 ? 00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
查看到进程信息
netstat -anpl | grep mysql
查看端口，可以看出mysql server的进程mysqld所使用的默认端口即3306
$ sudo netstat -anpl | grep tcp
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1412/sshd
tcp 0 52 192.168.186.103:22 192.168.186.1:54058 ESTABLISHED 2287/sshd: hadoop
tcp6 0 0 :::3306 :::* LISTEN 2574/mysqld
tcp6 0 0 192.168.186.103:3888 :::* LISTEN 2060/java
tcp6 0 0 :::22 :::* LISTEN 1412/sshd
tcp6 0 0 :::37791 :::* LISTEN 2060/java
tcp6 0 0 :::2181 :::* LISTEN 2060/java
找到临时密码
但是要登陆mysql，我们需要root密码，这个密码是安装时随机生成在MySQL的服务器日志中的
grep "temporary password" /var/log/mysqld.log
2020-02-26T17:05:45.104999Z 1 [Note] A temporary password is generated for root@localhost: bl/!6qaU.wuX
登录MySQL
# 回车后输入在日志中找到的临时root登录密码登录
mysql -u roop -p
问题: 日志里没有找到临时密码, 或者密码不能登录，可能的原因就是之前安装过MySQL
解决办法:
sudo rm -rf /var/lib/mysql
再重启服务后就可以在日志文件中找到重新生成的临时密码了
systemctl restart mysqld.service
$ cat /var/log/mysqld.log | grep "temporary password"
2020-02-26T17:05:45.104999Z 1 [Note] A temporary password is generated for root@localhost: bl/!6qaU.wuX
2020-08-01T16:07:26.941916Z 1 [Note] A temporary password is generated for root@localhost: x/ttqh=)_6Z/
也可以直接使用明文密码登陆,像这样
mysql -uroop -p"x/ttqh=)_6Z/"
初始密码我们可以这样操作，因为很快我们会将将其改为其他的密码。
但是正常使用时不建议这样 不安全
黑客可以使用history命令查看到你在命令行的输入获取到你的MySQL的root密码
另外MySQL报错在/var下面的log目录下可以查看
如果是虚拟机环境为了方便可以选择配置跳过密码验证
vim /etc/my.cnf
添加以下代码
default-authentication-plugin=mysql_native_password #配置文件本来就有去掉注释即可
symbolic-links=0
skip-grant-tables #跳过密码验证
第一次登陆后系统会很快提示你修改掉默认密码
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 by 'niit1234';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
密码安全策略
但是基于密码策略，所设置的密码必须要包含大小写字母、数字和字符。
修改策略并设置简单密码
将策略要求置为0(LOW)，长度要求置为1
mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=1;
关于密码安全策略：
通过show命令查看当前的策略
mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+--------------------------------------+--------+
7 rows in set (0.00 sec)
1） validate_password_policy：密码安全策略，默认MEDIUM策略
| 策略 |
检查规则 |
| 0 or LOW |
Length |
| 1 or MEDIUM |
Length; numeric, lowercase/uppercase, and special characters |
| 2 or STRONG |
Length; numeric, lowercase/uppercase, and special characters; dictionary file |
2）validate_password_dictionary_file：密码策略文件，策略为STRONG才需要
3）validate_password_length：密码最少长度
4）validate_password_mixed_case_count：大小写字符长度，至少1个
5）validate_password_number_count ：数字至少1个
6）validate_password_special_char_count：特殊字符至少1个
这样以后就可以使用新密码登陆了
开启远程登陆
mysql命令的-h选项可以设置需要远程登陆mysql的服务器的ip地址
如果是本地的话可以使用127.0.0.1或者直接省略
但是远程访问因为安全原因默认是关闭的
mysql> select Host,User from user;
+-----------+---------------+
| Host | User |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
3 rows in set (0.00 sec)
这里可以看到host都是localhost,这就是因为这些账号只有本地访问的权限
我们需要将root的访问权限扩大都允许任意方式访问(包括远程访问)
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY 'niit1234' WITH GRANT OPTION;
也可以创建一个专门用来支持远程访问的单独用户
GRANT ALL PRIVILEGES ON *.* TO 'remote'@'%' IDENTIFIED BY 'niit1234' WITH GRANT OPTION;
如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器，并使用mypassword作为密码
若你想限制能连接到mysql服务器的客户端所在的IP地址为'192.168.1.6'
并使用mypassword作为密码，则命令如下：
GRANT ALL PRIVILEGES ON . TO 'mysqluser'@'192.168.1.6'IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
使修改生效
现在虽然修改了远程访问权限，但是还没有生效
我们需要刷新权限（或者重启服务），当然直接在mysql执行环境刷新权限是更好的方法
mysql>FLUSH PRIVILEGES;
然后我们可以试试使用navicat远程连接虚拟机中mysql试试
navicat虽然是收费的还是可以下载到破解的版本
最后一点
如果你的服务器开启了防火墙也可能导致远程无法连接
因为mysql默认使用3306端口，我们需要开放这个端口给mysql客户端连接
修改编码
默认安装的设置对中文的支持不好，一般需要修改成utf8，但这里我们修改成utf8mb4编码，因为它是uft8的超集，减少更多乱码的问题，也是推荐使用的编码，比如需要插入emoji字符在数据库中，utf8的显示就会出现问题，而utf8mb4则没有问题。
首先查看当前的编码：
mysql> show variables like '%char%';
+--------------------------------------+----------------------------+
| Variable_name | Value |
+--------------------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| validate_password_special_char_count | 1 |
+--------------------------------------+----------------------------+
9 rows in set (0.00 sec)
为了修改编码，我们需要修改mysql的核心配置文件如下：
[hadoop@hadoop000 download]$ sudo vim /etc/my.cnf
# For advice on how to change settings please see
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
# 默认服务器内部操作字符集
character-set-server=utf8mb4
# 默认服务器内部操作字符集校对规则
collation-server=utf8mb4_general_ci
# 默认的存储引擎
default-storage-engine=InnoDB
# 初始化连接时设置以下字符集：
# character_set_client
# character_set_results
# character_set_connection
init_connect='set names utf8mb4'
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
验证
mysql> show variables like '%char%';
+--------------------------------------+----------------------------+
| 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/charsets/ |
| validate_password_special_char_count | 1 |
+--------------------------------------+----------------------------+
9 rows in set (0.00 sec)
- character_set_client 是指客户端发送过来的语句的编码
- character_set_database 是指服务器内部使用的编码
- character_set_connection 是指mysqld收到客户端的语句后，要转换到的编码
- character_set_results 是指server执行语句后，返回给客户端的数据的编码
- character_set_system是元数据编码，无需修改
- character_set_filesystem是文件系统的编码，2进制存储最有效，不能修改
【END】
rpm-bundle的离线安装
下载rpm-bundle包
$ wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-5.7.33-1.el7.x86_64.rpm-bundle.tar
$ tar xvf mysql-5.7.33-1.el7.x86_64.rpm-bundle.tar
$ mkdir mysql-jars
$ mv mysql-comm*.rpm mysql-jars/
$ cd mysql-jars/
$ ls
mysql-community-client-5.7.33-1.el7.x86_64.rpm
mysql-community-common-5.7.33-1.el7.x86_64.rpm
mysql-community-libs-5.7.33-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.33-1.el7.x86_64.rpm
mysql-community-server-5.7.33-1.el7.x86_64.rpm
依次手动安装
sudo rpm -ivh mysql-community-common-5.7.33-1.el7.x86_64.rpm
sudo rpm -ivh mysql-community-libs-5.7.33-1.el7.x86_64.rpm
sudo rpm -ivh mysql-community-libs-compat-5.7.33-1.el7.x86_64.rpm
sudo rpm -ivh mysql-community-client-5.7.33-1.el7.x86_64.rpm
sudo rpm -ivh mysql-community-server-5.7.33-1.el7.x86_64.rpm
Views: 74
