Database Backup/Recovery

Database Backup

Basic database dump.

mysqldump -u root -p  mysqldb > afile.text

 /usr/local/mysql/bin/mysqldump -u root -p --add-locks --extended-insert --all-databases > database_dump

and try adding "--add-drop-database", only work on mysql db version 4.1.31

Single Database backup example

mysqldump -h 10.1.1.205 -u theuser -p --add-locks --extended-insert --add-drop-database thedbtodump > thedbtodump_20100923.mysql

Recovery

To recover from this file, I needed to delete the first part of the file, it is info about the mysql internal database.  Then I was able to load the rest of the file and thus all databases.

-- MySQL dump 9.11
--
-- Host: localhost    Database:
-- ------------------------------------------------------
-- Server version       4.0.20-standard

--
-- Current Database: mysql
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ mysql;

USE mysql;

Delete up to the next, but not including the next "CREATE DATABASE" statement.

--
-- Current Database: adatabase
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ adatabase;

Then save the file, and you should be able to use the following statement to load the database in.

mysql < the_data_base_dump_file.mysql

Here is a VI Macro to create a drop line out of a CREATE line in a dump file, then go and do another find (ie so you have to of already done a find on CREATE for it to find another one).

yyP$r;0dwiDROP \ESCn

Note: Replace \ESC with an escape character, you can do this by entering insert mode and then doing Ctrl-V Esc

example

CREATE TABLE ac_table (

would be changed to

DROP TABLE ac_table;
CREATE TABLE ac_table (

This helped me reload a database, without having to drop the whole database.

Exporting Data

You can export select fields out of the database. You can use the select command to export a select part of a database table.  Here is an example that will export the database in a CSV format.

*Note* The user must have the XXX privliage to read and write os files.

Example select statement that would export data.

SELECT * 
FROM test_table
WHERE user = 'my_user_id'
INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Note, if you do not specify output options mysql will dump a tab delimited file.

Links

Load a file from mysql

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Another example of creating and export file in mysql

http://forums.mysql.com/read.php?79,11324,13062#msg-13062

Using MySQL dump

http://www.linuxcommand.org/man_pages/mysqldump1.html