MySQL Replication detailed MySQL database settings master-Slave synchronization method

The process for MySQL synchronization is roughly as follows:

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

2. The IO thread from the server (slave) gets the binary log from the primary server (Binlog dump thread) and saves a copy of its own binary log (relay logs) locally

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, as a backup mechanism, equivalent to hot backup (in the backup, to avoid the impact of the primary Server service during backup)

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

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

MySQL master-slave synchronization steps:

first, prepare the operation:

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

2, master-slave database data consistent

Second, the master database Master modification:  

1. Modify the MySQL configuration:

1#/etc/my.cnf2 Log-Bin=Mysql-bin3  4 # Primary database-side ID number5Server-Id= 1 6  7 Log-Bin=/Home/Mysql/Logs/Binlog/Bin-Log8Max_binlog_size=500M9Binlog_cache_size=128KTenBinlog-Do-Db=adb OneBinlog-Ignore-Db=MySQL A Log-Slave-Updates -Expire_logs_day=2 -Binlog_format="MIXED"

The meanings of each parameter in the above configuration and the related notes:

1 #服务器标志号, note that in the configuration file can not appear in more than one such identity, if there are multiple words MySQL to the first, a group of the master from which this identification number cannot be duplicated. 2Server-Id= 13  4 Log-Bin=/Home/Mysql/Logs/Binlog/Bin-Log#开启bin-Log, and specify the file directory and filename prefix. 5  6#每个bin-log maximum size, a new log file is automatically generated when this size equals 500M. A record is not written in 2 log files, so sometimes the log file exceeds this size. 7Max_binlog_size=500M8  9Binlog_cache_size=128K #日志缓存大小Ten   OneBinlog-Do-Db=adb #需要同步的数据库名字, if it is more than one, write a line in this format.  A   -Binlog-Ignore-Db=MySQL #不需要同步的数据库名字, if more than one, in this format to write a line.  -   the#当Slave从Master数据库读取日志时更新新写入日志中, if you only start log-Bin without starting Log-Slave-update slave only records updates for its own database operations.  - Log-Slave-Updates -   -Expire_logs_day=2#设置bin-log log file number of days saved, this parameter mysql5.0 the following version is not supported.  +   -Binlog_format="MIXED" #设置bin-The log file format is: MIXED, which prevents the primary key from being duplicated.

2. Restart MySQL to create an account for synchronization:

1 # Create slave account slave, password 1111112Mysql>Grant ReplicationSlave on *.*  to 'slave'@'%'Identified by '111111'; 3  4 # Update Database Permissions5Mysql>FlushPrivileges;

3. Query the status of master

1Mysql>show master status;2 +------------------+----------+--------------+------------------+ 3 | File    |Position|binlog_do_db|binlog_ignore_db| 4 +------------------+----------+--------------+------------------+ 5 |Mysql-Bin.000009 |  196 |    |     | 6 +------------------+----------+--------------+------------------+ 7 1Rowinch Set

Note: Do not operate the primary database until this step is complete, preventing changes in the primary database state value

Third, from the database slave modified:

1. Modify the MySQL configuration:

12 server-=2

2. Perform synchronous commands

1 # Execute Sync command, set primary database IP, synchronize account password, sync location2Mysql>Change Master toMaster_host='192.168.1.2', Master_user='slave', Master_password='111111', Master_log_file='mysql-bin.000009', Master_log_pos=196; 3  4 # enable sync function5Mysql>Start slave;

3. Check the status from the database:

1Mysql>show slave status\g;2 *************************** 1. Row*************************** 3Slave_io_state:waiting forMaster toSend Event4Master_host:192.168.1.2 5 Master_user:slave_account6Master_port:3306 7Connect_retry: - 8Master_log_file:mysql-Bin.000009 9Read_master_log_pos:196 TenRelay_log_file:vicky-Relay-Bin.000002  OneRelay_log_pos:253  ARelay_master_log_file:mysql-Bin.000009  - Slave_io_running:yes - Slave_sql_running:yes the replicate_do_db: - replicate_ignore_db: -    ...  -...

Note: The slave_io_running and slave_sql_running processes must be running normally, that is, the Yes state, otherwise the synchronization fails. These two judgments can be used to determine whether the server is hung off

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

Iv. other relevant parameters that may be used:

1, Master side:

1 # What databases are not synchronized2Binlog-Ignore-Db=MySQL3Binlog-Ignore-Db=Test4Binlog-Ignore-Db=Information_schema5  6 # Synchronize only which databases, other than the other, different steps7Binlog-Do-Db=Game8  9 # Log retention timeTenExpire_logs_days= Ten  One   A # Controls The write frequency of the binlog. How many transactions per execution are written once - # This parameter consumes a lot of performance, but can reduce the loss caused by MySQL crash -Sync_binlog= 5  the   - # log format, recommended mixed - # Statement Saving SQL statements - # Row Save affects record data + # Mixed in front of two combinations -Binlog_format=Mixed

2, Slave end

12 mysql>3  45 mysql> to Master_connect_retry=  6   7   8 mysql> start slave;

The above connection time-out settings, similar way can be used to set the primary database IP, synchronization account password, synchronization location

Determine if 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 criteria are judgedif('Yes' ==$Slave _io_running&& 'Yes' ==$Slave _sql_running) { } Else{$content.="From the Database ($host) hung off!!!"<Br/>";}

Summarize

The above is the entire content of this article, I hope that the content of this article on everyone's study or work can bring certain help, if there is doubt you can message exchange.

MySQL Replication detailed MySQL database settings master-Slave synchronization method