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_db5.7：mysqld –initialize ## 有初始密码5.7：mysqld –initialize-insecure ## 没有初始密码yum安装MySQL
## 1.下载MySQL的yum源[root@db04 ~]# wget https://dev.mysql.com/get/mysql80-community-release-el7-6.noarch.rpm## 2.安装MySQL的yum源[root@db04 ~]# rpm -ivh mysql80-community-release-el7-6.noarch.rpm## 3.查看仓库中mysql版本[root@db04 ~]# yum list[root@db04 ~]# yum list | grep mysql## 4.修改yum源[root@db04 ~]# vim /etc/yum.repos.d/mysql-community.repo[mysql57-community]name=MySQL 5.7 Community Serverbaseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearchenabled=0 改成 1[mysql80-community]name=MySQL 8.0 Community Serverbaseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearchenabled=1 改成 0gpgcheck=1## 5.安装mysql5.7[root@db04 ~]# yum install -y mysql-server### 如遇下面报错,需要在后面添加–nogpgcheck[root@db04 ~]# yum install -y mysql-server –nogpgcheck
## 6.启动服务[root@db04 [root@db04 ~]# cat /var/log/mysqld.log systemctl start mysqld## 7.查看日志[root@db04 ~]# cat /var/log/mysqld.log## 8.找密码[root@db04 ~]# grep ‘root’ /var/log/mysqld.log2022-08-04T07:49:05.985359Z 1 [Note] A temporary password is generated for root@localhost: #Uygmj)!t2ly## 9.连接数据库[root@db04 ~]# mysql -uroot -p’#Uygmj)!t2ly’## 10.修改配置文件MySQL使用简单密码[root@db04 ~]# vim /etc/my.cnfvalidate_password_length=1# 密码长度validate_password_mixed_case_count=0# 大小写字符个数validate_password_number_count=0# 数字个数validate_password_special_char_count=0 # 特殊字符个数## 11.重启mysql[root@db04 ~]# systemctl restart mysqld## 12.修改数据库用户密码mysql> alter user root@’localhost’ identified by ‘123’;Query OK, 0 rows affected (0.00 sec)MySQL用户权限管理MySQL用户操作
Linux用户的作用:
登录系统管理系统文件
Linux用户管理：
创建用户：useradd adduser删除用户：userdel修改用户：usermod
MySQL用户的作用:
登陆MySQL数据库管理数据库对象
MySQL中的用户定义
在MySQL中，用户不仅仅是用户名
用户名@主机域
主机域：
localhost127.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 (不支持)用户管理增## mysql5.6创建用户mysql> create user wsh@’localhost’;mysql> create user wsh1@’localhost’ identified by ‘123’;Query OK, 0 rows affected (0.00 sec)## mysql5.7创建用户(必须给密码创建)mysql> create user wsh@’localhost’;ERROR 1819 (HY000): Your password does not satisfy the current policy requirementsmysql> create user wsh@’localhost’ identified by ‘123’;Query OK, 0 rows affected (0.00 sec)### 注意：以前的5.7不支持grant在用户不存在的情况下执行，现在版本支持mysql> select user from mysql.user;+—————+| user |+—————+| mysql.session || mysql.sys || root || wsh |+—————+4 rows in set (0.00 sec)mysql> grant all on *.* to wsh2@’localhost’ identified by ‘123’;Query OK, 0 rows affected, 1 warning (0.00 sec)grant授权命令all == all privileges：所有权限，但不包括授权权限grant all on root@’localhost’ identified by ‘123’ with grant option;*.*：所有库，下面所有表wordpress.*wordpress.userwsh@’localhost’：完整用户identified by ‘123’：密码123删# drop user 用户名@’主机域’;mysql> drop user wsh2@’localhost’; # 相当于在linux删系统用户 userdel -r wsh2Query OK, 0 rows affected (0.00 sec)## 直接操作表删除用户:# 使用库mysql> use mysql;# 查看库里的表mysql> show tables;mysql> delete from mysql.user where user=’wsh’ and host=’loccalhost’;Query OK, 1 row affected (0.00 sec)改## 改权限# 添加权限mysql> create user wsh2@’localhost’ identified by ‘123’;Query OK, 0 rows affected (0.00 sec)mysql> grant update on *.* to wsh2@’localhost’;Query OK, 0 rows affected (0.00 sec)# 回收权限mysql> revoke select on *.* from wsh2@’localhost’;Query OK, 0 rows affected (0.00 sec)## 改密码[root@db04 ~]# mysqladmin -uroot -p123 password ‘456’mysql> grant all on *.* to root@’localhost’ identified by ‘789’;mysql> alter user root@’localhost’ identified by ‘123’;## 只能修改当前连接上的用户mysql> set password=PASSWORD(‘wsh2’);[root@db04 ~]# mysql -uroot -p’wsh2’# 只有使用update想修改密码时，才需要执行flush privilege;### 5.6mysql> update mysql.user set password=PASSWORD(‘abc’) where user=’root’ andhost=’localhost’;mysql> flush privileges;### 5.7mysql> update mysql.user set password=PASSWORD(‘abc’) where user=’root’ and host=’localhost’;mysql> flush privileges;忘记root密码[root@db04 ~]# systemctl stop mysqld.service [root@db04 ~]# mysqld –user=mysql –skip-grant-tables &查## 查看所有用户mysql> select * from mysql.user\G## 查看指定字段mysql> selecet user,host from mysql.user;## 查看用户的权限mysql> 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’; ## 单字段级别
企业中给开发开权限
INSERT,SELECT, UPDATE, DELETEINSERT,SELECT, UPDATE
开发：请给我开一个用户
沟通：
1、你需要对哪些库、表进行操作2、你从哪里连接过来3、用户名有没有要求4、密码要求5、发邮件*************
开发：你把root用户给我呗？
权限小练习#创建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%’用户查看所有库下的所有表权限,密码是123grant insert,delete,update on wordpress.* to wordpress@’10.0.0.5%’ identified by ‘123’;给wordpress@’10.0.0.5%’用户wordpress库下的所有表insert,delete,update权限，密码是123grant 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+32、对t2表的管理能力？ # select，insert,delete,update 1+23、对tb1表的管理能力？ # select 1结论：1、如果在不同级别都包含某个表的管理能力时，权限是相加关系。2、但是我们不推荐在多级别定义重复权限。3、最常用的权限设定方式是单库级别授权，即：wordpress.*MySQL多实例
**单实例：**一个后台进程 + 多个线程 + 一个预分配的内存结构
**多实例：**多个后台进程 + 多个线程 + 多个预分配的内存结构
多个配置文件
多个端口data目录socket文件多个日志文件pid文件
多个启动脚本
## 1.准备多个配置文件[root@db01 ~]# vim /data/3307/my.cnf[mysqld]basedir=/application/mysqldatadir=/data/3307/datasocket=/data/3307/data/3307.socklog_error=/data/3307/data/3307.errpid_file=/data/3307/data/3307.pidport=3307[root@db01 ~]# vim /data/3308/my.cnf[mysqld]basedir=/application/mysqldatadir=/data/3308/datasocket=/data/3308/data/3308.socklog_error=/data/3308/data/3308.errpid_file=/data/3308/data/3308.pidport=3308[root@db01 ~]# vim /data/3309/my.cnf[mysqld]basedir=/application/mysqldatadir=/data/3309/datasocket=/data/3309/data/3309.socklog_error=/data/3309/data/3309.errpid_file=/data/3309/data/3309.pidport=3309## 2.创建数据目录[root@db01 ~]# mkdir -p /data/330{7..9}## 3.初始化[root@db01 ~]# cd /application/mysql/scripts/[root@db01 scripts]# ./mysql_install_db –defaults-file=/data/3307/my.cnf –user=mysql –basedir=/application/mysql –datadir=/data/3307/data[root@db01 scripts]# ./mysql_install_db –defaults-file=/data/3308/my.cnf –user=mysql –basedir=/application/mysql –datadir=/data/3308/data[root@db01 scripts]# ./mysql_install_db –defaults-file=/data/3309/my.cnf –user=mysql –basedir=/application/mysql –datadir=/data/3309/data## 4.查看目录结构[root@db01 scripts]# tree -L 2 /data//data/├── 3307│?? ├── data│?? └── my.cnf├── 3308│?? ├── data│?? └── my.cnf└── 3309 ├── data └── my.cnf ## 5.启动[root@db01 scripts]# mysqld –defaults-file=/data/3307/my.cnf –user=mysql &[root@db01 scripts]# mysqld –defaults-file=/data/3308/my.cnf –user=mysql &[root@db01 scripts]# mysqld –defaults-file=/data/3309/my.cnf –user=mysql &#### 使用systemd管理## 6.连接[root@db01 scripts]# mysql -uroot -p -S /data/3307/data/3307.sock[root@db01 scripts]# mysql -uroot -p -S /data/3308/data/3308.sock[root@db01 scripts]# mysql -uroot -p -S /data/3309/data/3309.sock## 7.连接小技巧[root@db01 scripts]# vim /usr/bin/mysql3307mysql -uroot -p -S /data/3307/data/3307.sock[root@db01 scripts]# chmod +x /usr/bin/mysql3307# 编辑配置文件[root@db01 data]# vim /usr/lib/systemd/system/mysql3307.service[Unit]Description=MySQL ServerDocumentation=man:mysqld(8)Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.htmlAfter=network.targetAfter=syslog.target[Install]WantedBy=multi-user.target[Service]User=mysqlGroup=mysqlExecStart=/application/mysql/bin/mysqld –defaults-file=/data/3307/my.cnf –user=mysqlLimitNOFILE = 5000[root@db01 data]# systemctl start mysql3307[root@db01 data]# netstat -lntup |grep mysql3307[root@db01 data]# netstat -lntup |grep 3307tcp6 0 0 :::3307 :::* LISTEN 5034/mysqld [root@db01 data]# netstat -lntup |grep 3307[root@db01 data]# [root@db01 data]# mysql3307Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.50 Source distributionCopyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.mysql> 带着感恩的心启程，学会爱，爱父母，爱自己，爱朋友，爱他人。
