[root@localhost ~]# cd /usr/src/ [root@localhost src]# ls debug kernels mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz [root@localhost src]# groupadd -r mysql groupadd: group 'mysql' already exists [root@localhost src]# useradd -M -s /sbin/nologin -g mysql mysql
2.讲数据库包解压到/usr/local下，再给解压后的目录生成一个软链接mysql，把/usr/local/mysql的属主与属组更改为mysql。
[root@localhost src]# tar xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /usr/local [root@localhost src]# cd /usr/local [root@localhost local]# ls bin games lib libexec sbin src etc include lib64 mysql-5.7.31-linux-glibc2.12-x86_64 share [root@localhost local]# ln -sv mysql-5.7.31-linux-glibc2.12-x86_64/ mysql 'mysql' -> 'mysql-5.7.31-linux-glibc2.12-x86_64/' [root@localhost local]# ll total 0 drwxr-xr-x. 2 root root 6 Aug 12 2018 bin drwxr-xr-x. 2 root root 6 Aug 12 2018 etc drwxr-xr-x. 2 root root 6 Aug 12 2018 games drwxr-xr-x. 2 root root 6 Aug 12 2018 include drwxr-xr-x. 2 root root 6 Aug 12 2018 lib drwxr-xr-x. 2 root root 6 Aug 12 2018 lib64 drwxr-xr-x. 2 root root 6 Aug 12 2018 libexec lrwxrwxrwx. 1 root root 36 Dec 28 15:35 mysql -> mysql-5.7.31-linux-glibc2.12-x86_64/ drwxr-xr-x. 9 7161 31415 129 Jun 2 2020 mysql-5.7.31-linux-glibc2.12-x86_64 drwxr-xr-x. 2 root root 6 Aug 12 2018 sbin drwxr-xr-x. 5 root root 49 Nov 13 22:08 share drwxr-xr-x. 2 root root 6 Aug 12 2018 src [root@localhost local]# chown -R mysql:mysql /usr/local/mysql [root@localhost local]# ll /usr/local/mysql -d lrwxrwxrwx. 1 mysql mysql 36 Dec 28 15:35 /usr/local/mysql -> mysql-5.7.31-linux-glibc2.12-x86_64/
3.添加环境变量，再建立数据存放目录/opt/data。
[root@localhost local]# ls /usr/local/mysql bin docs include lib LICENSE man README share support-files [root@localhost local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [root@localhost local]# source /etc/profile.d/mysql.sh [root@localhost local]# echo $PATH /usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin [root@localhost local]# mkdir /opt/data [root@localhost local]# chown -R mysql:mysql /opt/data/ [root@localhost local]# ll /opt/ total 0 drwxr-xr-x. 2 mysql mysql 6 Dec 28 15:44 data
4.初始化数据库
[root@localhost local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/ 2020-12-28T07:52:02.545659Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2020-12-28T07:52:03.204103Z 0 [Warning] InnoDB: New log files created, LSN=45790 2020-12-28T07:52:03.303585Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2020-12-28T07:52:03.370771Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 92dca673-48e1-11eb-a9f4-000c29b5300b. 2020-12-28T07:52:03.371455Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2020-12-28T07:52:03.628412Z 0 [Warning] CA certificate ca.pem is self signed. 2020-12-28T07:52:03.949543Z 1 [Note] A temporary password is generated for root@localhost: %qt+-kq9R8&Z //请注意，这个命令的最后会生成一个临时密码，此处密码是%qt+-kq9R8&Z //再次注意，这个密码是随机的，你的不会跟我一样，一定要记住这个密码，因为一会登录时会用到
mysql的配置文件为/etc/my.cnf
mysql常用配置文件参数：
| 参数 | 说明 |
|---|---|
| port = 3306 | 设置监听端口 |
| socket = /tmp/mysql.sock | 指定套接字文件位置 |
| basedir = /usr/local/mysql | 指定MySQL的安装路径 |
| datadir = /data/mysql | 指定MySQL的数据存放路径 |
| pid-file = /data/mysql/mysql.pid | 指定进程ID文件存放路径 |
| user = mysql | 指定MySQL以什么用户的身份提供服务 |
| skip-name-resolve | 禁止MySQL对外部连接进行DNS解析 使用这一选项可以消除MySQL进行DNS解析的时间。 若开启该选项，则所有远程主机连接授权都要使用IP地址方 式否则MySQL将无法正常处理连接请求 |
5.生成配置文件
[root@localhost local]# vi /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /opt/data socket = /tmp/mysql.sock port = 3306 pid-file = /opt/data/mysql.pid user = mysql skip-name-resolve
6.配置服务启动脚本
[root@localhost local]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld [root@localhost local]# vi /etc/init.d/mysqld # If you change base dir, you must also change datadir. These may get # overwritten by settings in the MySQL configuration files. basedir=/usr/local/mysql #加上路径 datadir=/opt/data #加上路径 # Default value, in seconds, afterwhich the script should timeout waiting # for server start. # Value here is overriden by value in my.cnf.
7.启动mysql服务
[root@localhost local]# service mysqld start Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'. ... SUCCESS! [root@localhost local]# ss -antl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 0.0.0.0:22 0.0.0.0:* LISTEN 0 80 *:3306 *:* LISTEN 0 128 [::]:22 [::]:* root@localhost ~]# mysql -uroot -p'%qt+-kq9R8&Z' mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory [root@localhost ~]# yum whatprovides libncurses.so.5 Updating Subscription Management repositories. Unable to read consumer identity This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register. Last metadata expiration check: 2:45:08 ago on Mon 28 Dec 2020 03:49:42 PM CST. ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility : libraries Repo : BaseOS Matched from: Provide : libncurses.so.5 [root@localhost ~]# yum -y install ncurses-compat-libs [root@localhost ~]# mysql -uroot -p'%qt+-kq9R8&Z'
8.更改密码为123456，并设置开机自动启动（mysql是6以前的版本）
mysql> set password = password('123456'); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> quit Bye [root@localhost ~]# chkconfig --add mysqld [root@localhost ~]# chkconfig mysqld on [root@localhost ~]# chkconfig --list Note: This output shows SysV services only and does not include native systemd services. SysV configuration data might be overridden by native systemd configuration. If you want to list systemd services use 'systemctl list-unit-files'. To see services enabled on particular target use 'systemctl list-dependencies [target]'. mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
9.mysql配置文件（使mysql命令能直接登陆数据库）
[root@localhost ~]# vi .my.cnf [client] user=root password=123456 [root@localhost ~]# mysql Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 4 Server version: 5.7.31 MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql>
10.破解mysql密码，修改为csl123
[root@localhost ~]# vi /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /opt/data socket = /tmp/mysql.sock port = 3306 pid-file = /opt/data/mysql.pid user = mysql skip-name-resolve skip-grant-tables #加入跳过授权表 [root@localhost ~]# service mysqld restart Shutting down MySQL.... SUCCESS! Starting MySQL. SUCCESS! [root@localhost ~]# mysql mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from userG #查找user=root host=localhost里内容 mysql> update user set authentication_string=password('csl123')where User='root' and Host='localhost'; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> quit Bye [root@localhost ~]# vi /etc/my.cnf #删除skip-grant-tables [root@localhost ~]# service mysqld restart Shutting down MySQL.... SUCCESS! Starting MySQL. SUCCESS! [root@localhost ~]# mysql -uroot -pcsl123
| 备份方案 | 特点 |
|---|---|
| 全量备份 | 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长 |
| 增量备份 | 增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象 是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行 |
| 差异备份 | 备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。 |
11.数据库备份与恢复，全量备份到文件all.sql
[root@localhost ~]# mysql -uroot -pcsl123 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 4 Server version: 5.7.31 MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> create database school; Query OK, 1 row affected (0.00 sec) mysql> use school; Database changed mysql> create table student(id int not null primary key auto_increment,name varchar(50),age tinyint); Query OK, 0 rows affected (0.16 sec) mysql> insert student(name,age)values('tom',12),('xiaoming',23),('xiaohong',18); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> quit Bye [root@localhost ~]# mysqldump -uroot -pcsl123 --all-databases > all.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@localhost ~]# ls all.sql anaconda-ks.cfg csl passwd [root@localhost ~]# file all.sql all.sql: UTF-8 Unicode text, with very long lines [root@localhost ~]# mysql -uroot -pcsl123 -e 'drop database school;' mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost ~]# mysql -uroot -pcsl123 < all.sql mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost ~]# mysql -uroot -pcsl123 -e 'select * from school.student;' mysql: [Warning] Using a password on the command line interface can be insecure. +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | tom | 12 | | 2 | xiaoming | 23 | | 3 | xiaohong | 18 | +----+----------+------+
12.在school数据库中创建表student1,student2，向student1插入数据，备份表student1，备份文件为table_student1.sql
//语法： mysqldump [OPTIONS] database [tables ...] mysqldump [OPTIONS] --all-databases [OPTIONS] mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] //常用的OPTIONS： -uUSERNAME //指定数据库用户名 -hHOST //指定服务器主机，请使用ip地址 -pPASSWORD //指定数据库用户的密码 -P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307 [root@localhost ~]# mysql mysql> use school; mysql> create table student1(id int not null primary key auto_increment,name varchar(20) not null,age tinyint); mysql> insert student1(name,age) values('jack',11),('tim',12),('xiaohong',112),('xiaoming',11); mysql> create table student2(id int not null primary key auto_increment,name varchar(20) not null,age tinyint); mysql> show tables; +------------------+ | Tables_in_school | +------------------+ | student | | student1 | | student2 | +------------------+ mysql> select * from student1; +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | jack | 11 | | 2 | tim | 12 | | 3 | xiaohong | 12 | | 4 | xiaoming | 11 | +----+----------+------+ mysql> quit [root@localhost ~]# mysqldump -uroot school student1 > table_student1.sql #备份school中所有的表到文件table_school.sql [root@localhost ~]# mysqldump -uroot school > table_school.sql #创建库information，在里面创建表teacher并插入数据，给数据库school和information全备份到文件database.sql mysql> create database information; mysql> use information; mysql> create table teacher(id int not null primary key auto_increment,name varchar(100)not null,age tinyint(4)); mysql> insert teacher(name,age) values('nmi',31),('hil',43),('alice',28); mysql> exit [root@localhost ~]# mysqldump -uroot --databases school information > database.sql
13.删除库school，使用备份文件table_school.sql恢复库school数据。
mysql> drop database school; mysql> create database school; mysql> use school; mysql> source table_school.sql; ##也可以使用mysql -uroot -pcsl123 school < table_school.sql 来恢复，也要先创建库school。
14.删除库school和information，使用备份文件database.sql恢复两个库的数据。
mysql> drop database school; mysql> drop database information; mysql> exit [root@localhost ~]# mysql -uroot -pcsl123 < database.sql
差异备份
15.先完全备份现在状态的所有数据库，往表student2里插入数据，再更改其中一项数据，再删除库school
[root@localhost ~]# vi /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /opt/data socket = /tmp/mysql.sock port = 3306 pid-file = /opt/data/mysql.pid user = mysql skip-name-resolve log-bin=mysql_bin #开启二进制日志功能 server-id=1 #设置服务器标识符 [root@localhost ~]# service mysqld restart [root@localhost ~]# rm -rf *.sql [root@localhost ~]# mysqldump -uroot -pcsl123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202012291840.sql mysql> use school; mysql> insert student2(name,age) values('tom',20),('jerry',23),('lisi',30); mysql> update student2 set age=21 where id=3; mysql> drop database school;
16.我们要恢复先前库school里的所有数据（包括之前新增的数据），先找到日志存放路径，之后立刻刷新日志(刷新日志方便我们寻找要恢复的时间点)，然后恢复完全备份，最后查找删除库命令位置并恢复差异备份。
[root@localhost ~]# ls /opt/data auto.cnf ib_logfile1 performance_schema ca-key.pem ibtmp1 private_key.pem ca.pem information public_key.pem client-cert.pem localhost.localdomain.err server-cert.pem client-key.pem mysql server-key.pem ib_buffer_pool mysql_bin.000002 sys ibdata1 mysql_bin.index ib_logfile0 mysql.pid [root@localhost ~]# mysqladmin -uroot flush-logs [root@localhost ~]# ls /opt/data auto.cnf ib_logfile1 mysql.pid ca-key.pem ibtmp1 performance_schema ca.pem information private_key.pem client-cert.pem localhost.localdomain.err public_key.pem client-key.pem mysql server-cert.pem ib_buffer_pool mysql_bin.000002 server-key.pem ibdata1 mysql_bin.000003 sys ib_logfile0 mysql_bin.index [root@localhost ~]# mysql -uroot -pcsl123 < all-202012291840.sql [root@localhost ~]# mysql //检查误删数据库的位置在什么地方 mysql> show binlog events in 'mysql_bin.000002'G *************************** 14. row *************************** Log_name: mysql_bin.000002 Pos: 799 Event_type: Query Server_id: 1 End_log_pos: 897 Info: drop database school mysql> exit //使用mysqlbinlog恢复差异备份 [root@localhost ~]# mysqlbinlog --stop-position=799 /opt/data/mysql_bin.000002|mysql -uroot -pcsl123 mysql> select * from student2; +----+-------+------+ | id | name | age | +----+-------+------+ | 1 | tom | 20 | | 2 | jerry | 23 | | 3 | lisi | 21 | +----+-------+------+
增量备份
17.下载Xtrabackup包，准备环境。
[root@localhost ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.21/binary/redhat/8/x86_64/Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar [root@localhost ~]# tar xf Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar [root@localhost ~]# yum -y install percona*
18.使用innobackupex命令全量备份
[root@localhost backups]# innobackupex --user=root --password=csl123 --host=192.168.44.128 /backups/ #进行全库备份 #语法解释说明： #--user=root 指定备份用户 #--password=123456 指定备份用户密码 #--host 指定主机 #/backups 指定备份目录 [root@localhost backups]# ll total 0 drwxr-x---. 7 root root 256 Dec 29 21:41 2020-12-29_21-41-12 [root@localhost backups]# ll 2020-12-29_21-41-12 #查看备份数据 total 12340 -rw-r-----. 1 root root 487 Dec 29 21:41 backup-my.cnf #备份用到的配置选项信息文件 -rw-r-----. 1 root root 848 Dec 29 21:41 ib_buffer_pool -rw-r-----. 1 root root 12582912 Dec 29 21:41 ibdata1 drwxr-x---. 2 root root 58 Dec 29 21:41 information drwxr-x---. 2 root root 4096 Dec 29 21:41 mysql drwxr-x---. 2 root root 8192 Dec 29 21:41 performance_schema drwxr-x---. 2 root root 138 Dec 29 21:41 school drwxr-x---. 2 root root 8192 Dec 29 21:41 sys -rw-r-----. 1 root root 21 Dec 29 21:41 xtrabackup_binlog_info #mysql服务器当前正在使用的二进制日志文件和此时二进制日志时间的位置信息文件 -rw-r-----. 1 root root 135 Dec 29 21:41 xtrabackup_checkpoints #备份的类型、状态和LSN状态信息文件 -rw-r-----. 1 root root 489 Dec 29 21:41 xtrabackup_info -rw-r-----. 1 root root 2560 Dec 29 21:41 xtrabackup_logfile #备份的日志文件
总结全库备份与恢复三步曲：
a. innobackupex全量备份，并指定备份目录路径；
b. 在恢复前，需要使用--apply-log参数先进行合并数据文件，确保数据的一致性要求；
c. 恢复时，直接使用--copy-back参数进行恢复，需要注意的是，在my.cnf中要指定数据文件目录的路径。
19.删除数据库所有文件再恢复
[root@localhost backups]# /etc/init.d/mysqld stop #停止mysql服务 [root@localhost backups]# innobackupex --apply-log /backups/2020-12-29_21-41-12/ #合并数据，使数据文件处于一致性的状态 [root@localhost backups]# rm -rf /opt/data/ [root@localhost backups]# innobackupex --copy-back /backups/2020-12-29_21-41-12/ 201229 22:19:15 completed OK! #看到completed OK就是恢复正常了 [root@localhost backups]# ll /opt/data/ #可以看到数据已经恢复，但是属主会有问题，需要进行修改，所以一般使用mysql的运行用户进行恢复，否则需要进行修改属主和属组信息 total 122924 -rw-r-----. 1 root root 848 Dec 29 22:19 ib_buffer_pool -rw-r-----. 1 root root 12582912 Dec 29 22:19 ibdata1 -rw-r-----. 1 root root 50331648 Dec 29 22:19 ib_logfile0 -rw-r-----. 1 root root 50331648 Dec 29 22:19 ib_logfile1 -rw-r-----. 1 root root 12582912 Dec 29 22:19 ibtmp1 drwxr-x---. 2 root root 58 Dec 29 22:19 information drwxr-x---. 2 root root 4096 Dec 29 22:19 mysql drwxr-x---. 2 root root 8192 Dec 29 22:19 performance_schema drwxr-x---. 2 root root 138 Dec 29 22:19 school drwxr-x---. 2 root root 8192 Dec 29 22:19 sys -rw-r-----. 1 root root 24 Dec 29 22:19 xtrabackup_binlog_pos_innodb -rw-r-----. 1 root root 489 Dec 29 22:19 xtrabackup_info -rw-r-----. 1 root root 1 Dec 29 22:19 xtrabackup_master_key_id [root@localhost backups]# chown -R mysql.mysql /opt/data/ 修改属主属组 [root@localhost backups]# /etc/init.d/mysqld start #开启mysql服务 [root@localhost ~]# mysql -uroot -pcsl123 -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | information | | mysql | | performance_schema | | school | | sys | +--------------------+
20.增量备份
[root@localhost backups]# innobackupex --user=root --password=csl123 --host=192.168.44.128 /backups/ #全量备份数据 mysql> insert into student(name,age) values('zengliangshuju',1); #在student表中新插入数据 [root@localhost backups]# innobackupex --user=root --password=csl123 --host=192.168.44.128 --incremental /backups/ --incremental-basedir=/backups/2020-12-29_22-33-28/ #使用innobackupex进行增量备份 [root@localhost backups]# ll total 4 drwxr-x---. 7 root root 256 Dec 29 22:33 2020-12-29_22-33-28 #全量备份数据目录 drwxr-x---. 7 root root 4096 Dec 29 22:40 2020-12-29_22-40-12 #增量备份数据目录 [root@localhost backups]# cd 2020-12-29_22-33-28/ [root@localhost 2020-12-29_22-33-28]# cat xtrabackup_checkpoints #查看全量备份的xtrabackup_checkpoints backup_type = full-backuped #备份类型为全量备份 from_lsn = 0 #lsn从0开始 to_lsn = 5936187 #lsn到5936187结束 last_lsn = 5936196 compact = 0 recover_binlog_info = 0 flushed_lsn = 5936196 [root@localhost 2020-12-29_22-33-28]# cd /backups/2020-12-29_22-40-12/ [root@localhost 2020-12-29_22-40-12]# cat xtrabackup_checkpoints #查看增量备份的xtrabackup_checkpoints backup_type = incremental #备份类型为增量备份 from_lsn = 5936187 #lsn从5936187开始 to_lsn = 5936579 #lsn从5936579结束 last_lsn = 5936588 compact = 0 recover_binlog_info = 0 flushed_lsn = 5936588
21.使用增量数据恢复数据
[root@localhost ~]# /etc/init.d/mysqld stop Shutting down MySQL.. SUCCESS! [root@localhost ~]# rm -rf /opt/data/ //合并全备数据目录，确保数据的一致性 [root@localhost ~]# innobackupex --apply-log --redo-only /backups/2020-12-29_22-33-28/ //将增量备份数据合并到全备数据目录当中 [root@localhost ~]# innobackupex --apply-log --redo-only /backups/2020-12-29_22-33-28/ --incremental-dir=/backups/2020-12-29_22-40-12/ [root@localhost ~]# cat /backups/2020-12-29_22-33-28/xtrabackup_checkpoints backup_type = log-applied #查看到数据备份类型是增加 from_lsn = 0 #lsn从0开始 to_lsn = 5936579 #lsn结束号为最新的lsn last_lsn = 5936588 compact = 0 recover_binlog_info = 0 flushed_lsn = 5936588 //恢复数据 [root@localhost ~]# innobackupex --copy-back /backups/2020-12-29_22-33-28/ [root@localhost ~]# ll /opt/data/ total 12332 -rw-r-----. 1 root root 848 Dec 29 22:53 ib_buffer_pool -rw-r-----. 1 root root 12582912 Dec 29 22:53 ibdata1 drwxr-x---. 2 root root 58 Dec 29 22:53 information drwxr-x---. 2 root root 4096 Dec 29 22:53 mysql drwxr-x---. 2 root root 8192 Dec 29 22:53 performance_schema drwxr-x---. 2 root root 138 Dec 29 22:53 school drwxr-x---. 2 root root 8192 Dec 29 22:53 sys -rw-r-----. 1 root root 21 Dec 29 22:53 xtrabackup_binlog_pos_innodb -rw-r-----. 1 root root 561 Dec 29 22:53 xtrabackup_info -rw-r-----. 1 root root 1 Dec 29 22:53 xtrabackup_master_key_id [root@localhost ~]# chown -R mysql.mysql /opt/data/ #更改数据的属主属组 [root@localhost ~]# /etc/init.d/mysqld start #启动mysql mysql> select * from student; #查看数据是否恢复 +----+----------------+------+ | id | name | age | +----+----------------+------+ | 1 | tom | 12 | | 2 | xiaoming | 23 | | 3 | xiaohong | 18 | | 4 | zengliangshuju | 1 | +----+----------------+------+ 4 rows in set (0.00 sec)
总结：
（1）增量备份需要使用参数--incremental指定需要备份到哪个目录，使用incremental-dir指定全备目录；
（2）进行数据备份时，需要使用参数--apply-log redo-only先合并全备数据目录数据，确保全备数据目录数据的一致性；
（3）再将增量备份数据使用参数--incremental-dir合并到全备数据当中；
（4）最后通过最后的全备数据进行恢复数据，注意，如果有多个增量备份，需要逐一合并到全备数据当中，再进行恢复。