MYSQL5.7.28数据库之主从复制+mysql权限管理（数据库级别、表字段级别）

主数据库：192.168.15.102
从数据库：192.168.15.103

mysql安装版本：Server version: 5.7.28

第一步：
两台机器安装相同版本的mysql；
第二步：
检查两台机器的免密登录，防火墙等是否已经关闭；
第三步：
开始进行主从复制的配置
主从配置的主要原理是主库每操作一条DDL或者DML语句，都会向日志写入执行果的sql，从库也不断向日志读取sql执行

1-修改 /etc/my.cnf 文件(主库)
# 配置主从数据库(主)
[mysqld]
log-bin=mysql-bin
server-id=2
binlog-ignore-db=information_schema     #不需要同步的配置
binlog-ignore-db=mysql                  #可以配置多个不需要同步的库
binlog-do-db=hive                       #配置需要同步的库，也可以配置多个

2-修改 /etc/my.cnf 文件(从库)
[mysqld]
log-bin=mysql-bin               #开启日志
server-id=3
replicate-do-db=hive           #需要跟从的数据
log-slave-updates
slave-skip-errors=all            #跳过错误
slave-net-timeout=60          #slave_net_timeout表示slave在slave_net_timeout时间之内没有收到master的任何数据(包括binlog，heartbeat)，slave认为连接断开，会进行重连。

3-重启主库和从库两台节点的mysql服务
service mysqld restart（主库）
service mysqld restart（从库）

4-登陆主库，配置同步账号（slave_copy）
添加同步账号slave_copy

mysql> create user 'slave_copy'@'%' identified by '123456';

# 指定任何ip的slave_copy用户登录
#create user 'slave_copy'@'%' identified by '123456';

为slave_copy 账号赋权限：

grant replication slave on *.* to 'slave_copy'@'%' identified by '123456'; 

#grant 权限列表 on 数据库.表名 to '用户名'@'访问主机' identified by '密码'; 
#为slave_copy用户赋权，让其拥有复制权限 注意replication slave 是一种权限，连在一块的单词

flush privileges;

#刷新使得权限生效 

进入mysql执行 select user,host from mysql.user; 看看从库使用的账号是否创建成功
 select user,host from mysql.user;

mysql> select user,host from mysql.user;

show master status; 命令查询主库信息

mysql> show master status;

5-登陆从库配置数据同步账号 root账号登录
执行 
change master to master_host='192.168.15.102',
master_user='slave_copy',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=2443;   

#mysql> change master to master_host='主库ip',
#mysql>master_user='slave_copy',
#mysql>master_password='slave_copy账号的密码',
#mysql>master_log_file='mysql-bin.000001',
#mysql>master_log_pos=726;    

#master_log_file和master_log_pos
#和在主库执行的show master里面的信息保持一致即可

执行start slave;开启从库线程

mysql>  start slave;

执行 show slave status\G; 查看到主库的ip,port以及是否连接成功的信息
mysql>  show slave status\G;

检查
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
是否都为yes

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.15.102
                  Master_User: slave_copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 2443
               Relay_Log_File: hadoop103-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: hive
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2443
              Relay_Log_Space: 531
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: 647684af-e146-11eb-8f66-000c2962c44a
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

如果出现了  Slave_IO_Running: Connecting 这样的信息基本可以确定是密码 、pos 、 防火墙没关闭这三个原因其中的一种

6-验证是否成功
在主库的hive库里面添加一张表

mysql> CREATE TABLE student1(
    ->   id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
    ->   name VARCHAR(200) COMMENT '姓名',
    ->   age    int COMMENT '年龄'
    -> ) COMMENT='学生信息';

在从库里面检查这张表是否存在

从数据库开启停止主从复制：

开启：mysql>  start slave;

停止：mysql>  start slave;

补充知识：

对mysql用户等的操作

A-对新用户增删改

1.创建用户:
# 指定ip：192.118.1.1的alex用户登录
create user 'alex'@'192.118.1.1' identified by '123';
# 指定ip：192.118.1.开头的alex用户登录
create user 'alex'@'192.118.1.%' identified by '123';
# 指定任何ip的alex用户登录
create user 'alex'@'%' identified by '123';

2.删除用户
drop user '用户名'@'IP地址';

3.修改用户
rename user '用户名'@'IP地址' to '新用户名'@'IP地址';

4.修改密码
set password for '用户名'@'IP地址'=Password('新密码');

B-对当前的用户授权管理

#查看权限
show grants for '用户'@'IP地址'

#授权 alex用户仅对db1.t1文件有查询、插入和更新的操作
grant select ,insert,update on db1.t1 to "alex"@'%';

# 表示有所有的权限，除了grant这个命令，这个命令是root才有的。alex用户对db1下的t1文件有任意操作
grant all privileges  on db1.t1 to "alex"@'%';
#mjj用户对db1数据库中的文件执行任何操作
grant all privileges  on db1.* to "alex"@'%';
#mjj用户对所有数据库中文件有任何操作
grant all privileges  on *.*  to "alex"@'%';
 
#取消权限
 
# 取消alex用户对db1的t1文件的任意操作
revoke all on db1.t1 from 'alex'@"%";  

# 取消来自远程服务器的alex用户对数据库db1的所有表的所有权限

revoke all on db1.* from 'alex'@"%";  

取消来自远程服务器的alex用户所有数据库的所有的表的权限
revoke all privileges on *.* from 'alex'@'%';

mysql也可以进行表级别的字段限制：如下所示：

针对Mike账号 db1库下面的t1表的 id，name字段授予select权限，age字段授予update权限

授权格式 select(要授权的字段，要授权的字段) 用户括号 括起来  、update()

mysql> grant select(id,name),update(age) on db1.t1 to 'mike'@'localhost';
Query OK, 0 rows affected (0.11 sec)
 

授权的记录

mysql> select * from mysql.columns_priv;
+-----------+-----+------+------------+-------------+---------------------+-------------+
| Host      | Db  | User | Table_name | Column_name | Timestamp           | Column_priv |
+-----------+-----+------+------------+-------------+---------------------+-------------+
| localhost | db1 | mike | t1         | id          | 0000-00-00 00:00:00 | Select      |
| localhost | db1 | mike | t1         | name        | 0000-00-00 00:00:00 | Select      |
| localhost | db1 | mike | t1         | age         | 0000-00-00 00:00:00 | Update      |
+-----------+-----+------+------------+-------------+---------------------+-------------+
3 rows in set (0.00 sec)
 

验证

 

mysql> exit
Bye

[root@mysql ~]# mysql -umike -p123
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.6.36 Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

mysql> select * from t1;
ERROR 1142 (42000): SELECT command denied to user 'mike'@'localhost' for table 't1'


mysql> select id,name from t1;
+------+------+
| id   | name |
+------+------+
|    1 | mike |
|    2 | alex |
|    3 | NULL |
|    4 | NULL |
+------+------+
4 rows in set (0.00 sec)
*代表所有字段

只能查看t1表中的 id，name字段

 

不能查age字段 但可以用update age字段

mysql> select age from t1;
ERROR 1143 (42000): SELECT command denied to user 'mike'@'localhost' for column 'age' in table 't1'