MySQL Master-slave Replication (Master-slave) Practice

MySQL database itself provides the master-slave replication function can easily achieve multiple automatic data backup, to achieve the expansion of the database. Multiple data backup can not only enhance the security of data, but also can further improve the load performance of database by implementing read-write separation.

Describes a model of master-slave replication and read-write separation between multiple databases (source network):

In a master multi-slave database system, multiple from the server to update the main database changes asynchronously, the business Server in the execution of write or related modification of the database operation is on the primary server, read operation is on each slave server. If you configure multiple slave servers or multiple primary servers and involve the corresponding load balancing problem, the specific technical details about load balancing have not been studied, and today it is simple to implement a master-slave copy function.

MySQL Master-slave replication implementation schematic diagram is as follows (source network):

The basis for data replication between MySQL is the binary log file (binary logfile). A MySQL database once the binary log is enabled, as master, all operations in its database are recorded as "events" in the binary log, and other databases as slave communicate with the primary server through an I/O thread. and monitor the master binary log file changes, if the master binary log file changes, will change the changes to their own trunk log, and then slave a SQL thread will be related to the "event" into its own database, This implementation of the consistency from the database and the primary database, but also the implementation of master-slave replication.

The configuration required to implement MySQL master-slave replication:

Primary server:

• Open Binary Log
• Configure a unique Server-id
• Get the master binary log file name and location
• Create a user account for slave and master communication

From the server:

• Configure a unique Server-id
• Read the master binary log using the user account assigned by master
• Enable the Slave service

The specific implementation process is as follows:

First, the preparatory work:

1. Master-Slave database version best consistent

2. Consistent data within master and slave databases

Main database: 182.92.172.80/linux

From database: 123.57.44.85/linux

Second, the master database Master modification:

1. Modify the MySQL configuration

Locate the configuration file for the primary database my.cnf (or My.ini), my/etc/mysql/my.cnf, insert the following two lines in the [mysqld] section:

[Mysqld]log # Open Binary Log # Set Server-id

2. Restart MySQL to create a user account for synchronization

Open MySQL Session

Shell>---Ppassword

Create user and authorize: User: rel1 Password: slavepass

Mysql> CREATE USER 'Repl'@'123.57.44.85'Identified by 'Slavepass'; #创建用户 (IP is an IP that can access the master, any IP is written'%') MySQL> GRANT REPLICATIONSLAVE on *.*  to 'Repl'@'123.57.44.85'; #分配权限 (IP is an IP that can access the master, any IP is written'%') MySQL>FlushPrivileges; #刷新权限

3. Review the master status, record the binary file name (mysql-bin.000003) and location (73):

> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+| file | Position | binlog_do_db | binlog_ignore_db | +------------------+----------+--------------+------- -----------+| mysql-bin.000003 | 73 | test | manual,mysql | +------------------+----------+--------------+------- -----------+  

Third, from the server slave modified:

1. Modify the MySQL configuration

Also find the MY.CNF configuration file, add Server-id

[Mysqld]server # set Server-id, must be unique

2. Restart MySQL, open the MySQL session, execute the synchronous SQL statement (requires the master server hostname, login credentials, the name and location of the binary file):

Mysql>Change MASTER to     -Master_host='182.92.172.80',     -Master_user='Rep1',     -Master_password='Slavepass',     -Master_log_file='mysql-bin.000003',     -Master_log_pos= the;

3. Start the slave synchronization process:

MySQL>start slave;

4. Check the Slave Status:

Mysql>show slave status\g;*************************** 1. Row***************************slave_io_state:waiting forMaster toSend event Master_host:182.92.172.80master_user:rep1 Master_port:3306Connect_retry: -Master_log_file:mysql-Bin.000013Read_master_log_pos:11662Relay_log_file:mysqld-Relay-Bin.000022Relay_log_pos:11765Relay_master_log_file:mysql-Bin.000013Slave_io_running:yes Slave_sql_running:yes Replicate_do_db:replicat e_ignore_db: ...

When both slave_io_running and slave_sql_running are yes, the master-slave synchronization setting is successful. You can then perform some validation, such as inserting a single piece of data into a table in the test database of the master master database, checking for new data in the same data table in the Slave test library to verify that the master-slave copy function is valid, and shutting down slave (MySQL >stop slave;), and then modify master to see if Slave is also modified (stop slave, Master's changes will not be synchronized to slave), you can complete the master-slave replication function verification.

Other relevant parameters that can be used:

Master opens the binary log by default record all the operations of all the library tables, you can configure to specify only the specified database or even the specified table operation, specifically in the MySQL configuration file [mysqld] can be added to modify the following options:

# What databases   are out of sync binlog-ignore-db = mysql  binlog-ignore-db = Test  binlog-ignore-db = information_schema     # only which databases are synchronized, other than the other  , different steps binlog-do-db = game  

You can see that only the test library has been recorded, ignoring the manual and MySQL libraries, as viewed in the master state.

MySQL master-slave Replication (Master-slave) Practice