MySQL-day3-权限与主从、以及配置
文章目录
MySQL数据库权限与主从、以及配置
一、mysqld服务程序构成
- mysqld是一个守护进程
1.连接层
- 验证用户的身份，用户名密码是否匹配
- 提供两种连接方式（TCP/IP连接、socket连接）
- 连接层提供了一个与sql层交互的线程
2.SQL层
-
接收连接层传过来的SQL语句
-
验证执行的SQL语法
-
验证SQL的语义（DDL,DML,DQL,DCL）
-
解析器：解析SQL语句，生成执行计划
-
优化器：将解析器传来的执行计划选择最优的一条执行
-
执行器：将最优的一条执行
- 与存储引擎层建立交互的线程- 将要执行的sql发给存储引擎层
-
如果有缓存，则走缓存
-
记-录日志（binlog）
3.存储引擎层
- 接收SQL层传来的语句
- 与磁盘交互，获取数据，返回给sql层
- 建立与sql层交互的线程
二、mysql的多实例
Nginx多实例：
- 多个配置文件
mysql多实例：
- 多个数据目录
- 多个端口
- 多个socket文件
- 多个日志文件
1.创建多个数据目录
[root@db01 ~]# mkdir /data/{3307,3308,3309} -p
2.准备多个配置文件
[root@db01 data]# vim /data/3307/my.cnf
[mysqld]
basedir=/service/mysql
datadir=/data/3307/data
port=3307
socket=/data/3307/mysql.sock
log-error=/data/3307/data/mysql.err
log-bin=/data/3307/data/mysql-bin
server_id=7
-------------------------------------------
[root@db01 data]# vim /data/3308/my.cnf
[mysqld]
basedir=/service/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/data/mysql.err
log-bin=/data/3308/data/mysql-bin
server_id=8
--------------------------------------------
[root@db01 data]# vim /data/3309/my.cnf
[mysqld]
basedir=/service/mysql
datadir=/data/3309/data
port=3309
socket=/data/3309/mysql.sock
log-error=/data/3309/data/mysql.err
log-bin=/data/3309/data/mysql-bin
server_id=9
3.初始化多套数据目录
[root@db01 scripts]# ./mysql_install_db --defaults-file=/data/3307/my.cnf --user=mysql --basedir=/service/mysql --datadir=/data/3307/data
[root@db01 scripts]# ./mysql_install_db --defaults-file=/data/3308/my.cnf --user=mysql --basedir=/service/mysql --datadir=/data/3308/data
[root@db01 scripts]# ./mysql_install_db --defaults-file=/data/3309/my.cnf --user=mysql --basedir=/service/mysql --datadir=/data/3309/data
#使用tree可以查看
[root@db01 scripts]# tree -L 3 /data
4.授权目录
[root@db01 scripts]# chown -R mysql.mysql /data
5.启动数据库
[root@db01 scripts]# mysqld_safe --defaults-file=/data/3307/my.cnf &
[root@db01 scripts]# mysqld_safe --defaults-file=/data/3308/my.cnf &
[root@db01 scripts]# mysqld_safe --defaults-file=/data/3309/my.cnf &
6.启动数据库报错解决
1.启动以上服务失败比如：
[root@db01 scripts]# mysqld_safe --defaults-file=/data/3309/my.cnf & 失败！
“mysqld_safe mysqld from pid file /data/3309/data/web03. exepid” # 若出现此提示
2.错误原因：
是由于swap分区可用内存不够了，只需增加一下swap空间内存就可以了！
# 详情参考 https://www.copylian.com/technology/96.html 解决方法参考此文档！
3.解决方法如下：
sudo dd if=/dev/zero of=/swapfiles bs=1M count=1024 # 吐一个如此大的临时交换分区文件
sudo mkswap /swapfiles # 制作临时swap交换分区
sudo swapon /swapfiles # 开启临时swap交换分区
mysqld_safe --defaults-file=/data/3309/my.cnf & # 再次启动测试，成功！
7.检查启动
[root@db01 scripts]# netstat -lntup|grep 330
tcp6 0 0 :::3307 :::* LISTEN 25550/mysqld
tcp6 0 0 :::3308 :::* LISTEN 25722/mysqld
tcp6 0 0 :::3309 :::* LISTEN 25894/mysqld
8.多实例设置密码
[root@db01 scripts]# mysqladmin -uroot -S /data/3307/mysql.sock password '3307'
[root@db01 scripts]# mysqladmin -uroot -S /data/3308/mysql.sock password '3308'
[root@db01 scripts]# mysqladmin -uroot -S /data/3309/mysql.sock password '3309'
9.多实例验证
[root@db01 scripts]# mysql -uroot -p3307 -S /data/3307/mysql.sock -e "show variables like 'server_id';"
Warning: Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
[root@db01 scripts]# mysql -uroot -p3308 -S /data/3308/mysql.sock -e "show variables like 'server_id';"
Warning: Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 8 |
+---------------+-------+
[root@db01 scripts]# mysql -uroot -p3309 -S /data/3309/mysql.sock -e "show variables like 'server_id';"
Warning: Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
10.连接多实例小技巧
[root@db01 scripts]# vim /usr/bin/mysql3309
mysql -uroot -p3309 -S /data/3309/mysql.sock
[root@db01 scripts]# vim /usr/bin/mysql3308
mysql -uroot -p3308 -S /data/3308/mysql.sock
[root@db01 scripts]# vim /usr/bin/mysql3307
mysql -uroot -p3307 -S /data/3307/mysql.sock
[root@db01 scripts]# chmod +x /usr/bin/mysql*
三、数据库多实例主从
| 主库 | 从库 |
|---|---|
| db01 | db02 |
主库db01操作：
1.创建主从复制用户
[root@db01 ~]# mysql -uroot -p123
mysql> grant replication slave on *.* to rep@'127.0.0.1' identified by '123';
Query OK, 0 rows affected (0.00 sec)
2.查看binlog名和位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 467 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从库db02操作：
3.从库配置主库的信息
[root@db02 ~]# mysql -uroot -p123
#从库需要知道：我的主库是谁?主库的主从复制用户是谁?主从复制用户的密码?端口?binlog名?binlog位置
mysql> change master to
-> master_host='127.0.0.1',
-> master_user='rep',
-> master_password='123',
-> master_log_file='mysql-bin.000003',
-> master_log_pos=467,
-> master_port=3306;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
4.从库开启主从复制
[root@db02 ~]#
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
5.检查主从复制状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: rep
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 467
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
6.主从复制状态错误
1）如果IO线程是NO
1.检测网络:
[root@db01 data]# ping 127.0.0.1
2.检测端口:
[root@db01 data]# telnet 127.0.0.1 3307
3.检测账号,密码:
mysql -urep -p123 -h127.0.0.1 -P 3307
2）如果sql线程是NO
1.确定数据库主从之前数据同步
2.确定主库有的库和表从库也有
#解决方法：
方法一：数据要求不严格的情况，忽略错误后，继续同步
解决：
stop slave;
#表示跳过一步错误，后面的数字可变
set global sql_slave_skip_counter =1;
start slave;
之后再用mysql> show slave status\G 查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
ok，现在主从同步状态正常了。。。
方式二：重新做主从，完全同步
3）连接时有反向解析的问题
#连接时地址反向解析了
[root@db01 ~]# mysql -uroot -p123 -h10.0.0.51
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'db01' (using password: YES)
#配置忽略反向解析
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
skip-name-resolve
四、MySQL5.6与MySQL5.7安装的区别
1.cmake的时候加入了bostorg
建议大家使用二进制的包安装，已经过了cmake阶段，已经把bostorg装好了
[root@db02 mysql-5.7.20]# yum install -y gcc gcc-c++ automake autoconf
[root@db02 mysql-5.7.20]# yum install make cmake bison-devel ncurses-devel libaio-devel
[root@db02 mysql-5.7.20]# wget httpss://dl.bintray.com/boostorg/release/1.65.1/source/boost_1_59_0.tar.gz # 登录boost.org下载也可以
[root@db02 mysql-5.7.20]# tar xf boost_1_59_0.tar.gz -C /usr/local/
[root@db02 mysql-5.7.20]# 直接复制下面一段回车即可，注意目录，若要求不同可自行修改
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 \
#安装bostorg
-DDOWNLOAD_BOOST=1 \
#安装的目录
-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
2.初始化时 使用的命令不一样了
5.6版本初始化命令：
/service/mysql/scripts/mysql_install_db --user= --basedir= --datadir=
5.7版本初始化命令：
/usr/local/mysql/bin/mysqld --initialize --user= --basedir= --datadir=
# 初始--initialize会生成一个临时的随机密码，他会告诉你位置，你自己去找使用密码登录，而且修改密码也需要一个很复杂的密码，大小写数字特殊符号
# 想不让他生成密码的话，还可以用另外一个参数--initialize-insecure
五、mysql用户权限管理
1.linux和mysql用户对比
| 环境 | linux系统 | mysql数据库 |
|---|---|---|
| 用户作用 | 1.登陆系统 2.管理系统文件 | 1.登陆MySQL数据库 2.管理数据库对象 |
| 创建用户 | 1.useradd 2.adduser | 1.create user test@’%’; 2.grant |
| 用户密码 | 1.useradd cm -p 123 2.passwd cm | 1.create user test@’%’ identified by ‘123’; 2.mysqladmin |
| 删除用户 | userdel | 1.drop user test@’%’; 2.delete |
| 修改用户 | usermod | update |
2.在MySQL中,用户是如何定义的
mysql> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| | db01 |
| root | db01 |
| | localhost |
| root | localhost |
+------+-----------+
6 rows in set (0.00 sec)
#上面是6个用户，在数据库中一个完整的用户是：'用户名'@'主机域'
'用户名'@'主机域'
主机域写法：
localhost
127.0.0.1
10.0.0.%
10.0.%.%
10.%.%.%
%
10.0.0.5%（10.0.0.50-10.0.0.59）
db01
10.0.0.0/255.255.255.0
10.0.0.0/24 #可以创建但是不生效
测试：
grant all on *.* to lhd@'10.0.0.0/255.255.255.0' identified by '123';
grant all on *.* to qiudao@'10.0.0.0/24' identified by '123';
#创建的用户是数字，必须用引号一起来表示字符串
mysql> create user 123@'localhost';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '123@'localhost'' at line 1
mysql> create user '123'@'localhost';
Query OK, 0 rows affected (0.02 sec)
3.用户管理命令示例
1）
1.创建用户
mysql> create user lhd@'10.0.0.%' identified by '123';
2.查看用户
mysql> select user,host from mysql.user;
3.删除用户
mysql> drop user oldboy@‘10.0.0.%’;
4.修改密码
1）mysqladmin -uroot -p123 password '1'
2）update mysql.user set password=password('123') where user='root' and host='localhost';
3）set password=password('1'); 修改当前用户的密码
4）grant all on *.* to 'root'@'localhost' identified by '123';
5.忘记root用户密码
1）停止MySQL
[root@db01 data]# /etc/init.d/mysqld stop
2）跳过授权表,安全启动
[root@db01 data]# mysqld_safe --skip-grant-tables --skip-networking &
3）连接数据库
[root@db01 data]# mysql
4.）update修改密码
mysql> update mysql.user set password=password('123456789') where user='root' and host='localhost';
5)刷新授权表
mysql> flush privileges;
6)重启数据库
[root@db01 data]# /etc/init.d/mysqld restart
7)使用新密码链接数据库
[root@db01 data]# mysql -uroot -p567
4.权限管理
授权命令：
grant all privileges on *.* to oldboy@'10.0.0.%' identified by '123';
grant all on *.* to oldboy@'10.0.0.%' identified by '123';
授权SQL 所有权限 在 所有库.所有表 上面 给 用户名@主机域 密码 为 123；
1）作用对象：所有库、所有表
grant all privileges on *.*
grant select,update,insert on mysql.* 单库
grant select,update,insert on mysql.user 单表
grant select(user,host) on mysql.user to ptuser@'%' identified by '123'; # 单列级别；企业里称单列授权为 脱敏：脱离敏感信息
举例：（相亲网站）
grant select on mysql.user to vip@'%' identified by '123';
grant select(user) on mysql.user to diaosi@'%' identified by '123';
2）在企业中权限的设定
开发人员说：请给我开一个用户
1.首先进行沟通
1.你需要对哪些库、表进行操作
2.你从哪里连接过来
3.用户名有没有要求
4.密码要求
5.你要使用多长时间
6.发邮件
2.一般给开发创建用户权限 # 建议delete权限也不给
grant select,update,delete,insert on *.* to oldboy@’10.0.0.%’ identified by ‘123’;
3.注意：开发想要root用户，打死也不能给，这个申请到谁那里批准，也不给，让老大给
5.实践思考
1）准备数据库
1.创建wordpress数据库
create database wordpress;
2.使用wordpress库
use wordpress;
3.创建t1、t2表
create table t1 (id int);
create table t2 (id int);
4.创建blog库
create database blog;
5.使用blog库
use blog;
6.创建t1表
create table tb1 (id int);
2）授权
1.给wordpress@'10.0.0.5%'用户查询所有库,所有表的权限,密码是 123
grant select on *.* to wordpress@’10.0.0.5%’ identified by ‘123’;
2.给wordpress@'10.0.0.5%'用户插入,删除,修改 针对wordpress库下的所有表 密码是123
grant insert,delete,update on wordpress.* to wordpress@’10.0.0.5%’ identified by ‘123’;
3.给wordpress@'10.0.0.5%'用户所有权限针对wordpress库下的t1表 密码是123
grant all on wordpress.t1 to wordpress@’10.0.0.5%’ identified by ‘123’;
3）问题
一个客户端程序使用wordpress用户登陆到10.0.0.51的MySQL后：
- 对t1表的管理能力？ all
- 对t2表的管理能力？ select insert,delete,update 增删改查
- 3、对tb1表的管理能力? select
4）权限总结
- 如果在不同级别都包含某个表的管理能力时，权限是相加关系。
- 但是我们不推荐在多级别定义重复权限。
- 最常用的权限设定方式是单库级别授权，即：wordpress.*
- 如果涉及到用户的敏感信息,需要做脱敏,单列级别授权 即:select(列名) 单库.单表
九、作业
1.搭建数据库多实例，三个实例
2.将多实例3307,3308 做成3309的从库
