[MySQL] Backing up data

There are several ways to backing up data
1) Logical backup

* SQL dumps

#mysqldump dbname tblname

Not suitable for huge backup. Both table structure and the data are stored together.(option available)

* Delimited file backups
backing up :

mysql> SELECT * INTO OUTFILE '/tmp/t1.txt'
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> FROM test.t1

restore :

mysql> LOAD DATA INFILE '/tmp/t1.txt'
    -> INTO TABLE test1.t1
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';

*parallel dump: maatkit(mk-parallel-dump)

2) File system snapshot (LVM)
not covered here.

mysql backup/dump/export table schema, definition

1) use mysqldump

server# mysqldump -uroot -p dbname tblname -d 

with this method you will get sql statement. You can use this for backing up table definition as well.

2) create table from another table

mysql> create table if not exists tblname like old_tblname;

with this method you get a copy of table. It is useful if you want to have a another table with the same structure for backing up, testing.