Shulou(Shulou.com)05/31 Report--

This article mainly explains the "mysql backup and recovery commands", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "mysql backup and recovery commands" bar!

Under the windows server, mysql backup and recovery we often use the mysqldump command to complete these operations. Let me summarize the usage of the mysqldump backup and restore command in detail.

If mysql is installed on c: disk, the user name of mysql is root, the password is 123456, the database name is database_name, the backup database is stored under the root directory of d: disk, and the backup database name is backup20070713.sql (20070713.sql is the backup date)

Back up the database:

Dump uses a SQL-level backup mechanism, which exports data tables into SQL script files, which is relatively appropriate when upgrading between different MySQL versions, which is also the most commonly used backup method.

The code is as follows: www.2cto.com

Mysqldump-uroot-p123456 database_name > d:/backup20070713.sql

Above windows/ and below is linux.

I usually use the following SQL to back up the MyISAM table:

The code is as follows

/ usr/local/mysql/bin/mysqldump-uyejr-pyejr

-default-character-set=utf8-opt-extended-insert=false

-- triggers-R-- hex-blob-x db_name > db_name.sql

Use the following SQL to back up the Innodb table:

The code is as follows

/ usr/local/mysql/bin/mysqldump-uyejr-pyejr

-default-character-set=utf8-opt-extended-insert=false

-- triggers-R-- hex-blob-- single-transaction db_name > db_name.sql

In addition, if you want to implement online backup, you can also use the-- master-data parameter, as follows:

The code is as follows

/ usr/local/mysql/bin/mysqldump-uyejr-pyejr

-default-character-set=utf8-opt-master-data=1

-- single-transaction-- flush-logs db_name > db_name.sql

Restore the database:

Delete the original database, establish the database, and import the backup database.

The code is as follows

Mysqladmin-uroot-p123456 drop database_name

Mysqladmin-uroot-p123456 create database_name

Mysql-uroot-p123456 database_name

The file backed up with mysqldump is a SQL script that can be poured directly, and there are two ways to import the data.

Directly use mysql client www.2cto.com

For example:

The code is as follows

/ usr/local/mysql/bin/mysql-uyejr-pyejr db_name

< db_name.sql 用 SOURCE 语法 其实这不是标准的 SQL 语法，而是 mysql 客户端提供的功能，例如： 代码如下 SOURCE /tmp/db_name.sql; 这里需要指定文件的绝对路径，并且必须是 mysqld 运行用户(例如 nobody)有权限读取的文件。 注：在导入备份数据库前，database_name如果没有，是需要创建的;而且与backup20070713.sql中数据库名是一样的才能导入。 mysqldump(数据导出工具) mysqldump options db_name[table_name]//备份单个数据库 mysqldump 选项 --database database-name1 [databases-name2]....//备份指定的数据库一个或者多个 mysqldump 选项 --all-database //备份所有的数据库 链接选项: -u :指定用户名 -p:指定密码 -h:指定服务器ip或者域名 -P(大写):指定端口 eg:/usr/bin/mysqldump -u root -h 202.194.132.237 -P 3306 -p BBS user>

/ home/wuxiaoxiao/user.txt

Output options:

-- add-drop-database: each database creation statement is preceded by a drop database statement

-- add-drop-table: each table creation statement is preceded by a drop table statement

-n: does not contain database creation statements

-t: create statements that do not contain data tables

-d: does not contain data

Output format option: www.2cto.com

-- compact: make the output concise

-c-- compact-insert: causes the insert statement in the output file to contain the field name

-T: back up the data in the database table into two files: simple data text and table sql

-- fields-terminated-by=name (domain separator)

-- fields-enclosed-by=name (domain reference)

-- fields-optionally-enclosed-by=name (domain optional references)

-- fields-escaped-by=name (transfer character)

Eg:/usr/bin/mysqldump-u root-h 202.194.132.237-P 3306-p BBS user-T. / bak

Character set options:

-- default-character-set=name: sets the exported client character set

Eg:mysql-u root-p-- compact-- default-character-set=utf8 BBS user > test.txt

Thank you for your reading, the above is the content of "mysql backup and recovery command", after the study of this article, I believe you have a deeper understanding of the command of mysql backup and recovery, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.