Mysqldump 当中一些重要参数

 

未开启gtid的主库，创建从库，在从库通过mysqldump实时导入完毕数据后，

 如何实时同步binlog的位置点呢?

 

--master-data[=value]

Use this option to dump a source replication server to produce a dump file that can be used to set up another server as a replica of the source. 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 source 

server coordinates from which the replica should start replicating after you load the dump file into the replica.

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 is not written as a comment and takes effect when the dump file is reloaded. If no option value is specified, the default value is 1.

--master-data sends a SHOW MASTER STATUS statement to the server to obtain information, so it requires privileges sufficient to execute that statement. This option also requires the RELOAD privilege and the binary log must be enabled.

The --master-data option automatically turns off --lock-tables. It also turns on --lock-all-tables, unless --single-transaction also is specified, in which case, a global read lock is acquired only for a short time at the beginning of the dump (see the description for --single-transaction). In all cases, any action on logs happens at the exact moment of the dump.

 

master-data=1 默认值，在导出数据的dump文件会包含 change master to binlog/pos
master-data=2 change master to 会作为注释，不生效。

master-data的执行，需要获取一些有限级的权限，

--lock-all-tables

Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables.

锁全库，在整个dump过程中获取全局读锁，互斥关闭single-transaction/lock-tables

--lock-tables

For each dumped database, lock all tables to be dumped before dumping them. The tables are locked with READ LOCAL to permit concurrent inserts in the case of MyISAM tables. For transactional tables such as InnoDB, --single-transaction is a much better option than --lock-tables because it does not need to lock the tables at all.


在dump过程中，获取单个库的读锁，特别是有MyISAM表的时候，需要锁；对于InnoDB引擎，因为不需要锁全表，推荐用single-transaction

--single-transaction

This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.

When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.

While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

To dump large tables, combine the --single-transaction option with the --quick option.


single-transaction开启四种事务隔离的 REPEATEABLE READ（重复读），即事务开始读的数据，在全事务过程中是一致不变的，当然只有在InnoDB中可行。
同事非事务表 MyISAM/MEMORY表 这个参数自然失效
这个时候，ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE等DDL操作会被报错；
最后，single-transaction和 lock-tables是互斥的。