Linux Learning notes: MySQL master-slave synchronization

Generally speaking, the master-slave synchronization is, from the library to the master Library of a location point of backup, and then according to the main library Binlog log and continuously through the increment to perform additions and deletions, to achieve the same as the main library.

Working principle:

1. Master Turn on Binlog function (log function);

2. It is best to use master-data=1 as a backup when you import the whole library backup of master at a point in time to slave.

3. Assign a replication slave account on Master for synchronization;

4. Master contains an IO process, slave mysql includes the IO process and the SQL process (but the two processes are not associated);

5. Slave initiates a replication request and provides Master_log_file and Master_log_pos.

6. Master's main process through validation, the task to the IO process, and according to Master_log_file and Master_log_pos start, continue to send to slave from the binlog of the subsequent log (active push);

7. The slave IO process receives these logs, stores them in the relay log file, and registers the newly received master_log_file and Master_log_pos in the Master.info file.

8. The slave SQL process is read and executed cyclically from the relay log, making the slave library conform to the master library;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/7A/2B/wKioL1ak4JWw5Bj4AAGMK94Kb1s003.jpg "title=" Qq20160124223104.jpg "alt=" Wkiol1ak4jww5bj4aagmk94kb1s003.jpg "/>

Specific deployment steps

1. Turn on the Binlog function of master;

In my.cnf, configure the log-bin= path + prefix;

Log-bin =/disk2/mysql_multi_instances/3308/mysql-bin

MySQL will produce binlog data files in the/disk2/mysql_multi_instances/3308/directory, prefixed by Mysql-bin, with a suffix of six digits.

2. Back up the master database

[Email protected] ~]# mysqldump-uroot-p-H 127.0.0.1-p 3308-f-a-b-x--add-drop-database--events--master-data=1|g Zip >3308.sql.gz

-F cut binlog (optional)

-A All databases

--add-drop-database Insert drop database before CREATE database, which is suitable for the whole library backup situation

-X Lock table

-B can specify multiple dbname, but if the whole library is backed up, it can be specified without even writing

--events The export contains event scheduling (scheduler), if not added, there may be a warning, all in all, can add.

--master-data The most important parameter, insert in the exported content

Change MASTER to master_log_file= ' mysql-bin.000101 ', master_log_pos=3658;

This kind of statement, said slave can according to this sentence self-judgment master_log_file and Master_log_pos, but in my test found still need to manually specify, anyway, set this parameter you can easily find file and POS, why not, This parameter has two values of 1 and 2,1 is the above effect, 2 is the above effect commented out as a reference. File and Pos can also be found on master

Mysql> Show Master status;+------------------+----------+--------------+------------------+| File | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+|     mysql-bin.000101 |              3658 |                  | |+------------------+----------+--------------+------------------+

3. Assign accounts on Master for synchronization

mysql> grant replication Slave on * * to [e-mail protected] ' 192.168.5.% ' identified by ' jet ';

Note is the replication slave permission (two words)

4. Slave The backup file of the whole library into the library file.

[Email protected] ~]# gzip-d 3308.sql.gz

[Email protected] ~]# Mysql-uroot-p < 3308.sql

5. Write the change master statement on slave

Mysql> Change MASTER to

Master_host= ' 192.168.5.103 ',

Master_user= ' rep ',

Master_password= ' Jet ',

master_port=3308,

Master_log_file= ' mysql-bin.000101 ',

master_log_pos=107;

Note that this information must be strictly checked.

7. Execute slave start on slave, then view status

mysql> show slave status\g;*************************** 1. row ***************                slave_io _state: waiting for master to send event                   Master_Host: 192.168.5.103                   master_ user: rep                   Master_Port: 3308                 Connect_Retry: 60               Master_Log_File: mysql-bin.000101           read_master_log_pos: 3658                relay_log_file: mysqld-relay-bin.000024                 relay_log_pos: 252        relay_ master_log_file: mysql-bin.000101              slave_io_running: yes            slave_ sql_running: yes               replicate_do_db:           replicate_ignore_db:             Replicate_Do_Table:         replicate_ignore_table:       replicate_wild_do_ Table:   replicate_wild_ignore_table:                     Last_Errno: 0                    Last_Error:                   Skip_Counter: 0           Exec_Master_Log_Pos: 3658               Relay_Log_Space: 554               Until_Condition: None                Until_Log_File:                  Until_Log_Pos: 0            master_ssl_allowed: no            master_ssl_ca_file:            master_ssl_ca _path:               master_ssl_ cert:             master_ssl_cipher:                 master_ssl_key:          seconds_behind_master: 0master_ssl_verify_server_ cert: no                 last_io_errno: 0                 Last_IO_Error:                 last_sql_errno:  0               last_sql_error:  1 row in set  (0.00 SEC)

The most important of these are slave_io_running:yes and Slave_sql_running:yes, both of which are Yes to connect correctly for the synchronization session, and in the test I did not manually specify file and pos in the Change master statement. After starting slave, only slave_io_running:yes, and slave_sql_running:no, such synchronization is problematic, but also on the state interface to display the execution failed SQL statement; and then I put slave stop, and re-specify file and POS, and then slave start is normal.

Also, you can see that there are more master.info files and relay log files on the slave

[Email protected] ~]# LL/VAR/LIB/MYSQL/{MASTER.INFO,MYSQLD-RELAY*}-RW-RW----. 1 MySQL MySQL 22:12/var/lib/mysql/master.info-rw-rw----. 1 MySQL mysql 302 Jan 22:12/var/lib/mysql/mysqld-relay-bin.000023-rw-rw----. 1 MySQL mysql 252 Jan 22:12/var/lib/mysql/mysqld-relay-bin.000024-rw-rw----. 1 MySQL MySQL 22:12/var/lib/mysql/mysqld-relay-bin.index

In fact, those relay log files are also binlog files that can be viewed with mysqlbinlog, while master.info files are text files used to register some parameters about master

[Email protected] ~]# Less/var/lib/mysql/master.info

15

Mysql-bin.000101

3658

192.168.5.103

Rep

Jet

3308

60

0

0

0

Linux Learning notes: MySQL master-slave synchronization