前提:在两台centos6.7(64 bit) 上都安装完成mysql5.7.16,这步没做的请参照我的上一篇文章
maste中配置:
server-id=1
log-bin=mysql-bin
binlog-format=mixed
slave中配置:
server-id=2
mysql的用户与权限管理:
用户连接到mysql,并作各种查询
[用户]-->[服务器]
分为2个阶段:
1.你有没有权限连接上来
2.你有没有权限执行此操作(如select、update等)
对于1:服务器如何判断用户有没有权限连接上来?
依据3个参数:
你从哪儿来:host
你是谁:user
你的密码是多少:password
用户的这3个信息,存储在mysql.user表中
--修改host域,使IP可以连接
update user set host='192.168.0.222' where user='root'
flush privileges;--冲刷权限
--如何修改用户的密码
update user set password=password('111111') where user='root';
flush privileges;
对于2:
--新增一个用户
grant[权限1,权限2,权限3...] on *.* to user@'host' identified by 'password'
常用的权限有all,create,drop,insert,delete,update,select
grant all on *.* to lisi@'192.168.10.%' identified by '111111';
--收回权限
revoke all on *.* from lisi@'192.168.10.%';
--删除用户
DROP USER lisi@'192.168.0.%';
--针对某个库做授权
grant all on ecshop.* to lisi@'192.168.1.%';
REVOKE ALL ON ecshop.* FROM lisi@'192.168.0.%';
FLUSH PRIVILEGES;
--针对表做授权
grant insert,update,select on gy3.goods to lisi@'192.168.0.%';
--测试,用户可以增改查,但不能删除数据
提示:如果在开发中,某张表的数据,是原始数据,不能被删除,
除了在业务逻辑控制
还可以干什么?
提示:mysql的权限控制,甚至可以精确到列(自己动手)
mysql主从复制 replication
①.主服务器要配置binlog
②.从服务器要配置relaylog
③.从服务器如何有权读取master的binlog(binlog是比较敏感的) 答:授权,master要授予slave账号
④.从服务器用账号连接master
原理:
主从分离
主:
binlog-format=row/statement/mixed
statement:2进制记录执行语句,如update...
row:2进制记录的是磁盘的变化
哪个好?
update age=age+1 where id=3;//语句长而磁盘变化少,宜用row
update salary=salary+100;//语句短,但影响上万行,磁盘变化大,宜用statement
mixed:不知道用哪个,混合的,由系统根据语句,来决定
查看主是否具备主的条件:具备
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> grant replication client,replication slave on *.* to repl@'192.168.0.%' identified by 'repl';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 348 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
从:
查看从是否具备从的条件:不具备
mysql> show slave status;
Empty set (0.00 sec)
mysql> change master to
-> master_host='192.168.0.102',
-> master_user='repl',
-> master_password='repl',
-> master_log_file='mysql-bin.000002',
-> master_log_pos=348;
Query OK, 0 rows affected (0.10 sec)
看看从是否连接上主:
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.102
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 348
Relay_Log_File: mysql-relay.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
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: 348
Relay_Log_Space: 106
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
启动从:
mysql> start slave;
Query OK, 0 rows affected (0.13 sec)
stop slave;//关闭从的意思
场景:
实施:
change master to
master_host='192.168.0.102',
master_user='repl',
master_password='repl',
master_log_file='mysql-bin.000002',
master_log_pos=348;