MySQL DBA Advanced Operations Learning note-production scenario MySQL master-slave copy read/write separation authorization scheme and actual combat

When the MySQL master-slave copy is configured, all updates to the data content must be made on the main library. So why do all the updates take place on the master server? This is because the data is copied one-way and only updated on the main library to prevent users from updating the contents of the database on the primary server to match the content on the server, without conflict.

1. Production MySQL replication Environment user authorization scheme

So how can we achieve this effect?

(1) Production environment Master Library user authorization

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON ‘blog‘.* TO ‘blog‘@‘192.168.10.%‘ identified ‘123456‘;

Tip: Special business may have slightly more permissions and can all privileges if business security is not high

(2) Authorization of production environment from library users

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) Production authorization case Description: Here the user blog to 192.168.10.% to manage all tables of the blog database (* represents all tables) read-only permission (SELECT), the password is 123456.

GRANT SELECT,INSERT,UPDATE,DELETE ON ‘blog‘.* TO ‘blog‘@‘192.168.10.%‘ identified ‘123456‘;

Production environment master-Slave Library user authorization

Main Library:

GRANT SELECT,INSERT,UPDATE,DELETE ON ‘blog‘.* TO ‘blog‘@‘192.168.10.%‘ identified ‘123456‘;

From library:

GRANT SELECT ON ‘blog‘.* TO ‘blog‘@‘192.168.10.%‘ identified ‘123456‘;

How to implement the above authorization scheme

The simplest way is to configure Binlog-ignore-db=mysql in the main library

2. Methods and practices to prevent data from being written from the library by ignoring the authorization table

In a production environment, it is common to use the Ignore authorization table to synchronize, and then to the user on the slave server (slave) to authorize only select Read permissions, unsynchronized MySQL library, so that we ensure that the main library and the same user from the library can authorize different permissions. Specifies that the MySQL library is out of sync.
Ignores the master-slave synchronization of MySQL and information_schema libraries.

replicate-ignore-db=mysqlbinlog-ignore-db = mysqlbinlog-ignore-db = performance_schemabinlog-ignore-db = information_schema

Tip: How to ignore MySQL library synchronization on the main library by:

(1) only in the [master-slave] library to set Replicate-ignore-db=mysql can be done from the library out of sync MySQL library.

(2) Setting binlog-ignore-db=mysql on the main library does not log the MySQL library update binlog to reach the MySQL library from the library out of sync.

3. Prevent the database from writing a schema from the library via the read-only parameter

In addition to the above authorization to select only from the library, you can also increase the parameters in the Slave server startup option or add read-only parameters to the MY.CNF configuration file to ensure read-only from the library, using both the authorized user and the Read-only parameter to operate better.
Note The read-only parameter allows the slave server to allow updates only from slave server threads or users with super privileges. You can ensure that the slave server does not accept updates from ordinary users.

(1) Configure the reboot from the library my.cnf configuration file mysqld from the database

[[email protected] ~]# egrep "\[mysqld]|read-only" /data/3307/my.cnf [mysqld]read-only[[email protected] ~]# /data/3307/mysql stopStoping MySQL....[[email protected] ~]# /data/3307/mysql startStarting MySQL......

(2) read-only parameter is invalid for Super privilege user, create a normal user with super User login

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) Create a table in the school library exit log in with a normal user, insert a record in the created table, and demonstrate the effect of read-only.

[[email protected] ~]# mysql -unana -p123456 -S /data/3307/mysql.sock Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.5.32 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> select user();+----------------+| user() |+----------------+| [email protected] |+----------------+1 row in set (0.00 sec)mysql> use school;Database changedmysql> 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) Finally, let's see if we can synchronize the main library

Inserting data into the main library

[[email protected] ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock <<EOF> use linzhongniao> insert into test1 values(4,‘不认识‘),(5,‘你是谁‘);> exit> EOF

Synchronizing from Library

[[email protected] ~]# mysql -unana -p123456 -S /data/3307/mysql.sock Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.5.32 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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 Advanced Operations Learning note-production scenario MySQL master-slave copy read/write separation authorization scheme and actual combat