Master-slave synchronization configuration and read/write separation for MySQL databases, and master-slave mysql Databases

Master-slave synchronization configuration and read/write separation for MySQL databases, and master-slave mysql Databases

Benefits of using mysql master-slave replication include:

1. The architecture of master-slave servers improves stability. If the master server fails, we can use the slave server to provide services.

2. Separate processing of user requests on the Master/Slave server can improve data processing efficiency.

3. copy the data on the master server to the slave server to protect the data from unexpected losses.

Environment Description:

New enterprises need to build an architecture for master-slave mysql databases.

Master server (mysql-master): IP Address: 192.168.48.128. mysql has been installed without user data.

Slave server (mysql-slave): IP Address: 192.168.48.130, mysql has been installed, no user data.

Both the master and slave servers can provide services normally.

Configure the master server)

1. Edit the database configuration file my. cnf or my. ini (windows), which is usually in the/etc/directory.

Add the following code under [mysqld:

Log-bin = mysql-bin
Server-id = 1
Innodb_flush_log_at_trx_commit = 1
Sync_binlog = 1
Binlog-do-db = wordpress
Binlog_ignore_db = mysql

Note:

1 In server-id = 1 // can be defined, as long as it is unique.

Binlog-do-db = wordpress // indicates that only wordpress is backed up.

Binlog_ignore_db = mysql // indicates that the backup mysql is ignored.

If binlog-do-db and binlog_ignore_db are not added, all databases are backed up.

2. Restart MySQL :#service mysqld restart

3. log on to mysql, add a backup account to mysql, and grant permissions to the slave server.

[Root @ localhost ~] # Mysql-u root-p 123456 log on to mysql
Mysql> grant replication slave on *. * to 'backup '@ '192. 168.48.130' identifiedby 'backup ';

Create a backup user and authorize it to 192.168.48.130.

4. query the status of the primary database, and write down the values of FILE and Position, which will be used later When configuring slave servers.

Mysql> show masterstatus; Note the displayed information, which will be used by the configuration slave server.

+ ------ + ---- + ----- + ------

+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------ + ---- + ----- + ------

+ | Mysql-bin.000001 | 253 | dbispconfig | mysql |

+ ------ + ---- + ----- + ------ +

1 rowinset (0.00sec)

Operate on the slave server:

1) Make sure/etc/my. cnf contains the log-bin = mysql-bin and server-id = 1 parameters, and changes server-id = 1 to server-id = 10. The modification is as follows:

[Mysqld]

Log-bin = mysql-bin // start the binary file server-id = 10 // server ID

2) restart the mysql service.

[Root @ localhost ~] # Mysqladmin-p123456shutdown

[Root @ localhost ~] # Mysqld_safe -- user = mysql &

3) log on to mysql and run the following statement:

[Root @ localhost ~] # Mysql-uroot-p123456

Mysql> changemastertomaster_host = '192. 168.48.128 ', master_user = 'backup', master_password = 'backup ', master_log_file = 'mysql-bin.000003', master_log_pos = 192;

4) Start slave synchronization.

Mysql> start slave;

5) Check master-slave synchronization. If you see that Slave_IO_Running and Slave_ SQL _Running are both Yes, the master-slave replication connection is normal.
Mysql>show slave status\G

Verify whether the configuration is normal, and whether the mysql Master/Slave can be copied normally.

Create a new database in the primary database and write a table and some data in the database.

[Root @ localhost ~] # Mysql-u root-p 123456

Mysql> create database mysqltest;

Mysql> use mysqltest;

Mysql> create table user (idint (5), namechar (10 ));

Mysql> insert into user values (00001, 'hangsan ');

Verify from the database whether the data is copied normally.

[Root @ localhost ~] # Mysql-u root-p 123456

Mysql> show databases;

Mysql> select * from mysqltest. user;