Back Up And Restore

Mysql back up command

mysqlhotcopy' works only with some storage engines and hence is not as popular as 'mysqldump'. 'mysqlhotcopy'  is a perl script that comes with the mysql installation and as it is a perl script it requires the perl-DBD module installed, without which it won't work. It is a fast way to make a backup of the database or single tables, but it can be run only on the same machine where the database directories are located. 'mysqlhotcopy' works only for backing up MyISAM and ARCHIVE tables. It runs on Unix and NetWare. It uses FLUSH TABLES, LOCK TABLES, and cp or scp to make a database backup.

mysqlhotcopy --user=root --password=<password> <database-name> <path-to-the-destination>
      [shell] mysqlhotcopy -u [username]  -p[password] [db_name_1]  ... [db_name_n]  [/path/to/backup_dir]
   ex.
      [shell] mysqlhotcopy -u root -pSecretPass blog /home/jatin/mysqlbackups/


Restoring the backup created from 'mysqlhotcopy'

Restoring  data is very easy. Copy the files from the backup directory to the /var/lib/mysql/<db> directory (or the datadir specified in your my.ini mysql configuration file) . In order to avoid any issues, stop the mysql before you restore (copy) the files. After you copy the files to the /var/lib/mysql/<db> start the mysql again.

Above we just looked at the most common scenario, mysqlhotcopy has many options which you can look at by either using

perldoc mysqlhotcopy


mysqldump :

Although mysqlhotcopy is best suited for MyISAM table, it is most likely that you would be using the InnoDB tables in your database Or may not have perl installed or simply want a logical (SQL) output for your database. This is where mysqldump can come handy. Lets look at this type of backup and restore method :

The simplest syntax is
   1.
      mysqldump -u root -p[password] [database_name]> [dump_file_name].sql

So lets say we want to get the dump of our 'blog' database, we would use the following command
   1.
      mysqldump -u root -pSecretPass blog> /home/jatin/sql/blog.sql

Restoring the mysql tables from the mydqldump output files :

Restoring the database from the dump file is even easier. just use the following syntax :
   1.
      mysql -u root -p[root_password] [database_name] <  dumpfilename.sql




COPY One file:

rsync -v sourcefile destination


For Syncing the files

rsync -avz source destination