MySQL master-slave library setup

1, the main standby library construction:

Slightly

Main Library: 10.1.1.17 Library: 10.1.1.18

2, the main library authorization:

Permissions to allow replication of backups

GRANT REPLICATION SLAVE on * * to ' slaveuser ' @ ' 10.1.1.18 ' identified by ' hsdhsT56 '; FLUSH Privileges

3, the main library data export

Mysqldump-uroot-p-H 127.0.0.1--databases TestDB--default-character-set=utf8--net_buffer_length=16535--single-tra Nsaction--master-data=2 >testdb.sql

If there are multiple libraries on the machine, you can export all the libraries

Mysqldump-uroot-p-H 127.0.0.1--all-databases--default-character-set=utf8--net_buffer_length=16535--max_allowed_ packet=1048576--single-transaction--master-data=2 >all_databases.sql

Parameter description:

Because we are here to back up the data and import as slave, so we need to use--single-transaction --master-data collocation, if just export data can only use--single-transactionnet _BUFFER_LENGTH:TCP/IP and socket communication buffer sizes, creating rows up to Net_buffer_length, which can be faulted if the value is larger than the value of the target database. --max_allowed_packet=1048576 the maximum size of the buffer between client/server communication, use this parameter, mainly to speed up the export use when I set this parameter to the--max_allowed_packet= 21048576 reduced my 13G data export time from 8 minutes to 7 minutes ... single-transaction:innodb  table when backing up, you usually enable the option  --single-transaction  to ensure the consistency of the backup, In fact, it works by setting the isolation level for this session: Repeatable read, to ensure that this session (dump) does not see data that has been submitted by other sessions. Master-data=2:if the option value is 2, the change master to  statement is written as an SQL comment, and thus is  Informative only; it has no effect when the dump file is  reloaded. if the option value is 1, the statement takes  effect when the dump file is reloaded. if the&Nbsp;option value is not specified, the default value is 1. It means: If master-data=1, then Change master will be written into the statement, if it is master-data=2, then change master just as a comment to write to the content. Here we use 2, if use 1, then the data import, Change master immediately run. We want Change master, controlled by us by hand.

4, the above data into the standby library

Mysql-uroot-p-E--max_allowed_packet=1048576--net_buffer_length=16384 <

5.

See where the sync should start

Grep-i "Change Master" testdb.sqlmaster_log_file= ' mysql-bin.000004 ', master_log_pos=106 here explains, I am the end of this data export winner Apartments log is mysql-bin.000004, the location is 106. So after I've got the library up, I need to synchronize the data from this location

6. Start syncing

--Stop Slavestop Slave--change master to Main Library change master to master_host= ' 10.1.1.17 ', master_user= ' Slaveuser ', master_ Password= ' hsdhsT56 ', master_log_file= ' mysql-bin.000004 ', master_log_pos=106;--open Slavestart SLAVE; view SLAVE status Show SLAVE STATUS;

This article is from "H2O's Operation & Development Road" blog, reprint please contact the author!

MySQL master-slave library setup