Detailed explanation of how to set up master-slave synchronization in MySQL database

Introduction

MySQL master-slave synchronization is a widely used database architecture at present. The technology is relatively mature and the configuration is not complicated. Especially for websites with heavy loads, master-slave synchronization It can effectively relieve the pressure of database reading and writing.

MySQL master-slave synchronization mechanism:

The MySQL synchronization process is roughly as follows:

1. Master server ) writes change events (updates, deletes, table structure changes, etc.) to the binary log (master log).

2. The IO thread of the slave server (slave) obtains the binary log from the main server (binlog dump thread) and saves its own binary log (relay log) locally

3. Read the local log (relay log) from the server's SQL thread and replay the change events.



The role of MySQL master-slave synchronization:

1. It can be used as a backup mechanism, equivalent to hot backup (in the slave Backup to avoid affecting the main server service during the backup period)

2. It can be used to separate reading and writing and balance the database load (primary writing and slave reading)

3. When there is a problem with the main server, you can Switch to slave server.

MySQL master-slave synchronization steps:

1. Preparatory operations:

1. The master-slave database versions are consistent. Recommended version 5.5 or above

2. The master-slave database data is consistent

2. Modify the master database master:

1. Modify the MySQL configuration:

# /etc/my.cnf 
log-bin = mysql-bin 

# 主数据库端ID号 
server-id = 1 

log-bin=/home/mysql/logs/binlog/bin-log
max_binlog_size = 500M
binlog_cache_size = 128K
binlog-do-db = adb
binlog-ignore-db = mysql
log-slave-updates
expire_logs_day=2
binlog_format="MIXED"

The meaning of each parameter in the above configuration and related precautions:

#服务器标志号，注意在配置文件中不能出现多个这样的标识，如果出现多个的话mysql以第一个为准，一组主从中此标识号不能重复。
server-id = 1

log-bin=/home/mysql/logs/binlog/bin-log #开启bin-log，并指定文件目录和文件名前缀。

#每个bin-log最大大小，当此大小等于500M时会自动生成一个新的日志文件。一条记录不会写在2个日志文件中，所以有时日志文件会超过此大小。
max_binlog_size = 500M 

binlog_cache_size = 128K #日志缓存大小

binlog-do-db = adb #需要同步的数据库名字，如果是多个，就以此格式在写一行即可。

binlog-ignore-db = mysql #不需要同步的数据库名字，如果是多个，就以此格式在写一行即可。

#当Slave从Master数据库读取日志时更新新写入日志中，如果只启动log-bin 而没有启动log-slave-updates则Slave只记录针对自己数据库操作的更新。
log-slave-updates 

expire_logs_day=2 #设置bin-log日志文件保存的天数，此参数mysql5.0以下版本不支持。

binlog_format="MIXED" #设置bin-log日志文件格式为：MIXED，可以防止主键重复。

2, Restart mysql and create an account for synchronization:

# 创建slave帐号slave，密码111111 
mysql>grant replication slave on *.* to 'slave'@'%' identified by '111111'; 

# 更新数据库权限 
mysql>flush privileges;

3. Query the status of the master

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 main database after performing this step to prevent the status value of the main database from changing

3. Modify the slave database slave:

1. Modify MySQL Configuration:

# 从数据库端ID号 
server-id =2

2. Execute synchronization command

# 执行同步命令，设置主数据库ip，同步帐号密码，同步位置 
mysql>change master to master_host='192.168.1.2',master_user='slave',master_password='111111',master_log_file='mysql-bin.000009',master_log_pos=196; 

# 开启同步功能 
mysql>start slave;

3. Check 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 be running normally, that is, in the YES state, otherwise the synchronization fails. You can use these two items to determine whether the slave server is down.

At this point, the master-slave database setup work has been completed. You can create a new database and table, insert and modify data, and test whether it is successful

4. Other related parameters that may be used:

1. Master side:

# 不同步哪些数据库 
binlog-ignore-db = mysql 
binlog-ignore-db = test 
binlog-ignore-db = information_schema 

# 只同步哪些数据库，除此之外，其他不同步 
binlog-do-db = game 

# 日志保留时间 
expire_logs_days = 10 

# 控制binlog的写入频率。每执行多少次事务写入一次 
# 这个参数性能消耗很大，但可减小MySQL崩溃造成的损失 
sync_binlog = 5 

# 日志格式，建议mixed 
# statement 保存SQL语句 
# row 保存影响记录数据 
# mixed 前面两种的结合 
binlog_format = mixed

2. Slave side:

# 停止主从同步 
mysql> stop slave; 

# 连接断开时，重新连接超时时间 
mysql> change master to master_connect_retry=50; 

# 开启主从同步 
mysql> start slave;

The above connection timeout settings, similar methods can be used to set the master database ip, synchronize account password, synchronize location



Judge whether the master and slave servers are running normally. ：

//在从服务器中执行以下语句
 $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'];

 //下面两项为判断标准
 if ('Yes' == $Slave_IO_Running && 'Yes' == $Slave_SQL_Running) {

 } else {
  $content .= "从数据库( $host )挂掉了！！！ <br/>";
 }

Summary

The above is a detailed explanation of the method of setting up master-slave synchronization in MySQL database, more related Please pay attention to the PHP Chinese website (www.php.cn) for content!