The BACKUP and RESTORE commands

BACKUP

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

Syntax:

BACKUP TABLE [table_name] TO ‘[path]’;
Example:
BACKUP TABLE College TO ‘D:\\New’;

RESTORE

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 College FROM ‘D:\\New';

Other syntax:

To backup using mysqldump:

Mysqldump –u root –p[password] [database_name] > [path];

Mysqldump –u root clsu > D:\\New;

To restore:

Mysql –u root –p [password] [database_name] < [path];

SELECT INTO

Select form that enables a query to be written to a file.

Types of select ...into:

v  select … into outfile

o   Writes a selected row to a file

v  select … into dumpfile

o   writes a single row to a file

v  select … into var_list

o   selects column values and into variables

LOAD DATA INFILE

It is use to read the file back to the table

mysqldump

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

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

o   When reloading dump file specify database name

o   Or specify different name from original

o   If no database exist create first

mysqldump examples:

mysql\bin> mysqldump [arguments] > file_name

v  Dump all database

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

v  Dump specific databases

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

v  Dump single database

o   mysql\bin> mysqldump -u root --databases clsu > dump.sql

Example:
mysqldump -u root --databases clsu > D:\\New\clsu.sql

v  OK omit --databases in single database

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

v  Dump specific table

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

Example:
mysqldump -u root clsu college > college.sql

mysqlhotcopy

v  Runs only on Unix and NetWare

v  Used for bucking up MyISAM and ARCHIVE

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

Backing Innodb

v  Two way using mysqldump and copy the file

o   Too copy file shutdown MySQL server make sure no errors

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

o   Copy all .frm file

o   Copy all InnoDB log file

o   Copy you’re my.cnf configuration

Recover Innodb

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

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

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