Shulou(Shulou.com)05/31 Report--

This article shows you how to backup in MySQL 8.0.11 innodb cluster operation and maintenance management, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

MySQL 8.0.11 innodb cluster Operation and maintenance Management-introduction to msyqlbackup backup Mysqlbackup

Mysqlbackup is a hot backup tool, that is, it does not have a global lock on the table like mysqldump. Because mysqldump has this lock, the client can only read from the database and cannot write, which is why mysqldump is called a warm backup. But is mysqlbackup really hanging like that? The answer is no. Mysqlbackup hot standby for innodb engine tables, but mysqlbackup for non-innodb tables can only be warm, because this kind of engine does not support transactions, that is, backup consistency cannot be guaranteed through transaction logs, so you can only add a global lock to the table. In order to get a consistent backup, mysqlbackup keeps tracking the sln number of the mysql database, that is, mysqlbackup must connect to the database to perform a backup. Mysqlbackup backs up the database by copying files, that is, mysqlbackup is on the same machine as the database, similar to xtrabackup.

Install mysqlbackup

Mysqlbackup is in mysql Enterprise Edition, but you can use it. Log in to oracle Cloud to download, search mysql backup, and download binaries.

Mysql-commercial-backup-8.0.11-linux-glibc2.12-x86_64.tar.gz

Send it to the host and decompress it.

There will be bin and lib folders. Copy the files to the corresponding directory of / usr/local/mysql. Here, you need to copy all the soft links and libraries, otherwise an error will be reported.

Backup user

(official guidance order)

CREATE USER 'backup'@'localhost' IDENTIFIED BY' $'; GRANT RELOAD ON *. * TO 'backup'@'localhost';GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO' backup'@'localhost';GRANT CREATE, INSERT, SELECT, DROP, UPDATE ON mysql.backup_history TO 'backup'@'localhost';GRANT REPLICATION CLIENT ON *. * TO' backup'@'localhost';GRANT SUPER ON *. * TO 'backup'@'localhost';GRANT PROCESS ON *. * TO' backup'@'localhost' GRANT ALTER ON mysql.backup_history TO 'backup'@'localhost'; # this table will only be generated and written to GRANT LOCK TABLES, SELECT, CREATE, DROP, FILE ON *. * TO' backup'@'localhost';GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_sbt_history TO 'backup'@'localhost' when backed up on the main library. # this table is only available for full backup on the main database # # backup to a directory like / fullbak/2018-10-10cm 19-19-11/datadir mysqlbackup-- backup-dir=/data/backup/fullbak-- host=127.0.0.1-- port=3306-- user=backup-- password=$-- with-timestamp backup# to a single file such as / fullbak/2018-10-10cm 19-19-11/full_backup.bki Instead of backing up single files in the future, we will compress and backup smaller mysqlbackup-- backup-dir=/data/backup/fullbak-- host=127.0.0.1-- port=3306-- user=backup-- password=$-- backup-image=full_backup.bki-- with-timestamp backup-to-image.

-- user: user name.

-- password: password.

-- port: Port. Default is 3306.

-- backup-dir: can be seen as the working directory of mysqlback, for temporary use.

-- backup-image: the name of the backup file. This is the final file, but you don't want anything else. Location can be placed without a path, and it can be placed on backup-dir by default.

Backup-to-image: output all backup information to a backup file

-- with-timepstap: a folder with a date is automatically generated, where the temporary files used for backup and the last backup image are placed (the transformation of the image location is not specified)

Incremental backup

It turns out that a single file backup cannot be restored. The following example is abandoned.

Mysqlbackup-backup-dir=/data/backup-host=127.0.0.1-port=3306-user=backup-password=Qs3ce3saadr37tpP-incremental-with-redo-log-only-incremental-base=history:last_backup with-timestamp backup-image=incre_backup.bki backup-to-image

Do not back up as a single file, incremental backup does not seem to support, this is recoverable to specify the incremental directory to which the backup is backed up and the base source of the increment

Mysqlbackup-incremental-backup-dir=/data/backup/increbak-host=127.0.0.1-port=3306-user=backup-password=Qs3ce3saadr37tpP-incremental- incremental-base=history:last_backup-with-timestamp-backup-image=incre_backup.bki backup

Be sure to perform all of the above, last_backup this backup parameter will be found in the database, however, this backup should be carried out in the master database, can no longer slave database, because the slave database does not write backup information.

Remote backup across hosts

# backup to another server

The following command streams the backup as a single-file output to a remote host to be saved under

The file name my_backup.img (--backup-dir=/tmp designates the directory for storing temporary files

Rather than the final output file):

Mysqlbackup-defaults-file=~/my_backup.cnf-backup-image=--backup-dir=/tmp backup-to-image |\

Ssh @ 'cat > ~ / backups/my_backup.img'

The above content is how to backup in MySQL 8.0.11 innodb cluster operation and maintenance management. have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.