一、安装MySQL
1.1 配置 yum 源
centos 默认没有 MySQL 的 yum 源,所以需要先配置 yum 源。
1) 前往 官网,选择对应系统版本
2) 右键复制链接
- 在 centos 中下载 rpm 文件
wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
4)安装mysql源
yum localinstall mysql80-community-release-el7-1.noarch.rpm
- 检查 mysql 源是否安装成功
yum repolist enabled | grep "mysql.*-community.*"
出现下面列出的文件,就证明 MySQL 源安装成功了。
mysql-connectors-community/x86_64 MySQL Connectors Community 118
mysql-tools-community/x86_64 MySQL Tools Community 95
mysql57-community/x86_64 MySQL 5.7 Community Server 364
- 修改 mysql 安装版本
我们要安装的是 mysql 5.7,但是 rpm文件中默认是8.0
vim /etc/yum.repos.d/mysql-community.repo
将文件中的 mysql 5.7的 enabled 设为1, mysql8.0 的enabled 设为 0,表示默认安装 mysql 5.7,修改后文件如下:
# Enable to use MySQL 5.7
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql80-community]
name=MySQL 8.0 Community Server
baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
1.2 安装 MySQL
yum install mysql-community-server
二、 MySQL 服务命令
启动 MySQL 服务
systemctl start mysqld
查看 MySQL 服务状态
systemctl status mysqld
可以看到如下内容,表明服务已启动
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 五 2018-08-17 10:36:48 CST; 1min 11s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 4133 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 4055 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 4137 (mysqld)
CGroup: /system.slice/mysqld.service
└─4137 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
8月 17 10:36:40 VM_0_15_centos systemd[1]: Starting MySQL Server...
8月 17 10:36:48 VM_0_15_centos systemd[1]: Started MySQL Server.
关闭 MySQL 服务
systemctl stop mysqld
再次查看 MySQL 服务状态
mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead) since 五 2018-08-17 10:41:16 CST; 1s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 4133 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 4055 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 4137 (code=exited, status=0/SUCCESS)
8月 17 10:36:40 VM_0_15_centos systemd[1]: Starting MySQL Server...
8月 17 10:36:48 VM_0_15_centos systemd[1]: Started MySQL Server.
8月 17 10:41:15 VM_0_15_centos systemd[1]: Stopping MySQL Server...
8月 17 10:41:16 VM_0_15_centos systemd[1]: Stopped MySQL Server.
可以看到已经关闭了
开机自启动 MySQL 服务
systemctl enable mysqld # 配置mysql服务开机自启动
systemctl daemon-reload # 刷新配置
停止 MySQL 服务开机自启动
systemctl disable mysqld.service # 停止mysql服务开机自启动
systemctl daemon-reload # 刷新配置
三、MySQL 的使用
3.1 登录 MySQL
1)首次登陆需要先获取 root 密码,MySQL 安装后会在 /var/log/mysqld.log
文件中给 root 生成一个默认密码,我们需要先获取到这个密码,登录后再修改 root 密码。
grep 'temporary password' /var/log/mysqld.log
输出如下内容:
2018-08-17T02:36:42.026253Z 1 [Note] A temporary password is generated for root@localhost: qKPlZ!1aWaYc
qKPlZ!1aWaYc
就是我们的 root 密码。
- 登录 MySQL
mysql -uroot -p
输入密码 qKPlZ!1aWaYc 后,显示如下内容,即为登录成功。
[root@VM_0_15_centos /]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 279
Server version: 5.7.27 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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>
3.2 退出 MySQL
mysql> exit
3.3 修改 root 密码
-- 方式一
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';
-- 方式二
set password for 'root'@'localhost'=password('新密码');
要注意的是 MySQL 5.7 默认安装了密码安全检查插件(validate_password),默认密码检查策略要求密码必须包含:大小写字母、数字和特殊符号,并且长度不能少于8位。否则会提示如下图错误:
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
3.4 修改密码策略
1)显示密码策略相关信息
mysql> show variables like '%password%';
+---------------------------------------+-------+
| Variable_name | Value |
+---------------------------------------+-------+
| default_password_lifetime | 0 |
| disconnect_on_expired_password | ON |
| log_builtin_as_identified_by_password | OFF |
| mysql_native_password_proxy_users | OFF |
| old_passwords | 0 |
| report_password | |
| sha256_password_proxy_users | OFF |
+---------------------------------------+-------+
7 rows in set (0.00 sec)
- validate_password_policy:密码策略,默认为MEDIUM策略
- validate_password_dictionary_file:密码策略文件,策略为STRONG才需要
- validate_password_length:密码最少长度
- validate_password_mixed_case_count:大小写字符长度,至少1个
- validate_password_number_count :数字至少1个
- validate_password_special_char_count:特殊字符至少1个
2)修改密码策略
在 /etc/my.cnf
文件添加 validate_password_policy
配置,指定密码策略
# 选择0(LOW),1(MEDIUM),2(STRONG)其中一种,选择2需要提供密码字典文件
validate_password_policy=0
如果不需要密码策略,添加 my.cnf
文件中添加如下配置禁用即可:
validate_password = off
重新启动 MySQL 服务使配置生效:
systemctl restart mysqld
3.5 远程登录
默认只允许 root 帐户在本地登录,如果要在其它机器上连接 MySQL ,必须修改 root 允许远程连接,或者添加一个允许远程连接的帐户,这里我们分别演示下这两种方案怎么操作。
- 添加远程连接帐户
grant all privileges on *.* to 创建的用户名 @"%" identified by "密码";
flush privileges; -- 刷新刚才的内容*
格式:grant 权限 on 数据库教程名.表名 to 用户@登录主机 identified by "用户密码"; @ 后面是访问mysql的客户端ip地址(或是 主机名) % 代表任意的客户端,如果填写 localhost 为本地访问(那此用户就不能远程访问该mysql数据库了)。
实例:
GRANT ALL PRIVILEGES ON *.* TO 'marklogzhu'@'%' IDENTIFIED BY 'marklogzhu@163.com' WITH GRANT OPTION;
flush privileges;
- 修改现有账号远程连接
use mysql;
update db set host = '%' where user = '用户名'; (如果写成 host=localhost 那此用户就不具有远程访问权限)
flush privileges;
grant all privileges on *.* to '用户名'@'%' identified by '密码' with grant option;
3.6 编码格式
- 获取当前编码格式
mysql> show variables like '%character%';
+--------------------------+----------------------------+
| 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/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
可以看到默认已经是 utf8 编码了,如果不是的话就需要修改配置文件。
- 修改编码格式为utf8
修改 /etc/my.cnf
配置文件,在 [mysqld]
下添加编码配置,如下所示:
[mysqld]
character_set_server=utf8
init_connect='SET NAMES utf8'
重新启动 MySQL 服务即可。
四、删除 yum 安装的 MySQL
1)查看安装的模块
rpm -qa |grep -i mysql
mysql80-community-release-el7-1.noarch
mysql-community-client-5.7.23-1.el7.x86_64
mysql-community-libs-5.7.23-1.el7.x86_64
mysql-community-libs-compat-5.7.23-1.el7.x86_64
mysql-community-common-5.7.23-1.el7.x86_64
mysql-community-server-5.7.23-1.el7.x86_64
- 卸载安装的模块
yum remove mysql-community mysql-community-server mysql-community-libs mysql-community-common mysql-community-release
3)查找未删除的文件
find / -name mysql.
4)删除掉该文件
rm -rf /usr/share/mysql