当配置好 MySQL 主从复制以后所有对数据内容的更新就必须在主库上进行那么为什么所有的更新都要在主服务器上进行呢? 这是因为数据复制时单向的, 只有在主库上更新, 才能避免用户对主服务器上数据库内容的更新与对从服务器上内容的一致, 而不发生冲突
1. 生产 MySQL 复制环境用户授权方案
那么怎么才能达到上述效果呢?
(1) 生产环境主库用户授权
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON blog.* TO blog@192.168.10.% identified 123456;
提示: 特殊业务可能权限会略多, 如果业务安全性不高也可以 all privileges
(2) 生产环境从库用户的授权
- mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON blog.* TO blog@192.168.10.% identified 123456;
- REVOKE INSERT,UPDATE,DELETE ON blog.* TO blog@192.168.10.%;
(3) 生产授权案例说明: 这里表示给 192.168.10.% 的用户 blog 管理 blog 数据库的所有表 (* 表示所有表) 只读权限(SELECT), 密码为 123456
GRANT SELECT,INSERT,UPDATE,DELETE ON blog.* TO blog@192.168.10.% identified 123456;
生产环境主从库用户授权
主库:
GRANT SELECT,INSERT,UPDATE,DELETE ON blog.* TO blog@192.168.10.% identified 123456;
从库:
GRANT SELECT ONblog. * TOblog@192.168.10. % identified123456;
如何实现上述授权方案
最简单的方法是在主库配置 binlog-ignore-db=mysql
2. 忽略授权表的方式防止数据写从库的方法及实践
生产环境中一般采用忽略授权表的方式同步, 然后对从服务器 (slave) 上的用户仅授权 select 读权限, 不同步 mysql 库, 这样我们就保证主库和从库相同的用户可以授权不同的权限指定 mysql 库不同步
忽略 mysql 和 information_schema 库的主从同步
- replicate-ignore-db=mysql
- binlog-ignore-db = mysql
- binlog-ignore-db = performance_schema
- binlog-ignore-db = information_schema
提示: 如何在主库上忽略 mysql 库的同步, 方法:
(1)只有在 [主从] 库上分别设置 replicate-ignore-db=mysql 才可以做到从库不同步 mysql 库
(2)在主库上设置 binlog-ignore-db=mysql 不记录有关 mysql 库更新的 binlog 来达到从库不同步 mysql 库
3. 通过 read-only 参数防止数据库写从库的方案
除了上面从库仅做 SELECT 的授权外, 还可以在 slave 服务器启动选项增加参数或者在 my.cnf 配置文件中加 read-only 参数来确保从库只读, 使用授权用户和 read-only 参数二者同时操作效果更佳
注意 read-only 参数可以让 slave 服务器只允许来自 slave 服务器线程或具有 SUPER 权限的用户的更新可以确保 slave 服务器不接受来自普通用户的更新
(1)配置从库 my.cnf 配置文件 mysqld 下重启从数据库
- [root@mysql ~]# egrep "\[mysqld]|read-only" /data/3307/my.cnf
- [mysqld]
- read-only
- [root@mysql ~]# /data/3307/mysql stop
- Stoping MySQL....
- [root@mysql ~]# /data/3307/mysql start
- Starting MySQL......
(2)read-only 参数对 SUPER 权限的用户无效, 用 SUPER 用户登录创建一个普通用户
- mysql> grant select,insert,update,delete on *.* to nana@localhost identified by 123456;
- Query OK, 0 rows affected (0.00 sec)
- mysql> flush privileges;
- Query OK, 0 rows affected (0.00 sec)
(3)在 school 库中创建一个表退出用普通用户登录, 在创建的表中插入一条记录, 演示 read-only 的效果
- [root@mysql ~]# mysql -unana -p123456 -S /data/3307/mysql.sock
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 4
- Server version: 5.5.32 Source distribution
- Copyright (c) 2000, 2013, 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> select user();
- +----------------+
- | user() |
- +----------------+
- | nana@localhost |
- +----------------+
- 1 row in set (0.00 sec)
- mysql> use school;
- Database changed
- mysql> show tables;
- +------------------+
- | Tables_in_school |
- +------------------+
- | t|
- +------------------+
- 1 row in set (0.00 sec)
- mysql> insert into t values(2);
- ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
(4)最后我们看一下是否能同步主库
主库中插入数据
- [root@mysql ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock <<EOF
- > use linzhongniao
> insert into test1 values(4, 不认识),(5, 你是谁);
> exit
> EOF
从库同步情况
- [root@mysql ~]# mysql -unana -p123456 -S /data/3307/mysql.sock
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 4
- Server version: 5.5.32 Source distribution
- Copyright (c) 2000, 2013, 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> select * from linzhongniao.test1;
- +----+-----------+
- | id | name |
- +----+-----------+
| 1 | 张三 |
| 2 | 张三 |
| 3 | 我是谁 |
| 4 | 不认识 |
| 5 | 你是谁 |
- +----+-----------+
- 5 rows in set (0.00 sec)
Mysql DBA 高级运维学习笔记 - 生产场景 mysql 主从复制读写分离授权方案及实战
来源: http://www.bubuko.com/infodetail-2493976.html