前言

本环境是基于 Centos 7.8 系统构建MySQL-5.7.14
具体构建，请参考 MySQL-5.7.14 环境构建

素材准备

# 1.创建student和score表
# 创建school数据库create database schoolCREATE  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);

备份数据

	student库：mysql> select database();+------------+| database() |+------------+| school     |+------------+1 row in set (0.00 sec)score表：mysql> desc score;+--------+-------------+------+-----+---------+----------------+| Field  | Type        | Null | Key | Default | Extra          |+--------+-------------+------+-----+---------+----------------+| id     | int(10)     | NO   | PRI | NULL    | auto_increment || stu_id | int(10)     | NO   |     | NULL    |                || c_name | varchar(20) | YES  |     | NULL    |                || grade  | int(10)     | YES  |     | NULL    |                |+--------+-------------+------+-----+---------+----------------+4 rows in set (0.00 sec)student表：mysql> desc student;+------------+-------------+------+-----+---------+-------+| Field      | Type        | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| id         | int(10)     | NO   | PRI | NULL    |       || name       | varchar(20) | NO   |     | NULL    |       || sex        | varchar(4)  | YES  |     | NULL    |       || birth      | year(4)     | YES  |     | NULL    |       || department | varchar(20) | YES  |     | NULL    |       || address    | varchar(50) | YES  |     | NULL    |       |+------------+-------------+------+-----+---------+-------+6 rows in set (0.00 sec)2.为student表和score表增加记录score表：mysql> select * from score;+----+--------+-----------+-------+| id | stu_id | c_name    | grade |+----+--------+-----------+-------+|  1 |    901 | 计算机    |    98 ||  2 |    901 | 英语      |    80 ||  3 |    902 | 计算机    |    65 ||  4 |    902 | 中文      |    88 ||  5 |    903 | 中文      |    95 ||  6 |    904 | 计算机    |    70 ||  7 |    904 | 英语      |    92 ||  8 |    905 | 英语      |    94 ||  9 |    906 | 计算机    |    90 || 10 |    906 | 英语      |    85 |+----+--------+-----------+-------+10 rows in set (0.00 sec)student表：mysql> select * from student;+-----+-----------+------+-------+--------------+--------------------+| id  | name      | sex  | birth | department   | address            |+-----+-----------+------+-------+--------------+--------------------+| 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       || 902 | 张老二    | 男   |  1986 | 中文系       | 北京市昌平区       || 903 | 张三      | 女   |  1990 | 中文系       | 湖南省永州市       || 904 | 李四      | 男   |  1990 | 英语系       | 辽宁省阜新市       || 905 | 王五      | 女   |  1991 | 英语系       | 福建省厦门市       || 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       |+-----+-----------+------+-------+--------------+--------------------+6 rows in set (0.00 sec)--- 开始备份3.备份数据库school到/backup目录 [root@mysql-yum ~]# mysqldump -uroot -p123456 -B school > /backup/school_db.sql[root@mysql-yum ~]# ll /backup/school_db.sql -rw-r--r-- 1 root root 3447 Jan 31 19:48 /backup/school_db.sql4.备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库 [root@mysql-yum backup]# mysqldump -uroot -p123456 -B mysql > /backup/mysql.sql[root@mysql-yum backup]# ll /backup/mysql.sql -rw-r--r-- 1 root root 772565 Jan 31 19:57 /backup/mysql.sql5.直接将MySQL数据库压缩备份 [root@mysql-yum backup]# mysqldump -uroot -p123456 -B mysql | gzip > /backup/mysql.sql.gz[root@mysql-yum backup]# ll /backup/mysql.sql.gz -rw-r--r-- 1 root root 207823 Jan 31 19:59 /backup/mysql.sql.gz6.备份MySQL数据库某个(些)表。此例备份student表mysql.user:[root@mysql-yum backup]# mysqldump -uroot -p123456 mysql user > /backup/mysql_user.sql[root@mysql-yum backup]# ll //backup/mysql_user.sql-rw-r--r-- 1 root root 5906 Jan 31 20:02 //backup/mysql_user.sqlschool.student:[root@mysql-yum backup]# mysqldump -uroot -p123456 school student > /backup/school_student.sql[root@mysql-yum backup]# ll /backup/school_student.sql-rw-r--r-- 1 root root 2384 Jan 31 20:04 /backup/school_student.sql7.同时备份多个MySQL数据库（其他数据库素材自行准备） [root@mysql-yum backup]# mysqldump -uroot -p123456 -B db1 db2 school > /backup/db1_db2_school.sql[root@mysql-yum backup]# ll /backup/db1_db2_school.sql-rw-r--r-- 1 root root 9506 Jan 31 20:06 /backup/db1_db2_school.sql8.仅仅备份数据库结构 [root@mysql-yum backup]# mysqldump -uroot -p123456 -B school -d > /backup/school_db_nodata.sql[root@mysql-yum backup]# ll /backup/school_db_nodata.sql-rw-r--r-- 1 root root 2452 Jan 31 20:08 /backup/school_db_nodata.sql9.备份服务器上所有数据库 [root@mysql-yum backup]# mysqldump -uroot -p123456 -A > /backup/all_db_data.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[root@mysql-yum backup]# ll /backup/all_db_data.sql-rw-r--r-- 1 root root 788026 Jan 31 20:09 /backup/all_db_data.sql10.还原MySQL数据库 [root@mysql-yum backup]# mysql -uroot -p123456 < /backup/mysql.sql11.还原压缩的MySQL数据库 [root@mysql-yum backup]# zcat /backup/mysql.sql.gz | mysql -uroot -p12345612.使用xtrabackup 备份数据库 [root@mysql-yum ~]# innobackupex -u root -p 123456 --no-timestamp /backup/mysql/all_db_`date +%F`13.在另外的数据库服务器上还原xtrabackup 备份 [root@mysql-yum ~]# systemctl stop mysqld[root@mysql-yum ~]# cd /var/lib/mysql[root@mysql-yum mysql]# rm -rf *[root@mysql-yum mysql]# innobackupex -u root -p 123456 --apply-log /backup/mysql/all_db_2021-01-31/[root@mysql-yum mysql]# innobackupex -u root -p 123456 --copy-back /backup/mysql/all_db_2021-01-31/[root@mysql-yum mysql]# chown -R mysql.mysql /var/lib/mysql[root@mysql-yum mysql]# systemctl start mysqld14.使用mydumper备份数据库 [root@mysql-yum ~]# mydumper -u root -p 123456 -B school -o /back/mysql/[root@mysql-yum ~]# ll /back/mysqltotal 24-rw-r--r-- 1 root root 136 Jan 31 22:58 metadata-rw-r--r-- 1 root root  65 Jan 31 22:58 school-schema-create.sql-rw-r--r-- 1 root root 331 Jan 31 22:58 school.score-schema.sql-rw-r--r-- 1 root root 352 Jan 31 22:58 school.score.sql-rw-r--r-- 1 root root 377 Jan 31 22:58 school.student-schema.sql-rw-r--r-- 1 root root 503 Jan 31 22:58 school.student.sql15.使用mydumper恢复数据库[root@mysql-yum ~]# myloader -u root -p 123456 -B school -o -d /back/mysql