MySQL production scenario read/write separation

MYSQL production scenario read/write separation

M-------- à S

one-way synchronization to maintain data consistency can only be M Write data on

Method:

1. control from library, connection user authorization

2. Web The program writes to the master, reads the pointer from

3. let the library read only and not write ( read-only )

Production Authorization Scheme 1 :

Main Library: Grant Select,delete,insert,update on *.*to [email protected] '% ' identified by ' Dongliqiang ';

from library: Grant SELECT On * * to [email protected] '% ' identifiedby ' Dongliqiang ';

MY.CNF Ignore authorization table

Binlog-ignore-db=mysql

Binlog-ignore-db=information_schema

Binlog-ignore-db=performance_schema

Production Authorization Scheme 2 :

Main Library: Grant Select,delete,insert,update on *.*to [email protected] '% ' identified by ' Dongliqiang ';

from library: Grant Select,delete,insert,update on *.*to [email protected] '% ' identified by ' Dongliqiang ';

REVOKE update,insert,delete on * * [email protected] '% ';

Production Authorization Scheme 3 :

Main Library: Grant Select,delete,insert,update on *.*to [email protected] '% ' identified by ' Dongliqiang ';

from library: Grant Select,delete,insert,update on *.*to [email protected] '% ' identified by ' Dongliqiang ';

REVOKE update,insert,delete on * * [email protected] '% ';

Add read-only from library my.cnf configuration file or add--read-only parameter when starting mysql

[Mysqld]

Read-only

There are also 6 parameters on the slave side of the replication.

1.--REPLICATION-DO-DB Set the database to be copied separated by commas (,)

2,--replication-ignore-db set the database to be excluded with a comma (,) split

3.--replication-do-table Set the table to be copied separated by commas (,)

4,--replication-ignore-table set the table to be excluded to be separated by commas (,)

5,--replication-wild-do-db equivalent to 1, but can be added to the wildcard character

6,--replication-wild-ignore-db equivalent to 2, but can be added to the wildcard character

This article is from the "Dongliqiang" blog, make sure to keep this source http://6207422.blog.51cto.com/6197422/1978796

MySQL production scenario read/write separation