DATABASE BACKUPS

Backup - the main reason why it is needed or use to backup your database is to recover data if unexpected deletion may occur.

Backup Syntax:

 

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

Restore 

 On the other hand restore is used to return data to its original condition or getting  or copying the data to the database;

SYNTAX:

RESTORE TABLE [table_name]  from '[path]';

USING SELECT ... INTO  

   Another SELECT form which enables a query to be written  to a file

• SELECT ... INTO OUTFILE
• It writes a selected row to a file 

            SYNTAX:

                          SELECT                                      [name_of_column]      
                          INTO OUTFILE                          'file_name'
                          LINES TERMINATED BY       'terminator'
                          FROM                                          [table_name]

 All the records inside column name where copied in the col_name.txt file

• SELECT ... INTO DUMPFILE
• Writes a single row to a file

           SYNTAX:
             SELECT                        [row_name]
             INTO   DUMPFILE      [file_name_to_be_written]
             FROM                            [table_name]
             LIMIT                            [no_of_limit]

NOTE:  Disregarding LIMIT will cause an error especially if the column consist of numbers of records because select...into dump writes only a single row to a file without nothing the numbers of row to be copied in a file you will encounter error but still there will be a record that will be copied but only the first row of the records.

SELECT ... INTO var_list

• Select column values and into  variables

   SYNTAX :
          SELECT                           [column_name]
          INTO                               @[variable_name]
          FROM                              [table_name];

USING LOAD DATA INTO INFILE:

Use to read the file back to the table

SYNTAX:
    LOAD DATA INFILE           'filename'
    INTO TABLE                         [table_name];

NOTE: By the use of fields terminated by and starting by, the records that was read in the file was divided into different rows.



MYSQL DUMP

•      A backup program, that is use to dump the database, or it is a collection of database for backup. It contains SQL statements to create table, populate it or both.

SYNTAX:

• DUMP ALL DATABASE

                    mysql\bin> mysqldump --all-databases > 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 

                       mysqldump -u root clsu college> dump.sql

MYSQLHOTCOPY                 



    Runs only on UNIX and NetWare, it is used  for backing up MYISAM and ARCHIEVE.

NOTE: Must have select privilege, reload  privilege, and lock tables  privilege




SYNTAX:   

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

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

Backing Innodb

 Two ways in backing Innodb

                                  using mysqldump 

                                  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

1. Run Mysql server with binary log on defore taking the buckup

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

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