Shulou(Shulou.com)06/01 Report--

This article mainly introduces the detailed explanation of the backup and restore of mysql under the windows system, the things involved, learned from the theoretical knowledge, there are many books and documents for your reference, from a practical point of view, accumulated years of practical experience can be shared with you.

Windows mysql backup and restore

Step 1: back up the suppliers table to the file C:\ bktestdir\ suppliers_bk.sql using the mysqldump command.

Mysqldump-u root-p test suppliers > C:\ bktestdir\ suppliers_bk.sql

Step 2: restore the suppliers table to the test database using the mysql command

DELETE FROM suppliers

Source C:/bktestdir/suppliers_bk.sql

Step 3: use SELECT … The INTO OUTFILE statement exports the records in the suppliers table. The exported file is located in the directory C:\ bktestdir and is named suppliers_out.txt.

SELECT * FROM test.suppliers INTO OUTFILE "C:/bktestdir/suppliers_out.txt"

FIELDS

TERMINATED BY','

ENCLOSED BY'\ "'

LINES

STARTING BY'\ r\ n'

Step 4: import suppliers_out.txt data into the suppliers table using the LOAD DATA INFILE statement.

LOAD DATA INFILE'Clux INTO TABLE test.suppliers btestdirmax suppliers

FIELDS

TERMINATED BY','

ENCLOSED BY'\ "'

LINES

STARTING BY'\ r\ n'

Step 5: use the musqldump command to export records from the suppliers table to the file C:\ bktestdir\ suppliers_html.html.

Mysql-u root-p-- html-- execute= "SELECT * FROM suppliers;" test > C:/bktestdir/suppliers_html.html

Linux mysql backup and restore

Backup operation

(1) Export some tables in the specified library

Mysqldump [option] library name [table name 1] [table name 2]. > / backup path / backup file name

(2) Export one or more complete libraries (including all tables in them)

Mysqldump [option]-- databases library name 1 [library name 2]. > / backup path / backup file name

(3) back up all libraries in mysql CVM

Mysqldump [options]-- opt-- all-databases > / backup path / backup file name

Restore operation

Mysql [option] [Library name] [Table name]

< /备份路径/备份文件名 举例： 随便找一个数据库，创建张表插入数据 备份(-u 指定登录用户 -p 数据库名 表名) 备份名称 mysqldump -u root -p dingxue student >

Dingxue.sql

Enter password:****

Restore (restore the table directly to the specified database)

Mysql-u root-p dingxue

< dingxue.sql /***************************样例表***************************/ CREATE DATABASE booksDB; user 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); /***************************样例表***************************/ 【例14.1】使用mysqldump命令备份数据库中的所有表 mysqldump -u root -p booksdb >

C:/backup/booksdb_20110101.sql

[example 14.2] back up the books table in booksDB database

Mysqldump-u root-p booksDB books > C:/backup/books_20110101.sql

Using mysqldump to back up booksDB and test databases

Mysqldump-u root-p-- databases booksDB test > C:\ backup\ books_testDB_20110101.sql

[example 14.4] use mysqldump to back up all databases in the server

Mysqldump-u root-p-- all-databases > C:/backup/alldbinMySQL.sql

[example 14.5] use mysqlhotcopy to back up test database to / usr/backup directory

Mysqlhotcopy-u root-p test / usr/backup

Use the mysql command to import the backup from the C:\ backup\ booksdb_20110101.sql file into the database

Mysql-u root-p booksDB

< C:/backup/booksdb_20110101.sql 【例14.7】使用root用户登录到服务器，然后使用souce导入本地的备份文件booksdb_20110101.sql use booksdb; source C:/backup/booksdb_20110101.sql 【例14.8】从mysqlhotcopy拷贝的备份恢复数据库 cp -R /usr/backup/test usr/local/mysql/data 【例14.9】将www.abc.com主机上的MySQL数据库全部迁移到www.bcd.com主机上。在www.abc.com主机上执行的命令如下： mysqldump -h www.bac.com -uroot -ppassword dbname | mysql -hwww.bcd.com -uroot -ppassword 【例14.10】使用SELECT...INTO OUTFILE将test数据库中的person表中的记录导出到文本文件 SELECT * FROM test.person INTO OUTFILE "C:/person0.txt"; 【例14.11】使用SELECT...INTO OUTFILE将test数据库中的person表中的记录导出到文本文件，使用FIELDS选项和LINES选项，要求字段之间使用逗号','间隔，所有字段值用双引号括起来，定义转义字符定义为单引号'\'' SELECT * FROM test.person INTO OUTFILE "C:/person1.txt" FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\'' LINES TERMINATED BY '\r\n'; 【例14.12】使用SELECT...INTO OUTFILE将test数据库中的person表中的记录导出到文本文件，使用LINES选项，要求每行记录以字符串">

"start, end with the"string"

SELECT * FROM test.person INTO OUTFILE "C:/person2.txt"

LINES

STARTING BY'>'

TERMINATED BY''

SELECT * FROM test.person INTO OUTFILE "C:/person2.txt"

LINES

STARTING BY'>'

TERMINATED BY'\ r\ n'

Use mysqldump to export records from the person table in the test database to a text file

Mysqldump-T CRV / test person-u root-p

[example 14.14] use the mysqldump command to export records from the person table in the test database to a text file, use the FIELDS option, require a comma', 'interval between fields, enclose the field values of all character types in double quotes, define the escape character as a question mark'?', and each line of record ends with the carriage return newline "\ r\ n".

Mysqldump-TC:\ backup test person-u root-p-- fields-terminated-by=,-- fields-optionally-enclosed-by=\ "--fields-escaped-by=?-- lines-terminated-by=\ r\ n

[example 14.15] use MySQL statement to export records from person table in test database to a text file

Mysql-u root-p-- execute= "SELECT * FROM person;" test > C:\ person3.txt

Use the mysql command to export the records from the person table in the test database to a text file, and use the-- vertical parameter to display the results

Mysql-u root-p-- vertical-- execute= "SELECT * FROM person;" test > C:\ person4.txt

[example 14.17] use the mysql command to export records from the person table in the test database to a html file

Mysql-u root-p-- html-- execute= "SELECT * FROM person;" test > C:\ person5.html

[example 14.18] use the mysql command to export records from the person table in the test database to a xml file

Mysql-u root-p-- xml-- execute= "SELECT * FROM person;" test > C:\ person6.xml

Use the LOAD DATA command to import the data from the C:\ person0.txt file into the person table in the test database

LOAD DATA INFILE'Cposition person0.txt' INTO TABLE test.person

[example 14.20] use the LOAD DATA command to import the data from the C:\ person1.txt file into the person table in the test database, use the FIELDS option and the LINES option, require a comma', 'interval between fields, enclose all field values in double quotes, and define the escape character as single quotation mark'\'.

LOAD DATA INFILE'Cposition person1.txt' INTO TABLE test.person

FIELDS

TERMINATED BY','

ENCLOSED BY'\ "'

ESCAPED BY'\'

LINES

TERMINATED BY'\ r\ n'

Use the mysqlimport command to import the contents of the person.txt file under the C:\ backup directory into the test database. The fields are separated by commas, the field values of the character types are enclosed in double quotes, the escape characters are defined as question marks, and each line of record ends with the carriage return newline character "\ r\ n".

Mysqlimport-u root-p test C:/backup/person.txt-- fields-terminated-by=,-- fields-optionally-enclosed-by=\ "--fields-escaped-by=?-- lines-terminated-by=\ r\ n

Read the above windows system mysql backup and restore detailed introduction, I hope it can bring some help to everyone in the practical application. Due to the limited space in this article, there will inevitably be deficiencies and areas that need to be supplemented. You can continue to pay attention to the industry information section and will update your industry news and knowledge regularly. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.