CentOS7上数据库运维---数据库备份

数据库备份，数据库为school，素材如下

创建数据库并指定字符，后边用到中文mysql> create database school DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

1.创建student和score表

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表。SQL代码如下：
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)
);

2.为student表和score表增加记录
向student表插入记录的INSERT语句如下：

INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');

向score表插入记录的INSERT语句如下：

INSERT INTO score VALUES(NULL,901, '计算机',98);
INSERT INTO score VALUES(NULL,901, '英语', 80);
INSERT INTO score VALUES(NULL,902, '计算机',65);
INSERT INTO score VALUES(NULL,902, '中文',88);
INSERT INTO score VALUES(NULL,903, '中文',95);
INSERT INTO score VALUES(NULL,904, '计算机',70);
INSERT INTO score VALUES(NULL,904, '英语',92);
INSERT INTO score VALUES(NULL,905, '英语',94);
INSERT INTO score VALUES(NULL,906, '计算机',90);
INSERT INTO score VALUES(NULL,906, '英语',85);

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

[root@node01 ~]# mkdir backup
[root@node01 ~]# mysqldump -uroot -p -B school > /backup/school01.sql
Enter password:
[root@node01 ~]#
[root@node01 ~]#
[root@node01 ~]# ll /backup/
total 4
-rw-r--r-- 1 root root 3453 Mar 19 22:31 school01.sql

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

[root@node01 ~]# mysqldump --add-drop-table -uroot -p -d school > /backup/drop.sql
Enter password:
[root@node01 ~]# ll /backup/
total 8
-rw-r--r-- 1 root root 2315 Mar 19 22:34 drop.sql
-rw-r--r-- 1 root root 3453 Mar 19 22:31 school01.sql

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

[root@node01 ~]# mysqldump -uroot -p school | gzip > /backup/db.sql.gz
Enter password:
[root@node01 ~]# ll /backup/
total 12
-rw-r--r-- 1 root root 1158 Mar 19 22:35 db.sql.gz
-rw-r--r-- 1 root root 2315 Mar 19 22:34 drop.sql
-rw-r--r-- 1 root root 3453 Mar 19 22:31 school01.sql

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

[root@node01 ~]# mysqldump -uroot -p school student > /backup/student.sql
Enter password:
[root@node01 ~]# ll /backup/
total 16
-rw-r--r-- 1 root root   20 Mar 19 22:37 db.sql.gz
-rw-r--r-- 1 root root 2315 Mar 19 22:34 drop.sql
-rw-r--r-- 1 root root 3453 Mar 19 22:31 school01.sql
-rw-r--r-- 1 root root 2390 Mar 19 22:37 student.sql

7.同时备份多个MySQL数据库(其他数据库素材自行准备)

mysql> create database company;
Query OK, 1 row affected (0.00 sec)

mysql> use company
Database changed


CREATE TABLE `emp`  (
  `empno` int(4) NOT NULL,
  `ename` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `job` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `mgr` int(4) NULL DEFAULT NULL,
  `hiredate` date NOT NULL,
  `sai` int(255) NOT NULL,
  `comm` int(255) NULL DEFAULT NULL,
  `deptno` int(2) NOT NULL,
  PRIMARY KEY (`empno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `emp` VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);
INSERT INTO `emp` VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO `emp` VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO `emp` VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO `emp` VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO `emp` VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO `emp` VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO `emp` VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO `emp` VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO `emp` VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO `emp` VALUES (1011, '周泰', '文员', 1006, '2007-05-23', 11000, NULL, 20);
INSERT INTO `emp` VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO `emp` VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO `emp` VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO `emp` VALUES (1015, '张三', '保洁员', 1001, '2013-05-01', 80000, 50000, 50);

[root@node01 ~]# mysqldump -uroot -p -B school company > /backup/two.sql
Enter password:
[root@node01 ~]# ll /backup/
total 24
-rw-r--r-- 1 root root   20 Mar 19 22:37 db.sql.gz
-rw-r--r-- 1 root root 2315 Mar 19 22:34 drop.sql
-rw-r--r-- 1 root root 3453 Mar 19 22:31 school01.sql
-rw-r--r-- 1 root root 2390 Mar 19 22:37 student.sql
-rw-r--r-- 1 root root 5278 Mar 19 22:42 two.sql

8.仅仅备份数据库结构

[root@node01 ~]# mysqldump -uroot -p -d school > /backup/structure.sql

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

[root@node01 ~]# mysqldump -uroot -p -A> /backup/all.sql

10.还原MySQL数据库

[root@node01 ~]# mysql -uroot -p -e 'source /backup/school01.sql'

11.还原压缩的MySQL数据库

[root@node01 ~]# zcat < /backup/db.sql.gz  | mysql -uroot -p school

12.使用xtrabackup 备份数据库

[root@node01 ~]# innobackupex -uroot -p --no-timestamp /backup/bak_$(date +%F)

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

# 应用日志文件的数据
[root@node01 ~]#  innobackupex -u root -p --apply-log /backup/bak_2023-3-19/
# 停止 mysql
[root@node01 ~]# systemctl stop mysqld
# 删除 mysql 数据目录中的文件
[root@node01 ~]# rm -rf /var/lib/mysql/*
# 恢复数据
[root@node01 ~]# innobackupex --copy-back /backup/bak_2023-3-19/
# 修改目录所属者、所属组
[root@node01 ~]#  chmod -R mysql.mysql /var/lib/mysql/*
# 启动 mysqld
[root@node01 ~]# systemctl start mysql

14.使用mydumper备份数据库

Mydumper安装
# yum -y install glib2-devel mysql-devel zlib-devel pcre-devel # tar xvf mydumper-0.5.1.tar.gz
# cd mydumper-0.5.1/ # cmake .
# make
# make install

[root@node01 ~]# mydumper -uroot  -S /tmp/mysql.sock -B school -o /backup/

15.使用mydumper恢复数据库

[root@localhost ~]# myloader -uroot  -S /tmp/mysql.sock -d /backup/ -o -B school