[大数据]MySQL——备份与恢复

一、创建素材

创建并查看student表

CREATE  TABLE  student (
id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY,
name  VARCHAR(20)  NOT NULL,
sex  VARCHAR(4),
birth  YEAR,
department  VARCHAR(20),
address  VARCHAR(50) 
);

创建并查看score表

CREATE  TABLE  score (
id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY  AUTO_INCREMENT,
stu_id  INT(10)  NOT NULL,
c_name  VARCHAR(20),
grade  INT(10)
);

二、练习

1.备份数据库school到/backup目录

mysqldump -uroot -pMySQL@123 school > /backup

2.备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库

mysqldump --add-drop-table -h127.0.0.1 -uroot -pMySQL@123 school > backupfile.sql

3.直接将MySQL数据库压缩备份

mysqldump -h127.0.0.1 -uroot -pMySQL@123 school | gzip > /etc/lvm/backup/backupfile.sql.gz

4.备份MySQL数据库某个(些)表。此例备份student表

mysqldump -h127.0.0.1 -uroot -pMySQL@123 school student > /etc/lvm/backup/backfile2.sql

5.同时备份多个MySQL数据库（其他数据库素材自行准备）

mysqldump -h127.0.0.1 -uroot -pMySQL@123 --databases mysql school > multibackupfile.sql

6.仅仅备份数据库结构

 mysqldump  --no-data -h127.0.0.1 -uroot -pMySQL@123  --databases school > structurebackupfile.sql

7.备份服务器上所有数据库

mysqldump  --all-databases -h127.0.0.1 -uroot  -pMySQL@123 > allbackupfile.sql

8.还原MySQL数据库

mysqldump -h127.0.0.1 -uroot -pMySQL@123 school < backupfile.sql

9.还原压缩的MySQL数据库

gunzip < backupfile.sql.gz | mysql -h127.0.0.1 -uroot -pMySQL@123 school

10.使用xtrabackup 备份数据库

[root@localhost tmp]# innobackupex -v
innobackupex version 2.4.3 Linux (x86_64) (revision id: 6a46905) 
//主库备份
[root@localhost ~]# /usr/bin/innobackupex  --defaults-file=/etc/my.cnf --user=root --password='MySQL@123' --host=192.168.64.132 --stream=xbstream  /data/backup  | ssh root@192.168.64.133 "xbstream -x -C /data/backup"

11.在另外的数据库服务器上还原xtrabackup 备份

1. 从库还原（主库：192.168.64.132，从库：192.168.64.133）

[root@localhost ~]# mysqladmin -uroot -p shutdown;//关闭数据库
[root@localhost ~]# mv /data/mysql/data /data/mysql/data.old  //move走老数据库文件目录
[root@localhost ~]# mkdir /data/mysql/data

2. 应用日志

[root@localhost ~]# cat resume.sh 
/usr/bin/innobackupex --defaults-file=/etc/my.cnf --user root  --socket=/tmp/mysql.sock --apply-log /data/backup

3. 还原数据库文件：

[root@localhost ~]# cat move-back.sh 
/usr/bin/innobackupex --defaults-file=/etc/my.cnf --user root  --socket=/tmp/mysql.sock --move-back /home/mysql/backup

4. 启动数据库MySQL

[root@localhost ~]# chown -R mysql:mysql /data/mysql/*
[root@localhost ~]# /usr/data/mysql/bin/mysqld_safe --user=mysql &

5. 启动后登陆数据库，到/data/backup里找到xtrabackup_binlog_info文件

[root@localhost ~]# cat /data/backup/xtrabackup_binlog_info
mysql-bin.000008        229003653

6. 登陆从库做change master

mysql>change master to  master_host='192.168.64.133',master_user='root',master_password='MySQL@123',master_log_file='mysql-bin.000008',master_log_pos=229003653,master_connect_retry=10;
mysql> start root;

12.使用mydumper备份数据库

[root@localhost ~]# mydumper -u root -p MySQL@123 -t 6 -B school -o /data/school

13.使用mydumper恢复数据库

[root@localhost ~]# myloader -u root -p MySQL@123 -d /data/school/ -o -B school

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖