Mysqldump--help
--master-data[=#] this causes the binary log position and filename to be appended to the output. if equal to 1, will print it as a CHANGE MASTER command; if equal to 2, that command will be prefixed with a comment symbol. this option will turn --lock-all-tables on, unless --single-transaction is specified too (in which case a global read lock is only taken a short time at the beginning of the dump; don ' t forget to read about --single-transaction below). In all cases, any action on logs will happen at the exact moment of the dump. Option automatically turns --lock-tables off.
--master-data[=#] append the location and name of the binary Binlog file in the backup exported file if the value equals 1, a change master statement is added if the value is equal to 2, the change Add comment before master statement (no effect) This parameter will--lock-all-tables the lock table, Unless you specify--single-transaction . in this case, the lock table will only last for a short time at the start of the dump, supposedly at dump, any action will affect the Binlog file After the dump is finished, the option automatically turns off the lock table function
Do not know the translation is right, make a look at it ~ ~
Simply put, is the master-slave replication in the full amount of backup, this option can automatically help us lock the table and identify the Binlog critical file, we do not need to lock the table, then look at the critical file number, and then perform the change Master fills in the Binglong location information from the library Master.info file, improving the efficiency of the deployment from the library.
example test a bit
Back up the current database
#注意 in the decision to copy the database from the backup, it is best not to bring the MySQL with a few libraries, such as MySQL, information_schema otherwise open slave switch to replicate the time will appear "Last_sql_error:error ' can ' t Create DATABASE "error, so when the backup to exclude these libraries, and because mysqldump only ignore-table parameters, and there is no ignore-database can use the command to implement
[Email protected] 3309]# mysql-uroot-poldboy1234-s/data/3306/mysql.sock-e "show databases;" | Grep-ev "Database|information_schema|performance_schema|mysql" |xargs mysqldump-uroot-poldboy1234-s/data/3306/ Mysql.sock-b-f-r--master-data=1--events|gzip >/server/backup/mysql_$ (Date +%f). sql.gz
The--maste-data parameter automatically adds the change mastes to ... in the backup file.
650) this.width=650; "Src=" http://www.lichengbing.cn/ueditor/php/upload/image/20160715/1468549801544998.jpg " Title= "1468549801544998.jpg" alt= "8f3c.tmp.jpg"/>
We restore a full-scale backup to the slave library
[Email protected] 3309]# mysql-uroot-s/data/3309/mysql.sock </server/backup/mysql_2016-07-07.sql
Configure Change MASTER to: Command
Mysql> change master to master_host= ' 172.16.2.10 ', master_port=3306, master_ User= ' rep ', master_password= ' oldboy123 ';# Note at this time I did not configure Master_log_file and Master_log_pos to open salve Switch Mysql> start slave;mysql> show slave status;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.000023 Read_Master_Log_Pos: 279 relay_log_file: relay-bin.000037 relay_log_pos: 344 Relay_Master_Log_File: mysql-bin.000023 Slave_IO_Running: Yes Slave_SQL_Running: Yes 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: 279 Relay_Log_Space: 640 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: replicate_ignore_server _IDS:&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;MASTER_SERVER_ID: 11 row in set (0.00&NBSP;SEC)
we create a Lilongzi database in the main library to verify that the main library and the connection from the library are successful
[[email protected] 3309]# mysql -uroot -poldboy1234 -s /data/3306/ mysql.sockmysql> crmysql> show databases;+--------------------+| database |+--------------------+| information_schema | | lilongzi | | lilongzi_gbk | | mysql | | performance_schema | | test | | www | | zzz |+--- -----------------+8 rows in set (0.00 sec) eate database&Nbsp;lilongzi;
[[email protected] 3309]# mysql -uroot -s /data/3309/mysql.sockmysql> show databases;+--------------------+| database |+--------------------+| information_schema | | lilongzi | | lilongzi_gbk | | mysql | | performance_schema | | test | | www | | zzz |+--- -----------------+8 rows in set (0.00 sec)
Verify success!
This article comes from the "change from every day" blog, so be sure to keep this source http://lilongzi.blog.51cto.com/5519072/1828776
Mysqldump--master-data parameter for rapid deployment of master-slave replication