MySQL master/slave configuration read/write separation

Master configuration

1. Create a User:

Create a user ' repl ' on master MySQL and allow the other slave server to access the master remotely, through which the user can read the binary log for data synchronization.

Create user Repl;

2. Configure user permissions:

REPL users must have replication slave permissions, but there is no need to add unnecessary permissions, the password is MySQL. Explain the 192.168.0.%, this configuration is to indicate the REPL user's server, where% is a wildcard character, indicating that 192.168.0.0-192.168.0.255 server can be REPL users to the primary server. Of course you can also specify a fixed IP.

GRANT REPLICATION  on *. *  to ' Repl '@ '%' by' 123456';

3. Modify the configuration file

Locate the MySQL installation path to modify the My.cnf file and add the following configuration to the [Mysqld] node.

Server-id=1log_bin=mysql-binbinlog-do -db=binlog-Ignore-db=

Server-id=1 #设置服务器id, 1 indicates the primary server, note: If the original configuration file already has this line, you can no longer add.
Log_bin=mysql-bin #启动MySQ二进制日志系统, Note: If you already have this line in your original configuration file, you won't have to add it anymore.
binlog-do-db= #需要同步的数据库名, if you have multiple databases, you can repeat this parameter, one row per database
binlog-ignore-db= #不同步mysql系统数据库

4. Restart the database

Service MySQL Restart

5. record file and position

The file and position of master must be recorded and used when configuring slave.

Show master status;

Slave configuration

1. Modify the configuration file

Locate the MySQL installation path to modify the My.cnf file and add the following configuration to the [Mysqld] node.

server-id=2log_bin=mysql-binbinlog-do-db=binlog-ignore-db=  

Server-id=1 #设置服务器id, 2 indicates from the server, note: If the original configuration file already has this line, it is no longer added.
Log_bin=mysql-bin #启动MySQ二进制日志系统, Note: If you already have this line in your original configuration file, you won't have to add it anymore.
Binlog-do-db=osyunweidb #需要同步的数据库名, if you have multiple databases, you can repeat this parameter, one row per database
Binlog-ignore-db=mysql #不同步mysql系统数据库

2. Configure slave

Note: After MySQL 5.1.7, it is not supported to write the master configuration attribute to the MY.CNF configuration file, just write the synchronized database and the database to be ignored.

slave Stop;change master to master_host='192.168.21.169', master_user='  repl', master_password='123456', master_log_file=' mysql-bin.000019 ' , master_log_pos=7131;
Slave start;

Slave stop; #停止slave同步进程
Change Master to master_host= ' 192.168.21.169 ', master_user= ' Osyunweidbbak ', master_password= ' 123456 ', Master_log_ File= ' mysql-bin.000019 ', master_log_pos=7131; #执行同步语句. where Master_logfile and master_log_pos need to be queried from master.
Slave start; #开启slave同步进程

3. View status

SHOW SLAVE Status\g

Slave_io_running:yes
Slave_sql_running:yes
The values for these two parameters are yes, which means that the configuration was successful.

MySQL master/slave configuration read/write separation