Mysql Backups

·         The BACKUP statement creates a copy of the definition and data files of a MyISAM table. The RESTORE statement restores the data.

®     The purpose of backup is to recover data from an earlier time.

                                Syntax:

                                                BACKUP TABLE [table_name] to ‘[path]’;

                                Example:

                                                mysql> BACKUP TABLE Department TO ‘d:\\backup_folder';

®     A restore is performed in order to return data to its original condition if files have become damaged, or to copy or move data to a new location.

                                Syntax:

                                                RESTORE TABLE [table_name] from ‘[path]’;

                                Example:

                                                RESTORE TABLE Department FROM ‘d:\\backup_folder';

·         Directly copying the files. You need to do your own locking. Returning the data files to the data directory restores the data.

·         mysqldump, which creates a text file containing the SQL statements needed to regenerate the tables. Using the file as input to the MySQL daemon restores the data.

                                mysqldump

                                                mysql\bin> mysqldump [arguments] > file_name

                                Dump all database

                                                mysql\bin> mysqldump --all-databases > dump.sql

                                Dump specific databases

                                                mysql\bin> mysqldump -u root --databases db1 db2 db3 > dump.sql

                                Dump single database

                                                ysqldump -u root --databases clsu > dump.sql  

                                Dump specific table

                                                mysql\bin > mysqldump -u root clsu college student> dump.sql

·         mysqldump

®     If dump with --databases  create dump with create database and use

®     If without --databases create dump without create databases and use

ü  When reloading dump file specify database name

ü  Or specify different name from original

ü  If no database exist create first

·         mysqlhotcopy

§  Runs only on Unix and NetWare

§  Used for bucking up MyISAM and ARCHIVE

§  Must have select privilege, reload privilege, and lock tables privilege

                                                                shell> mysqlhotcopy db_name [/path/to/new_directory]

                                                                shell> mysqlhotcopy db_name_1 ... db_name_n                                                                                                                              /path/to/new_directory

·         Backing Innodb

§  Two way using mysqldump and copy the file

§  Too copy file shutdown MySQL server make sure no errors

§  Copy all InnoDB data file (ibdata file and .ibd file) to safe place

§  Copy all .frm file

§  Copy all InnoDB log file

§  Copy you’re my.cnf configuration

·         Recover Innodb

§  Run Mysql server with binary log on defore taking the buckup

§  To achieve point-in-time recovery you can apply changes from binary log

§  To recover from mysql crash just restart it and the Innodb automatically logs and perform rolls back up to present

·         Using SELECT INTO statements create a text file that can be used to restore the data with the LOAD DATA command or the mysqlimport utility.

·         Using the mysqlhotcopy utility. This is a Perl script that copies the data files to another directory. Returning the data files to the data directory restores the data.

·         mysqldump, which creates a text file containing the SQL statements needed to regenerate the tables. Using the file as input to the MySQL daemon restores the data.