MySQL Operations management-production scenarios MySQL master-slave copy read/write separation authorization scheme and actual combat 15

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‘;mysql> 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 approach is to configure BINLOG-IGNORE-DB=MYSQL2 in the main library. Ways and practices of preventing data from being written from libraries by ignoring authorization tables

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 conflicts from library data cause synchronization to stop

Show Slave Status Error
and show Slave status\g

Slave_IO_Running: YesSlave_SQL_Running: NOSeconds_Behind_Master: NULLLast_Error:Error ‘Can’t create database ‘linzhongniao’;database exists’on query.Default database:’linzhongniao’.Query：‘create database linzhongniao’

Workaround:

Method One:

Stop slaveSet global sql_slave_skip_counter=1Start slave

(1) for the ordinary Internet business, ignoring the problem is not very big, of course, to confirm that does not affect the company's business premise.

(2) The enterprise scenario solves the master-slave synchronization, which is more important to the current business than the master. If the master-slave data is consistent it is important to find a time to recover this from the library.

Method Two: Skip the specified error based on the error number

[[email protected] ~]# grep skip /data/3306/my.cnfskip-name-resolve 忽略名称解析解决mysql连接慢的问题。slave-skip-errors = 1032,1062 一般由于入库重复导致的失败就可以忽略。

Of course, you can also set the value of Slave-skip-errors to all, which causes the master-slave library data synchronization data to be missing. We can refer to the MySQL common error code to set the error number we want to ignore, and the error number to resolve the problem with MySQL. 2.mysql Common error Codes

1005: Failed to create table

1006: Failed to create database

1007: Database already exists, database creation failed <================= can be ignored

1008: Database does not exist, delete database failed <================= can be ignored

1009: Unable to delete database file causes database failure to be deleted

1010: Unable to delete data directory causes database failure to be deleted

1011: Failed to delete database file

1012: Cannot read records in system tables

1020: Record has been modified by another user

1021: Insufficient space on the hard drive, please increase the hard disk free space

1022: keyword Repeat, change record failed

1023: An error occurred while shutting down

1024: Read File error

1025: An error occurred while changing the name

1026: Write File Error

1032: Record does not exist <============================= can be ignored

1036: The data table is read-only and cannot be modified

1037: Insufficient system memory, please restart the database or restart the server

1038: Insufficient memory for sorting, increase the sort buffer

1040: The maximum number of connections to the database has been reached, please increase the number of available connections to the database

1041: Insufficient system memory

1042: Invalid host name

1043: Invalid connection

1044: The current user does not have permission to access the database

1045: Unable to connect to database, user name or password error

1048: field cannot be empty

1049: Database does not exist

1050: Data table already exists

1051: Data table does not exist

1054: field does not exist

1062: Duplicate field value, inbound failure <========================== can be ignored

1065: Invalid SQL statement, SQL statement is empty

1081: Unable to establish socket connection

1114: The data sheet is full and cannot hold any records

1116: Too many Open data tables

1129: Database is abnormal, please restart database

1130: Connection to database failed with no permissions to connect to database

1133: Database user does not exist

1141: The current user is not authorized to access the database

1142: The current user does not have permission to access the data table

1143: The current user does not have permission to access fields in the datasheet

1146: Data table does not exist

1147: User access to the data table is undefined

1149:sql statement Syntax error

1158: Network error, read error, please check network connection status

1159: Network error, read timeout, check network connection status

1160: Network error, write error, please check network connection status

1161: Network error, write timeout, please check network connectivity status

1169: Duplicate field value, update record failed

1177: Open Data table failed

1180: Commit TRANSACTION failed

1181: ROLLBACK TRANSACTION failure

1203: The current user and database establish a connection that has reached the maximum number of connections to the database, increase the number of available database connections or restart the database

1205: Lock timeout

1211: The current user does not have permission to create a user

1216: FOREIGN KEY constraint check failed, update child table record failed

1217: FOREIGN KEY constraint check failed, delete or modify master table record failed

1226: The current user is using more resources than allowed, please restart the database or restart the server

1227: Insufficient permissions, you do not have permission to do this

1235:mysql version is too low to have this feature

MySQL Operations management-production scenarios MySQL master-slave copy read/write separation authorization scheme and actual combat 15