/***************************样例表***************************/
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);
/***************************样例表***************************/
1、使用mysqldump命令备份数据库中的所有表
[root@localhost ~]# mysqldump -uroot -p123 test > ./test.sql;
2、备份booksDB数据库中的books表
[root@localhost ~]# mysqldump -uroot -p123 booksDB books > ./booksDB_books.sql;
3、使用mysqldump备份booksDB和test数据库
[root@localhost ~]# mysqldump -uroot -p123 --databases booksDB test > ./booksDB_test.sql;
4、使用mysqldump备份服务器中的所有数据库
[root@localhost ~]# mysqldump -uroot -p123 -A > all_D.sql;
5、使用mysql命令还原第二题导出的book表
[root@localhost ~]# mysql -uroot -p123 --database=booksDB < booksDB_books.sql;
6、进入数据库使用source命令还原第二题导出的book表
[root@localhost ~]# mysql -uroot -p123 -e "source booksDB_books.sql"
或责进入数据库中直接使用source命令:
source 备份的文件路径。