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

How to achieve Mysql backup and recovery, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Logical backup:

1.mysqldump (data Export tool)

Mysqldump options db_ name [table _ name] / / back up a single database

Mysqldump option-- database database-name1 [databases-name2].... / / back up one or more specified databases

Mysqldump option-- all-database / / back up all databases

Link options:

-u: specify the user name

-p: specify password

-h: specify server ip or domain name

-P (uppercase): designated port

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 options:

-- 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

Other options:

-F: refresh the log before backup

-l: put a read lock on all tables (used during backup to keep the backed up data consistent)

[@ more@]

Backup:

Back up all databases:

Mysqldump-u root-p-- all-database > test.sql

Backup database test

Mysqldump-u root-p test > test.sql

Back up the temp table under database test:

Mysqldump-u root-p test demp > test.sql

All tables under the backup database are comma-separated text, backed up to / temp

Mysqldump-u root-p test-T / temp-- fields-terminated-by','

Full recovery:

Msyql-u root-p

< bakfile 注意:将备份恢复后数据并不完整,还需要将备份后执行的日志进行重做 mysqlbinlog binlog-file | mysql -u root -p*** 举个完整的mysqldump备份和恢复的例子: 上午9点备份数据库 mysqldump -u root -p -l -F test >

Test.dmp

The backup is finished at 09:30, and then I want to insert data into the database.

The database suddenly failed at 10:00 and the data could not be accessed. Backup needs to be restored.

Mysql-u root-p test

< test.dmp 恢复后的数据并不完整,9点半插入的数据并没有恢复 使用mysqlbinlog恢复自mysqldump备份以来的binlog mysqlbinlog binlogfilename | mysql -u root -p test 基于时间点恢复: 如果上午10点发生了误操作.可以用下面语句进行备份和binlog将数据库恢复到故障前: mysqlbinlog --stop-date="2005-04-20 9:59:59" binlogfile | mysql -u root -p test 跳过故障的时间点,继续执行后面的binlog,完成恢复 mysqlbinlog --start-date="2005-04-20 9:59:59" binlogfile | mysql -u root -p test 基于位置恢复: mysqlbinlog --start-date="2005-04-20 9:55:59" --stop-date="2005-04-20 10:05:00" binlogfile >

Test.sql

Look at this file to find out the location number before and after the error statement, such as 368312 368315

Mysqlbinlog-- stop-position= "368312" binlogfile | mysql-u root-p test

Mysqlbinlog-- start-position= "368315" binlogfile | mysql-u root-p test

Import and export of tables:

Export:

Mysqldump-u username-p-T target_dir dbname tablename [options]

Options:

-- 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)

All tables under the backup database are comma-separated text, backed up to / temp

Mysqldump-u root-p-T / temp test-- fields-terminated-by','--fields-optionally-enclosed-by'"'

Import:

Msyqlimport-u root-p [LOCAL] dbname order_tab.txt [options]

OPTIONS:

-- 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:mysqlimport-u root-p test order.txt-- fields-terminated-by=','-- fields-enclosed-by=' "'

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.