There are many ways to complate the bakup task for mySQL database. If your database is small, use mysqldump is a good choice.
The mysqldump is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup. The dump typically contains SQL statements to create the table, populate it, or both.
Store your password in an option file, you can list your password in the [client] and [mysqldump] section of the .my.cnf file in your home directory:
[robert@server ~]$vim ~/.my.cnf
** NOTE **: It will not work well if you do not quote your password when the password contains characters such as “=” or “;”. The mysql will report the following error:
MySQL ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
To keep the password safe, the file should not be accessible to anyone but yourself. To ensure this, set the file access mode to 400 or 600. For example:
[robert@server ~]$chmod 600 .my.cnf
In order to facilitate the management, all the crond script are placed in a directory, such as:
[robert@server ~]#cd ~ [robert@server ~]#mkdir ~/crondScripts
Download and unzip the script file:
[robert@server ~]# wget https://gist.github.com/lampnode/5113701/download -O mysql_backup_all.tar.gz [robert@server ~]# tar -xzvf mysql_backup_all.tar.gz
Find the script file in upzip directory, and move this script file to the “crondScripts” directory.
To specify the backup destination directory, the default is “/opt/site-bak”. Note this directory permissions.
mysqldump offer a lot of options, the options are often used in the following table:
dd a DROP TABLE statement before each CREATE TABLE statement.
Surround each table dump with LOCK TABLES and UNLOCK TABLES statements. This results in faster inserts when the dump file is reloaded.
Use complete INSERT statements that include column names.
continue even if an SQL error occurs during a table dump. One use for this option is to cause mysqldump to continue executing even when it encounters a view that has become invalid because the definition refers to a table that has been dropped. Without –force, mysqldump exits with an error message. With –force, mysqldump prints the error message, but it also writes an SQL comment containing the view definition to the dump output and continues executing.
For each dumped database, lock all tables to be dumped before dumping them. The tables are locked with READ LOCAL to permit concurrent inserts in the case of MyISAM tables.
Do not write any table row information (that is, do not dump table contents). This is useful if you want to dump only the CREATE TABLE statement for the table (for example, to create an empty copy of the table by loading the dump file).