本文详细介绍了如何使用mysqldump进行主主复制，包括备份主库、在从库加载数据、修改从库的log位置以及启动和检查slave状态。同时，解析了mysqldump的关键参数，如--master-data、--add-drop-database和--add-drop-table等，这些参数在数据库备份和主从复制中起到重要作用。
mysqldump用于主主（主从）复制（不加锁，不影响业务）
操作
1.备份主库数据
mysqldump -uroot -pR1234qwroot -F --master-data=2 -A --add-drop-database --add-drop-table > all.sql
2.从库加载数据
source /home/odcp/all.sql
3.从库修改log位置
#查看MASTER_LOG_FILE
head -30 all.sql
change master to master_host='10.1.234.110',master_user='repuser',master_password='repuser',master_port=3306;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000202',MASTER_LOG_POS=154;
4.启动slave
start slave;
5.查看slave
show slave status\G
mysqldump参数解释
-A ：所有库；
-F：刷新logs
–add-drop-database：在每个 CREATE DATABASE 语句之前添加 DROP DATABASE 语句
–add-drop-table 在每个 CREATE TABLE 语句之前添加 DROP TABLE 语句
–master-data：
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 after you load the dump file into the slave.
使用此选项dump a master replication server到dump文件，可用于设立另一台服务器作为master的slave。
它会使dump输出包含CHANGE MASTER TO语句，标记dump源的二进制日志坐标（文件名和位置）。
在把load dump文件加载到slave之后，slave应当从该master坐标开始复制。
更多master-data参考
mysqldump官网参数
| 参数 | 描述 | 介绍 |
|---|---|---|
| –add-drop-database | Add DROP DATABASE statement before each CREATE DATABASE statement | |
| –add-drop-table | Add DROP TABLE statement before each CREATE TABLE statement | |
| –add-drop-trigger | Add DROP TRIGGER statement before each CREATE TRIGGER statement | |
| –add-locks | Surround each table dump with LOCK TABLES and UNLOCK TABLES statements | |
| –all-databases | Dump all tables in all databases | |
| –allow-keywords | Allow creation of column names that are keywords | |
| –apply-slave-statements | Include STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output | |
| –bind-address | Use specified network interface to connect to MySQL Server | |
| –character-sets-dir | Directory where character sets are installed | |
| –comments | Add comments to dump file | |
| –compact | Produce more compact output | |
| –compatible | Produce output that is more compatible with other database systems or with older MySQL servers | |
| –complete-insert | Use complete INSERT statements that include column names | |
| –compress | Compress all information sent between client and server | |
| –create-options | Include all MySQL-specific table options in CREATE TABLE statements | |
| –databases | Interpret all name arguments as database names | |
| –debug | Write debugging log | |
| –debug-check | Print debugging information when program exits | |
| –debug-info | Print debugging information, memory, and CPU statistics when program exits | |
| –default-auth | Authentication plugin to use | |
| –default-character-set | Specify default character set | |
| –defaults-extra-file | Read named option file in addition to usual option files | |
| –defaults-file | Read only named option file | |
| –defaults-group-suffix | Option group suffix value | |
| –delete-master-logs | On a replication source server, delete the binary logs after performing the dump operation | |
| –disable-keys | For each table, surround INSERT statements with statements to disable and enable keys | |
| –dump-date | Include dump date as “Dump completed on” comment if --comments is given | |
| –dump-slave | Include CHANGE MASTER statement that lists binary log coordinates of replica’s source | |
| –enable-cleartext-plugin | Enable cleartext authentication plugin | 5.7.10 |
| –events | Dump events from dumped databases | |
| –extended-insert | Use multiple-row INSERT syntax | |
| –fields-enclosed-by | This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA | |
| –fields-escaped-by | This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA | |
| –fields-optionally-enclosed-by | This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA | |
| –fields-terminated-by | This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA | |
| –flush-logs | Flush MySQL server log files before starting dump | |
| –flush-privileges | Emit a FLUSH PRIVILEGES statement after dumping mysql database | |
| –force | Continue even if an SQL error occurs during a table dump | |
| –get-server-public-key | Request RSA public key from server | 5.7.23 |
| –help | Display help message and exit | |
| –hex-blob | Dump binary columns using hexadecimal notation | |
| –host | Host on which MySQL server is located | |
| –ignore-error | Ignore specified errors | |
| –ignore-table | Do not dump given table | |
| –include-master-host-port | Include MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with --dump-slave | |
| –insert-ignore | Write INSERT IGNORE rather than INSERT statements | |
| –lines-terminated-by | This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA | |
| –lock-all-tables | Lock all tables across all databases | |
| –lock-tables | Lock all tables before dumping them | |
| –log-error | Append warnings and errors to named file | |
| –login-path | Read login path options from .mylogin.cnf | |
| –master-data | Write the binary log file name and position to the output | |
| –max-allowed-packet | Maximum packet length to send to or receive from server | |
| –net-buffer-length | Buffer size for TCP/IP and socket communication | |
| –no-autocommit | Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements | |
| –no-create-db | Do not write CREATE DATABASE statements | |
| –no-create-info | Do not write CREATE TABLE statements that re-create each dumped table | |
| –no-data | Do not dump table contents | |
| –no-defaults | Read no option files | |
| –no-set-names | Same as --skip-set-charset | |
| –no-tablespaces | Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output | |
| –opt | Shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset | |
| –order-by-primary | Dump each table’s rows sorted by its primary key, or by its first unique index | |
| –password | Password to use when connecting to server | |
| –pipe | Connect to server using named pipe (Windows only) | |
| –plugin-dir | Directory where plugins are installed | |
| –port | TCP/IP port number for connection | |
| –print-defaults | Print default options | |
| –protocol | Transport protocol to use | |
| –quick | Retrieve rows for a table from the server a row at a time | |
| –quote-names | Quote identifiers within backtick characters | |
| –replace | Write REPLACE statements rather than INSERT statements | |
| –result-file | Direct output to a given file | |
| –routines | Dump stored routines (procedures and functions) from dumped databases | |
| –secure-auth | Do not send passwords to server in old (pre-4.1) format | |
| –server-public-key-path | Path name to file containing RSA public key | 5.7.23 |
| –set-charset | Add SET NAMES default_character_set to output | |
| –set-gtid-purged | Whether to add SET @@GLOBAL.GTID_PURGED to output | |
| –shared-memory-base-name | Shared-memory name for shared-memory connections (Windows only) | |
| –single-transaction | Issue a BEGIN SQL statement before dumping data from server | |
| –skip-add-drop-table | Do not add a DROP TABLE statement before each CREATE TABLE statement | |
| –skip-add-locks | Do not add locks | |
| –skip-comments | Do not add comments to dump file | |
| –skip-compact | Do not produce more compact output | |
| –skip-disable-keys | Do not disable keys | |
| –skip-extended-insert | Turn off extended-insert | |
| –skip-mysql-schema | Do not drop the mysql schema | 5.7.36 |
| –skip-opt | Turn off options set by --opt | |
| –skip-quick | Do not retrieve rows for a table from the server a row at a time | |
| –skip-quote-names | Do not quote identifiers | |
| –skip-set-charset | Do not write SET NAMES statement | |
| –skip-triggers | Do not dump triggers | |
| –skip-tz-utc | Turn off tz-utc | |
| –socket | Unix socket file or Windows named pipe to use | |
| –ssl | Enable connection encryption | |
| –ssl-ca | File that contains list of trusted SSL Certificate Authorities | |
| –ssl-capath | Directory that contains trusted SSL Certificate Authority certificate files | |
| –ssl-cert | File that contains X.509 certificate | |
| –ssl-cipher | Permissible ciphers for connection encryption | |
| –ssl-crl | File that contains certificate revocation lists | |
| –ssl-crlpath | Directory that contains certificate revocation-list files | |
| –ssl-key | File that contains X.509 key | |
| –ssl-mode | Desired security state of connection to server | 5.7.11 |
| –ssl-verify-server-cert | Verify host name against server certificate Common Name identity | |
| –tab | Produce tab-separated data files | |
| –tables | Override --databases or -B option | |
| –tls-version | Permissible TLS protocols for encrypted connections | 5.7.10 |
| –triggers | Dump triggers for each dumped table | |
| –tz-utc | Add SET TIME_ZONE=‘+00:00’ to dump file | |
| –user | MySQL user name to use when connecting to server | |
| –verbose | Verbose mode | |
| –version | Display version information and exit | |
| –where | Dump only rows selected by given WHERE condition | |
| –xml | Produce XML output |
616
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
