Describes how to set master-slave synchronization for MySQL databases.

Describes how to set master-slave synchronization for MySQL databases.

Introduction

MySQL master-slave synchronization is a widely used database architecture with mature technologies and complex configurations. Especially for websites with large loads, master-slave synchronization can effectively relieve the database read/write pressure.

MySQL master-slave synchronization mechanism:

The MySQL synchronization process is roughly as follows:

1. The master server writes the change event (Update, delete, table structure change, and so on) to the binary log (master log ).

2. Obtain binary logs from the I/O thread of the server (slave) from the binlog dump thread of the master server, and save a copy of its own binary log (relay log) locally)

3. Read the local log (relay log) from the SQL thread of the server and repeat the change event.

Role of MySQL master-slave synchronization:

1. It can be used as a backup mechanism, which is equivalent to Hot Backup (the master server service is not affected during Backup)

2. It can be used for read/write splitting to balance the database load (Master write slave read)

3. When a problem occurs on the master server, you can switch to the slave server.

Steps for MySQL master-slave synchronization:

I. preparation:

1. The Master/Slave database versions are the same. We recommend that you use Versions later than 5.5.

2. Master-slave Database Data Consistency

2. master database modification:

1. Modify MySQL Configuration:

#/Etc/my. cnf log-bin = mysql-bin # master database id server-id = 1 log-bin =/home/mysql/logs/binlog/bin-logmax_binlog_size = 500Mbinlog_cache_size = 128Kbinlog-do-db = adbbinlog-ignore -db = mysqllog-slave-updatesexpire_logs_day = 2binlog_format = "MIXED"

The meanings and precautions of parameters in the preceding configuration are as follows:

# Server flag number. Note that there cannot be multiple such identifiers in the configuration file. If there are multiple identifiers, take the first one as the standard. the IDs of the master and slave nodes in a group cannot be repeated. Server-id = 1log-bin =/home/mysql/logs/binlog/bin-log # enable bin-log and specify the file directory and file name prefix. # The maximum size of each bin-log. When the size is MB, a new log file is automatically generated. A record is not written in two log files, so sometimes the log file size exceeds this limit. Max_binlog_size = 500 M binlog_cache_size = 128 K # binlog cache size binlog-do-db = adb # Name of the database to be synchronized. If there are multiple database names, write them in one row. Binlog-ignore-db = mysql # Name of the database that does not need to be synchronized. If there are multiple database names, write a row in this format. # When Slave reads logs from the Master database, it updates the logs. If only log-bin is started, but log-slave-updates is not started, Slave only records the updates for operations on its own database. Log-slave-updates expire_logs_day = 2 # set the number of days for saving the bin-log file. This parameter is not supported in versions earlier than mysql5.0. Binlog_format = "MIXED" # Set the bin-log file format to MIXED to prevent duplicate primary keys.

2. Restart mysql to create an account for synchronization:

# Create an slave account and password 111111 mysql> grant replication slave on *. * to 'slave '@' % 'identified by '000000'; # update database permissions mysql> flush privileges;

3. query the master Status

mysql> show master status; +------------------+----------+--------------+------------------+ | File    | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000009 |  196 |    |     | +------------------+----------+--------------+------------------+ 1 row in set 

Note: Do not operate the primary database after this step is completed to prevent the status value of the primary database from changing.

3. Modify from database slave:

1. Modify MySQL Configuration:

# Slave Database id server-id = 2

2. Execute the synchronization command

# Execute the synchronization command, set the master database ip address, synchronize the account password, and synchronize the location mysql> change master to master_host = '2017. 168.1.2 ', master_user = 'slave', master_password = '000000', master_log_file = 'mysql-bin.000009 ', master_log_pos = 111111; # enable the synchronization function mysql> start slave;

3. Check the slave database status:

mysql> show slave status\G; *************************** 1. row ***************************     Slave_IO_State: Waiting for master to send event      Master_Host: 192.168.1.2      Master_User: slave_account      Master_Port: 3306     Connect_Retry: 60     Master_Log_File: mysql-bin.000009    Read_Master_Log_Pos: 196     Relay_Log_File: vicky-relay-bin.000002     Relay_Log_Pos: 253   Relay_Master_Log_File: mysql-bin.000009     Slave_IO_Running: Yes    Slave_SQL_Running: Yes     Replicate_Do_DB:    Replicate_Ignore_DB:    ...    ...

Note: The Slave_IO_Running and Slave_ SQL _Running processes must run normally, that is, the YES state. Otherwise, the synchronization fails. Use these two items to determine whether the slave server is suspended

By now, the Master/Slave database settings have been completed. You can create a new database and table, insert and modify data, and test whether the database is successfully created.

4. other parameters that may be used:

1. master:

# Which databases are not synchronized binlog-ignore-db = mysql binlog-ignore-db = test binlog-ignore-db = information_schema # Which databases are synchronized only. In addition, others do not synchronize binlog-do-db = game # Log Retention Time expire_logs_days = 10 # control the binlog write frequency. How many transactions are written each time # This parameter consumes a lot of performance, but it can reduce the loss caused by MySQL crash. sync_binlog = 5 # log format, it is recommended that mixed # statement save SQL statement # row save affect record data # binlog_format = mixed

2. slave:

# Stop master-slave synchronization mysql> stop slave; # Time-out for re-connection when the connection is disconnected mysql> change master to master_connect_retry = 50; # enable master-slave synchronization mysql> start slave;

The above connection timeout settings can be used to set the master database ip address, synchronization account password, and synchronization location.

Determine whether the Master/Slave server runs properly:

// Execute the following statement on the slave server: $ SQL = "show slave status"; $ result = mysql_query ($ SQL, $ slave_link); $ row = mysql_fetch_assoc ($ result ); $ Slave_IO_Running = $ row ['slave _ IO_Running ']; $ Slave_ SQL _Running = $ row ['slave _ SQL _Running']; // The following two items are the criteria for judging if ('yes' = $ Slave_IO_Running & 'yes' = $ Slave_ SQL _Running) {} else {$ content. = "dropped from database ($ host !!! <Br/> ";}

Summary

The above is all about this article. I hope this article will help you in your study or work. If you have any questions, please leave a message.