MySQL Master-Slave construction

I. principle of Master-slave replication

MySQL master-slave synchronization process mainly relies on bin-log, mainly divided into the following three steps:

1. Master changes the record to binary log (these are called binary log events, binary logs event);
2. Slave copies the binary log events of master to its trunk logs (relay log);
3. Slave redo the event in the trunk log and change the data to reflect its own.

　　

　　　　　　

Two. master-Slave construction process

1.master Authorization: Create an account for synchronization on master and grant replication permissions　

GRANT REPLICATION SLAVE on * * to ' repl ' @ ' 10.10.1.1 ' identified by ' 123456 '; Flush privileges; 2.master Open Bin-log log: my.conf Configuration example: Server-id = 2
BINLOG-DO-DB = Test
binlog-ignore-db = MySQL
REPLICATE-DO-DB = Test
replicate-ignore-db = MySQL
Sync-binlog = 1
log-salve-updates = 1 Restart MASTER, run show Master STATUS, record the Binlog file name and location Position;3.slave configuration server_id and MASTER address: (1) My. Conf configuration: server_id = 3
Log_slave_updates = 1 (Sets the option to update from the library Binlog if the master of the other slave from the library needs to be set and the Binlog is turned on); (2) Restart Slave, execute at mysql command line: Change MASTER to
Master_host= ' 10.10.1.2 ',
Master_user= ' Repl ',
Master_password= ' 123456 ',
Master_log_file= ' mysql-bin.000017 ', //According to the main library situation determined
master_log_pos=868040264 //According to the main library situation determined(3) Execute the Show SLAVE status statement to see if the SLAVE settings are correct: 

Three. master-Slave switching

In the case of a primary database outage, we need to switch from the library to the primary library:

Master-Slave Switching steps:
Execute from Library:
Slave stop;
Reset slave;
Reset master; (record binlog position when master/slave replacement)
Show master status;
When the main library is restored, it is updated from the library data to the main library and executes:
Slave stop;
Change MASTER to;
Slave start;

Four. Troubleshooting and recovery

1. master-Slave Status View:

Main Library Status: Show Master Status\g;

From library status: show slave status\g;

When synchronizing the exception, look for the reason, according to the specific situation to operate;

Synchronous Error Recovery
Change Master to master_log_file= ' mysql-bin.000288 ', master_log_pos=627625631;
SET GLOBAL sql_slave_skip_counter = 1;

Refer to: http://716737.blog.51cto.com/706737/1302972

2.mysql Load Query optimization:

Look at the values of the status in the DB. At the same time, look at the system memory CPU IO equivalent situation;

Load average:0.49, 0.52, 0.53
System load, which is the average length of the task queue. The three values were 1 minutes, 5 minutes, and 15 minutes ago to the present average.

Troubleshooting Ideas
1. Determine the type of high load, the top command to see whether the load is high CPU or IO.
PS aux process situation;
Top view memory and CPU;
Iostat-d-X 10 3 View system IO situation;
-C displays only CPU statistics. Mutually exclusive with the-D option.
-D displays only disk statistics. Mutually exclusive with-c option.
-K Displays the number of disk requests per second, in the default block of units, in units.
-P Device | All
Mutually exclusive with the-X option to display statistics for block devices and system partitions. You can also specify a device name after-p, such as:
# iostat-p HDA
or Show All devices
# iostat-p All
-T prints the time to collect data when outputting data.
-V Prints the version number and help information.
-X Output extension information.

Vmstat Viewing system memory usage

2. mysql executes the SQL statement that looks at the current number of connections and executes.
Show Processlist view current connection;
Kill QUERY ID When the load is too large, forcibly disconnect;

3. Check the slow query log, which may be slow queries cause a high load.
Record Slow queries
Edit the MySQL configuration file (my.cnf) and add the following lines in the [Mysqld] field:
Log_slow_queries =/usr/local/mysql/var/slow_queries.log #慢查询日志路径
Long_query_time = 5 #记录SQL查询超过5s的语句
log-queries-not-using-indexes = 1 #记录没有使用索引的sql

Reference Links:

http://716737.blog.51cto.com/706737/1302972

http://blog.csdn.net/hguisu/article/details/7325124

MySQL Master-Slave construction