Sometimes, there is a requirement to make a backup of a table in MySQL. For instance, there could be a data update planned, schema change needed or that it is necessary to work with a copy of a table, perhaps for some report.
A possible approach to this is to run a full backup of the database to call upon if something goes wrong. This is feasible but sometimes not practical. The database dump could take some time to complete and if something goes wrong and you need to restore, restoring the dump could equally take some time to complete.
In this post, I will show a couple of ways to take backups of tables in MySQL.
Method 1 – Taking a Backup of a MySQL Table Using INSERT INTO
CREATE TABLE table_backup; INSERT INTO table_backup SELECT * FROM table;
This method creates the structure of the table including indexes and then loading the data in via one statement.
The advantages of this method are that makes a backup of the table immediately available with the database. It is however to all extents and purposes just like any other SQL statement by way of consuming disk space on the data drive. So disk space should be a consideration when performing this method.
Method 2 – Taking a Backup of a MySQL Table Using mysqldump
mysqldump -u{backup_user} -p{backup_password} from_db_name table_to_backup > backup_file.sql
Instead of backing up the entire database, a backup of the table can be made using mysqldump.
This is my personal preference if the requirement is just that a backup is needed and that there is no requirement to have the table online like in the first example. The backup is taken using mysqldump and can be directed to a location of choice. Disk space is therefore not a consideration for the data drive, rather it is necessary just for the location being backed up to.
Leave a Reply