一、mysql服务程序构成

1.连接层

1.验证用户身份
2.提供两种连接方式
3.与SQL层建立交互的进程

2.SQL层

1.接收连接层发送的SQL语句
2.验证执行的SQL语法
3.验证SQL语义
4.解析器：解析SQL语句，生成执行计划
5.优化器：将解析器的执行计划选择出最优的一条
6.执行器：执行优化器选出来的最优的执行计划
    与存储引擎层建立交互的进程，将sql发送给存储引擎层
7.缓存
8.binlog日志

3.存储引擎层

1.与SQL层建立交互的进程
2.接收SQL层发过来的SQL语句
3.去磁盘去除要获取的数据

二、mysql的多实例

nginx多实例，就是配置多个配置文件

mysql多实例：
1.有多个配置文件
2.多端口
3.多个socket文件
4.多个日志文件
5.多个server_id

1.创建多实例存放目录

[root@db01 ~]# mkdir /service/{3307,3308,3309} -p

2.配置多实例的配置文件

[root@db01 ~]# vim /service/3307/my.cnf
[mysqld]
basedir=/service/mysql
datadir=/service/3307/data
port=3307
socket=/service/3307/mysql.sock
log-error=/service/3307/data/mysql.err
log-bin=/service/3307/data/mysql-bin
server_id=7

[root@db01 ~]# vim /service/3308/my.cnf
[mysqld]
basedir=/service/mysql
datadir=/service/3308/data
port=3308
socket=/service/3308/mysql.sock
log-error=/service/3308/data/mysql.err
log-bin=/service/3308/data/mysql-bin
server_id=8

[root@db01 ~]# vim /service/3309/my.cnf
[mysqld]
basedir=/service/mysql
datadir=/service/3309/data
port=3309
socket=/service/3309/mysql.sock
log-error=/service/3309/data/mysql.err
log-bin=/service/3309/data/mysql-bin
server_id=9

3.初始化多实例数据库

[root@db01 ~]# cd /service/mysql/scripts/

[root@db01 scripts]# ./mysql_install_db --defaults-file=/service/3307/my.cnf --user=mysql --basedir=/service/mysql --datadir=/service/3307/data

[root@db01 scripts]# ./mysql_install_db --defaults-file=/service/3308/my.cnf --user=mysql --basedir=/service/mysql --datadir=/service/3308/data

[root@db01 scripts]# ./mysql_install_db --defaults-file=/service/3309/my.cnf --user=mysql --basedir=/service/mysql --datadir=/service/3309/data

[root@db01 scripts]# tree -L 3 /service/
/service/
├── 3307
│   ├── data
│   │   ├── ibdata1
│   │   ├── ib_logfile0
│   │   ├── ib_logfile1
│   │   ├── mysql
│   │   ├── mysql-bin.000001
│   │   ├── mysql-bin.000002
│   │   ├── mysql-bin.index
│   │   ├── mysql.err
│   │   ├── performance_schema
│   │   └── test
│   └── my.cnf

4.授权

[root@db01 service]# chown -R mysql.mysql /service/

5.启动数据库多实例

[root@db01 service]# mysqld_safe --defaults-file=/service/3307/my.cnf &

[root@db01 service]# mysqld_safe --defaults-file=/service/3308/my.cnf &

[root@db01 service]# mysqld_safe --defaults-file=/service/3309/my.cnf &

6.检查启动

[root@db01 service]# netstat -lntp | grep 330
tcp6       0      0 :::3306                 :::*                    LISTEN      7495/mysqld         
tcp6       0      0 :::3307                 :::*                    LISTEN      8471/mysqld         
tcp6       0      0 :::3308                 :::*                    LISTEN      8643/mysqld         
tcp6       0      0 :::3309                 :::*                    LISTEN      8815/mysqld

7.登录验证多实例

[root@db01 service]# mysql -uroot -S /service/3307/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 7     |
+---------------+-------+

[root@db01 service]# mysql -uroot -S /service/3308/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 8     |
+---------------+-------+

[root@db01 service]# mysql -uroot -S /service/3309/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 9     |
+---------------+-------+

8.多实例设置密码

[root@db01 service]# mysqladmin -uroot password '123' -S /service/3307/mysql.sock 
[root@db01 service]# mysqladmin -uroot password '123' -S /service/3308/mysql.sock 
[root@db01 service]# mysqladmin -uroot password '123' -S /service/3309/mysql.sock

9.连接数据库小技巧

[root@db01 service]# vim /usr/bin/mysql3307
mysql -uroot -p123 -S /service/3307/mysql.sock

[root@db01 service]# vim /usr/bin/mysql3308
mysql -uroot -p123 -S /service/3308/mysql.sock

[root@db01 service]# vim /usr/bin/mysql3309
mysql -uroot -p123 -S /service/3309/mysql.sock

[root@db01 service]# chmod +x /usr/bin/mysql330*

三、数据库多实例主从

1.主库创建主从使用的用户（3307数据库）

mysql> grant replication slave on *.* to rep@'127.0.0.1' identified by '123';

2.主库查看binlog信息（3307数据库）

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      662 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.从库配置（3308数据库）

#从库需要知道主的信息：
    主库是谁？
    做主从同步的用户是谁？
    做主从同步的密码是多少？
    端口？
    从哪里同步？binlog名字？binlog的位置点？

mysql> change master to
    -> master_host='127.0.0.1',
    -> master_user='rep',
    -> master_password='123',
    -> master_port=3307,
    -> master_log_file='mysql-bin.000003',
    -> master_log_pos=662;
Query OK, 0 rows affected, 2 warnings (0.06 sec)

4.从库开启主从复制

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: 662
               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.如果IO线程是NO

1.检测网络
[root@db01 ~]# ping 127.0.0.1

2.检测端口
[root@db01 ~]# telnet 127.0.0.1 3307

3.检测账号密码
[root@db01 ~]# mysql -urep -p123 -h 127.0.0.1 -P 3307

change master to
master_host='127.0.0.1',
master_user='rep',
master_password='123',
master_port=3307,
master_log_file='mysql-bin.000003',
master_log_pos=753;

7.如果SQL线程是NO

#说明从库与主库数据不一致
解决方法：
 最根本的，先同步主库he从库数据再重新做主从

四、mysql5.6和mysql5.7安装的区别

1.cmake的时候加入了boostorg

如果安装mysql5.7，建议使用二进制包安装，因为它已经cmake过了，已经装好了boostorg了

[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 \
#安装boost
-DDOWNLOAD_BOOST=1 \
#指定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

2.初始化的命令不一样了

#5.6初始化命令
[root@db01 ~]# /service/mysql/scripts/mysql_install_db --user=mysql --basedir=/service/mysql --datadir=/service/mysql/data

#5.7初始化命令
[root@db01 ~]# /service/mysql/bin/mysqld --initialize --user=mysql --basedir=/service/mysql --datadir=/service/mysql/data
--initialize 会生成一个密码文件
--initialize-insecure 还可以用另外一个参数，这个不会生成密码文件

五、mysql用户权限管理

1.linux用户和mysql用户对比

2.在mysql中，用户是怎么定义的

#在mysql中，定义一个完整的用户是：
'用户名'@'主机域'

用户名写法：
    如果是字符不需要加引号：rep@'27.0.0.1'
    如果是数字作为用户则加引号：'123'@'127.0.0.1'

主机域的写法：
    localhost
    127.0.0.1
    db01
    ::1
    %
    10.%.%.%
    10.0.%.%
    10.0.0.%   (10.0.0.1-10.0.0.255)
    10.0.0.5%  (10.0.0.50-10.0.0.59)
    10.0.0.0/255.255.255.0
    10.0.0.0/24 #可以创建，但是不生效

3.用户管理

1）创建用户

create user lhd@'10.0.0.%' identfied by '123';

2）查看用户

select user,host from mysql.user;

3）删除用户

drop user lhd@'10.0.0.%';

4）修改用户密码

1.命令行使用mysqladmin修改密码
[root@db01 ~]# mysqladmin -uroot -p123 password '123456'

2.授权的方式修改密码
mysql> grant all on *.* to root@'localhost' identified by '123';

3.更新数据修改密码
mysql> update mysql.user set password=password('123456') where user='root' and host='localhost';
mysql> flush privileges;   #刷新权限    

4.set不加条件修改密码
mysql> set password=password('123');     ##修改当前登录数据库用户的密码

5）忘记了root用户的密码

1.停止数据库
[root@db01 ~]# systemctl stop mysql

2.跳过授权表和网络启动数据库
[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &

3.连接数据库
[root@db01 ~]# mysql

4.update修改密码
mysql> update mysql.user set password=password('123456') where user='root' and host='localhost';

5.刷新授权表
mysql> flush privileges;

6.正常启动数据库测试
[root@db01 ~]# mysqladmin -p123456 shutdown
[root@db01 ~]# systemctl start mysql
[root@db01 ~]# mysql -uroot -p123456

4.权限管理

#授权的命令
grant all privileges on *.* to root@'localhost' identified by '123';
grant all on *.* to root@'localhost' identified by '123';

grant            #授权命令
all                #所有权限
on                #在......上
*.*                #所有库.所有表
to                #给
root@'localhost'    #'用户名'@'主机域'
identified        #密码
by                #是
'123'            #密码本身

1）所有权限

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

2）对象的授权级别

grant all on *.* to root@'localhost' identified by '123';            所有库所有表
grant all on mysql.* to gcc@'localhost' identified by '123';        单库授权
grant all on mysql.user to zy@'localhost' identified by '123';        单表授权

#企业里面称单列授权为 脱敏：脱离敏感信息
grant select(user,host) on mysql.user to zzy@'localhost' identified by '123';

#举例：
    1.普通用户只能看到user列
    grant select(user) on mysql.user to diaosi@'localhost' identified by '123';
    2.VIP用户能看到所有
    grant all on mysql.user to vip@'localhost' identified by '123';

3）在企业里权限的设定

#开发要开一个用户连接数据库

1.进行沟通：
    1.1 你要操作哪些库哪些表？
    1.2 你从哪个ip连接过来？
    1.3 用户名你想用什么？
    1.4 密码想设置什么？
    1.5 这个用户你要用多久？
    1.6 发邮件

2.一般给开发什么权限
    grant select,update,insert on test.* to dev@'10.0.0.101' identified by 'Lhd@123.com';

3.千万千万不能随便把root权限交出去

5.权限实践

1）准备数据库

#创建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;
#创建tb1表
create table tb1 (id int);

2）授权

#给wordpress@'10.0.0.5%'用户查询所有库下所有表的权限，密码是123
1、grant select on *.* to wordpress@'10.0.0.5%' identified by '123';

#给wordpress@'10.0.0.5%'用户增加，删除，修改wordpress库下所有表的权限，密码是123
2、grant insert,delete,update on wordpress.* to wordpress@'10.0.0.5%' identified by '123';

#给wordpress@'10.0.0.5%'所有权限操作wordpress库下面的t1表，密码是123
3、grant all on wordpress.t1 to wordpress@'10.0.0.5%' identified by '123';

3）提问：

一个客户端程序使用wordpress用户登陆到10.0.0.51的MySQL后，
1.对t1表的操作权限？            所有权限：all
2.对t2表的操作权限？            增删改查：select,insert,delete,update
3.对tb1表的操作权限？            查的权限：select

4）权限总结

1.如果在不同级别授权，权限是相加关系
2.我们不推荐在多级别定义重复的权限
3.最常见的授权方式就是单库授权
    grant select,insert,delete,update on wordpress.* to wordpress@'10.0.0.5%' identified by '123';
4.如果涉及到用户的敏感信息，我们可以选择使用脱敏授权，即单列授权
5.查看已授权的用户权限
    mysql> show grants for root@'localhost';