MySQL master-slave replication configuration

MySQL master-slave replication is used for read-write separation, the main library is responsible for writing, read from the library. This will undoubtedly provide higher concurrency performance for the database.

After the master-slave replication is configured, the main library records the binary log to local, reads the main library's binaries from the library and saves it to the local trunk log file, and then redo the log to achieve the results of synchronizing the data.

Main Library Configuration
1. Create a replication user

From the library through this user identity in the primary binary log.

GRANT REPLICATION slave,replication CLIENT on * * to [e-mail protected] '% ' identified by ' Gechong ';

2. Main Library Configuration

Modify MY.CNF

Server-id=1 #必须有 and must be the only Log-bin=/var/log/mysql/mysql_bin.log #二进制文件的路径binlog-do-db=employees # Libraries that need to log binary logs Binlog-ignore-db=mysql,information_schema #忽略的库sync_binlog =1 #即时记录 to prevent transactions from being uncommitted and losing binary logs

Configuration of the Standby library1. Standby Storage Configuration

Modify the My.cnf file

Server-id=2log-bin=/var/log/mysql/mysql_bin.loglog-slave-updates=1read-only=1 #阻止任何没有特权权限的线程修改数据, Avoid data conflicts with the main library Replicate-do-db=employeesreplicate-ignore-db=mysql,information_schema

2. Start replication

Running in MySQL

Stop slave; --First turn off change master to master_host= ' IP ', master_user= ' slave_1 ', master_password= ' 123456 ', master_log_file= ' Mysql_ bin.000006 ', Master_log_pos=106;start slave; --Start

If both slave_io_running and slave_sql_running are yes, the configuration succeeds.

Precautions

This is a brief introduction to MySQL master-slave configuration. In addition, there are a number of key points to note about this topic.

How to copy

The ways of copying are divided into line-based and statement-based.

Statement-based (5.0 and previous version support) because there is a time difference from the library redo statement, so there is an error in the timestamp.

The line-based approach will record the actual data to a binary file, overcoming the similar drawbacks.

The main library and the data from the library conflict

From the library is primarily used as reading data, synchronized with the main library. If you arbitrarily modify from the library, it is likely to result in conflicting and inconsistent data.

The configuration of Read_only=1 from the library can be effectively avoided.

Event loss due to database crash

Sync_binlog=1

The purpose of this configuration is that MySQL synchronizes the binary logs to disk before each commit transaction, preventing the database from crashing and causing loss.

Summarize

In addition to a master one from the outside, can also allow a master multi-slave structure. This is undoubtedly useful for systems with a small amount of written reading.

1) Disaster recovery, the deployment of one from the server to the far end, can fire and shockproof anti-flooding.

MySQL master-slave replication configuration