Shulou(Shulou.com)06/01 Report--

The following content mainly brings you mysql master-slave configuration definition and process analysis, the knowledge here is slightly different from books, are summed up by professional and technical personnel in the process of contact with users, have a certain experience sharing value, hope to bring help to the majority of readers.

Configuration of Mysql master-slave synchronization

Main library IP:192.168.1.2

From library IP:192.168.1.3

Add a user for master-slave synchronization:

GRANT REPLICATION SLAVE ON *. * TO 'repl'@'%' IDENTIFIED BY' 1q2w3e4r'

If you want to monitor mysql master and slave, please add a super permission:

GRANT SUPER, REPLICATION SLAVE ON *. * TO 'repl'@'%' IDENTIFIED BY' 1q2w3e4r'

1. Configuration of the main library

Configuration of the following versions of 1.1.mysql5.0

Modify the configuration file of the main library mysql and add the following in the [mysqld] section:

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

1.2. Configuration of versions above mysql5.0

Modify the configuration file of the main library mysql and add the following in the [mysqld] section:

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"

1.3. The meaning of each parameter and related notes:

Server-id = 1 # CVM flag number. Note that multiple such identities cannot appear in the configuration file. If more than one occurs, the mysql shall prevail as the first, and a group of primary IDs cannot be duplicated.

Log-bin=/home/mysql/logs/binlog/bin-log # enables bin-log and specifies the file directory and file name prefix.

Max_binlog_size = 500m # the maximum size of each bin-log, when this size equals 500m, a new log file is automatically generated. A record is not written in 2 log files, so sometimes the log file exceeds this size.

Binlog_cache_size = 128K # log cache size

Binlog-do-db = adb # requires synchronized database names. If there are multiple database names, you can write a single line in this format.

Binlog-ignore-db = mysql # there is no need for synchronized database names, if there are more than one, you can write a line in this format.

Log-slave-updates # updates the new write log when Slave reads the log from the Master database. If only log-bin is started and log-slave-updates is not started, Slave only records updates for its own database operations.

Expire_logs_day=2 # sets the number of days for bin-log log files to be saved. This parameter is not supported in versions below mysql5.0.

Binlog_format= "MIXED" # sets the bin-log log file format to: MIXED to prevent duplicate primary keys.

2. Configuration of slave library

Previous versions of 2.1.mysql5.1.7

Modify the slave mysql configuration file and add the following in the [mysqld] section:

Server-id=2

Master-host=192.168.1.2

Master-user=repl

Master-password=1q2w3e4r

Master-port=3306

Master-connect-retry=30

Slave-skip-errors=1062

Replicate-do-db = adb

Replicate-ignore-db = mysql

Slave-skip-errors=1007,1008,1053,1062,1213,1158,1159

Master-info-file = / home/mysql/logs/master.info

Relay-log = / home/mysql/logs/relay-bin

Relay-log-index = / home/mysql/logs/relay-bin.index

Relay-log-info-file = / home/mysql/logs/relay-log.info

If you modify the information related to the connection master library, be sure to delete the master.info file before restarting, otherwise the slave library will not be automatically connected to the slave library due to the change of connection information after restart, resulting in synchronization failure. This file holds the information about the connection to the main library.

Later versions of 2.2.mysql5.1.7

The Mysql5.1.7 version has very little configuration on the cluster library, mainly because it adopts a new synchronous information recording method. It no longer supports configuring the information related to the connection to the main library in the configuration file, but records the connection and other related information in the master-info-file = / home/mysql/logs/master.info file. If the storage has changed, the change of the connection information can take effect directly on the mysql command line, which is more flexible. Without having to restart mysql. Modify the slave mysql configuration file and add the following in the [mysqld] section:

Slave-skip-errors=1007,1008,1053,1062,1213,1158,1159

2.3. The meaning of each parameter and related attention items

Here we will only talk about two parameters, and all the other parameters are the information of connecting to the master database from the library and the setting of the intermediate log relay-log.

Master-connect-retry=30 # this option controls the retry interval, which defaults to 60 seconds.

Slave-skip-errors=1007,1008,1053,1062,1213,1158,1159 # is an error that is ignored during synchronization. These errors will not affect the integrity of the data. Errors that occur frequently are generally ignored by settings. Where 1062 is a primary key repeat error.

3. Realize master-slave synchronization

3.1. Realize the unification of database

Check the configuration file of the master-slave database to see if it is configured correctly. To achieve synchronization for the first time, you need to back up the database that needs to be synchronized on the master library, and then import it completely into the slave library. Note: previous versions of mysql5.0 involved problems with replication filtering in mysql itself, and all databases need to be backed up and imported into the cluster database and maintained.

3.2. View and record the main library bin-log information

Enter the main library mysql and execute: show master status; to display the following information:

Mysql > show master status

+-+

| | File | Position | Binlog_do_db | Binlog_ignore_db | |

+-+

| | bin-log.003 | 4 | adb | mysql | |

+-+

1 row in set (0.00 sec)

Record File and Position information

3.3. Execute a synchronization statement on the slave library

Enter mysql and execute the following statement:

Slave stop

Change master to

Master_host='192.168.1.2'

Master_user='repl'

Master_password='1q2w3e4r'

Master_port=3306

Master_log_file='bin-log.003'

Master_log_pos=4

Slave start

3.4. View master-slave synchronization status

Enter mysql and execute show slave status\ G; the query results are different for different mysql versions, but the important metrics are the same:

Previous versions of Mysql5.0 are as follows:

Previous versions of Mysql5.5 are as follows:

The version of Mysql5.5 is as follows:

The important indicators are:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Master_Log_File: bin-log.003

Relay_Master_Log_File: bin-log.003

Read_Master_Log_Pos: 4

Exec_master_log_pos: 4

Seconds_Behind_Master: 0 (this option is not available in versions prior to 5.0)

The above options correspond to each other, and as long as the results are consistent, the master-slave synchronization is successful.

3.5. Common errors and handling in synchronization

1. Phenomenon: show slave status\ G on the slave library; the following occurs

Slave_IO_Running: Yes

Slave_SQL_Running: No

Seconds_Behind_Master: NULL

Reason:

a. The program may have written on slave.

b. It may also be caused by the rollback of the transaction after the slave machine is restarted

C. It is possible to encounter some kind of error during synchronization, which will see an error when viewing the status from the library, the least common being the error of 1062 repeating the primary key.

Solution:

Enter master

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000040 | 324 | adb | mysql | |

+-+

Then perform a manual synchronization on the slave server

Slave stop

Change master to

Master_host='10.14.0.140'

Master_user='repl'

Master_password='1q2w3e4r'

Master_port=3306

Master_log_file='mysql-bin.000040'

Master_log_pos=324

Slave start

Show slave status\ G

2. Phenomenon: unable to synchronize from database, show slave status shows:

Slave_IO_Running: No

Slave_SQL_Running: Yes

Seconds_Behind_Master: NULL

Solution: first of all, check the err log of the database to see what the error is, to see if the IP, user, password and other related information connected to the master database from the slave database is incorrect, and if so, re-execute the synchronization; if confirmed, restart the master database.

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000001 | 98 | adb | mysql | |

+-+

Enter the slave mysql and execute:

Slave stop

Change master to Master_Log_File='mysql-bin.000001',Master_Log_Pos=98

Slave start

Or something like this:

Stop slave

Set global sql_slave_skip_counter = 1

Start slave

This phenomenon is mainly caused by the problem with the master database, which is caused by the error in connecting to the master database and the failure of the master database. In practice, I can restart master and then restart slave to solve this problem. If this problem occurs, it is necessary to restart the master database.

4. Mysql master and master cluster

1. The realization of mysql master.

In the actual production application, in order to quickly recover the business when the master database crashes or the master server fails seriously, it will switch directly to the slave database and let it run as a cluster database directly when the failure of the master database is completed. At this time, the master is a good choice.

Fifth, the monitoring of mysql master and slave.

In the application of mysql master and slave, as long as there is a reasonable setting, basically there will be no problems, but the monitoring of him is essential to avoid unnecessary data loss due to the real problems and do not know.

1. The main ideas of mysql master-slave monitoring.

Mysql master-slave monitoring, which mainly monitors some important parameters on the slave database:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Master_Log_File: bin-log.003

Relay_Master_Log_File: bin-log.003

Read_Master_Log_Pos: 4

Exec_master_log_pos: 4

Seconds_Behind_Master: 0 (this option is not available in versions prior to 5.0)

The above parameters can reflect whether the state of the master library and the slave library is normal, and whether the slave library lags behind the master database. It is worth mentioning that in previous versions of mysql5.0, the status indicator of Slave_IO_Running is unreliable and will not become a NO,Seconds_Behind_Master parameter and does not exist if the main database is hung up directly. Monitor the above parameters to monitor the mysql master / slave.

2. The realization of mysql master-slave monitoring.

No matter which version of the mysql is, the Exec_master_log_pos on the slave library, Log_File and Position on the Master on the Exec_master_log_pos; master library, these four parameters can determine the current master-slave state. The following are master-slave monitoring shell scripts for all versions of mysql:

# / bin/sh

User=repl

Passwd=123415

Master_ip= "192.168.1.2"

Log= "/ data3/check_repl.log"

Value ()

{

Master= `/ usr/local/mysql/bin/mysql-u$user-p$passwd-h$master_ip-e "show master status\ G;" | egrep "File | Position"`

# mysql 4.0

Slave= `/ usr/local/mysql/bin/mysql-u$user-p$passwd-h227.0.0.1-e "show slave status\ G;" | egrep "Relay_Master_Log_File | Exec_master_log_pos"`

# mysql 5.0

# slave= `MySQL-u$user-p$passwd-e "show slave status\ G;" | egrep "Relay_Master_Log_File | Exec_Master_Log_Pos" `

# take the bin-log number on the main database and the current log location written

Master_Log= `echo $master | awk'{print $2}'| awk-F "."'{print $2}'`

Master_Log_Pos= `echo $master | awk'{print $4}'`

# fetch the location of the current synchronization master library on the library

Relay_Master_Log_File= `echo $slave | awk'{print $2}'| awk-F "."'{print $2}'`

Exec_Master_Log_Pos= `echo $slave | awk'{print $4}'`

Echo "Master_Log:" $Master_Log > > $log

Echo "Master_Log_Pos:" $Master_Log_Pos > > $log

Echo "Relay_Master_Log_File:" $Relay_Master_Log_File > > $log

Echo "Exec_Master_Log_Pos:" $Exec_Master_Log_Pos > > $log

}

For ((item1witi > $log)

Value

Time= `date + "% Y-%m-%d% H:%M:%S" `

If [$Master_Log-eq $Relay_Master_Log_File]; then

A = `expr $Master_Log_Pos-$Exec_Master_Log_ Pos`

If [$A-lt 0]; then

A = `expr 0-$A`

Fi

Echo $A > > $log

If [$A-lt 10000]; then

Echo "$time Master-Slave is OK." > > $log

# echo "$I"

Break

Else

If [$I ge 3]; then

Echo "$time Warning:Slave-Master lag $A" > > $log

Echo "$I"

Fi

Sleep 30

Continue

Fi

Else

Sleep 60

Fi

If [$I-eq 10]; then

Echo "$I"

Echo "$time Error:Slave-Master must be check!" > > $log

Fi

Done

In later versions of mysql5.0, the mysql master and slave is quite mature and can only monitor the Slave_IO_Running,Slave_SQL_Running,Seconds_Behind_Master status, which is not explained here.

For the above analysis on the definition and process of mysql master-slave configuration, if you need to know more, you can continue to pay attention to the innovation of our industry. If you need professional solutions, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.