Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

How to Backup and Restore MySQL Database Files

DZone 's Guide to

How to Backup and Restore MySQL Database Files

Let's discuss some general methods for making backups in MySQL Database.

· Database Zone ·
Free Resource

Let's discuss some general methods for making backups in MySQL Database. There are many instances where the MySQL File can be corrupted, and some of the reasons for MySQL data being lost are:

  • If you are using the “killall -9 mysqld” command to shut down the server and killing all the ongoing MySQL processes.
  • Improper or abrupt shutdown of the MySQL database server due to a power cut.
  • Making changes with the data files, especially under the feet of mysqld
  • Internal error in MySQL database server can be caused due to many reasons.
  • Virus Attack

Now, let's take a look at the methods to prevent MySQL Database. One such method is to keep the backups of the database files. In case you lose the files due to any of the above-stated reasons, you can get them back easily. So, some of the methods to keep the backup of MySQL files are described below.

Methods to backup and restore MY SQL Database:

# Backup Using MySQL Enterprise Backup

Customers of MySQL Enterprise Edition can use the MySQL Enterprise Backup product to perform selected files or the entire databases, tables, or both. Backing up by backing up physical database files makes restore much faster than logical techniques such as the mysqldump command. InnoDB tables, which make a majority of data are copied using a hot backup mechanism. The warm backup mechanism is used to backup tables from other storage parts.

#Backups Using mysqldump or mysqlhotcopy

You can use the mysqldump program and the mysqlhotcopy to make backups.

The mysqldump is used to back up all kinds of tables.

The mysqlhotcopy works only with some storage engines.

# Backup by Copying Table Files

This method is helpful for engines that contain tables having their own files, tables, etc. These can be backed up by copying them.

If you want a stable backup, stop the server or lock and flush the relevant tables: 

FLUSH TABLES tbl_list WITH READ LOCK; 

You can use the read lock, which permits other clients to continue to query the tables while you are making a copy of the files in the database directory.

The flush is essential to ensure that the all active index pages are written on to the disk prior to the start of backup.

# By Creating a Binary backup

Simply by copy all table files, as long as the server isn't updating anything. The mysqlhotcopy script uses this method. Note: This method does not work for InnoDB tables as it does not store tables in databases.

# By creating Delimited-Text File Backups

To create a text file containing a table's data, you can use

 SELECT *INTO OUTFILE ‘file_name’ FROM tbl_name 

This particular file is created on the MySQL server host. This procedure is fruitful for any kind of data file but saves only table data, not the table structure.

Another way to create text data files is by using the mysqldump.

# By Creating Incremental Backups by Enabling the Binary Log

MySQL database supports incremental backups.

For this, you need to start the server with the --log-bin option to enable binary logging.

The binary log files lay down the information needed to replicate changes to the database.

When you want to create an incremental backup you should rotate the binary log by using FLUSH Logs. With this done, you need to copy to the backup location all binary logs, which range from one of the moments of the last full or incremental backup to the last but one.

Use FLUSH Logs to create complete binary backup

 mysqldump --flush-logs, or mysqlhotcopy --flushlog. 

# By Create Backups Using Replication Slaves

In case you face performance problems with your master server while making backups, you can set up replication and perform backups on the slave rather than on the master.

In the Slave replication process, you need to create a back up of its master.info and relay-log.info files regardless of the backup method you select.

You need the info files to restore Slave data.

If your slave is replicating the LOAD DATA, then you need to back up any SQL_LOAD also.

The slave needs these files to resume replication of any interrupted LOAD DATA backups. The directory location is the value of the --slave-load-tmpdir option.

# By Recovering Corrupt Tables

This method is used to restore MyISAM tables that are corrupt and to try to recover them using REPAIR TABLE or myisamchk -r first methods.

 REPAIR[NO_WRITE_TO_BINLOG|LOCAL] 

 TABLEtbl_name[,tbl_name]... 

 [QUICK][EXTENDED][USE_FRM] 

# By Creating Backups Using a File System Snapshot

This works in case of the Veritas file system. Follow the steps given below:

You can execute FLUSH TABLES WITH READ LOCK for a client program.

  1. From another shell, execute mount vxfs snapshot.
  2. From the first client, execute UNLOCK TABLES.
  3. Copy files from the snapshot.
  4. Unmount the snapshot.

You can find likewise snapshot capabilities may be available in other file systems, such as LVM or ZFS.

# Repair and Restore Corrupt MySQL Database Files

If you have lost the MySQL database files due to any possible reasons that are explained above, then you can use a tool such as the My SQL Database Recovery Tool By SysInfo Tools. The procedure to use this tool is simple. It recovers InnoDB and MyISAM storage engines of MySQL database.

You can follow the steps to do the recovery.

  1. Launch the MySQL database recovery tool and select the files to be recovered.

2. Wait for the scanning process to complete.

3. The tool shows the preview of recovered data.

4. Now click on the recovered files and select a location for it to save.

5. Finally, the Database successfully recovered dialogue box is created. Click on OK.

To Sum Up

I have explained the various reasons behind MySQL Database corruption. The various methods to create the backup are explained. Well, you can try and create the backups manually. But in case these methods are too complicated and you lose the MySQL Database files, then you can use the professional recovery tool to restore and repair the MySQL files easily.

Topics:
database ,mysql ,mysql database files ,backup and restore files ,tutorial

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}