1、创建数据库school
mysql> create database school default character set utf8mb4;
创建student和score表
创建student表。SQL代码如下： 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@host1 /]# mkdir -p /backup [root@host1 /]# mysqldump -uroot -p'MySQL@123' school -B > /backup/school.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@host1 /]# cd backup/ [root@host1 backup]# ls school.sql
4.备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库
--add-drop-table
[root@host1 backup]# mysqldump -uroot -p'MySQL@123' -B school --add-drop-table > school1.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.
5.直接将MySQL数据库压缩备份
压缩备份school数据库 [root@host1 backup]# mysqldump -uroot -p'MySQL@123' -B school | gzip > school.sql.gz mysqldump: [Warning] Using a password on the command line interface can be insecure. ? 所有压缩备份 [root@host1 backup]# mysqldump -uroot -p'MySQL@123' -A -B | gzip > mysql.sql.gz mysqldump: [Warning] Using a password on the command line interface can be insecure.
6.备份MySQL数据库某个(些)表。此例备份student表
[root@host1 backup]# mysqldump -uroot -p'MySQL@123' school student > school_student.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.
7.同时备份多个MySQL数据库（其他数据库素材自行准备）
[root@host1 backup]# mysqldump -uroot -p'MySQL@123' student -B db -B > stu_db.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. 或者 [root@host1 backup]# mysqldump -uroot -p'MySQL@123' student db -B ? > stu_db1.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.
8.仅仅备份数据库结构
-A备份所有数据库，-B会将创建数据库语句也备份，-d 用来备份结构
备份数据库school [root@host1 backup]# mysqldump -uroot -pMySQL@123 -B school -d > school.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. ? 备份所有数据库 [root@host1 backup]# mysqldump -uroot -pMySQL@123 -A -B -d > mysql1.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.
9.备份服务器上所有数据库
[root@host1 backup]# mysqldump -uroot -p'MySQL@123' -A > mysql.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.
10.还原MySQL数据库
mysql> source /backup/mysql.sql 或者 [root@host1 backup]# mysql -uroot -p'MySQL@123' < /backup/mysql.sql
11.还原压缩的MySQL数据库
[root@host1 backup]# zcat /backup/school.sql.gz | mysql -uroot -pMySQL@123
?12.使用xtrabackup 备份数据库
[root@host1 ~]# innobackupex -u root -p MySQL@123 /backup/mysql/all ? [root@host1 all]# innobackupex -u root -p MySQL@123 --no-timestamp /backup/mysql/all/
13.在另外的数据库服务器上还原xtrabackup 备份
[root@host1 all]# innobackupex --apply-log /backup/mysql/all/ [root@host1 all]# systemctl stop mysqld [root@host1 all]# rm -rf /var/lib/mysql/* [root@host1 all]# innobackupex --copy-back /backup/mysql/all/ [root@host1 mysql]# chown -R mysql.mysql /var/lib/mysql [root@host1 mysql]# ll /var/lib/mysql total 77864 drwxr-x---. 2 mysql mysql ? ? ? 20 May 12 23:06 @6570@636e@5e93@540d drwxr-x---. 2 mysql mysql ? ? ? 94 May 12 23:06 db drwxr-x---. 2 mysql mysql ? ? 4096 May 12 23:06 db1 drwxr-x---. 2 mysql mysql ? ? ? 92 May 12 23:06 db2 -rw-r-----. 1 mysql mysql ? ? 508 May 12 23:06 ib_buffer_pool -rw-r-----. 1 mysql mysql 79691776 May 12 23:06 ibdata1 drwxr-x---. 2 mysql mysql ? ? ? 51 May 12 23:06 ll drwxr-x---. 2 mysql mysql ? ? 4096 May 12 23:06 mysql drwxr-x---. 2 mysql mysql ? ? 8192 May 12 23:06 performance_schema drwxr-x---. 2 mysql mysql ? ? ? 92 May 12 23:06 school drwxr-x---. 2 mysql mysql ? ? ? 86 May 12 23:06 student drwxr-x---. 2 mysql mysql ? ? 8192 May 12 23:06 sys -rw-r-----. 1 mysql mysql ? ? 485 May 12 23:06 xtrabackup_info [root@host1 mysql]# systemctl start mysqld
?14.使用mydumper备份数据库
[root@host1 backup]# mydumper -u root -p 'MySQL@123' -B school -o /backup/ [root@host1 backup]# ls metadata ? ? ? ? ? ? ? ? school.score.00000.sql school.score-schema.sql ? school.student-metadata school-schema-create.sql school.score-metadata ? school.student.00000.sql school.student-schema.sql
15.使用mydumper恢复数据库
[root@host1 backup]# myloader -u root -p 'MySQL@123' -d /backup/ -B school -o
