MySQL master-slave replication principle Practice

mysql master-slave replication

MySQL master-slave Replication actually copies the SQL command collection that was originally created and modified to the database locally from the library, and re-executes the SQL commands locally from the library to create the same data as the main library. After building from the library, it includes all data that replicates the current cutoff location of the main library, and the next main library is still growing data. In fact, there's nothing wrong with backing up all the data and copying it in the past, the question is, how do you replicate this data growth in real time? And this will be the use of binlog log function ...

Master-slave replication principle

650) this.width=650; "Src=" Http://www.lichengbing.cn/ueditor/php/upload/image/20160714/1468479960438319.png " Title= "1468479960438319.png" alt= "MySQL master-slave copy. png"/>

1) Open the main library log-bin function, is for the user in the process of writing a database, MySQL will also establish a binlog log to record the database to make additions and deletions, and so on, to establish a connection between the library and the main library to verify the relationship between the swap account rep, and authorization;

2) lock table, the database to do full standby operation, the maximum degree of consistency to ensure data, the most important thing is to get the current database Binlog log demarcation point;

3) Restore the full amount of the database after the shutdown to the slave library

4) from the library, perform the change MASTER to ... command to authenticate the user, and tell the database that is currently backed up from the library binlog the log demarcation point, and generate the Master.info file;

5) Open from the library, at this time the MySQL replication function only really play a role, from library B will use their own IO thread to constantly ask the main library A: Dude, I have a code (password) is one of our own, uh ... I have these local goods, you have no new goods (new data), also give me some. Then the main library a will go to see their own binlog log, found that some new goods, will give these new goods from the Library B, and a list to tell from the library B he now has what goods in the warehouse, the next time from the Library B to find his new goods just take out this list A to know if his goods are the same as their own, If the same will not be sent, if not the same, according to the list of goods cut-off location of the latest, the new goods here can be understood as Binlog file content, the list is Binlog location information post;

Every time you get a new item from the library, you'll be happy. Start your own SQL thread to resolve these new shipments to your own database, ensuring that it is fully consistent with the master library A.

Master-slave replication case Practice

in the local multi-MySQL multi-instance,/data/3306/mysql.sock as the main library,/data/3307/mysql.sock as the library

• Main Library

1) Open the main library binlog function, and ensure that the server ID is not the same

[Email protected] ~]# egrep "Log-bin|server-id" Log-bin/data/3306/my.cnf/data/3307/my.cnf/data/3306/my.cnf:log-bin =/data/3306/mysql-bin/data/3306/my.cnf:server-id = 1/data/3307/my.cnf: #log-bin =/data/3307/mysql-bin/data/3307/ My.cnf:server-id = 3

2) Main Library authorized from library account Rep

mysql> grant replication Slave on * * to [e-mail protected] ' 172.16.2.% ' identified by ' oldboy123 '; Query OK, 0 rows Affected (0.00 sec) mysql> flush privileges;

3) Lock table (cannot exit current MySQL control window after lock table)

Mysql> Flush table with read lock;

4) View Binlog critical point

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

5) Fully prepared

[Email protected] ~]# mysqldump-uroot-poldboy1234-s/data/3306/mysql.sock-a-B--events|gzip >/SERVER/BACKUP/MYSQ l_$ (date +%f). Sql.gz[[email protected] backup]# ll mysql_2016-07-07.sql.gz-rw-r--r--1 root root 144620 Jul 7 04:18 MySQL _2016-07-07.sql.gz

6) Ching

mysql> unlock table;

• From the Library

  
  

1) Make sure the server ID is different

[[email protected] ~]# grep Server-id/data/3307/my.cnfserver-id = 4

2) Restore full standby

[Email protected] backup]# mysql-uroot-poldboy456-s/data/3307/mysql.sock </server/backup/mysql_2016-07-07.sql

3) Configure Master, fill in the correct Binlog location point

mysql> change MASTER to master_host= ' 172.16.2.10 ', master_port=3306, master_user= ' rep ', Master_password= ' oldboy123 ', master_log_file= ' mysql-bin.000015 ', master_log_pos=3204;# #此时如果master密码配置错误, re-master.info file does not take effect, need to perform reset slave All [email protected] data]# cat master.info18mysql-bin.0000153204172.16.2.10repoldboy1233306600

4) turn on the copy switch from the library, verify the data

mysql> start slave;mysql> show slave status\g;***************************  1. row ***************************                Slave_IO_State: Waiting for master to send event                   master_ host: 172.16.2.10                   Master_User: rep                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000015           Read_Master_Log_Pos: 3289                Relay_Log_File: relay-bin.000002                 Relay_Log_Pos: 338         Relay_Master_Log_File: mysql-bin.000015              Slave_IO_Running: Yes      #从库从主库复制binlog日志进程              Slave_SQL_Running: Yes       #从库读取中继日志转换成SQL语句应用到数据库进程                replicate_do_db:          replicate_ignore _db: mysql           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: 3289               Relay_Log_Space: 488               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: 0      #在复制过程中, delay seconds from Cubby Main library Master_ssl_verify_server_ cert: no                 last_io_errno: 0                 last_io_error:               last_sql_errno: 0                Last_SQL_Error:   Replicate_Ignore_Server_Ids:              master_server_id: 1

Validation is complete.

This article comes from the "change from every day" blog, so be sure to keep this source http://lilongzi.blog.51cto.com/5519072/1828779

MySQL master-slave replication principle Practice