Backing Up/Restoring Data
Backing up and restoring data on your server makes it possible to safeguard the information in your application in case problems occur. When you create a backup, you get a copy of the current state of the database and Web files at the time of backup. Then, you can use that copy to restore the application to that state if any part of the application is lost or corrupted. SQLyog's various backup features allow you to easily backup/restore your critical data.
In the following section we will learn how to backup data in many common formats.
Data As SQL Batch Scripts - SQLyog allows you to take a backup of a database in SQL batch scripts. Click on the database in the Object Browser and select DB -> Export Database As Batch Scripts... or by pressing (Ctrl+Alt+E).
Fill in all the required details and press Export to backup your database. You can restore the backup later by using SQLyog's "Execute batch file..." option discussed later.
Data As CSV - One of the most preferred ways of exporting data of a table chosen by developers is in the CSV format. To export data from table into CSV format select the table in the Object Browser and select Table -> Export Table Data As CSV... or press (Ctrl+Alt+C).
Select the columns which you want to export data from, specify the escaping characters (pressing the Change button will popup the escaping character dialog box), give a file name and press Export. The data exported is compatible with the LOAD LOCAL INFILE option provided by MySQL.
Using MySQL's Backup Option -- Using MySQL's Backup command through SQLyog you can make a SQL level backup of a table. Select a database and select DB -> Backup Database. Select the table(s) and give the directory where you want to export data.
Similarly there are various ways to restore/import data.
Importing From a SQL Batch File -- If you have your data backed up as SQL batch scripts then you can restore it using SQLyog's Select Tools -> Execute Batch File. Give the batch file name and press Execute. SQLyog can execute a batch file of any size (limited only by your OS).
Importing From CSV - You can Import a CSV data into a table by using Table -> Import Data From CSV. Select the table, column(s) and give the escaping characters. SQLyog uses the MySQL LOAD DATA LOCAL INFILE command to import data from CSV. Therefore this option will work only with MySQL 3.23 or greater.
Using MySQL's Restore Option -- The Restore command restores the table(s) from the backup that was made with BACKUP TABLE. To restore a database select the database and select DB -> Restore Database. Next select the table(s) and specify the folder where your table(s) is backed up.