實(shí)例解讀mysqldump參數(shù)--master-data

zhiming.yk

實(shí)例解讀mysqldump參數(shù)--master-data
先來(lái)看看官方文檔的解釋
Use this option to dump a master replication server to produce a dump file that can be used to set up another server as a slave of the master. It causes the dump output to include a
CHANGE MASTER TO
statement that indicates the binary log coordinates (file name and position) of the dumped server. These are the master server coordinates from which the slave should start replicating.
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 option value is not specified, the default value is 1.
大概就這么個(gè)意思：這個(gè)參數(shù)在建立slave數(shù)據(jù)庫(kù)的時(shí)候會(huì)用到，當(dāng)這個(gè)參數(shù)的值為1的時(shí)候，mysqldump出來(lái)的文件就會(huì)包括
CHANGE MASTER TO
這個(gè)語(yǔ)句，
CHANGE MASTER TO
后面緊接著就是file和position的記錄，file和position記錄的位置就是slave從master端復(fù)制文件的起始位置。默認(rèn)情況下這個(gè)值是1
當(dāng)這個(gè)值是2的時(shí)候，chang master to也是會(huì)寫到dump文件里面去的，但是不會(huì)有上面那個(gè)作用了（thus is information only）
翻譯過(guò)來(lái)真感覺(jué)拗口，呵呵，湊活看看吧。上實(shí)例！
一、先dump一個(gè)庫(kù)
[root@HI0-OA-nagios ~]# mysqldump -uroot -pdfs123 --master-data=1 nagios > dumpfile
二、觀察file和position的值，此時(shí)的table是被lock住不能寫入的
mysql> show master status;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000016 | 760292258 |              |                  |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> show master status;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000016 | 760292258 |              |                  |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> show master status;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000016 | 760292258 |              |                  |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)
三、觀察dump出來(lái)的文件，file和position的值和上面是相同的
[root@HI0-OA-nagios ~]# grep -i "CHANGE MASTER TO" dumpfile
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=760292258;
四、編輯slave端配置文件如下
[mysqld]
port            = 3306
socket          = /usr/local/mysql/var/mysql.sock
skip-locking
key_buffer = 64M
max_allowed_packet = 16M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-bin=mysql-bin
server-id = 3
master-host=10.83.200.153
master-port=3306
master-user=repl
master-password=slavepass
master-connect-retry=60
五、重啟slave端mysql
Service mysql restart
六、此時(shí)netstat -an|grep 3306 看到slave和master的連接已經(jīng)建立�，F(xiàn)在不需要它們之間連接，登陸slave端，執(zhí)行stop slave，連接成功斷開(kāi)
七、Mysqldump 導(dǎo)入master 導(dǎo)出的文件dumpfile
八、開(kāi)啟slave端同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
在這一步驟，如果--master-data 參數(shù)為二，此時(shí)你會(huì)發(fā)現(xiàn)你還需要輸入
CHANGE MASTER TO
  MASTER_HOST='master2.mycompany.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;
類似的參數(shù)后，才能繼續(xù)執(zhí)行start slave。當(dāng)然這里你也可以寫到配置文件中去
九、查看slave端狀態(tài)，大功告成
mysql> show processlist\G;
*************************** 1. row ***************************
     Id: 6
   User: root
   Host: localhost
     db: nagios
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 2. row ***************************
     Id: 7
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 27
  State: Waiting for master to send event
   Info: NULL
*************************** 3. row ***************************
     Id: 8
   User: system user
   Host:
     db: nagios
Command: Connect
   Time: 2958
  State: updating
   Info: DELETE FROM nagios_servicechecks WHERE instance_id='1' AND start_time
3 rows in set (0.01 sec)
本文出自 “
一代小怪追大怪
” 博客，請(qǐng)務(wù)必保留此出處
http://asmboy001.blog.51cto.com/340398/197750


本文來(lái)自ChinaUnix博客，如果查看原文請(qǐng)點(diǎn)：http://blog.chinaunix.net/u2/66227/showart_2111589.html