Difference between revisions of "Backup The MySQL Database"

Latest revision as of 09:10, 1 July 2017

There are two main methods for backing up MySQL databases - an SQL dump and a raw file copy. SQL dumps have the advantage of being easy to use and flexible, as the SQL statements can be manipulated as required using standard text tools, so it is easy to restore only certain records or tables. However, they are larger, and often much slower to create and restore than raw file copies.

How you choose to backup the MySQL database may also depend on the MySQL engine employed. MySQL Engines MyISAM and Innodb and both be backed up using mysqldump. However, with Innodb, just copying the .frm and .ibd files from one location to another will likely lead to a 'table does not exist' error when you try to access the database on the restored system.

Raw Dump

This works fine with MyISAM engine databases. It can also work with Innodb if you are making a backup that is to be restored to the exact same system, in the exact same place, with the exact same version of MySQL. Raw Dumps are not advised with Innodb if you are moving the database to another server, say, for migration.

"Just copying the .frm and .ibd files from one location to another is asking for trouble. Copying the .frm and .ibd file of an InnoDB table is only good if and only if you can guarantee that the tablespace id of the .ibd file matches exactly with the tablespace id entry in the metdata of the ibdata1 file."

source: http://stackoverflow.com/questions/5745221/import-frm-and-opt-files-to-mysql

Raw Dump Types:

1. mysqlhotcopy
2. backup statement
3. manually copying the files

The Process

Steps

(1). Do a full backup of your databases:

shell> mysqldump --tab=/path/to/some/dir --opt --all

or

shell> mysqlhotcopy database /path/to/some/dir

If your MySQL database uses the MyISAM engine, you can simply copy all table files (`*.frm', `*.MYD', and `*.MYI' files) as long as the server isn't updating anything. The script mysqlhotcopy does use this method.

tar -sp -zcvf var-lib-mysql.tar.gz /var/lib/mysql -P

(2). Stop mysqld if it's running, then start it with the --log-bin[=file_name] option. See section 4.9.4 The Binary Log. The binary log file(s) provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you executed mysqldump.

from the commands notes file:

Backing up a LIVE MySQL database:
----------------------------------------
hotcopy:
mysqlhotcopy databasename /home/username/placetoputbackupfile -p mysqlpassword
`
tarball:
tar -sp -zcvf mysqlhotcopy.tar.gz  /home/username/placetoputbackupfile -P
`
tar -sp -zcvf archive.tar.gz /mounted/directory -P
`
-s same owner
-p same permissions
`
-z gzip compression
-c create
-v verbose
-f filename
`
-P absolute path

SQL Dump

Using mysqldump is an effective tool to backup your MySQL database. This method creates text files in sql syntax with DROP table, CREATE table and INSERT into sql-statements of the source database. To restore the database, execute the *.sql file on the destination database. Using mysqldump is the recommended method for using with the Innodb engine. For databases using the MyISAM engine the use of mysqlhotcopy is faster.

Note: The mysqld service is running when you use mysqldump.

mysqldump backup

The most common use of mysqldump is probably for making a backup of whole databases.

 mysqldump --all-databases --lock-tables -p > all_databases.sql

This will back up all databases and prevent access during the backup process. The --lock-tables is what prevents the database from being modified during the backup. The -p switch will prompt you for your mysql password before commencing. The > is to output to the file name of your choice ending in the .sql extension, for creation of a text readable file.

If you wish to backup a specific table

 mysqldump --opt payroll --lock-tables -p > payroll_backup.sql

In this example we pipe the backup into gnuzip to compress it for archival purposes.

 mysqldump --opt payroll --lock-tables -p | gzip > payroll_backup.sql.gz

In this example the payroll table is being dumped to a backup file. It is possible to dump several databases with one command.

 mysqldump --databases payroll accounting --lock-tables -p > business_backup.sql

mysql restore

To restore a single table to the database.

 mysql -p payroll < payroll_backup.sql

To restore multiple tables to the database.

 mysql -p < business_backup.sql