Database Backups

     When managing databases, it is a good practice to have a backup of your database. Backups are an earlier version of one's database. This would give the assurance that the data would always be there; even if the system have failed or something went wrong with the process that would led to the destruction of one's database.  Today we are going to discuss the different ways of backing up database in mysql.

BACKUP TABLE
     To backup a table inside mysql, you will need to use the 'BACKUP TABLE' statement. The syntax for this is "BACKUP TABLE [table_name] to '[path]';"
     Here is an example:

     To restore the backup means to use the backup saved. It is used to return the data to its original condition. The syntax for this is "RESTORE TABLE [tablename] from '[path'];"
     Here is an example:

MYSQLDUMP
     Another way of backing up mysql database is by using mysqldump. You will need to use this outside of the mysql program. The syntax for this is "mysqldump -u root -p [database_name] > [path][filename]"
     Here is an example:

     To restore the backup stored using mysqldump you will need to use this syntax "mysql -u root -p [databasename]<[path][filename]"
     Here is an example:

   
     There are many arguments you can use in mysqldump.
To backup all the databases use this syntax:
     "mysqldump --all-databases > [path][filename]"
To backup a specific database use this syntax:
     "mysqldump -u root -p --databases [database_name] [database_name]  > [path][filename]"
To backup all the tables in a database use this syntax:
     "mysqldump -u root -p [database_name] > [path][filename]"
To backup a specific table use this syntax:

     "mysqldump -u root -p [database_name] [table_name] [table_name]  > [path][filename]"

SELECT...INTO
     Another way of creating a backup is by the use of "SELECT INTO" statement. "SELECT...INTO" enables a query to be written on a file. There are three kinds of using this. There is the "SELECT...INTO OUTFILE", the "SELECT...INTO DUMPFILE" and the "SELECT...INTO VAR_LIST".
     The "SELECT...INTO OUTFILE writes a selected table into a file. The syntax for this is "SELECT...INTO OUTFILE [filename] FIELDS TERMINATED BY [terminator] FROM [tablename];"
     Here is an example:

     Here is the output:

   
     To restore the data back to the database you will need to use the "LOAD DATA INFILE" statement. To use this the syntax is "LOAD DATA INFILE [filename] INTO TABLE [name_of_tables] LINES TERMINATED BY [terminator];.

     Here is an example:

     Here is the output:

     For more information about select into visit the mysql website here.