Mysqldump--master-data parameter for rapid deployment of master-slave replication

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:              MASTER_SERVER_ID:  11 row in set  (0.00 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