MySQL database synchronization configuration +mysql read and write separation

The benefits of using MySQL master-slave replication are:

1, the use of master-slave server This architecture, stability can be improved. If the primary server fails, we can use the slave server to provide the service.

2, processing the user's request separately on the master-slave server, can improve the data processing efficiency.

3. Copy the data on the primary server to the slave server to protect the data from accidental loss.

Environment Description:

The new enterprise wants to build a MySQL database that is based on replication.

Primary server (mysql-master): IP address: 192.168.48.128,mysql installed, no user data.

From server (mysql-slave): IP address: 192.168.48.130,mysql installed, no user data.

The master-slave server can provide the service normally.

Configuring the primary server (master)

1. Edit the database configuration file my.cnf or My.ini (Windows), typically in the/etc/directory.

Add the following code below [mysqld]:

log-bin=mysql-binserver-id=1innodb_flush_log_at_trx_commit=1  Sync_binlog=1binlog-do-db=wordpressbinlog_ignore_db=mysql

Description

Server-id=1//In 1 can be defined, as long as the only one on the line.

Binlog-do-db=wordpress//is to indicate that only WordPress is backed up.

Binlog_ignore_db=mysql//Indicates that backup MySQL is ignored.

Without binlog-do-db and binlog_ignore_db, that means backing up all the databases.

2. Then restart MySQL: #servicemysqldrestart

3. Log in to MySQL, add a backup account to MySQL, and authorize it to the slave server.

[[Email protected]~] #mysql-uroot–p123456 login mysqlmysql>grantreplicationslaveon*.*to ' backup ' @ ' 192.168.48.130 ' Identifiedby ' backup ';

Create a backup user and authorize it for use by 192.168.48.130.

4, query the state of the main database, and note the value of file and position, this is used when the configuration from the server later.

mysql>showmasterstatus; Please make a note of the information displayed and the configuration will be used from the server. + —————— + ———-+ ———— –+ —————— +| file| position| binlog_do_db| binlog_ignore_db|+ —————— + ———-+ ———— –+ —————— +|mysql-bin.000001|253|dbispconfig|mysql|+ —————— + ———-+ ———— –+ —————— +

1rowinset (0.00SEC)

To operate from the server:

1), ensure that there are log-bin=mysql-bin and server-id=1 parameters in the/ETC/MY.CNF, and change the server-id=1 to server-id=10. The changes are as follows:

[Mysqld]

log-bin=mysql-bin   //Start binary file server-ID=10        //server ID

2), restart the MySQL service.

[Email protected]~] #mysqladmin-p123456shutdown

[Email protected]~] #mysqld_safe--user=mysql&

3), log in to MySQL, execute the following statement

[Email protected]~] #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=401;

4), start slave synchronization.

mysql>startslave;

5), check the master-slave synchronization, if you see slave_io_running and slave_sql_running are yes, the master-slave replication connection is normal. Mysql>showslavestatus\g

Verify that the configuration is correct and that MySQL master/slave can replicate normally.

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

[Email protected]~] #mysql-uroot–p123456

mysql>createdatabasemysqltest;

mysql>usemysqltest;

Mysql>createtableuser (Idint (5), Namechar (10));

Mysql>insertintouservalues (00001, ' Zhangsan ');

Verify from the database that it is copied to the data normally.

[Email protected]~] #mysql-uroot–p123456

mysql>showdatabases;

mysql>select*frommysqltest.user;

MySQL database synchronization configuration +mysql read and write separation