Shulou(Shulou.com)06/01 Report--

1. Backup of mysql

1 、 mysqldump

Logical backup, support warm backup, hot backup, use mysqldump full backup, binlog incremental backup

Mysqldump-utest-ptest-h292.168.0.204 test > database-date +'% Ymuri% mmi% d'

1) when backing up the database of the innodb engine

Mysqldump-- single-transaction-- master-data=2-- flush-log-u-p > mysql.bak

-- single-transaction starts a large transaction to dump

-- master-data=# records change-master-to

When = 2 is recorded as a comment, 1 is not commented

CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=614

-- flush-logs re-creates new binary logging

2) restore backup

A new binary log will be generated when the data is recovered. If you do not need to record it, you need to set it up.

Set session sql_log_bin=0

Then import the fully backed up data source

And then set session sql_log_bin=1

Mysqlbinlog-- start-position=#-- stop-position=# binlog.00000# | mysql-u-p

II. Xtarbackup

1. Backup of xtarbackup

Version innobackupex version 2.4.8 Linux (x86x64) (revision id: 97330f7)

Parameter description

-- compress: this option represents a backup of compressed innodb data files.

-- compress-threads: this option represents the number of parallel compressed worker threads.

-- compress-chunk-size: this option represents the size of each compressed thread worker buffer, in bytes. The default is 64K.

-- encrypt: this option means to encrypt the backup of innodb data files through ENCRYPTION_ALGORITHM 's algorithm. Currently, the supported algorithm is ASE128,AES192,AES256.

-- encrypt-threads: this option represents the number of worker threads encrypted in parallel.

-- encrypt-chunk-size: this option represents the size of each encrypted thread worker buffer, in bytes. The default is 64K.

-- encrypt-key: this option uses the appropriate length to encrypt key, which is not recommended because it is logged to the command line.

-- encryption-key-file: this option indicates that the file must be a simple binary or text file, and encrypted key can be generated from the following command-line command: openssl rand-base64 24.

-- include: this option indicates that the name of the table [db.tb] is matched with a regular expression, requiring that it be specified to match the full name of the table to be backed up, that is, databasename.tablename.

-- user: this option indicates a backup account.

-- password: this option indicates the backup password.

-- port: this option indicates the port on which the database is backed up.

-- host: this option indicates the address of the backup database.

-- databases: the parameter accepted by this option is the data name. If you want to specify multiple databases, they need to be separated by spaces, such as "xtra_test dba_test". At the same time, when you specify a database, you can specify only one of the tables. Such as "mydatabase.mytable". This option is not valid for innodb engine tables, or will back up all innodb tables. In addition, this option can also accept a file as a parameter, with each behavior in the file having an object to back up.

-- tables-file: this option specifies the file containing the list of tables in the format database.table, which is passed directly to-- tables-file.

-- socket: this option indicates the location of the mysql.sock so that the backup process logs in to mysql.

-- no-timestamp: this option can indicate that instead of creating a timestamp directory to store backups, specify to the backup folder you want.

-- ibbackup: this option specifies which xtrabackup binary to use. IBBACKUP-BINARY is the command to run percona xtrabackup. This option applies to xtrbackup binaries that are not in your search and working directory. If this option is specified, innoabackupex automatically determines which binaries to use.

-- slave-info: this option means to use when backing up slave, printing out the name of master and binlog pos, and also writing this information to the xtrabackup_slave_info file with the command of change master. You can start a slave library based on this backup.

-- safe-slave-backup: this option means that to ensure a consistent replication state, this option stops the SQL thread and starts the backup when the slave_open_temp_tables in show status is 0. If the temporary table is not opened, bakcup will start immediately, otherwise the SQL thread starts or shuts down the temporary table that is not open. If slave_open_temp_tables is not 0 after-- safe-slave-backup-timeount (default 300 seconds), the slave sql thread will restart when the backup is complete.

-- rsync: this option means to optimize local transfer through the rsync tool. When this option is specified, innobackupex uses rsync to copy non-Innodb files instead of cp. It is much faster when there are many DB and tables, and cannot be used together with-- stream.

-- kill-long-queries-timeout: this option represents the number of seconds to wait between the start of FLUSH TABLES WITH READ LOCK execution and the time kill drops the queries that block it. The default value is 0 and no queries are kill. Using this option, xtrabackup requires Process and super permissions.

-- kill-long-query-type: this option indicates the type of kill. The default is all. Optional select.

-- ftwrl-wait-threshold: this option indicates that a long query is detected, in seconds, and represents the threshold of the long query.

-- ftwrl-wait-query-type: this option means that that kind of query is allowed to complete before the global lock is obtained. The default is ALL, and optional update.

-- galera-info: this option means that a file xtrabackup_galera_info file containing the status of the local node at the time the backup was created is generated. This option applies only to backup PXC.

-- stream: this option indicates the format of streaming backup. After backup is completed, it will be in the specified format to STDOUT. Currently, only tar and xbstream are supported.

-- defaults-file: this option specifies which file to read the MySQL configuration from, and must be placed at the location of the first option on the command line.

-- defaults-extra-file: this option specifies which additional file to read the MySQL configuration from before the standard defaults-file, which must be the location of the first option on the command line. A profile that is typically used to store the user name and password of the backup user.

-defaults-group: this option represents the group read from the configuration file, which is used when deploying multiple innobakcupex instances.

-- no-lock: this option means to close the table lock of FTWRL. Only if all tables are Innodb tables and do not care about the binlog pos points of backup, if any DDL statements are being executed or non-InnoDB is being updated (including tables under the mysql library), this option should not be used. The result is that the backup data is inconsistent. If you consider that the backup failed to acquire the lock, you can consider-safe-slave-backup immediately stops the replication thread.

-- tmpdir: this option means that when you specify-- stream, specify where the temporary file is stored, and the transaction log is first stored in the temporary file before streaming and copying to the remote server. In the use of parameter stream=tar backup, your xtrabackup_logfile may be temporarily placed in the / tmp directory, if you backup and write large xtrabackup_logfile may be very large (5G +), it is likely to fill your / tmp directory, you can use the parameter-tmpdir to specify the directory to solve this problem.

-- history: this option indicates that the backup history of percona server is in the percona_schema.xtrabackup_ calendar table.

-- incremental: this option means to create an incremental backup and you need to specify-- incremental-basedir.

-- incremental-basedir: this option means that a string parameter is accepted to specify the directory containing full backup as the base directory for incremental backup, which is used in conjunction with-- incremental.

-- incremental-dir: this option indicates the directory of the incremental backup.

-- incremental-force-scan: this option means that data pages in all incremental backups are forced to be scanned when an incremental backup is created.

-- incremental-lsn: this option indicates the LSN of the specified incremental backup, used with the-- incremental option.

-- incremental-history-name: this option represents the name of the history stored in PERCONA_SCHEMA.xtrabackup_history based on incremental backups. Percona Xtrabackup searches the history table to find the most recent (innodb_to_lsn) successful backup and starts the accident lsn with the to_ LSN value as an incremental backup. Mutually exclusive with innobackupex--incremental-history-uuid. If no valid lsn,xtrabackup is detected, error is returned.

-- incremental-history-uuid: this option represents the UUID stored in percona_schema.xtrabackup_history based on specific history of incremental backups.

-- close-files: this option closes the file handle that is no longer accessed. When xtrabackup opens the table space, it usually does not close the file handle in order to handle the DDL operation correctly. If the number of tablespaces is large, this is a way to close file handles that are no longer accessed. Using this option is risky and there is the possibility of inconsistent backups.

-- compact: this option means to create a compact backup without a secondary index.

-- throttle: this option represents the number of IO operations per second and is valid only for the bakcup phase. Apply-log and-- copy-back don't work together.

1) connect to the server permissions

MariaDB [hellodb] > CREATE USER 'backuser'@'localhost' IDENTIFIED BY' smile'

MariaDB [hellodb] > GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT,PROCESS ON *. * TO 'backuser'@'localhost'

MariaDB [hellodb] > FLUSH PRIVILEGES

Pay special attention to the fact that localhost cannot be used when authorizing.

Description of permissions in the official document:

2) backup

Innobackupex-ubackuser-psmile / backup

Generate a folder with the current timestamp under the / backup folder after backup

Xtrabackup_binlog_info records the binary log files currently in use by the mysql server and the location of binary log events up to the moment of backup

[root@node4 2017-12-04 56MariaDB 15-56-40] # cat xtrabackup_binlog_info binlog.000010 1814 0-1-56MariaDB [hellodb] > SHOW MASTER STATUS +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-+ -+ | binlog.000010 | 1814 | +-+

Xtrabackup_checkpoints records backup type, log serial number (LSN), and backup status

[root@node4 2017-12-04 backup_type 15-56-40] # cat xtrabackup_checkpoints backup_type = full-backuped (backup_type = full-prepared after-- apply-log) from_lsn = 0to_lsn = 1899901last_lsn = 1899910compact = 0recover_binlog_info = 0

Ib_buffer_pool, the hot data in buffer pool, when you set innodb_buffer_pool_dump_at_shutdown=1, when you close MySQL, the hot data in memory will be saved in the ib_buffer_pool file on disk, located in the data directory.

Ibdata1, backup of shared tablespace files

Xtrabackup_info, record the basic backup information, uuid, backup command, backup time, binlog, LSN, and other encryption and compression information.

[root@node4 2017-12-04 / 15-56-40] # cat xtrabackup_info uuid = abd4d399-d8c8-11e7-b084-000c293e4e76name = tool_name = innobackupextool_command =-ubackuser-psmile / backuptool_version = 2.4.8ibbackup_version = 2.4.8server_version = 10.2.10-MariaDB-logstart_time = 2017-12-04 15:56:40end_time = 2017-12-04 15:56:44lock_time = 0binlog_pos = filename 'binlog.000010', position' 1814' GTID of the last change'0-1-56'innodb_from_lsn = 0innodb_to_lsn = 1899901partial = Nincremental = Nformat = filecompact = Ncompressed = Nencrypted = N

Xtrabackup_logfile, the redo log file backed up.

Backup-my.cnf, configuration option information used by backup command

2. Restore

Prepare backup files are required before restore

Innobackupex-- apply-log [--use-memory=B] [--defaults-file=MY.CNF] [--export] [--redo-only] [--ibbackup=IBBACKUP-BINARY] BACKUP-DIR

-- apply-log: this option indicates the-prepare parameter of xtrabackup. In general, data cannot be used for recovery operations after the backup is completed, because the backed up data may contain transactions that have not yet been committed or transactions that have been committed but have not been synchronized to the data file. Therefore, the data file still handles the inconsistent state at this time. The role of apply-log is to keep data files in a consistent state by rolling back uncommitted transactions and synchronizing committed transactions to data files.

-- use-memory: this option indicates the amount of memory (in bytes) allocated by xtrabackup for crash recovery when used with the-- apply-log option for prepare backup. Also available (1MB, 1M, 1G, 1GB), recommended 1G.

-- defaults-file: this option specifies which file to read the MySQL configuration from, and must be placed at the location of the first option on the command line.

-- export: this option means that you can export separate tables and then import them into other Mysql.

-- redo-only: this option is used when merge incremental backups (but not the last one) to prepare base full backup.

Reduction

Innobackupex-- copy-back [--defaults-file=MY.CNF] [--defaults-group=GROUP-NAME] BACKUP-DIRinnobackupex-move-back [--defaults-file=MY.CNF] [--defaults-group=GROUP-NAME] BACKUP-DIR

-- copy-back: copy the backup data files to the datadir of the MySQL server during data recovery.

-- move-back: this option is similar to-- copy-back, except that it does not copy files, but moves them to their destination. This option removes backup files and must be used with care. Usage scenario: there is not enough disk space for colleagues to keep data files and Backup copies

Note:

The 1.datadir directory must be empty. Unless specified by the innobackupex-- force-non-empty-directorires option, the-- copy-backup option does not override

two。 Before restore, you must shutdown MySQL the instance. You cannot restore a running instance to the datadir directory.

3. Since the file properties will be retained, in most cases you need to change the owner of the file to mysql before starting the instance, and these files will belong to the user who created the backup

Chown-R mysql:mysql / data1/dbrestore

The above needs to be done before the user calls Innobackupex

-- force-non-empty-directories: when this parameter is specified, the innobackupex-- copy-back or-- move-back option transfers files to a non-empty directory, and existing files will not be overwritten. If the-- copy-back and-- move-back files need to copy a file that already exists in datadir from the backup directory, an error will fail.

3. Incremental backup

1) complete operation as above

2) add equipment on the basis of complete equipment

[root@node4 2017-12-05 / 10-35-33] # innobackupex-ubackuser-psmile-- incremental--incremental-basedir=/backup/2017-12-05 / 10-35-33 / backup/

3) return the original-- apply-log and-- redo-only complete, and then on the prepared full-ready-- apply-log and-- redo-only add equipment for the last time without-- redo-only.

[root@node4 2017-12-05 / 10-35-33] # innobackupex-- apply-log-- redo-only / backup/2017-12-05 / 10-35-33 innobackupex-- apply-log-- incremental-dir=/backup/2017-12-05 / 10-47-40 / / backup/2017-12-05 / 10-35-33

Backup error:

Innobackupex version 2.4.8 based on MySQL server 5.7.13 Linux (x86 / 64) (revision id: 97330f7)

Xtrabackup: uses posix_fadvise ().

Xtrabackup: cd to / data/mydata

Xtrabackup: open files limit requested 0, set to 1024

Xtrabackup: using the following InnoDB configuration:

Xtrabackup: innodb_data_home_dir =.

Xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend

Xtrabackup: innodb_log_group_home_dir =. /

Xtrabackup: innodb_log_files_in_group = 2

Xtrabackup: innodb_log_file_size = 50331648

InnoDB: Number of pools: 1

InnoDB: Operating system error number 2 in a file operation.

InnoDB: The error means the system cannot find the path specified.

InnoDB: File. / ib_logfile0: 'open' returned OS error 71. Cannot continue operation

InnoDB: Cannot continue operation.

To report this error, you need to specify-- defaults-file and specify datadir and basedir in the configuration file and restart mysql

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.