https://downloads.mysql.com/archives/community/
4.5 MySQL 5.7.26 二进制版本安装 4.5.1 下载并上传软件至/server/tools [root@db01 ~]# mkdir -p /server/tools [root@db01 ~]# cd /server/tools/ [root@db01 /server/tools]# yum install -y lrzsz [root@db01 /server/tools]# ls mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz 4.5.2 解压软件 [root@db01 /server/tools]# tar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz [root@db01 ~]# mkdir /application [root@db01 /server/tools]# mv mysql-5.7.26-linux-glibc2.12-x86_64 /application/mysql 4.5.3 用户的创建处理原始环境 [root@db01 ~]# yum remove mariadb-libs-5.5.60-1.el7_5.x86_64 -y [root@db01 ~]# rpm -qa |grep mariadb [root@db01 ~]# useradd -s /sbin/nologin mysql
4.5.4 设置环境变量 vim /etc/profile export PATH=/application/mysql/bin:$PATH [root@db01 ~]# source /etc/profile [root@db01 ~]# mysql -V mysql Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using EditLine wrapper 4.5.5 创建数据路径并授权 1. 添加一块新磁盘模拟数据盘 2. 格式化并挂载磁盘 [root@db01 ~]# mkfs.xfs /dev/sdb [root@db01 ~]# mkdir /data [root@db01 ~]# blkid [root@db01 ~]# vim /etc/fstab [root@db01 ~]# UUID="b21ec3e0-e251-4ded-bc12-2d940f938dd5" /data xfs defaults 0 0 [root@db01 ~]# mount -a [root@db01 ~]# df -h 3. 授权 [root@db01 ~]# chown -R mysql.mysql /application/* [root@db01 ~]# chown -R mysql.mysql /data
4.5.6 初始化数据（创建系统数据） # 5.6 版本 初始化命令 /application/mysql/scripts/mysql_install_db # 5.7 版本 [root@db01 ~]# mkdir /data/mysql/data -p [root@db01 ~]# chown -R mysql.mysql /data [root@db01 ~]# mysqld --initialize --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data 说明： --initialize 参数： 1. 对于密码复杂度进行定制：12位，4种 2. 密码过期时间：180 3. 给root@localhost用户设置临时密码 报错: mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory 解决： [root@db01 ~]# yum install -y libaio-devel [root@db01 ~]# mysqld --initialize --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data 2019-06-13T04:21:27.706564Z 1 [Note] A temporary password is generated for root@localhost: =mrV)_16is4U --initialize-insecure 参数： 无限制，无临时密码 [root@db01 /data/mysql/data]# \rm -rf /data/mysql/data/* [root@db01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data
4.5.7 配置文件的准备 cat >/etc/my.cnf <<EOF [mysqld] user=mysql basedir=/application/mysql datadir=/data/mysql/data socket=/tmp/mysql.sock server_id=6 port=3306 [mysql] socket=/tmp/mysql.sock EOF 4.5.8 启动数据库 1. sys-v [root@db01 /etc/init.d]# cp /application/mysql/support-files/mysql.server /etc/init.d/mysqld [root@db01 /etc/init.d]# service mysqld restart ERROR! MySQL server PID file could not be found! Starting MySQL.Logging to '/data/mysql/data/localhost.localdomain.err'. SUCCESS!
2. systemd
注意： sysv方式启动过的话，需要先提前关闭，才能以下方式登录
cat >/etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
5. 如何分析处理MySQL数据库无法启动 5.1 without updating PID 类似错误 查看日志： 在哪？ /data/mysql/data/主机名.err [ERROR] 上下文 可能情况： /etc/my.cnf 路径不对等 /tmp/mysql.sock文件修改过 或 删除过 数据目录权限不是mysql 参数改错了
6.管理员密码的设定（root@localhost） [root@db01 ~]# mysqladmin -uroot -p password oldboy123 Enter password: 7. 管理员用户密码忘记了？ 7.1 关闭数据库 [root@db01 ~]# /etc/init.d/mysqld stop Shutting down MySQL.. SUCCESS! 7.2 启动数据库到维护模式 [root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking & 7.3 登录并修改密码 mysql> alter user root@'localhost' identified by '1'; ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement mysql> flush privileges; mysql> alter user root@'localhost' identified by '1'; Query OK, 0 rows affected (0.01 sec) 7.4 关闭数据库，正常启动验证
登录数据库报错案例：忘记密码
7.1 关闭数据库
[root@db01 ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
7.2 启动数据库到维护模式
[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
7.3 登录并修改密码
1，
mysql> alter user root@'localhost' identified by '1';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> flush privileges;
mysql> alter user root@'localhost' identified by '1';
Query OK, 0 rows affected (0.01 sec)
2，
[root@localhost data]# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
update user set authentication_string=password("123456") where user='root';
flush privileges;
killall mysqld
ps -aux|grep mysql
mysqld_safe --skip-grant-tables --skip-networking &
mysql -uroot -p
killall mysqld
ps -aux|grep mysqld
service mysqld start
mysql -uroot
use mysql;
update user set authentication_string=password("123456") where user='root';
flush privileges;
mysql远程连接报错： Host * is not allowed to connect to this MySQL server，解决方法
localhost改成%
1.进入mysql的BIN目录
注：root为管理员用户名，password为用户root的密码：
mysql -u root -p password
mysql>use mysql;
mysql>update user set host ='%'where user ='root' and host ='localhost';
mysql>flush privileges;
注：这时候连接此服务器的mysql客户端需要重新启动下，然后再连接此服务器。
mysql> select user,host ,authentication_string from mysql.user; +---------------+-----------+-------------------------------------------+ | user | host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | sunlong | 10.0.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +---------------+-----------+-------------------------------------------+ mysql> update user set host ='%'where user ='root' and host ='localhost.localdomain';
mysql> flush privileges; Query OK,
登录mysql出现报错：
Your password has expired. To log in you must change it using a client that supports expired passwords
错误直接翻译过来即：您的密码已过期，您必须使用支持过期密码的客户端进行更改
解决方法：
mysql -u root -p 输入之前正确的密码进入 设置新密码 set password=password('123456') 设置密码永不过期 alter user 'root'@'%' password expire never
用户管理：
2.1.2 用户的定义
用户名@'白名单'
白名单？
oldguo@'10.0.0.51'
oldguo@'10.0.0.%'
oldguo@'10.0.0.5%'
oldguo@'10.0.0.0/255.255.254.0'
oldguo@'%'
oldguo@'oldguo.com'
oldguo@'localhost'
oldguo@'db01'
常用的：
oldguo@'10.0.0.%'
oldguo@'10.0.0.5%'
oldguo@'10.0.0.0/255.255.254.0'
oldguo@'localhost'
bc@'白名单' 密码123
白名单: 允许白名单中的IP,可以通过abc用户及123密码登录和管理MySQL
白名单支持定义的方式:
10.0.0.52 10.0.0.% ==> 10.0.0.1~10.0.0.254/24 10.0.0.5% ==> 10.0.0.50 ~10.0.0.59（包含10.0.0.5） www.oldguo.com % 10.0.0.0/255.255.254.0
2.2、用户管理命令
2.2.1、创建用户
create user abc@'10.0.0.%' identified by '123'; select user ,host from mysql.user;
2.2.2、删除用户
drop user abc@'10.0.0.%';
2.2.3、修改用户密码
alter user abc@'10.0.0.%' identified by '456';
查询用户
mysql> select user,host ,authentication_string from mysql.user; +---------------+-----------+-------------------------------------------+ | user | host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | sunlong | 10.0.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +---------------+-----------+-------------------------------------------+ 4 rows in set (0.00 sec)
2.2.2 权限的定义
MySQL的权限定义就是SQL语句。
ALL:
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
ALL : 以上所有权限，一般是普通管理员拥有的
with grant option：超级管理员才具备的，给别的用户授权的功能
2.2.3 授权管理
（1）语法：
grant ALL on wordpress.* to wordpress@'10.0.0.%' identified by '123';
grant 权限 on 范围 to 用户 identified by '密码'
grant select,update,insert,delete on 范围 to 用户 identified by '密码'
范围：
*.*
wordpress.*
wordpress.t1
（2）例子：
1. 从windows中的navicat软件使用root管理mysql数据库
grant all on *.* to root@'10.0.0.%' identified by '123';
2. 创建 zhihu业务用户能够对zhihu业务库进行业务操作
grant select,update,delete ,insert on zhihu.* to zhihu@'10.0.0.%' identified by '123';
（3）思考一个问题（课后自己进行验证）：
1. grant select,update on *.* to oldboy@'10.0.0.%';
2. grant delete on wordpress.* to oldboy@'10.0.0.%';
3. grant insert on wordpress.t1 to oldboy@'10.0.0.%';
问，oldboy@'10.0.0.%' 能对t1表具备什么权限？
MySQL中的权限是可以继承，多次授权是叠加的。
所以，想要取消某个权限，必须通过回收的方式实现，而不能多次重复授权。
（4）查看用户权限
mysql> show grants for root@'localhost';
（5）回收权限
mysql> revoke delete on zhihu.* from 'zhihu'@'10.0.0.%';
3. MySQL 连接管理
3.1 自带的客户端工具
mysql 参数
-u
-p
-S
-h
-P
[root@db01 ~]# mysql -uroot -p -S /tmp/mysql.sock
[root@db01 ~]# mysql -uoldguo -p -h10.0.0.51 -P3306
问题：
怎么证明你的数据库是可用的？
1. 证明进程和端口存在
ps -ef |grep mysqld
netstat -lnp|grep mysqld
2. 验证用户远程连接性
[root@db01 ~]# mysql -uoldguo -p -h10.0.0.51
-e
<
[root@db01 ~]# mysql -uoldguo -p123 -h10.0.0.51 -e "show databases;"
[root@db01 ~]# mysql -uroot -p <world.sql
3.2 远程的客户端工具
navicat sqlyog
略。
4. MySQL 启动方式介绍
辅助脚本方式（普通的启动关闭）：
sys-v : /etc/init.d/mysqld
/etc/init.d/mysqld start
----> /application/mysql/bin/mysqld_safe
----> /application/mysql/bin/mysqld
systemd : /etc/systemd/system/mysqld.service
systemctl start mysqld
---->/application/mysql/bin/mysqld
维护性的启动方式：
/application/mysql/bin/mysqld_safe --skip-grant-tables --skip-networking &
/application/mysql/bin/mysqld &
5.2 初始化配置文件(my.cnf)
5.2.1 初始化配置文件默认读取位置
[root@db01 ~]# mysqld --help --verbose |grep "my.cnf"
/etc/my.cnf --》 /etc/mysql/my.cnf --》 /usr/local/mysql/etc/my.cnf --》 ~/.my.cnf
最后一个会覆盖前面的配置文件
指定配置文件启动方式
--defaults-file=/opt/my.cnf
/application/mysql/bin/mysqld_safe --defaults-file=/opt/my.cnf
/application/mysql/bin/ --defaults-file=/opt/my.cnf
5.3 命令行模式
[root@db01 ~]# mysqld_safe --defaults-file=/opt/my.cnf --socket=/tmp/asdad &
5.4.3 mysql初始化配置常用参数(通用模板)
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/data/mysql.log
log_bin=/data/mysql/data/mysql-bin
[mysql]
socket=/tmp/mysql.sock
mysql多实例
一台机器启动多个mysql实例
5.5.1 创建目录 mkdir -p /data/330{7,8,9}/data 5.5.2 准备配置文件 cat > /data/3307/my.cnf <<EOF [mysqld] basedir=/application/mysql datadir=/data/3307/data socket=/data/3307/mysql.sock log_error=/data/3307/mysql.log port=3307 server_id=7 log_bin=/data/3307/mysql-bin EOF cat > /data/3308/my.cnf <<EOF [mysqld] basedir=/application/mysql datadir=/data/3308/data socket=/data/3308/mysql.sock log_error=/data/3308/mysql.log port=3308 server_id=8 log_bin=/data/3308/mysql-bin EOF cat > /data/3309/my.cnf <<EOF [mysqld] basedir=/application/mysql datadir=/data/3309/data socket=/data/3309/mysql.sock log_error=/data/3309/mysql.log port=3309 server_id=9 log_bin=/data/3309/mysql-bin EOF 5.5.3 初始化三套数据 mv /etc/my.cnf /etc/my.cnf.bak mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/application/mysql mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/application/mysql mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/application/mysql 5.5.4 systemd管理多实例 cd /etc/systemd/system cp mysqld.service mysqld3307.service cp mysqld.service mysqld3308.service cp mysqld.service mysqld3309.service vim mysqld3307.service ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf vim mysqld3308.service ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf vim mysqld3309.service ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf 5.5.5 授权 chown -R mysql.mysql /data/* 5.5.6 启动 systemctl start mysqld3307.service systemctl start mysqld3308.service systemctl start mysqld3309.service 5.5.7 验证多实例 netstat -lnp|grep 330 mysql -S /data/3307/mysql.sock -e "select @@server_id" mysql -S /data/3308/mysql.sock -e "select @@server_id" mysql -S /data/3309/mysql.sock -e "select @@server_id"
登录多实例：
mysql -uroot -p -S /data/3307/mysql.sock -P3307
修改密码：
mysql> mysql -u root -p 输入之前正确的密码进入 设置新密码 mysql> set password=password('123456') 设置密码永不过期 mysql> alter user 'root'@'localhost' password expire never
mysql> use mysql;
mysql> select user,host ,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> use mysql;
mysql> update user set host ='localhost'where user ='root' and host ='localhost';
mysql> flush privileges;