MySQL Table Backup: 7 ways to backup MySQL table

Many people are interested in the question how to make a backup of a single MySQL database table, not the entire database.

This backup can be used, for example, if you exceed the size limit of the files on the hosting, where you store the database. It may also be convenient if you have a table or tables that are updated more frequently than the rest of the database - then it makes sense to do a backup of the single table more often.

Frequent backups of the heavily used data files can shorten the recovery time in some situations. Daily backups of frequently updated table spaces will reduce the number of transactions that have to be re-applied without having to do a full daily backup of the database.

There are several ways to make a backup of the MySQL table.

Contents [hide]

Method 1: Backup Table Operator

The "backup table" MySQL operator can make up individual database tables. That can be applied to the MyISAM table type only.

The syntax of the backup table:

BACKUP TABLE tbl_name [, tbl_name] ... TO '/way-to-backup-catalog'

Please note this operator is available in MySQL version 5.0 and earlier versions. Now, it is deprecated and the recommendation is to use the mysqlhotcopy script and the mysqldump utility.

Method 2: Mysqlhotcopy Script

Mysqlhotcopy is a special Perl script for MySQL backup. This script allows you to make a backup of a single database table, but it has several restrictions:

Firstly, it does not work remotely (you have to run it on the computer where the database is located).

Secondly, the script makes a backup of only certain types of tables such as MyISAM and ARCHIVE.

Thirdly, it does not work on Window systems.

Method 3: Mysqldump Utility

The mysqldump utility is more general than the mysqlhotcopy, because it allows you to backup any type of tables. This is “built-in” utility and you do not need to download it.

Mysqldump allows you to make a backup of a single or multiple MySQL database tables. The syntax is:

mysqldump-u USER-p PASSWORD DATABASE TABLE_NAME> C: \ table_backup.sql

Method 4: Direct Copying of Files

In some cases, the tables are stored in separate files (eg, tables MyISAM). This allows you to make a backup by simple copying all the files associated with the table (.frm, .MYD, and .MYI). You need to stop the server, or do the “read lock” (you can make copies of all files under the condition that the server is currently in the “no updates” state).

This method does not work for the InnoDB table type, because in that case MySQL can store the table data to different locations and not only in the database directory.

MySQL backup table can be done also by using special software.

Method 5: MySQL Workbench

MySQL Workbench is a visual development tool that includes a variety of components for graphical MySQL database administration.

MySQL Workbench allows you to make a backup of a single database table using the visual editor. To do this, go to the Server Administration, open the database and select the Data Dump. Click on your database and select a table from the list that you want to back up.

Method 6: Handy Backup

Handy Backup has a built-in plug-in that allows you to make a backup of a single MySQL database table.

MySQL table backup

The program makes a dump file for each table. This file contains all the information necessary to rebuild the table. Note that the file attributes (such as file size, creation and modification time) are not supported, so you cannot do incremental backup, but can do differential backup of a single table.

MySQL plug-in for Handy Backup is available in two versions - Small Server and Server Network.

Handy Backup Small Server
Handy Backup Server Network

The setup file is common for both editions. Try free for 30 days!

You can also buy this plug-in independently for Standard and Professional versions. More information about the plug-in can be found on this page.

MySQL table backup is easy with a Handy Backup!

Method 7: Give Up and Do a Full Backup

If you did not find the way backup MySQL table you like, just give it up! May be, full MySQL database backup that contain your table is better for you. It’s easier, really :)

Contact Us

support@handybackup.net

sales@handybackup.net

+1 (707) 703-13-11

Term of the Day

  • Disc duplication
    “Disc duplication is a process of copying the entire contents, be it data files or audio, of one disc to another. Usually special equipment is used...”

Backup Software Glossary

Current glossary explains various terms that you may come across while working with Handy Backup or other backup software.

Handy Backup is a backup utility for Windows Servers and PCs that is famous for its flexibility and ease of use.

The glossary contains 219 terms. The current section Program Backup contains 4 terms.

Other “Program Backup” Terms

  • Backup Data MSN
    “MSN data backup is an additional copy of MSN data that can be used to restore the original after a computer crash, a virus, or other event of data...”
  • SQL Server Data Types
    “Saving SQL server data to another location, user can manipulate with all parts of vital content linked to the particular SQL server instance...”
  • MySQL Table Backup - How to make a backup of a single database table
    “Many people are interested in the question how to make a backup of a single MySQL database table, not the entire database. This backup can be...”

Who uses our backup software?