MySQL 8.0.11 INNODB cluster Operations Management manual four-msyqlbackup backup

MySQL 8.0.11 INNODB cluster Operations Management manual four-msyqlbackup backup author Fanglian mysqlbackup Introduction

Mysqlbackup is a hot backup tool, that is, it is not as mysqldump as a global lock on the table, because the mysqldump on the lock, so that the client can only read the database can not write, which is called mysqldump for warm backup reasons. But is mysqlbackup really such a hanging? The answer is no. For InnoDB engine table Mysqlbackup hot-standby, but for non-InnoDB table mysqlbackup can only be prepared, because this kind of engine does not support transactions, that is, the transaction log can not guarantee the consistency of the backup, so you can only add a global lock to the table to solve. In order to get a consistent backup mysqlbackup to keep track of the sln of the MySQL database, that is to say mysqlbackup to perform a backup then it must connect to the database. Mysqlbackup database backup is done by copying files, that is, mysqlbackup to the database on the same machine, and xtrabackup very similar.

Installing Mysqlbackup

Mysqlbackup is an enterprise version of MySQL, but can be used to log in to Oracle Cloud download, search MySQL backup, download binary files
Mysql-commercial-backup-8.0.11-linux-glibc2.12-x86_64.tar.gz
Upload to the host and unzip,
There will be bin and Lib folder, copy the files to/usr/local/mysql corresponding directory, here to the soft connection, the library all copies, or will be error

Backing Up users

(Official instruction 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‘;  #此表在主库上备份才会生成和写入GRANT LOCK TABLES, SELECT, CREATE, DROP, FILE ON *.* TO ‘backup‘@‘localhost‘;GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_sbt_history TO ‘backup‘@‘localhost‘;#此表仅在主库上有效

Full-scale backup

##备份到/fullbak/2018-10-10_19-19-11/datadir 类似这样的目录中mysqlbackup --backup-dir=/data/backup/fullbak --host=127.0.0.1 --port=3306 --user=backup --password=$$$$$$$$ --with-timestamp backup##############备份到/fullbak/2018-10-10_19-19-11/full_backup.bki 这样一个单文件中，以后不用备份单文件，我们自己压缩备份更小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, the default value is 3306.
--backup-dir: Can be regarded as the working directory of Mysqlback, temporary use.
--backup-image: Back up the filename, this is the final file, nothing else. The location can be placed without a path, by default, to Backup-dir
Backup-to-image: Output All backup information to a backup file
--with-timepstap: Automatically generates a date folder with the temporary files and the last backup image (without specifying the mirror location) for the backup

Incremental backup

As a practical proof, a single file backup cannot be restored, and the following example discards
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 to a single file, incremental backup does not seem to support, this is a backup can be restored to the incremental directory and the increment of the base source

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 the above fully, last_backup this backup parameter will be found in the database, however, this backup to be done in the main library, no longer from the library because the backup information is not written from the library.

Remote backup across Hosts

######## #备份到其他服务器
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 <user name>@<remote host name> ' Cat > ~/backups/my_backup.img '

MySQL 8.0.11 INNODB cluster Operations Management manual four-msyqlbackup backup