MySQL Backup

BACKUP

1. Single table backup(work, produce .frm & .MYD)
mysq>use chian;
mysql>backup table cec to 'c:/db_backup';

2.Backup single database (work, NO " " & ; )
(contain SQL statements needed to create the table and populate the table in a new database server )

C:\mysql\bin>mysqldump -u chian -penunst os > c:/db_backup/os_backup.sql

3.Multiple database backup
C:/mysql/bin> mysqldump -u chian -p w3e3 --databases Cust, order> multibackup.sql

4. Backup all database in server (work, NO ; )
C:/mysql/bin>mysqldump --all-databases> c:/db_backup/alldb.sql

5. Real data backup (work)
mysql>select * from cec into outfile 'c:/db_backup/bcec.txt'

FFIELDS TERMINATED BY ','
ENCLOSED BY ' " '
LINES TERMINATED BY '\n'

result: "1","Tech-Recipes sock puppet","14.95"

6. Full database backup
-simply copy all table files (`*.frm', `*.MYD', and `*.MYI' files)

7. Backup all database and compress file (use pipe)
mysqldump --all-databases | bzip2 -c >databasebackup.sql.bz2

8.Real data backup
mysql> select * into outfile 'c:/db_backup/bcec.txt' from cec;

RESTORE

1. Real data backup
mysql> select * into outfile 'c:/db_backup/bcec.txt' from cec;

2.Restore real data back into table
mysql> LOAD DATA INFILE 'c:/db_backup/bcec.txt' Replace INTO TABLE cec
-> LINES TERMINATED BY '\r\n';

3.Concatenate 2 backup
mysql>cat backup1.sql backup.sql | mysql -u chian -p enunst

Extra Info

Copy an existing MySQL table to a new table

CREATE TABLE new_table LIKE production.recipes;
INSERT new_table SELECT * FROM production.recipes;