xtrabackup备份

最新推荐文章于 2025-05-23 14:02:38 发布

$疯狂的程序员

最新推荐文章于 2025-05-23 14:02:38 发布

阅读量147

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/weixin_52310457/article/details/111563635

一.本机备份

本机下载

yum -y install epel-release
yum -y install xtrabackup   下载xtrabackup包
rpm -qa | grep xtrabackup   查看
  percona-xtrabackup-2.3.6-1.el7.x86_64
yum -y install mariadb mariadb-server  下载mysql数据库
systemctl start mariadb       启动mysql数据库

mysql      进入mysql
 grant all on *.* to 'wg'@'localhost' identified by '123';   授权
 flush privileges;   刷新
 \q    退出

上面授权的用户密码
 innobackupex --user=wg --password=123 /quan/
    quan是执行上面的命令自动创建的

在这里插入图片描述

查看全备
cd /quan/
ll 2020-12-22_18-03-56/
总用量 18460
-rw-r----- 1 root root      386 12月 22 18:03 backup-my.cnf
-rw-r----- 1 root root 18874368 12月 22 18:03 ibdata1
drwx------ 2 root root     4096 12月 22 18:03 mysql
drwx------ 2 root root     4096 12月 22 18:03 performance_schema
drwx------ 2 root root       20 12月 22 18:03 test
-rw-r----- 1 root root       21 12月 22 18:03 xtrabackup_binlog_info
-rw-r----- 1 root root      113 12月 22 18:03 xtrabackup_checkpoints
-rw-r----- 1 root root      464 12月 22 18:03 xtrabackup_info
-rw-r----- 1 root root     2560 12月 22 18:03 xtrabackup_logfile

增量备份

innobackupex --user=wg --password=123 --incremental /zeng/ --incremental-basedir=/quan/2020-12-22_18-03-56
    zeng是自动创建的   
    quan/2020-12-22_18-03-56   tab 不出来可以复制

查看增量备份
 cd /zeng/2020-12-22_18-06-11/
  ll
总用量 48
-rw-r----- 1 root root   386 12月 22 18:06 backup-my.cnf
-rw-r----- 1 root root 16384 12月 22 18:06 ibdata1.delta
-rw-r----- 1 root root    44 12月 22 18:06 ibdata1.meta
drwx------ 2 root root  4096 12月 22 18:06 mysql
drwx------ 2 root root  4096 12月 22 18:06 performance_schema
drwx------ 2 root root    20 12月 22 18:06 test
-rw-r----- 1 root root    21 12月 22 18:06 xtrabackup_binlog_info
-rw-r----- 1 root root   117 12月 22 18:06 xtrabackup_checkpoints
-rw-r----- 1 root root   532 12月 22 18:06 xtrabackup_info
-rw-r----- 1 root root  2560 12月 22 18:06 xtrabackup_logfile

进入mysql创建几个库
 mysql
    create database pp;
    use pp;
    create table pp(id int(2), name varchar(10));
    exit

innobackupex --user=wg --password=123 --incremental /zeng/ --incremental-basedir=/quan/2020-12-22_18-03-56
必须在执行一次增量备份的命令

cd /zeng/2020-12-22_18-0
2020-12-22_18-06-11/ 2020-12-22_18-07-25/
 cd /zeng/2020-12-22_18-07-25/
 ll
总用量 368
-rw-r----- 1 root root    386 12月 22 18:07 backup-my.cnf
-rw-r----- 1 root root 344064 12月 22 18:07 ibdata1.delta
-rw-r----- 1 root root     44 12月 22 18:07 ibdata1.meta
drwx------ 2 root root   4096 12月 22 18:07 mysql
drwx------ 2 root root   4096 12月 22 18:07 performance_schema
drwx------ 2 root root     34 12月 22 18:07 pp
drwx------ 2 root root     20 12月 22 18:07 test
-rw-r----- 1 root root     21 12月 22 18:07 xtrabackup_binlog_info
-rw-r----- 1 root root    117 12月 22 18:07 xtrabackup_checkpoints
-rw-r----- 1 root root    532 12月 22 18:07 xtrabackup_info
-rw-r----- 1 root root   2560 12月 22 18:07 xtrabackup_logfile

到这里可以看到总用量发生了变化

增量备份做完了

远程备份
做这一步之前把远程的数据库备份

3.13主机
 mv /var/lib/mysql/   /root/mysql.bak

3.12主机
scp -r /var/lib/mysql/ root@192.168.3.13:/root/mysql
[root@localhost mysql]# ll /var/lib/mysql/
总用量 29760
-rw-r----- 1 mysql mysql    16384 12月 22 18:41 aria_log.00000001
-rw-r----- 1 mysql mysql       52 12月 22 18:41 aria_log_control
-rw-r----- 1 mysql mysql 18874368 12月 22 18:41 ibdata1
-rw-r----- 1 mysql mysql  5242880 12月 22 18:41 ib_logfile0
-rw-r----- 1 mysql mysql  5242880 12月 22 18:41 ib_logfile1
drwx------ 2 mysql mysql     4096 12月 22 18:41 mysql
-rw-r----- 1 mysql mysql    30379 12月 22 18:41 mysql-bin.000001
-rw-r----- 1 mysql mysql  1038814 12月 22 18:41 mysql-bin.000002
-rw-r----- 1 mysql mysql      635 12月 22 18:41 mysql-bin.000003
-rw-rw---- 1 mysql mysql      245 12月 22 18:41 mysql-bin.000004
-rw-r----- 1 mysql mysql       76 12月 22 18:41 mysql-bin.index
srwxrwxrwx 1 mysql mysql        0 12月 22 18:41 mysql.sock
drwx------ 2 mysql mysql     4096 12月 22 18:41 performance_schema
drwx------ 2 mysql mysql       34 12月 22 18:41 pp
drwx------ 2 mysql mysql        6 12月 22 18:41 test


[root@localhost mysql]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| pp                 |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> use pp;
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
MariaDB [pp]> show tables;
+--------------+
| Tables_in_pp |
+--------------+
| pp           |
+--------------+
1 row in set (0.00 sec)

MariaDB [pp]>

3.13主机
systemctl stop mariadb
cp -r /root/mysql/ /var/lib/

[root@localhost mysql]# ll /var/lib/mysql/
总用量 29764
-rw-r----- 1 mysql mysql    16384 12月 22 19:15 aria_log.00000001
-rw-r----- 1 mysql mysql       52 12月 22 19:15 aria_log_control
-rw-r----- 1 mysql mysql 18874368 12月 22 19:15 ibdata1
-rw-r----- 1 mysql mysql  5242880 12月 22 19:16 ib_logfile0
-rw-r----- 1 mysql mysql  5242880 12月 22 19:15 ib_logfile1
drwx------ 2 mysql mysql     4096 12月 22 19:15 mysql
-rw-r----- 1 mysql mysql    30379 12月 22 19:15 mysql-bin.000001
-rw-r----- 1 mysql mysql  1038814 12月 22 19:15 mysql-bin.000002
-rw-r----- 1 mysql mysql      635 12月 22 19:15 mysql-bin.000003
-rw-r----- 1 mysql mysql      245 12月 22 19:15 mysql-bin.000004
-rw-rw---- 1 mysql mysql      245 12月 22 19:16 mysql-bin.000005
-rw-r----- 1 mysql mysql       95 12月 22 19:16 mysql-bin.index
srwxrwxrwx 1 mysql mysql        0 12月 22 19:16 mysql.sock
drwx------ 2 mysql mysql     4096 12月 22 19:15 performance_schema
drwx------ 2 mysql mysql       34 12月 22 19:15 pp
drwx------ 2 mysql mysql        6 12月 22 19:15 test
[root@localhost mysql]#


**
chown -R mysql:mysql /var/lib/mysql/  属组:属主一定要修改
systemctl restart mariadb**


[root@localhost mysql]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| pp                 |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> use pp;
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
MariaDB [pp]> show tables;
+--------------+
| Tables_in_pp |
+--------------+
| pp           |
+--------------+
1 row in set (0.00 sec)

MariaDB [pp]>

两台服务器数据库一样就OK了