show binary logs; +..."/>
您好,登錄后才能下訂單哦!
準(zhǔn)備一個(gè)干凈的備份目錄;
[root@mysql ~]$ll /backups/ total 0 MariaDB [(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)
數(shù)據(jù)備份:
默認(rèn)就是以root用戶的身份進(jìn)行的備份; [root@mysql ~]$innobackupex --user=root /backups/ [root@mysql ~]$ll /backups/ total 0 drwxr-x--- 6 root root 217 Feb 25 14:14 2018-02-25_14-14-07 [root@mysql ~]$ll /backups/2018-02-25_14-14-07/ total 18460 -rw-r----- 1 root root 417 Feb 25 14:14 backup-my.cnf drwxr-x--- 2 root root 272 Feb 25 14:14 hellodb -rw-r----- 1 root root 18874368 Feb 25 14:14 ibdata1 drwxr-x--- 2 root root 4096 Feb 25 14:14 mysql drwxr-x--- 2 root root 4096 Feb 25 14:14 performance_schema drwxr-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 113 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_14-14-07/ [root@mysql 2018-02-25_14-14-07]$ls backup-my.cnf ibdata1 performance_schema xtrabackup_binlog_info xtrabackup_info hellodb mysql test xtrabackup_checkpoints xtrabackup_logfile [root@mysql 2018-02-25_14-14-07]$ # lsn是日志序列號(hào),在磁盤上保存了數(shù)據(jù)庫(kù)的所有數(shù)據(jù);文件很大,分成很多的小塊存儲(chǔ)在了磁盤上; 每個(gè)數(shù)據(jù)塊的小塊都有所謂的lsn號(hào);如從100-200,如果將100這個(gè)數(shù)據(jù)塊中的數(shù)據(jù)做了修改,那么他的LSN會(huì)加1,即 變?yōu)榱?01;所以根據(jù)LSN的大小,可以判斷數(shù)據(jù)塊中的數(shù)據(jù)是否備份過; 這里是全備份,所以就是將所有的LSN對(duì)應(yīng)的數(shù)據(jù)塊的數(shù)據(jù)都進(jìn)行了備份。 [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 盡管表面來看是拷貝的文件,但是底層實(shí)際拷貝的是數(shù)據(jù)塊,所有效率很高。 這個(gè)文件中顯示的是全備份備份到二進(jìn)制文件的哪個(gè)位置; [root@mysql 2018-02-25_14-14-07]$cat xtrabackup_binlog_info mysql-bin.000005 423 [root@mysql 2018-02-25_14-14-07]$file xtrabackup_logfile xtrabackup_logfile: data
還原數(shù)據(jù)到一個(gè)新的MySQL主機(jī)上
找一個(gè)干凈的主機(jī),將在27.7主機(jī)上備份的數(shù)據(jù)在27.17上實(shí)現(xiàn)還原; [root@mysql17 ~]$vim /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log_bin innodb_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-25_14-14-07/ 192.168.27.17:/app/ [root@mysql17 ~]$ls /app/ 2018-02-25_14-14-07
數(shù)據(jù)庫(kù)的整理操作:
包括將不完整的事務(wù)進(jìn)行回滾;因?yàn)閭浞莸臅r(shí)間點(diǎn)極有可能被一個(gè)事務(wù)橫跨;
[root@mysql17 ~]$innobackupex --apply-log /app/2018-02-25_14-14-07/ 180225 01:53:05 completed OK!
確保要恢復(fù)的數(shù)據(jù)庫(kù)的數(shù)據(jù)目錄是空的; [root@mysql17 ~]$ll /var/lib/mysql/ total 0 將整理過數(shù)據(jù)復(fù)制到數(shù)據(jù)庫(kù)的數(shù)據(jù)目錄; 這個(gè)過程就是復(fù)制數(shù)據(jù),對(duì)于innodb引擎,他是基于塊的方式實(shí)現(xiàn)的;對(duì)于myISAM引擎, 那么就是單個(gè)文件的復(fù)制; [root@mysql17 ~]$innobackupex --copy-back /app/2018-02-25_14-14-07/ ... 180225 01:56:10 completed OK! [root@mysql17 ~]$ll /var/lib/mysql/ total 40976 drwxr-x--- 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 ibtmp1 drwxr-x--- 2 root root 4096 Feb 25 01:56 mysql drwxr-x--- 2 root root 4096 Feb 25 01:56 performance_schema drwxr-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----- 1 root root 454 Feb 25 01:56 xtrabackup_info [root@mysql17 ~]$chown -R mysql.mysql /var/lib/mysql/ [root@mysql17 ~]$ll /var/lib/mysql/ total 40976 drwxr-x--- 2 mysql mysql 272 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 ibtmp1 drwxr-x--- 2 mysql mysql 4096 Feb 25 01:56 mysql drwxr-x--- 2 mysql mysql 4096 Feb 25 01:56 performance_schema drwxr-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_info [root@mysql17 ~]$ll /var/lib/mysql/ -d drwxr-xr-x 6 mysql mysql 198 Feb 25 01:56 /var/lib/mysql/
[root@mysql17 ~]$systemctl start mariadb MariaDB [(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) 以上就是使用xtrabackup實(shí)現(xiàn)將一個(gè)主機(jī)的數(shù)據(jù)全備份后還原到一個(gè)遠(yuǎn)程的新的主機(jī)的過程。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。