用mysqldump --master-data 建立slave
用mysqldump --master-data 建立slave
先來看看官方文件的解釋
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.
大概就這麼個意思:這個引數在建立slave資料庫的時候會用到,當這個引數的值為1的時候,mysqldump出來的檔案就會包括CHANGE MASTER TO這個語句,CHANGE MASTER TO後面緊接著就是file和position的記錄,file和position記錄的位置就是slave從master端複製檔案的起始位置。預設情況下這個值是1
當這個值是2的時候,chang master to也是會寫到dump檔案裡面去的,但是不會有上面那個作用了(thus is information only)
翻譯過來真感覺拗口,呵呵,湊活看看吧。上例項!
一、先dump一個庫
mysqldump -uroot -p123456 --master-data=1 --quick --all-databases --flush-logs --lock-all-tables > alldb.sql
二、觀察file和position的值,此時的table是被lock住不能寫入的
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000019 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000019 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000019 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000019 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> show processlist;
+----+------+------------+--------+-------------+------+----------------------------------------------------------------+-------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+------------+--------+-------------+------+----------------------------------------------------------------+-------------------------------------------------------------+
| 1 | rep | node2:2514 | NULL | Binlog Dump | 757 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 3 | root | localhost | test | Query | 0 | NULL | show processlist |
| 4 | root | localhost | orders | Query | 23 | Writing to net | SELECT /*!40001 SQL_NO_CACHE */ * FROM `order_status_track` |
+----+------+------------+--------+-------------+------+----------------------------------------------------------------+-------------------------------------------------------------+
3 rows in set (0.00 sec)
+----+------+------------+--------+-------------+------+----------------------------------------------------------------+-------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+------------+--------+-------------+------+----------------------------------------------------------------+-------------------------------------------------------------+
| 1 | rep | node2:2514 | NULL | Binlog Dump | 757 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 3 | root | localhost | test | Query | 0 | NULL | show processlist |
| 4 | root | localhost | orders | Query | 23 | Writing to net | SELECT /*!40001 SQL_NO_CACHE */ * FROM `order_status_track` |
+----+------+------------+--------+-------------+------+----------------------------------------------------------------+-------------------------------------------------------------+
3 rows in set (0.00 sec)
三、觀察dump出來的檔案,file和position的值和上面是相同的
[root@node1 ~]# grep -i "CHANGE MASTER TO" alldb.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=106;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=106;
四、編輯slave端配置檔案如下
log-bin=mysql-bin
server-id = 3
master-host=192.168.1.201
master-port=3306
master-user=rep
master-password=rep
master-connect-retry=60
server-id = 3
master-host=192.168.1.201
master-port=3306
master-user=rep
master-password=rep
master-connect-retry=60
五、重啟slave端mysql
[root@node1 mysql]# /etc/init.d/mysql stop
Shutting down MySQL...... [ OK ]
[root@node1 mysql]# /etc/init.d/mysql start
Starting MySQL... [ OK ]
Shutting down MySQL...... [ OK ]
[root@node1 mysql]# /etc/init.d/mysql start
Starting MySQL... [ OK ]
六、此時netstat -an|grep 3306 看到slave和master的連線已經建立。現在不需要它們之間連線,登陸slave端,執行stop slave,連線成功斷開
七、Mysqldump 匯入master 匯出的檔案dumpfile
mysql -uroot -p123456 < alldb.sql
八、開啟slave端同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
在這一步驟,如果--master-data 引數為二,此時你會發現你還需要輸入
CHANGE MASTER TO
MASTER_HOST='192.168.1.202',
MASTER_USER='rep',
MASTER_PASSWORD='rep';
MASTER_HOST='192.168.1.202',
MASTER_USER='rep',
MASTER_PASSWORD='rep';
類似的引數後,才能繼續執行start slave。當然這裡你也可以寫到配置檔案中去
九、檢視slave端狀態,大功告成
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected (0.05 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.201
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000019
Read_Master_Log_Pos: 106
Relay_Log_File: node1-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000019
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 106
Relay_Log_Space: 406
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:
1 row in set (0.00 sec)
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.201
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000019
Read_Master_Log_Pos: 106
Relay_Log_File: node1-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000019
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 106
Relay_Log_Space: 406
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:
1 row in set (0.00 sec)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/758322/viewspace-680471/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mysqldump dump-slaveMySql
- mysqldump --master-data=2引數的含義MySqlAST
- mysqldump的single-transaction和master-dataMySqlAST
- [轉帖]mysqldump --master-data=2 --single-transactionMySqlAST
- 用xtrabackup工具建立slave節點
- MySQL 5.5 Slave節點備份指令碼(mysqldump)MySql指令碼
- 深入理解mysqldump原理 --single-transaction --lock-all-tables --master-dataMySqlAST
- Mysqldump實現mysql的master-slave主從複製MySqlAST
- mysql建立master/slave詳細步驟MySqlAST
- 建立slave型別的dns伺服器型別DNS伺服器
- mysqldump單表備份加--single-transaction --master-data=2引數執行過程的內部剖析MySqlAST
- mysqldumpMySql
- Mysql 建立心跳錶來監控Replication的Slave是否延遲MySql
- MySQL 5.7 用mysqldump搭建gtid主從MySql
- 【mysqldump】mysqldump及備份恢復示例MySql
- mysqldump流程MySql
- mysqldump使用MySql
- mysqldump命令MySql
- zabbix應用-監控mysql slave 主從狀態MySql
- Redis master and slaveRedisAST
- MySQL master/slaveMySqlAST
- MySQL新建SlaveMySql
- 運用mysqldump 工具時需要注意的問題MySql
- 【Mysql】mysqldump 匯出各種場景的應用MySql
- mysqldump小談MySql
- mysqldump原理分析MySql
- mysqldump原理圖MySql
- 5.7 mysqldump routinesMySql
- MySQL mysqldump命令MySql
- mysqldump與mysqlpumpMySql
- mysqldump 選項MySql
- MySQL複製跳過錯誤--slave_skip_errors、sql_slave_skip_counter、slave_exec_modeMySqlError
- slave-skip-errorsError
- MYSQL 配置slave故障MySql
- slave_exec_mode
- MySql的Master/SlaveMySqlAST
- jobq slave waitAI
- mysqldump 使用規範MySql