--dump-slave用户在slave端dump数据,建立新的slave。
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.110.216
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.002810
Read_Master_Log_Pos: 16550261
Relay_Log_File: relay-bin.000419
Relay_Log_Pos: 252
Relay_Master_Log_File: mysql-bin.002810
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: WATCDB01,WATCDB02,WATCDB03,WATCDB04
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: 16550261
Relay_Log_Space: 547
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: 2163306
1 row in set (0.00 sec)
ERROR:
No query specified
[root@MYSQL5 ~]# /usr/local/mysql/bin/mysqldump -A --dump-slave=2 -q --single-transaction > /u1/all.sql
^C
[root@MYSQL5 ~]# more /u1/all.sql
-- MySQL dump 10.13 Distrib 5.5.20, for linux2.6 (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.5.20-enterprise-commercial-advanced-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from (the master of this slave)
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.002810', MASTER_LOG_POS=16550261;
会记录SLAVE的那个点。
--dump-slave[=#] This causes the binary log position and filename of the
master to be appended to the dumped data output. Setting
the value to 1, will printit as a CHANGE MASTER command
in the dumped data output; 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.
转载于:https://blog.51cto.com/liang3391/819248