Detailed Mysql database set master-Slave synchronization method _mysql

Brief introduction

MySQL master-slave synchronization is currently using a more extensive database architecture, technology is more mature, configuration is not complex, especially for the load is relatively large site, master-Slave synchronization can effectively alleviate the pressure of database reading and writing.

MySQL master-slave synchronization mechanism:

The process of MySQL synchronization is roughly as follows:

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

2, from the server (slave) IO thread from the primary server (Binlog dump thread) to obtain the binary log, and local save a copy of their binary log (relay log)

3, read the local log (relay log) from the server's SQL thread, and replay the Change event.

The role of MySQL master-slave synchronization:

1, can be used as a backup mechanism, equivalent to hot backup (in from backup, to avoid the impact of the primary server during backup)

2, can be used to do read and write separation, balance database load (master write from Read)

3. When there is a problem with the primary server, you can switch to from the server.

MySQL master-slave synchronization steps:

First, prepare the operation:

1, the master-slave database version consistent, recommended version 5.5 or more

2, master-slave database data consistency

Second, master database Master modification:

1, modify the MySQL configuration:

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

# Main database end ID number 
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 configuration above and the relevant attention items:

#服务器标志号, note that you cannot have more than one such identity in the configuration file, and if more than one, MySQL will be the first, a group of main from which the identification number cannot be duplicated.
Server-id = 1

log-bin=/home/mysql/logs/binlog/bin-log #开启bin-log and specifies the file directory and filename prefix.

#每个bin-log maximum size, a new log file is automatically generated when this size is equal to 500M. A record is not written in 2 log files, so there are times when the log file exceeds this size.
max_binlog_size = 500M 

binlog_cache_size = 128K #日志缓存大小

binlog-do-db = adb #需要同步的数据库名字, if multiple, write a line in this format.

binlog-ignore-db = MySQL #不需要同步的数据库名字, if more than one, you can write a line in this format.

#当Slave从Master数据库读取日志时更新新写入日志中, if only Log-bin is started and log-slave-updates is not started slave only updates for your own database operations are logged.
log-slave-updates 

expire_logs_day=2 #设置bin The number of days that the-log log file was saved, this parameter mysql5.0 not supported by the following version.

binlog_format= "MIXED" #设置bin-log log file format is: MIXED, you can prevent duplicate primary key.

2, restart MySQL, create the account for synchronization:

# Create slave account slave, password 111111 
mysql>grant replication slave to ' *.* ' @ '% ' slave by ' identified '; 

# Update Database permissions 

3, query the state of master

Mysql> show master status; 
+------------------+----------+--------------+------------------+ 
| File    | Position | binlog_do_db | binlog_ignore_db | 
+------------------+----------+--------------+------------------+ 
| mysql-bin.000009 |  196 | | | 
+------------------+----------+--------------+------------------+ 

Note: Do not operate the primary database after performing this step, prevent the main database state value from changing

Third, from the Database slave modification:

1, modify the MySQL configuration:

# from the database-side ID number 

2. Execute Sync command

# Execute Sync command, set primary database IP, sync account password, sync location 
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; 

# Open Sync function 

3, check from the database state:

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 function correctly, that is, the Yes state, otherwise the synchronization fails. Use these two to determine if the server hangs off

Here, the master-slave database setup work has been completed, you can create new databases and tables, insert and modify data, test whether the success

Other related parameters that may be used:

1, Master end:

# do not sync which database 
binlog-ignore-db = mysql 
binlog-ignore-db = Test 
binlog-ignore-db = information_schema 

# Only which databases are synchronized, in addition, other different steps 
binlog-do-db = game 

# log retention 
expire_logs_days = 

# to control the write frequency of Binlog. Every number of transactions written once 
# This parameter consumes a lot of performance, but can reduce the loss caused by MySQL crash 
sync_binlog = 5 

# log format, recommended mixed 
# statement Save SQL statements 
# Row Save impact Record Data 
# Mixed two combinations of front 

2, Slave end:

# Stop the master-slave sync 
mysql> stop slave; 

# When the connection is disconnected, reconnect timeout time 
mysql> change master to master_connect_retry=50; 

# Open master/slave sync 

The above connection timeout setting, similar method can be used to set the primary database IP, sync account password, sync location

To determine whether the master-slave server is running code correctly:

 Execute the following statement from the 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 judged standard
 if (' yes ' = = $Slave _io_running && ' yes ' = = = $Slave _sql_running) {

 } else {
  $content. = "From data The library ($host) hung!!!. <br/> ";
 }

Summarize

The above is the entire content of this article, I hope the content of this article for everyone's study or work can bring certain help, if you have questions you can message exchange.