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

Prepare a clean backup directory

[root@mysql ~] $ll / backups/total 0MariaDB [(none)] > show binary logs +-+-+ | Log_name | File_size | +-+-+ | mysql-bin.000001 | 30331 | | mysql-bin.000002 | 1038814 | mysql-bin.000003 | 7698 | mysql-bin.000004 | 442 | | mysql-bin.000005 | | 423 | +-+-+ 5 rows in set (0.00 sec) |

Data backup:

The default is the backup as the root user [root@mysql] $innobackupex-- user=root / backups/ [root@mysql] $ll / backups/total 0drwxr ll-6 root root 217 Feb 25 14:14 2018-02-25 October 14-14-07 [root@mysql ~] $ll / backups/2018-02-25 October 14-14-07/total 18460 Mustang r-1 root root 417 Feb 25 14:14 backup-my.cnfdrwxr-x--- 2 root root 272 Feb 25 14:14 hellodb-rw-r -1 root root 18874368 Feb 25 14:14 ibdata1drwxr-x--- 2 root root 4096 Feb 25 14:14 mysqldrwxr-x--- 2 root root 25 14:14 mysqldrwxr-x--- 2 root root 25 14:14 performance_schemadrwxr-x--- 2 root root 20 Feb 25 14:14 test-rw-r- 1 root root 21 Feb 25 14:14 xtrabackup_binlog_info-rw-r- 1 root root 13 Feb 25 14:14 xtrabackup_checkpoints-rw -r-1 root root 454 Feb 25 14:14 xtrabackup_info-rw-r- 1 root root 2560 Feb 25 14:14 xtrabackup_ logfile [root @ mysql ~] $cd / backups/2018-02-25 Feb 14-14-07 / [root@mysql 2018-02-25 Feb 14-14-07] $lsbackup-my.cnf ibdata1 performance_schema xtrabackup_binlog_info xtrabackup_infohellodb mysql test xtrabackup_checkpoints xtrabackup_ logfile [root @ mysql 2018-02-25 14-14-07] $# lsn is the log serial number All the data of the database is saved on disk. The file is very large, divided into many small blocks and stored on disk; the small block of each data block has a so-called lsn number; for example, if the data in this data block is modified from 100,200, then his LSN will be increased by 1, that is, it will become 201. therefore, according to the size of the LSN, you can judge whether the data in the data block has been backed up. This is a full backup, so the data of all the blocks corresponding to the LSN are backed up. [root@mysql 2018-02-25 14-14-07] $less xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 1640915 last_lsn = 1640915 compact = 0 recover_binlog_info = 0 although it appears to be a copied file, the underlying layer actually copies data blocks, which is very efficient. This file shows where the full backup is backed up to the binary file; [root@mysql 2018-02-25 backup 14-14-07] $cat xtrabackup_binlog_info mysql-bin.000005 423 [root@mysql 2018-02-25 backup 14-14-07] $file xtrabackup_logfile xtrabackup_logfile: data

Restore data to a new MySQL host

Find a clean host and restore the data backed up on the 27.7 host on 27.17 [root@mysql17 ~] $vim / etc/my.cnf [mysqld] datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log_bininnodb_file_per_ Table [root @ mysql17 ~] $yum install percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm [root@mysql backups] $scp-rp / backups/2018-02-25levels 14-14-07 / 192.168.27.17:/app/ [ Root@mysql17 ~] $ls / app/2018-02-25 14-14-07

Collation of the database:

Includes rolling back incomplete transactions; because the point in time of the backup is most likely to be spanned by a transaction

[root@mysql17] $innobackupex-- apply-log / app/2018-02-25 completed OK 14-14-07 Universe 180225 01:53:05 completed OK! Make sure that the data directory of the database to be restored is empty; [root@mysql17 ~] $ll / var/lib/mysql/total 0 copies the collated data to the data directory of the database; the process is to copy the data, which is implemented in a block-based manner for the innodb engine; for the myISAM engine, it is the replication of a single file [root@mysql17] $innobackupex-- copy-back / app/2018-02-25 villages 14-14-07 completed OK. 180225 01:56:10 completed OK! [root@mysql17 ~] $ll / var/lib/mysql/total 40976drwxr hellodb-rw-r--2 root root 272 Feb 25 01:56 hellodb-rw-r- 1 root root 18874368 Feb 25 01:56 ibdata1-rw-r- 1 root root 5242880 Feb 25 01:56 ib_logfile0-rw-r -1 root root 5242880 Feb 25 01:56 ib_logfile1-rw-r- 1 root root 12582912 Feb 25 01:56 ibtmp1drwxr-x--- 2 root root 4096 Feb 25 01:56 mysqldrwxr-x--- 2 root root 4096 Feb 25 01:56 performance_schemadrwxr-x--- 2 root root 20 Feb 25 01:56 test-rw-r- 1 root root 35 Feb 25 01:56 xtrabackup_binlog_pos_innodb-rw-r-- -root root Feb 25 01:56 hellodb-rw-r- 1 mysql mysql 18874368 Feb 25 01:56 ibdata1-rw-r- 1 mysql mysql 5242880 Feb 25 01:56 ib_logfile0-rw-r- 1 mysql Mysql 5242880 Feb 25 01:56 ib_logfile1-rw-r- 1 mysql mysql 12582912 Feb 25 01:56 ibtmp1drwxr-x--- 2 mysql mysql 4096 Feb 25 01:56 mysqldrwxr-x--- 2 mysql mysql 4096 Feb 25 01:56 performance_schemadrwxr-x--- 2 mysql mysql 20 Feb 25 01:56 test-rw-r- 1 mysql mysql 35 Feb 25 01:56 xtrabackup_binlog_pos_innodb-rw-r- 1 mysql mysql 454 Feb 25 01:56 xtrabackup_ in [root @ mysql17 ~] $ll / var/lib/mysql/-ddrwxr-xr-x 6 mysql mysql 198 Feb 25 01:56 / var/lib/mysql/ [root@mysql17 ~] $systemctl start mariadbMariaDB [(none)] > show databases +-+ | Database | +-+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +-+ 5 rows in set (0.00 sec) MariaDB [(none)] > MariaDB [(none)] > show master logs +-+-+ | Log_name | File_size | +-+-+ | mariadb-bin.000001 | 245 | +- -+ 1 row in set (0.00 sec) MariaDB [(none)] > select * from hellodb.students Empty set (0.00 sec) is the process of using xtrabackup to restore the data of a host to a remote new host after a full backup.

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.