MySQL备份

备份主要采用两种方法,分别是mysqldump和xtrabackup,mysqldump适合较小数据量的备份,而xtrabackup则备份较大数据量。

mysqldump

mysqldump是MySQL自带的备份工具,它执行逻辑备份,生成一组SQL语句,然后可以执行这些语句来重现原始数据库对象定义和表数据。 它转储一个或多个MySQL数据库以备份或传输到另一个SQL服务器。 mysqldump命令还可以生成CSV,其他分隔文本或XML格式的输出。

Usage: mysql [OPTIONS] [database]
  -?, --help          Display this help and exit.
  -I, --help          Synonym for -?
  --auto-rehash       Enable automatic rehashing. One doesn't need to use
                      'rehash' to get table and field completion, but startup
                      and reconnecting may take a longer time. Disable with
                      --disable-auto-rehash.
                      (Defaults to on; use --skip-auto-rehash to disable.)
  -A, --no-auto-rehash
                      No automatic rehashing. One has to use 'rehash' to get
                      table and field completion. This gives a quicker start of
                      mysql and disables rehashing on reconnect.
  --auto-vertical-output
                      Automatically switch to vertical output mode if the
                      result is wider than the terminal width.
  -B, --batch         Don't use history file. Disable interactive behavior.
                      (Enables --silent.)
  --bind-address=name IP address to bind to.
  -b, --binary-as-hex Print binary data as hex
  --character-sets-dir=name
                      Directory for character set files.
  --column-type-info  Display column type information.
  -c, --comments      Preserve comments. Send comments to the server. The
                      default is --skip-comments (discard comments), enable
                      with --comments.
  -C, --compress      Use compression in server/client protocol.
  -#, --debug[=#]     This is a non-debug version. Catch this and exit.
  --debug-check       This is a non-debug version. Catch this and exit.
  -T, --debug-info    This is a non-debug version. Catch this and exit.
  -D, --database=name Database to use.
  --default-character-set=name
                      Set the default character set.
  --delimiter=name    Delimiter to be used.
  --enable-cleartext-plugin
                      Enable/disable the clear text authentication plugin.
  -e, --execute=name  Execute command and quit. (Disables --force and history
                      file.)
  -E, --vertical      Print the output of a query (rows) vertically.
  -f, --force         Continue even if we get an SQL error.
  --histignore=name   A colon-separated list of patterns to keep statements
                      from getting logged into syslog and mysql history.
  -G, --named-commands
                      Enable named commands. Named commands mean this program's
                      internal commands; see mysql> help . When enabled, the
                      named commands can be used from any line of the query,
                      otherwise only from the first line, before an enter.
                      Disable with --disable-named-commands. This option is
                      disabled by default.
  -i, --ignore-spaces Ignore space after function names.
  --init-command=name SQL Command to execute when connecting to MySQL server.
                      Will automatically be re-executed when reconnecting.
  --local-infile      Enable/disable LOAD DATA LOCAL INFILE.
  -b, --no-beep       Turn off beep on error.
  -h, --host=name     Connect to host.
  -H, --html          Produce HTML output.
  -X, --xml           Produce XML output.
  --line-numbers      Write line numbers for errors.
                      (Defaults to on; use --skip-line-numbers to disable.)
  -L, --skip-line-numbers
                      Don't write line number for errors.
  -n, --unbuffered    Flush buffer after each query.
  --column-names      Write column names in results.
                      (Defaults to on; use --skip-column-names to disable.)
  -N, --skip-column-names
                      Don't write column names in results.
  --sigint-ignore     Ignore SIGINT (CTRL-C).
  -o, --one-database  Ignore statements except those that occur while the
                      default database is the one named at the command line.
  --pager[=name]      Pager to use to display results. If you don't supply an
                      option, the default pager is taken from your ENV variable
                      PAGER. Valid pagers are less, more, cat [> filename],
                      etc. See interactive help (\h) also. This option does not
                      work in batch mode. Disable with --disable-pager. This
                      option is disabled by default.
  -p, --password[=name]
                      Password to use when connecting to server. If password is
                      not given it's asked from the tty.
  -P, --port=#        Port number to use for connection or 0 for default to, in
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
                      /etc/services, built-in default (3306).
  --prompt=name       Set the mysql prompt to this value.
  --protocol=name     The protocol to use for connection (tcp, socket, pipe,
                      memory).
  -q, --quick         Don't cache result, print it row by row. This may slow
                      down the server if the output is suspended. Doesn't use
                      history file.
  -r, --raw           Write fields without conversion. Used with --batch.
  --reconnect         Reconnect if the connection is lost. Disable with
                      --disable-reconnect. This option is enabled by default.
                      (Defaults to on; use --skip-reconnect to disable.)
  -s, --silent        Be more silent. Print results with a tab as separator,
                      each row on new line.
  -S, --socket=name   The socket file to use for connection.
  --ssl-mode=name     SSL connection mode.
  --ssl               Deprecated. Use --ssl-mode instead.
                      (Defaults to on; use --skip-ssl to disable.)
  --ssl-verify-server-cert
                      Deprecated. Use --ssl-mode=VERIFY_IDENTITY instead.
  --ssl-ca=name       CA file in PEM format.
  --ssl-capath=name   CA directory.
  --ssl-cert=name     X509 cert in PEM format.
  --ssl-cipher=name   SSL cipher to use.
  --ssl-key=name      X509 key in PEM format.
  --ssl-crl=name      Certificate revocation list.
  --ssl-crlpath=name  Certificate revocation list path.
  --tls-version=name  TLS version to use, permitted values are: TLSv1, TLSv1.1,
                      TLSv1.2
  -t, --table         Output in table format.
  --tee=name          Append everything into outfile. See interactive help (\h)
                      also. Does not work in batch mode. Disable with
                      --disable-tee. This option is disabled by default.
  -u, --user=name     User for login if not current user.
  -U, --safe-updates  Only allow UPDATE and DELETE that uses keys.
  -U, --i-am-a-dummy  Synonym for option --safe-updates, -U.
  -v, --verbose       Write more. (-v -v -v gives the table output format).
  -V, --version       Output version information and exit.
  -w, --wait          Wait and retry if connection is down.
  --connect-timeout=# Number of seconds before connection timeout.
  --max-allowed-packet=#
                      The maximum packet length to send to or receive from
                      server.
  --net-buffer-length=#
                      The buffer size for TCP/IP and socket communication.
  --select-limit=#    Automatic limit for SELECT when using --safe-updates.
  --max-join-size=#   Automatic limit for rows in a join when using
                      --safe-updates.
  --secure-auth       Refuse client connecting to server if it uses old
                      (pre-4.1.1) protocol. Deprecated. Always TRUE
  --server-arg=name   Send embedded server this as a parameter.
  --show-warnings     Show warnings after every statement.
  -j, --syslog        Log filtered interactive commands to syslog. Filtering of
                      commands depends on the patterns supplied via histignore
                      option besides the default patterns.
  --plugin-dir=name   Directory for client-side plugins.
  --default-auth=name Default authentication client-side plugin to use.
  --binary-mode       By default, ASCII '\0' is disallowed and '\r\n' is
                      translated to '\n'. This switch turns off both features,
                      and also turns off parsing of all clientcommands except
                      \C and DELIMITER, in non-interactive mode (for input
                      piped to mysql or loaded using the 'source' command).
                      This is necessary when processing output from mysqlbinlog
                      that may contain blobs.
  --server-public-key-path=name
                      File path to the server public RSA key in PEM format.
  --connect-expired-password
                      Notify the server that this client is prepared to handle
                      expired password sandbox mode.

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
The following groups are read: mysql client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file,
                        except for login file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=#
                        Also read groups with concat(group, suffix)
--login-path=#          Read this path from the login file.

连接选项

--bind-address=ip_address 
# On a computer having multiple network interfaces, use this option to select which interface to use for connecting to the MySQL server.

 --compress, -C 
# Compress all information sent between the client and the server if both support compression. 
# 在客户端与服务器之间启用压缩传输信息。 

--default-auth=plugin 
# A hint about the client-side authentication plugin to use.  
# 客户端插件默认使用权限。 
$ mysqldump -uroot -p --host=localhost --all-databases --default-auth=”/usr/local/lib/plugin/<PLUGIN>”

--enable-cleartext-plugin
# Enable the mysql_clear_password cleartext authentication plugin. This option was added in MySQL 5.7.10.

--get-server-public-key
# Request from the server the public key required for RSA key pair-based password exchange. 
# This option applies to clients that that authenticate with the caching_sha2_password authentication plugin. 
# For that plugin, the server does not send the public key unless requested. T
# his option is ignored for accounts that do not authenticate with that plugin. 
# It is also ignored if RSA-based password exchange is not used, as is the case when the client connects to the server using a secure connection.
# If --server-public-key-path=file_name is given and specifies a valid public key file, it takes precedence over --get-server-public-key.
# The --get-server-public-key option was added in MySQL 5.7.23.

--host=host_name, -h host_name
# Dump data from the MySQL server on the given host. The default host is localhost.
# 需要导出的主机信息

--login-path=name
# Read options from the named login path in the .mylogin.cnf login path file. 
# A “login path” is an option group containing options that specify which MySQL server to connect to and which account to authenticate as. 
# To create or modify a login path file, use the mysql_config_editor utility.

--password[=password], -p[password]
# The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. 
# If you omit the password value following the --password or -p option on the command line, mysqldump prompts for one.
# 数据库连接密码。

--pipe, -W
# On Windows, connect to the server using a named pipe. This option applies only if the server supports named-pipe connections.
# 使用管道连接MySQL。

--plugin-dir=dir_name
# The directory in which to look for plugins. Specify this option if the --default-auth option is used to specify an authentication plugin but mysqldump does not find it. 
# See Section 6.3.9, “Pluggable Authentication”.

--port=port_num, -P port_num
# The TCP/IP port number to use for the connection.
# 连接数据库端口号。

--protocol={TCP|SOCKET|PIPE|MEMORY}
# The connection protocol to use for connecting to the server. 
# It is useful when the other connection parameters normally would cause a protocol to be used other than the one you want. 
# For details on the permissible values, see Section 4.2.2, “Connecting to the MySQL Server”.
# 连接使用的协议。

--secure-auth
# Do not send passwords to the server in old (pre-4.1) format. This prevents connections except for servers that use the newer password format.
# As of MySQL 5.7.5, this option is deprecated and will be removed in a future MySQL release. 
# It is always enabled and attempting to disable it (--skip-secure-auth, --secure-auth=0) produces an error. 
# Before MySQL 5.7.5, this option is enabled by default but can be disabled.

--server-public-key-path=file_name
# The path name to a file containing a client-side copy of the public key required by the server for RSA key pair-based password exchange. 
# The file must be in PEM format. This option applies to clients that authenticate with the sha256_password or caching_sha2_password authentication plugin. 
# This option is ignored for accounts that do not authenticate with one of those plugins. 
# It is also ignored if RSA-based password exchange is not used, as is the case when the client connects to the server using a secure connection.

# If --server-public-key-path=file_name is given and specifies a valid public key file, it takes precedence over --get-server-public-key.

# For sha256_password, this option applies only if MySQL was built using OpenSSL.

# For information about the sha256_password and caching_sha2_password plugins, see Section 6.5.1.4, “SHA-256 Pluggable Authentication”, and Section 6.5.1.5, “Caching SHA-2 Pluggable Authentication”.

# The --server-public-key-path option was added in MySQL 5.7.23.

--socket=path, -S path
# For connections to localhost, the Unix socket file to use, or, on Windows, the name of the named pipe to use.
# 连接指定MySQL的socket文件位置。

--ssl*
# Options that begin with --ssl specify whether to connect to the server using SSL and indicate where to find SSL keys and certificates. 
See Section 6.4.2, “Command Options for Encrypted Connections”.

--tls-version=protocol_list
# The protocols permitted by the client for encrypted connections. The value is a comma-separated list containing one or more protocol names. 
# The protocols that can be named for this option depend on the SSL library used to compile MySQL. 
# For details, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.
# This option was added in MySQL 5.7.10.

--user=user_name, -u user_name
# The MySQL user name to use when connecting to the server.
# 连接MySQL的用户名。

You can also set the following variables by using --var_name=value syntax:
max_allowed_packet
# The maximum size of the buffer for client/server communication. The default is 24MB, the maximum is 1GB.

net_buffer_length
# The initial size of the buffer for client/server communication. 
# When creating multiple-row INSERT statements (as with the --extended-insert or --opt option), mysqldump creates rows up to net_buffer_length bytes long. 
# If you increase this variable, ensure that the MySQL server net_buffer_length system variable has a value at least this large.

选项文件选项

--defaults-extra-file=file_name
# Read this option file after the global option file but (on Unix) before the user option file. 
# If the file does not exist or is otherwise inaccessible, an error occurs. 
# file_name is interpreted relative to the current directory if given as a relative path name rather than a full path name.

--defaults-file=file_name
# Use only the given option file. If the file does not exist or is otherwise inaccessible, an error occurs. 
# file_name is interpreted relative to the current directory if given as a relative path name rather than a full path name.
# Exception: Even with --defaults-file, client programs read .mylogin.cnf.

--defaults-group-suffix=str
# Read not only the usual option groups, but also groups with the usual names and a suffix of str. 
# For example, mysqldump normally reads the [client] and [mysqldump] groups. 
# If the --defaults-group-suffix=_other option is given, mysqldump also reads the [client_other] and [mysqldump_other] groups.

--no-defaults
# Do not read any option files. 
# If program startup fails due to reading unknown options from an option file, --no-defaults can be used to prevent them from being read.
# The exception is that the .mylogin.cnf file, if it exists, is read in all cases. 
# This permits passwords to be specified in a safer way than on the command line even when --no-defaults is used. 
# (.mylogin.cnf is created by the mysql_config_editor utility. See Section 4.6.6, “mysql_config_editor — MySQL Configuration Utility”.)

--print-defaults
# Print the program name and all options that it gets from option files.

DDL选项

--defaults-extra-file=file_name
# Read this option file after the global option file but (on Unix) before the user option file. 
# If the file does not exist or is otherwise inaccessible, an error occurs. 
# file_name is interpreted relative to the current directory if given as a relative path name rather than a full path name.

--defaults-file=file_name
# Use only the given option file. If the file does not exist or is otherwise inaccessible, an error occurs. 
# file_name is interpreted relative to the current directory if given as a relative path name rather than a full path name.
# Exception: Even with --defaults-file, client programs read .mylogin.cnf.

--defaults-group-suffix=str
# Read not only the usual option groups, but also groups with the usual names and a suffix of str. 
# For example, mysqldump normally reads the [client] and [mysqldump] groups. 
# If the --defaults-group-suffix=_other option is given, my--add-drop-database
# Write a DROP DATABASE statement before each CREATE DATABASE statement. 
# This option is typically used in conjunction with the --all-databases or --databases option because no CREATE DATABASE statements are written unless one of those options is specified.

--add-drop-table
# Write a DROP TABLE statement before each CREATE TABLE statement.

--add-drop-trigger
# Write a DROP TRIGGER statement before each CREATE TRIGGER statement.

--all-tablespaces, -Y
# Adds to a table dump all SQL statements needed to create any tablespaces used by an NDB table. 
# This information is not otherwise included in the output from mysqldump. 
# This option is currently relevant only to NDB Cluster tables, which are not supported in MySQL 5.7.
# 全部表空间

--no-create-db, -n
# Suppress the CREATE DATABASE statements that are otherwise included in the output if the --databases or --all-databases option is given.
# 只导出数据,而不添加create database语句。

--no-create-info, -t
# Do not write CREATE TABLE statements that create each dumped table.
# 只导出数据,而不添加create table语句。

# Note
# This option does not exclude statements creating log file groups or tablespaces from mysqldump output; however, you can use the --no-tablespaces option for this purpose.

--no-tablespaces, -y
# This option suppresses all CREATE LOGFILE GROUP and CREATE TABLESPACE statements in the output of mysqldump.
# 不导出任何表空间信息。

--replace
# Write REPLACE statements rather than INSERT statements.sqldump also reads the [client_other] and [mysqldump_other] groups.
# 使用REPLACE into取代 insert into语句。

--no-defaults
# Do not read any option files. If program startup fails due to reading unknown options from an option file, --no-defaults can be used to prevent them from being read.
# The exception is that the .mylogin.cnf file, if it exists, is read in all cases. 
# This permits passwords to be specified in a safer way than on the command line even when --no-defaults is used. 
# (.mylogin.cnf is created by the mysql_config_editor utility. See Section 4.6.6, “mysql_config_editor — MySQL Configuration Utility”.)

--print-defaults
# Print the program name and all options that it gets from option files.

Debug选项

--allow-keywords
# Permit creation of column names that are keywords. This works by prefixing each column name with the table name.
# 允许创建时关键词的列名字。

--comments, -i
# Write additional information in the dump file such as program version, server version, and host. This option is enabled by default. To suppress this additional information, use --skip-comments.
# 添加注释信息。

--debug[=debug_options], -# [debug_options]
# Write a debugging log. A typical debug_options string is d:t:o,file_name. The default value is d:t:o,/tmp/mysqldump.trace.

--debug-check
# Print some debugging information when the program exits.

--debug-info
# Print debugging information and memory and CPU usage statistics when the program exits.
# 输出调试信息和memory、CPU使用信息。

--dump-date
# If the --comments option is given, mysqldump produces a comment at the end of the dump of the following form:

-- Dump completed on DATE
# However, the date causes dump files taken at different times to appear to be different, even if the data are otherwise identical. 
# --dump-date and --skip-dump-date control whether the date is added to the comment. The default is --dump-date (include the date in the comment). 
# --skip-dump-date suppresses date printing.

--force, -f
# Ignore all errors; continue even if an SQL error occurs during a table dump.
# 在导出的过程中,忽略出现的错误。

# One use for this option is to cause mysqldump to continue executing even when it encounters a view that has become invalid 
# because the definition refers to a table that has been dropped. Without --force, mysqldump exits with an error message. 
# With --force, mysqldump prints the error message, but it also writes an SQL comment containing the view definition to the dump output and continues executing.

# If the --ignore-error option is also given to ignore specific errors, --force takes precedence.

--log-error=file_name
# Log warnings and errors by appending them to the named file. The default is to do no logging.
# 将警告和错误添加到指定文件。

--skip-comments
# See the description for the --comments option.

--verbose, -v
# Verbose mode. Print more information about what the program does.
# 输出更多程序信息。

国际化选项

--character-sets-dir=dir_name
# The directory where character sets are installed. See Section 10.14, “Character Set Configuration”.

--default-character-set=charset_name
# Use charset_name as the default character set. See Section 10.14, “Character Set Configuration”. If no character set is specified, mysqldump uses utf8.

--no-set-names, -N
# Turns off the --set-charset setting, the same as specifying --skip-set-charset.

--set-charset
# Write SET NAMES default_character_set to the output. This option is enabled by default. To suppress the SET NAMES statement, use --skip-set-charset.

复制选项

--apply-slave-statements
# For a slave dump produced with the --dump-slave option, add a STOP SLAVE statement before the CHANGE MASTER TO statement and a START SLAVE statement at the end of the output.

--delete-master-logs
# On a master replication server, delete the binary logs by sending a PURGE BINARY LOGS statement to the server after performing the dump operation. 
# This option automatically enables --master-data.

--dump-slave[=value]
# This option is similar to --master-data except that it is used to dump a replication slave server to produce a dump file that can be used to set up another server 
# as a slave that has the same master as the dumped server. 
# 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 slave's master. 
# The CHANGE MASTER TO statement reads the values of Relay_Master_Log_File and Exec_Master_Log_Pos from the SHOW SLAVE STATUS output and uses them for 
# MASTER_LOG_FILE and MASTER_LOG_POS respectively. These are the master server coordinates from which the slave should start replicating.
# Note
# Inconsistencies in the sequence of transactions from the relay log which have been executed can cause the wrong position to be used. 
# See Section 16.4.1.33, “Replication and Transaction Inconsistencies” for more information.

--dump-slave 
# causes the coordinates from the master to be used rather than those of the dumped server, as is done by the --master-data option. 
# In addition, specfiying this option causes the --master-data option to be overridden, if used, and effectively ignored.

# Warning
# This option should not be used if the server where the dump is going to be applied uses gtid_mode=ON and MASTER_AUTOPOSITION=1.

# The option value is handled the same way as for --master-data (setting no value or 1 causes a CHANGE MASTER TO statement to be written to the dump, setting 2 causes the statement to be written but encased in SQL comments) and has the same effect as --master-data in terms of enabling or disabling other options and in how locking is handled.

# This option causes mysqldump to stop the slave SQL thread before the dump and restart it again after.

# In conjunction with --dump-slave, the --apply-slave-statements and --include-master-host-port options can also be used.

--include-master-host-port
# For the CHANGE MASTER TO statement in a slave dump produced with the --dump-slave option, add MASTER_HOST and MASTER_PORT options for the host name 
# and TCP/IP port number of the slave's master.

--master-data[=value]
# 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.

# 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.

# This option 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.

# It is also possible to set up a slave by dumping an existing slave of the master, using the --dump-slave option, which overrides --master-data and causes it to be ignored if both options are used.

--set-gtid-purged=value
# This option enables control over global transaction ID (GTID) information written to the dump file, 
# by indicating whether to add a SET @@global.gtid_purged statement to the output. 
# This option may also cause a statement to be written to the output that disables binary logging while the dump file is being reloaded.

The following table shows the permitted option values. The default value is AUTO.

Value   Meaning
OFF Add no SET statement to the output.
ON  Add a SET statement to the output. An error occurs if GTIDs are not enabled on the server.
AUTO    Add a SET statement to the output if GTIDs are enabled on the server.
A partial dump from a server that is using GTID-based replication requires the --set-gtid-purged={ON|OFF} option to be specified. 
Use ON if the intention is to deploy a new replication slave using only some of the data from the dumped server. 
Use OFF if the intention is to repair a table by copying it within a topology. 
Use OFF if the intention is to copy a table between replication topologies that are disjoint and will remain so.

The --set-gtid-purged option has the following effect on binary logging when the dump file is reloaded:

--set-gtid-purged=OFF: SET @@SESSION.SQL_LOG_BIN=0; is not added to the output.

--set-gtid-purged=ON: SET @@SESSION.SQL_LOG_BIN=0; is added to the output.

--set-gtid-purged=AUTO: SET @@SESSION.SQL_LOG_BIN=0; is added to the output if GTIDs are enabled on the server you are backing up (that is, if AUTO evaluates to ON).

格式选项

--compact
# Produce more compact output. This option enables the --skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys, and --skip-set-charset options.

--compatible=name
# Produce output that is more compatible with other database systems or with older MySQL servers. 
# The value of name can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, or no_field_options. 
# To use several values, separate them by commas. These values have the same meaning as the corresponding options for setting the server SQL mode. 
# See Section 5.1.10, “Server SQL Modes”.

# This option does not guarantee compatibility with other servers. It only enables those SQL mode values that are currently available for making dump output more compatible. 
For example, --compatible=oracle does not map data types to Oracle types or use Oracle comment syntax.

--complete-insert, -c
# Use complete INSERT statements that include column names.

--create-options
# Include all MySQL-specific table options in the CREATE TABLE statements.

--fields-terminated-by=..., --fields-enclosed-by=..., --fields-optionally-enclosed-by=..., --fields-escaped-by=...
# These options are used with the --tab option and have the same meaning as the corresponding FIELDS clauses for LOAD DATA INFILE. 
# See Section 13.2.6, “LOAD DATA INFILE Syntax”.

--hex-blob
# Dump binary columns using hexadecimal notation (for example, 'abc' becomes 0x616263). The affected data types are BINARY, VARBINARY, the BLOB types, and BIT.

--lines-terminated-by=...
# This option is used with the --tab option and has the same meaning as the corresponding LINES clause for LOAD DATA INFILE. 
# See Section 13.2.6, “LOAD DATA INFILE Syntax”.

--quote-names, -Q
# Quote identifiers (such as database, table, and column names) within ` characters. 
# If the ANSI_QUOTES SQL mode is enabled, identifiers are quoted within " characters. 
# This option is enabled by default. It can be disabled with --skip-quote-names, 
# but this option should be given after any option such as --compatible that may enable --quote-names.

--result-file=file_name, -r file_name
# Direct output to the named file. The result file is created and its previous contents overwritten, even if an error occurs while generating the dump.
# This option should be used on Windows to prevent newline \n characters from being converted to \r\n carriage return/newline sequences.

--tab=dir_name, -T dir_name
# Produce tab-separated text-format data files. For each dumped table, mysqldump creates a tbl_name.
# sql file that contains the CREATE TABLE statement that creates the table, and the server writes a tbl_name.
# txt file that contains its data. The option value is the directory in which to write the files.

# Note
# This option should be used only when mysqldump is run on the same machine as the mysqld server. 
# Because the server creates *.txt files in the directory that you specify, 
# the directory must be writable by the server and the MySQL account that you use must have the FILE privilege. Because mysqldump creates *.sql in the same directory, 
# it must be writable by your system login account.

# By default, the .txt data files are formatted using tab characters between column values and a newline at the end of each line. 
# The format can be specified explicitly using the --fields-xxx and --lines-terminated-by options.
# Column values are converted to the character set specified by the --default-character-set option.

--tz-utc
# This option enables TIMESTAMP columns to be dumped and reloaded between servers in different time zones. mysqldump sets its connection time zone to 
# UTC and adds SET TIME_ZONE='+00:00' to the dump file. Without this option, TIMESTAMP columns are dumped and reloaded in the time zones 
# local to the source and destination servers, which can cause the values to change if the servers are in different time zones. 
# --tz-utc also protects against changes due to daylight saving time. --tz-utc is enabled by default. To disable it, use --skip-tz-utc.

--xml, -X
# Write dump output as well-formed XML.

过滤选项

--all-databases, -A
# Dump all tables in all databases. This is the same as using the --databases option and naming all the databases on the command line.

--databases, -B
# Dump several databases. Normally, mysqldump treats the first name argument on the command line as a database name and following names as table names. 
# With this option, it treats all name arguments as database names. CREATE DATABASE and USE statements are included in the output before each new database.
# This option may be used to dump the INFORMATION_SCHEMA and performace_schema databases, which normally are not dumped even with the --all-databases option. 
# (Also use the --skip-lock-tables option.)

--events, -E
# Include Event Scheduler events for the dumped databases in the output. This option requires the EVENT privileges for those databases.
# The output generated by using --events contains CREATE EVENT statements to create the events. However, 
# these statements do not include attributes such as the event creation and modification timestamps, so when the events are reloaded, 
# they are created with timestamps equal to the reload time.

# If you require events to be created with their original timestamp attributes, do not use --events. 
# Instead, dump and reload the contents of the mysql.event table directly, using a MySQL account that has appropriate privileges for the mysql database.

--ignore-error=error[,error]...
# Ignore the specified errors. The option value is a comma-separated list of error numbers specifying the errors to ignore during mysqldump execution. 
# If the --force option is also given to ignore all errors, --force takes precedence.

--ignore-table=db_name.tbl_name
# Do not dump the given table, which must be specified using both the database and table names. 
# To ignore multiple tables, use this option multiple times. This option also can be used to ignore views.

--no-data, -d
# Do not write any table row information (that is, do not dump table contents). 
# This is useful if you want to dump only the CREATE TABLE statement for the table (for example, to create an empty copy of the table by loading the dump file).

--routines, -R
# Include stored routines (procedures and functions) for the dumped databases in the output. This option requires the SELECT privilege for the mysql.proc table.
# The output generated by using --routines contains CREATE PROCEDURE and CREATE FUNCTION statements to create the routines. However, 
# these statements do not include attributes such as the routine creation and modification timestamps, 
# so when the routines are reloaded, they are created with timestamps equal to the reload time.

# If you require routines to be created with their original timestamp attributes, do not use --routines. 
# Instead, dump and reload the contents of the mysql.proc table directly, using a MySQL account that has appropriate privileges for the mysql database.

--tables
# Override the --databases or -B option. mysqldump regards all name arguments following the option as table names.

--triggers
# Include triggers for each dumped table in the output. This option is enabled by default; disable it with --skip-triggers.

# To be able to dump a table's triggers, you must have the TRIGGER privilege for the table.

# Multiple triggers are permitted. mysqldump dumps triggers in activation order so that when the dump file is reloaded, 
# triggers are created in the same activation order. However, if a mysqldump dump file contains multiple triggers for a table that have the same trigger event and action time, 
# an error occurs for attempts to load the dump file into an older server that does not support multiple triggers. 
# (For a workaround, see Section 2.11.2.1, “Changes Affecting Downgrades from MySQL 5.7”; you can convert triggers to be compatible with older servers.)

--where='where_condition', -w 'where_condition'
# Dump only rows selected by the given WHERE condition. Quotes around the condition are mandatory if it contains spaces or 
# other characters that are special to your command interpreter.

Examples:
--where="user='jimf'"
-w"userid>1"
-w"userid<1"

性能选项

--disable-keys, -K
# For each table, surround the INSERT statements with /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; and /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; statements. 
# This makes loading the dump file faster because the indexes are created after all rows are inserted. This option is effective only for nonunique indexes of MyISAM tables.

--extended-insert, -e
# Write INSERT statements using multiple-row syntax that includes several VALUES lists. This results in a smaller dump file and speeds up inserts when the file is reloaded.

--insert-ignore
# Write INSERT IGNORE statements rather than INSERT statements.

--opt
# This option, enabled by default, is shorthand for the combination of --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It gives a fast dump operation and produces a dump file that can be reloaded into a MySQL server quickly.
# 等同于同时指定了 –add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, 以及 –disable-keys这些参数。默认就是启用状态。使用–skip-opt来禁用该参数。
# Because the --opt option is enabled by default, you only specify its converse, the --skip-opt to turn off several default settings. See the discussion of mysqldump option groups for information about selectively enabling or disabling a subset of the options affected by --opt.

--skip-opt
# See the description for the --opt option.
# 关闭--opt选项。

--quick, -q
# This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than 
# retrieving the entire row set and buffering it in memory before writing it out.

事务选项

--add-locks
# Surround each table dump with LOCK TABLES and UNLOCK TABLES statements. This results in faster inserts when the dump file is reloaded. 
# See Section 8.2.4.1, “Optimizing INSERT Statements”.

--flush-logs, -F
# Flush the MySQL server log files before starting the dump. This option requires the RELOAD privilege. 
# If you use this option in combination with the --all-databases option, the logs are flushed for each database dumped. 
# The exception is when using --lock-all-tables, --master-data, or --single-transaction: In this case, the logs are flushed only once, 
# corresponding to the moment that all tables are locked by FLUSH TABLES WITH READ LOCK. If you want your dump and the log flush to happen at exactly the same moment, 
# you should use --flush-logs together with --lock-all-tables, --master-data, or --single-transaction.

--flush-privileges
# Add a FLUSH PRIVILEGES statement to the dump output after dumping the mysql database. 
# This option should be used any time the dump contains the mysql database and any other database that depends on the data in the mysql database for proper restoration.

# Note
# For upgrades to MySQL 5.7 or higher from older versions, do not use --flush-privileges. 
# For upgrade instructions in this case, see Section 2.11.1.2, “Changes Affecting Upgrades to MySQL 5.7”.

--lock-all-tables, -x
# 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.

--lock-tables, -l
# 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.
# Because --lock-tables locks tables for each database separately, this option does not guarantee that the tables in the dump file are logically consistent between databases. 
# Tables in different databases may be dumped in completely different states.

# Some options, such as --opt, automatically enable --lock-tables. If you want to override this, use --skip-lock-tables at the end of the option list.

--no-autocommit
# Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements.

--order-by-primary
# Dump each table's rows sorted by its primary key, or by its first unique index, if such an index exists. 
# This is useful when dumping a MyISAM table to be loaded into an InnoDB table, but makes the dump operation take considerably longer.

--shared-memory-base-name=name
# On Windows, the shared-memory name to use, for connections made using shared memory to a local server. The default value is MYSQL. 
# The shared-memory name is case-sensitive.

# The server must be started with the --shared-memory option to enable shared-memory connections.

--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.

4.5.4 mysqldump — A Database Backup Program
Mysqldump参数大全 

mysqldump默认的参数有以下几个:
–opt
–add-drop-table
–add-locks
-i,–comments
-a,–create-options
-e, –extended-insert
-l, –lock-tables
-q, –quick
-K, –disable-keys
-Q, –quote-names
–dump-date
–ssl
–triggers
–tz-utc

示例

# 获取一个完整备份,不锁库备份。
$ mysqldump -uroot -p --triggers --routines --events -A -B --single-transaction --master-data=2 > backup.$(date +%F).sql

# 导出指定库
$ mysqldump -uroot -p -B dbname > backup.$(date +%F).sql

# 导出多个库
$ mysqldump -uroot -p -B DB1 DB2 DB3 > backup.$(date +%F).sql

# 导出指定表的结构和数据
$ mysqldump -uroot -p DBNAME table1 table2 > tablename.sql  

# 导出指定表的结构,不包含数据
$ mysqldump -uroot -p --no-data DBNAME table1 table2 > backup.$(date +%F).sql

# 导出指定表的数据
$ mysqldump -uroot -p --no-create-info DBNAME table1 table2 > backup.$(date +%F).sql

# 导出整个数据库结构(包括表结构)
$ mysqldump -uroot -p --no-data DBNAME > backup.$(date +%F).sql

# 导出数据直接压缩
$ mysqldump -uusername -p -B DBNAME | gzip > backup.sql.gz

mysqldump 参数和使用方法介绍 

使用mysqldump搭建从库

-- 停止从库同步(从库执行)
>stop slave;

-- 重置配置信息(从库执行)
>reset slave all;

备份主库数据库

# 导出主库数据库(主库执行)
# mysqldump --user=root --password="" --routines --all-databases --opt > /opt/backups/alldb.sql;

# 将文件拷贝到从库服务器上(主库执行)
# scp alldb.sql jipeng03@gh-mysql-jp-test02:/tmp

导入主库数据

--(从库执行)
>source /tmp/alldb.sql

-- 在主库中查看同步的文件盒位置(主库执行)
>show master status;
+------------------+-----------+--------------+------------------+--------------------------------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
+------------------+-----------+--------------+------------------+--------------------------------------------+
| mysql-bin.000002 | 541774550 |              |                  | b0d8cb67-8a3a-11e8-a54a-002230a30c9f:1-259 |
+------------------+-----------+--------------+------------------+--------------------------------------------+
1 row in set (0.00 sec)

-- 配置主从复制(从库执行)
>CHANGE MASTER TO MASTER_HOST='10.27.143.195', 
MASTER_USER='replication',
MASTER_PASSWORD='123456',
MASTER_PORT=5002,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=541774550;

-- 启动复制(从库执行)
>start slave;

>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.27.143.195
                  Master_User: replication
                  Master_Port: 5002
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 541774550
               Relay_Log_File: relay.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             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: 541774550
              Relay_Log_Space: 517
              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: 27143195
                  Master_UUID: b0d8cb67-8a3a-11e8-a54a-002230a30c9f
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: b0d8cb67-8a3a-11e8-a54a-002230a30c9f:256
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

至此,在主库执行的操作都会同步到从库。

xtrabackup

使用xtrabackup备份时,不打断其他的事务。在备份innodb表时,不会锁库锁表,能够快速、可靠地完成备份。

参数

--host=HOST 
# 执行通过TCP/IP连接访问数据库的主机,它传给mysql的子进程
--user=USER 
# 指定连接到mysql时使用的用户名
--password=PASSWORD 
# 指定连接到数据库的账户密码 
--port=PORT 
# 该选项指定通过TCP/IP连接到数据库时所用的端口
--apply-log 
# 通过应用同一目录下的事务日志文件xtrabackup_logfile,在BACKUP-DIR目录准备一个备份。页建立一个新的事务日志文件。innoDB的配置是从innobackupex备份时建立的文件backup-my.cnf读取。
--close-files
# 不保持文件被打开。默认备份时tablespace不关闭,但如果表空间很大并且不适合任何限制,有一个可选的方法是关闭不再访问的文件。使用该选项会产生不一致的备份。
--compact
# 建立一个忽略耳机索引页的简洁备份。
--compress
# 建立一个innoDB数据文件的压缩备份。它直接提交给xtrabackup的子进程
--compres-threads=#
# 并行压缩的工作进场数量,它直接提交给xtrabackup的子进程
--compress-chunk-size=#
# 指定每个压缩进程的内部工作缓冲区的尺寸,用字节来测量。它直接提交给xtrabackup的子进程
--copy-back
# 复制所有的备份到他们原来的位置
--databases=LIST 
# 指定将要备份的数据库列表。支持databasename.tablename格式,如果没指定参数,则备份所有数据库
--decompress
# 解压所有以选项--compress备份的,结尾是.qp的文件。使用参数--parallel允许多个文件同时被解密和或解压。
--decrypt=ENCRPYTION-ALGORITHM
# 解密用--encrpyt选项加密的以.xbcrypt结尾的文件。
--defaults-file=[my.cnf]
# 指定my.cnf文件
--defaults-extra-file=[my.cnf]
# 在从标准的默认文件中取值默认之前的额外文件。接收一个字符串作为选项
--defaults-group=GROUP-NAME
# 如果用了Mysqld_multi,可设置读取配置文件的特定组
--encrypt=ENCRYPTION-ALGORITHM
# 该选项指引xtrabackup使用参数ENCRYPTION_ALGORITHM参数制定的算法,加密innoDB数据文件的备份,它直接指向子进程
--encrypt-key=ENCRYPTION_KEY
# 指示xtrabackup在备份时使用ENCRYPTION_KEY指定的key做--encrypt加密。它直接传给子进程
--encrypt-key-file=ENCRYPTION_KEY_FILE
# 当用选项--encrpyt加密时使用存储在ENCRYPTION_KEY_FILE里存储的加密key
--encrypt-threads=#
# 指定并行加密的工作线程数。它直接传给子进程
--encrypt-chunk-size=#
# 指定每个加密进程使用的内粗工作缓冲区的尺寸,以字节计算大小
--export
# 它用于导出单个表用于导入另一个server
--extra-lsndir=DIRECTORY
# 指定xtrabackup_checkpoints文件的保留目录
--force-non-empty-directories
# 该参数使得选项--copy-back or --move-back选项传输文件到非空目录。不存在的文件将被覆盖。如果选项--copy-back or --move-back必须从备份目录到一个已经存在的目标目录,则将失败
--galera-info
# 该选项在备份时建立包含本地节点状态xtrabackup_galera_info文件。用于执行Percona-XtraDB-Cluster备份
--ibbackup=IBBACKUP-BINARY
# 接收字符串参数,它用来指定要使用的xtrabackup binary、
--include=REGEXP
# 指定一个正则表达式,用语匹配格式为databasename.tablename的表名称,它传递给--tables选项
--incremental
# 建立一个增量备份,传递给xtrabackup的子进程。该参数可以和参数--incremental-lsn or --incremental-basedir配合使用。
--incremental-basedir=DIRECTORY
# 指定一个包换全库备份的目录作为增量备份的基础数据库
--incremental-dir=DIRECTORY
# 指定增量备份与全库备份合并去建立一个新的全备份的目录。
--incremental-lsn=LSN
# 指定增量备份将要开始的LSN,它替代选项--incremental-basedir
--kill-long-queries-timeout=SECONDS
# 该选项指定innobackupex在开始FLUSH TABLES WITH READ LOCK和杀掉这些阻碍他的查询之间的时间的等待时间,以秒计算,默认为0,意味着innobackupex不尝试杀任何查询,该选项需要process and super权限
--kill-long-query-type=all|select
# 指定解锁全局锁时将被杀掉的查询类型,默认是all
--lock-wait-timeout=SECONDS
# 运行FLUSH TABLES WITH READ LOCK之前,innobackupex等待阻塞查询的时间数(秒数)
--lock-wait-threashold=SECONDS
# 选项指定查询运行时间阀值,当innobackupex发现长运行查询伴随着--lock-wait-timeout的一个非0值,
--lock-wait-query-type=all|update
# 指定innobackupex发出一个全局锁之前什么类型的查询允许完成
--lock-copy-interval=#
# 指定日志日志复制线程检车完成的时间间隔,以毫秒计算
--move-back
# 移动之前的所有备份从一个备份目录到他们的原始位置
--no-lock
# 不允许使用flush tables with read lock表锁。如果你的所有表示INNODB并且你不关心二进制日志备份的位置。如果有任何DDL语句被执行或任何非INNODB表上的update操作,这个选项就不能使用
--notimestamp
# 把备份放在一个通过选项backup-root-dir指定的子目录里
--no-version-check
# 禁止版本检查
--parallel=NUMBER-OF-THREADS
# 该选项接收一个整数,xtarbackup子进程将用于同时备份文件的并发数。如果有多个.ibd文件可以并行,如果只有一个表空间文件,则该选项无效
--rebuild-indexes
# 只有用--apply-log选项时它才有效,当应用日志后使得xtrabackup重建所有的二级索引。一般用于准备简约备份
--rebuild-threas=NUMBER-OF-THREADS
# 当一起使用选项--apply-log and --rebuild-indexes选项时才有用,使用后,当重建索引时,xtrabackup处理表空间时用一定数量的线程的并行模式
--redo-only
# 选项用于准备全库备份和合并处最有一个备份外的所有增量备份。它强制xtrabackup忽略“rollback”阶段只做“redo”.
--rsync
# 使用rsync工具优化本地文件传输。它让xtrabackup使用rsync复制所有非innoDB文件,而不是使用多个cp
--safe-slave-backup
# 停止从SQL进程并等待启动备份直到slave_open_temp_tables的值为0。如果没有打开临时表,备份会进行,否则SQL进程将启动并直到没有打开的临时表时停止。如果slave_open_temp_tables在--safe-slave-backup-timeout秒后没有变成0,则备份会失败。备份结束后,从SQL进程将重新启动
--safe-slave-backup-timeout=SECONDS
  --safe-slave-backup要等slave_open_temp_tables变成0的时间,默认为300秒
--scopt=SCP-OPTIONS
# 当参数--remost-host指定时传递给scp的参数
--slave-info
# 当备份一个复制从库操作的时候用,它打印二进制日志的position和主库的名字,它页把这些信息写入xtrabackup_slave_info文件作为一个CHANGE MASTER命令
--socket=SOCKET
# 指定连接到本地数据库sever时使用的一个unix domain socket,它没有修改的传入mysql子进程
--sshopt=SSH-OPTIONS
# 当使用参数--remost-host时,使用ssh的命令行参数
--stream=STREMNAME
# 当使用流备份时使用的特定格式。备份将以特定格式传到STDOUT。支持的格式为tar and xbstream
--tables-file=FILE
# 指定备份的表的列表,格式为database.tablename
--throttle=IOS
# 指定I/O操作的数量/秒。该参数只适用于备份阶段。不适用于参数--apply-log,--copy-back
--tmpdir=DIRECTORY
# 在参数--stream使用时指定,是指临时文件被存储的位置
--use-memory=#
# 该参数只能和参数--apply-log配合使用,被用于xtrabackup做creash恢复时准备锁使用的内存量(单位:字节)。也支持其他单位,如:1MB,1M,1GB,1G
--version-check
# 指定该选项后,innobackupex将在建立一个连接后,在备份阶段执行一个版本检查
--version 
# 显示innobackupex的版本信息和版权等信息

全量备份

增量备份

使用xtrabackup搭建从库

1. 备份主库中的数据库

# 全库备份,--include=employees可以只指定备份一个库
# innobackupex --defaults-file=/etc/my.cnf --user=root --password="" --no-timestamp /opt/backups/datafull

# 重放redo log,撤销未提交的事务,检查备份的数据一致性
# innobackupex --defaults-file=/etc/my.cnf --user=root --password="" --apply-log /opt/backups/datafull

这里查看/opt/backups/datafull目录,会发现xtrabackup会把MySQL的datadir目录下的所有文件都复制到该目录,同时还多了一些以xtrabackup开头的文件。主要关注xtrabackup_binlog_info这个文件,因为这个文件记录了做从库需要change到的主库的binlog的位置和pos点。

2. 恢复数据到从库

# 停止从库
# /etc/init.d/mysqld stop

# 把备份的文件拷贝到从服务器上,这里考虑到权限的问题,首先拷贝到/tmp目录下,然后再移动至从库的数据目录下
# scp -r /opt/backups/datafull/ jipeng03@gh-mysql-jp-test02:/tmp
# 为保险起见,先备份原数据库文件
# mv /opt/local/mysql/var /opt/local/mysql/var.bak
# 移动主库的备份文件到从库的数据目录下
# mv /tmp/datafull/ /opt/local/mysql/var

# 修改文件权限
# chown -R mysql:mysql /opt/local/mysql/var

# 启动数据库
# sudo /etc/init.d/mysql start

# 登录从库,并查看数据库
# mysql -u root -p
>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
| sys                |
| testrep            |
+--------------------+
6 rows in set (0.00 sec)

# 查看同步的位置点
# cat /opt/local/mysql/var/xtrabackup_binlog_info
mysql-bin.000002    541773560    b0d8cb67-8a3a-11e8-a54a-002230a30c9f:1-255

3. 主从复制配置

-- 创建用户(在主库上执行)
>CREATE USER 'replication'@'10.27.143.196' IDENTIFIED BY '123456';
-- 配置主从复制权限(在主库上执行)
>GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.27.143.196';

-- 在从库上配置
>CHANGE MASTER TO MASTER_HOST='10.27.143.195', 
MASTER_USER='replication',
MASTER_PASSWORD='123456',
MASTER_PORT=5002,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=541773560;

-- 开启主从同步
>start slave;

-- 查看状态 
>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.27.143.195
                  Master_User: replication
                  Master_Port: 5002
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 541773560
               Relay_Log_File: relay.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             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: 541773560
              Relay_Log_Space: 517
              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: 27143195
                  Master_UUID: b0d8cb67-8a3a-11e8-a54a-002230a30c9f
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

至此,配置完成,主库上执行的操作都会同步到从库。

mysqldump — A Database Backup Program