Database BackUps

BACKUP AND RESTORE COMMANDS

What is Backup?

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';

What is Restore?

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';

Other syntax:

To backup using mysqldump:

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

To restore:

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

MYSQLDUMP

The Syntax:

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

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

•      OK omit --databases in single database

mysql\bin> mysqldump -u root 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

Dump all database

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

b. mysql\bin> mysqldump –u root --all-databases > dump.sql

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

Dump specific databases

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

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

c. mysql\bin> mysqldump db1 db2 db3 > dump.sql

Dump single database

a. mysql\bin> mysqldump --databases clsu > dump.sql

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

c. mysql\bin> mysqldump clsu > dump.sql

Which one is correct

a. without --databases no create databases and use

b. with --databases no create databases and use

c .without --databases has create databases and use

What is backing up the mysqlhotcopy

a. ARCHIVE

b. MyISAM

c. MyISAM and ARCHIVE

BACKUP BY DIRECTLY COPYING FILES

Backing up

Backing up databases can also be done by directly copying files.

This can work with MyISAM tables and only applicable in this table.

MyISAM tables

MyISAm tables are stored as files

            .MYD

            .MYI

            .FRM

Lock and Flush

Does not directly lock tables unlike on backup

flush the tables to make sure any unwritten indexes are written to disk

In copying files we need to open two(2) windows:

Window1:

LOCK TABLES table_name READ,table_name READ,…..READ;

FLUSH TABLES  table_name , table_name 2…;

Directly Copying Files:

On Window2:

C:\mydata\data\db>copy table_name.* C:\backup

    Data directory      table to copy      backup path

Example:

After copying, release the table using unlock statement on window1:

Mysql>UNLOCK TABLES;

To test the backup, you're going to drop a table.

BACKUP WITH SELECT INTO AND LOAD DATA INFILE

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

select ...into :

q  select … into outfile

Writes a selected row to a file

q  select … into dumpfile

q  select … into var_list 

Syntax:

SELECT … INTO OUTFILE ‘file_name’ fields terminated by ‘terminatot’ from [table_name];

 

Load Data Infile

It is use to read the file back to the table

Syntax:

LOAD DATA INFILE [‘file_name’] into table [name_of_table];

If the data inside the data.txt looks like this:

Output:

Solution:

LOAD DATA INFILE [‘file_name’] into table [name_of_table] lines terminated by [‘terminator’];

Output:

Skipping over a prefix:

LOAD DATA INFILE [‘file_name’] into table [name_of_table] fields terminated by [‘terminator’]  lines starting by [‘starting_value’];

Sample file:

Output: