Difference between revisions of "Backup The MySQL Database"

Revision as of 20:56, 21 January 2014

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

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

You can also 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