学无止境·MySQL⑥（数据库备份和还原）

备份和还原练习

1、创建库和表

CREATE DATABASE booksDB;
use booksDB;

CREATE TABLE books
(
  bk_id  INT NOT NULL PRIMARY KEY,
  bk_title VARCHAR(50) NOT NULL,
  copyright YEAR NOT NULL
);
INSERT INTO books
VALUES (11078, 'Learning MySQL', 2010),
(11033, 'Study Html', 2011),
(11035, 'How to use php', 2003),
(11072, 'Teach youself javascript', 2005),
(11028, 'Learing C++', 2005),
(11069, 'MySQL professional', 2009),
(11026, 'Guide to MySQL 5.5', 2008),
(11041, 'Inside VC++', 2011);

CREATE TABLE authors
(
  auth_id     INT NOT NULL PRIMARY KEY,
  auth_name  VARCHAR(20),
 auth_gender CHAR(1)
);
INSERT INTO authors  
VALUES (1001, 'WriterX' ,'f'),
(1002, 'WriterA' ,'f'),
(1003, 'WriterB' ,'m'),
(1004, 'WriterC' ,'f'),
(1011, 'WriterD' ,'f'),
(1012, 'WriterE' ,'m'),
(1013, 'WriterF' ,'m'),
(1014, 'WriterG' ,'f'),
(1015, 'WriterH' ,'f');

CREATE TABLE authorbook
(
  auth_id  INT NOT NULL,
  bk_id   INT NOT NULL,
  PRIMARY KEY (auth_id, bk_id),
  FOREIGN KEY (auth_id) REFERENCES authors (auth_id),
  FOREIGN KEY (bk_id) REFERENCES books (bk_id)
);

INSERT INTO authorbook
VALUES (1001, 11033), (1002, 11035), (1003, 11072), (1004, 11028),
(1011, 11078), (1012, 11026), (1012, 11041), (1014, 11069);

2、使用mysqldump命令备份数据库中的所有表

mysqldump -uroot -p booksDB > /backup/db/booksDB.sql

3、备份booksDB数据库中的books表

mysqldump -uroot -p booksDB books > /backup/db/bDB_books.sql

4、使用mysqldump备份booksDB和test数据库

mysqldump -uroot -p --databases booksDB test > /backup/db/bDB_test.sql

5、使用mysqldump备份服务器中的所有数据库

mysqldump -uroot -p -A > /backup/db/All_db.sql

6、使用mysql命令还原第二题导出的book表

首先取消外键
alter table authorbook drop foreign key authorbook_ibfk_2;
需要用 show create table authorbooks，去查看books表对应的外键名是什么
再删表
drop table books;
最后使用mysqldump命令还原
mysql -uroot -p -D booksDB< /backup/db/bDB_books.sql

7、进入数据库使用source命令还原第二题导出的book表

先删表
mysql -uroot -p -e ‘drop table booksDB.books’

source还原
mysql -uroot -p -D booksDB -e ‘source /backup/db/bDB_books.sql’