Mysqldump -- master-data parameter for Fast deployment of master-slave Replication

Mysqldump -- master-data parameter for Fast 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
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 to the backup and Export File
If the value is equal to 1, a change master statement is added.
If the value is equal to 2, a comment will be added before the change master Statement (does not work ~)
This parameter locks the -- lock-all-tables Table, unless you specify -- single-transaction
In this case, the lock table will only last for a short period of time at the beginning of dump.
During dump, any action will affect the binlog file.
After dump is completed, the option automatically disables the table lock function.

I don't know if the translation is correct. Let's take a look ~~
To put it simply, when the master-slave replication performs full backup, this option can automatically help us lock the table and identify the binlog critical file, so we do not need to lock the table, and then look at the critical file number, then, execute change master to enter the binglong location information to the slave master.info file, which improves the slave database deployment efficiency.

Test the instance
Back up the current database
# Note that it is best not to include several databases that come with mysql when you copy and back up the database from the master node, such as mysql and information_schema. Otherwise, the "Last_ SQL _Error: error 'can't create database "Error, so we need to exclude these databases during backup, and because mysqldump only has the ignore-table parameter, there is no ignore-database, you Can use the following command to implement it

[Root @ db02 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 must have f0000. SQL .gz

-- The maste-data parameter automatically adds change mastes to... TO the backup file...

We will restore the full backup to the slave Database
[Root @ db02 3309] # mysql-uroot-S/data/3309/mysql. sock </server/backup/mysql_2016-07-07. SQL

Configure the change master to .. command
Mysql> change master to MASTER_HOST = '192. 16.2.10 ', MASTER_PORT = 172, MASTER_USER = 'rep', MASTER_PASSWORD = 'oldboy123 ';
# Note that MASTER_LOG_FILE and MASTER_LOG_POS are not configured at this time.
Enable salve
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: 0
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
1 row in set (0.00 sec)

We create a lilongzi database in the master database to verify whether the connection between the master database and the slave database is successful.

[Root @ db02 3309] # mysql-uroot-poldboy1234-S/data/3306/mysql. sock
Mysql> cr
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Lilongzi |
| Lilongzi_gbk |
| Mysql |
| Performance_schema |
| Test |
| Www |
| Zzz |
+ -------------------- +
8 rows in set (0.00 sec)
Eate database lilongzi;

From the database side
[Root @ db02 3309] # mysql-uroot-S/data/3309/mysql. sock
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Lilongzi |
| Lilongzi_gbk |
| Mysql |
| Performance_schema |
| Test |
| Www |
| Zzz |
+ -------------------- +
8 rows in set (0.00 sec)

Verification Successful!

This article permanently updates the link address: