Mysql版本区别及管理

Mysql版本区别及管理

mysql5.6与mysql5.7的安装的区别

1.编译，需要下载boost
2.编译参数
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.7.20 \
-DMYSQL_DATADIR=/application/mysql-5.7.20/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.7.20/tmp/mysql.sock \
-DDOWNLOAD_BOOST=1 \ ## mysql-5.7.38.tar.gz没有boost需要添加该参数
-DWITH_BOOST=/usr/local/boost_1_59_0 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITH_ZLIB=bundled \
-DWITH_SSL=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLE_DOWNLOADS=1 \
-DWITH_DEBUG=0

3.初始化
5.6：mysql_install_db
5.7：mysqld --initialize ## 有初始密码
5.7：mysqld --initialize-insecure ## 没有初始密码

yum安装mysql

# 下载mysql的yum源
[root@db01 ~]# wget https://dev.mysql.com/get/mysql80-community-release-el7-6.noarch.rpm

# 安装mysql的yum源
[root@db01 ~]# rpm -ivh mysql80-community-release-el7-6.noarch.rpm

# 修改yum源（5.7）
[root@db01 yum.repos.d]# vim mysql-community.repo
[mysql57-community]
enabled=0 改成 1
[mysql80-community]
enabled=1 改成 0

# 安装mysql5.7.38
yum install -y mysql-community-server-5.7.38

# 查看日志
[root@db01 yum.repos.d]# cat /var/log/mysqld.log

# 找密码
[root@db01 yum.repos.d]# grep 'root' /var/log/mysqld.log
2022-08-04T09:48:49.008364Z 1 [Note] A temporary password is generated for
root@localhost: Q6>kAhmpY:0w

# 连接
mysql -uroot -p'iiSxNhX<-3MN'

#  修改配置文件mysql使用简单密码
validate_password_length=1
validate_password_mixed_case_count=0
validate_password_number_count=0
validate_password_special_char_count=0

[root@db01 yum.repos.d]# systemctl restart mysqld

# 改密码
alter user root@'localhost' identified by '123';

mysql用户权限管理

mysql用户操作

linux用户的作用

• 登录系统
• 管理系统文件

linux用户管理

• 创建用户
• 删除用户
• 修改用户

MySQL用户的作用

• 登陆MySQL数据库
• 管理数据库对象

MySQL中的用户定义

在MySQL中，用户不仅仅是用户名

用户名@主机域

主机域：

localhost
127.0.0.1
%
10.0.0.%
10.0.%.%
10.%.%.%
10.0.0.5%(10.0.0.50-10.0.0.59包括10.0.0.5)
db01（不能直接连，需要做解析）
10.0.0.0/255.255.255.0 == 10.0.0.%
10.0.0.0//24（不支持）

用户管理

增

# 5.6创建用户
create user wc2@'localhost' identified by '789'；

# 5.7创建用户
create user wc2@'localhost' identified by '789'

mysql> grant all on *.* to wc3@'localhost' identified by '123';

grant	授权命令
all == all privileges	所有权限，但不包括授权权限
grant all on root@'localhost' identified by '123' with grant option;
*.*		所有库，下面所有表
zh.*
zh.user
wc3@'localhost'		完整用户
identified by '123'	密码123

删

# drop user 用户@'主机域'
drop user wc1@'localhost';

# 直接操作表删除用户
delete from mysql.user where user='wc2' and host='localhost';

改

## 刷新授权表
mysql> flush privileges;
flush privileges 命令本质上的作用是将当前user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中提取到内存里。 
MySQL用户数据和权限有修改后，希望在"不重启MySQL服务"的情况下直接生效，那么就需要执行这个命令


# 添加权限
grant update on *.* to wc1@'localhost';

# 回收权限
revoke select on *.* from wc1@'localhost';

# 改密码
[root@db01 ~]# mysqladmin -uroot -p456 password '789'
grant all on *.* to root@'localhost' identified by '123';
alter user root@'localhost' identified by '789';

# 只能修改连接上的用户
alter user root@'localhost' identified by '789';

# 只有使用update想修改密码时，才需要执行flush privilege；
5.6：
update mysql.user set password=PASSWORD('abc') where user='wc7' and host='localhost';
flush privileges;

5.7
update mysql.user set authentication_string=PASSWORD('abc') where user='wc1' and host='localhost';
flush privileges;

忘记root密码

[root@db01 yum.repos.d]# systemctl stop mysqld.service 
[root@db01 yum.repos.d]# mysqld --user=mysql --skip-grant-tables &
如果起不来，就去注释my.cnf文件中的简单密码配置
[root@db01 yum.repos.d]# mysql

update mysql.user set authentication_string=PASSWORD('123') where user='root' and host='localhost';
flush privileges;
重启mysql

查

# 查看所有用户
select * from mysql.user\G;

# 查看指定字段
select user,host from mysql.user;

# 查看用户权限
show grants for root@'localhost';

用户的权限

INSERT,SELECT, 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

作用对象分解

. [当前MySQL实例中所有库下的所有表] 
wordpress.* [当前MySQL实例中wordpress库中所有表（单库级别）]
wordpress.user [当前MySQL实例中wordpress库中的user表（单表级别）]

脱敏

# 单字段级别
mysql> grant select(user,host) on mysql.user to test@'%' identified by '123'; 

权限小练习

#创建wordpress数据库
create database wordpress;
#使用wordpress库
use wordpress;
#创建t1、t2表
create table t1 (id int);
create table t2 (id int);
#创建blog库
create database blog;
#使用blog库
use blog;
#创建t1表
create table tb1 (id int);

wordpress库
- t1表
- t2表
blog库
- tb1表

grant select on *.* to wordpress@’10.0.0.5%’ identified by ‘123’;
给wordpress@'10.0.0.5%'用户查看所有库下的所有表权限,密码是123

grant insert,delete,update on wordpress.* to wordpress@’10.0.0.5%’ identified by‘123’;
给wordpress@'10.0.0.5%'用户wordpress库下的所有表insert,delete,update权限，密码是123

grant all on wordpress.t1 to wordpress@’10.0.0.5%’ identified by ‘123’;
给wordpress@'10.0.0.5%'用户wordpress库下的t1表所有的权限，密码是123

问：
一个客户端程序使用wordpress用户登陆到10.0.0.51的MySQL后，

1、对t1表的管理能力？ # 所有权限 1+2+3
2、对t2表的管理能力？ # select，insert,delete,update 1+2
3、对tb1表的管理能力？ # select 1

结论：
1.如果在不同级别都包含在某个表的管理能力时，权限是相加关系。
2.但是我们不推荐在多级别定义重复权限
3.最常用的权限设定方式是单库级别授权，即：wordpress.*

MySQL多实例

单实例：一个后台进程 + 多个线程 + 一个预分配的内存结构

多实例：多个后台进程 + 多个线程 + 多个预防配的内存结构

多个配置文件

多个端口

data目录

socket文件

多个日志文件

pid文件

多个启动脚本

# 创建data目录
[root@db02 scripts]# mkdir -p /data/330{7..9}

# 准备多个配置文件
[root@db02 scripts]# cat /data/3307/my.cnf 
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/data/3307.sock
log_error=/data/3307/data/3307.err
pid_file=/data/3307/data/3307.pid
port=3307

[root@db02 scripts]# cat /data/3307/my.cnf 
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
socket=/data/3308/data/3308.sock
log_error=/data/3308/data/3308.err
pid_file=/data/3308/data/3308.pid
port=3308

[root@db02 scripts]# cat  /data/3309/my.cnf 
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/data/3309.sock
log_error=/data/3309/data/3309.err
pid_file=/data/3309/data/3309.pid
port=3309

# 初始化
[root@da02 ~]# cd /application/mysql/scripts/
[root@da02 scripts]# ./mysql_install_db --defaults-file=/data/3307/my.cnf --user=mysql
--basedir=/application/mysql --datadir=/data/3307/data
[root@da02 scripts]# ./mysql_install_db --defaults-file=/data/3308/my.cnf --user=mysql
--basedir=/application/mysql --datadir=/data/3308/data
[root@da02 scripts]# ./mysql_install_db --defaults-file=/data/3309/my.cnf --user=mysql
--basedir=/application/mysql --datadir=/data/3309/data

# 查看目录结构
[root@da02 scripts]# tree -L 2 /data/
/data/
├── 3307
│ ├── data
│ └── my.cnf
├── 3308
│ ├── data
│ └── my.cnf
└── 3309
├── data
└── my.cnf

# 启动
[root@da02 scripts]# mysqld --defaults-file=/data/3307/my.cnf --user=mysql &
[root@da02 scripts]# mysqld --defaults-file=/data/3308/my.cnf --user=mysql &
[root@da02 scripts]# mysqld --defaults-file=/data/3309/my.cnf --user=mysql &

# 连接
[root@da02 scripts]# mysql -uroot -p -S /data/3307/data/3307.sock
[root@da02 scripts]# mysql -uroot -p -S /data/3308/data/3308.sock
[root@da02 scripts]# mysql -uroot -p -S /data/3309/data/3309.sock

# 连接小技巧
[root@da02 scripts]# vim /usr/bin/mysql3309
mysql -uroot -p -S /data/3309/data/3309.sock
[root@da02 scripts]# chmod +x /usr/bin/mysql3309

[root@da02 scripts]# vim /usr/bin/mysql3308
mysql -uroot -p -S /data/3308/data/3308.sock
[root@da02 scripts]# chmod +x /usr/bin/mysql3308

[root@da02 scripts]# vim /usr/bin/mysql3307
mysql -uroot -p -S /data/3307/data/3307.sock
[root@da02 scripts]# chmod +x /usr/bin/mysql3307

作业

使用MySQL5.7做多实例

使用systemd管理多实例

# 1.准备多个配置文件 
[root@db01 ~]# vim /data/3307/my.cnf 
[mysqld] 
basedir=/application/mysql 
datadir=/data/3307/data 
socket=/data/3307/data/3307.sock 
log_error=/data/3307/data/3307.err 
pid_file=/data/3307/data/3307.pid 
port=3307 
[root@db01 ~]# vim /data/3308/my.cnf 
[mysqld] 
basedir=/application/mysql 
datadir=/data/3308/data 
socket=/data/3308/data/3308.sock 
log_error=/data/3308/data/3308.err 
pid_file=/data/3308/data/3308.pid 
port=3308 
[root@db01 ~]# vim /data/3309/my.cnf 
[mysqld] 
basedir=/application/mysql 
datadir=/data/3309/data 
socket=/data/3309/data/3309.sock 
log_error=/data/3309/data/3309.err
pid_file=/data/3309/data/3309.pid
port=3309

# 2.创建数据目录 
[root@db01 ~]# mkdir -p /data/330{7..9}
# 3.初始化 
[root@db01 ~]# /application/mysql/bin/mysqld --initialize-insecure --user=mysql -- basedir=/application/mysql --datadir=/data/3307/data
[root@db01 ~]# /application/mysql/bin/mysqld --initialize-insecure --user=mysql -- basedir=/application/mysql --datadir=/data/3308/data
[root@db01 ~]# /application/mysql/bin/mysqld --initialize-insecure --user=mysql -- basedir=/application/mysql --datadir=/data/3309/data 
# 4.查看目录结构 
[root@db01 ~]# tree -L 2 /data/ 

# 5.启动 
[root@db01 ~]# mysqld --defaults-file=/data/3307/my.cnf --user=mysql & 
[root@db01 ~]# mysqld --defaults-file=/data/3308/my.cnf --user=mysql & 
[root@db01 ~]# mysqld --defaults-file=/data/3309/my.cnf --user=mysql & 
# 使用systemd管理 
[root@db01 ~]# vim /usr/lib/systemd/system/mysqld3307.service 
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=/data/3307/my.cnf LimitNOFILE = 5000 
[root@db03 ~]# vim /usr/lib/systemd/system/mysqld3308.service 
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=/data/3308/my.cnf LimitNOFILE = 5000

[root@db01 ~]# vim /usr/lib/systemd/system/mysqld3309.service 
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=/data/3309/my.cnf LimitNOFILE = 5000

# 6.连接 
[root@db01 ~]# mysql -uroot -p -S /data/3307/data/3307.sock 
[root@db01 ~]# mysql -uroot -p -S /data/3308/data/3308.sock
[root@db01 ~]# mysql -uroot -p -S /data/3309/data/3309.sock

# 7.连接小技巧 
[root@db01 ~]# vim /usr/bin/mysql3309 mysql -uroot -p -S/data/3309/data/3309.sock 
[root@da02scripts]# chmod +x /usr/bin/mysql3309 
[root@db01 ~]# vim /usr/bin/mysql3308 mysql -uroot -p -S /data/3308/data/3308.sock
[root@da02scripts]# chmod +x /usr/bin/mysql3308 
[root@db01 ~]# vim /usr/bin/mysql3307 mysql -uroot -p -S /data/3307/data/3307.sock 
[root@db01 ~]# chmod +x /usr/bin/mysql3307