MYSQL backup with mysqldump
First, mySQLDUMP command
The mysql database is exported to the MySQLDUMP tool to use MySQL, and the basic usage is:
Shell> mysqldump [options] Database [TABLES]
If you don't give any table, the entire database will be exported.
By performing mysqldump --help, you can get the option table for your MySQLDUMP's version support.
Note that if you run mysqldump no --quick or --opt option, mysqldump will load the entire result set to memory before the export result, if you are exporting a large database, this will might be a problem.
1.1, MySQLDUMP supports the following options:
--Add-Locks
Add Lock Tables before each table and then UNLOCK TABLE. (In order to make it faster into mysql).
-dd-drop-table
Add a DROP TABLE before each CREATE statement.
- Allow-Keywords
Allow creation is a column name of the keyword. This is died by the table name.
-c, --complete-insert
Use a complete INSERT statement (with column name).
-C, --compress
If both the customer and the server support compression, compress all information between the two.
-delayed
Insert with the insert delayed command.
-e, --extended-insert
Use a new multi-line INSERT syntax. (Give more tightening and faster insert statements)
- #, --debug [= Option_String]
Tracking the usage (for debugging).
--help
Display a help message and exit.
Load Data Infile
--fields-terminated-by = ...
--fields-enclosed-by = ...
--fields-optionally-enclosed-by = ...
--fields-escaped-by = ...
--fields-terminated-by = ...
These options are used with -t selection and have the same meaning as the corresponding Load Data INFILE clause.
Load Data Infile syntax.
-F, --flush-logs
Wash the log file in the MySQL server before starting the export.
-f, --force,
Even if we get a SQL error during a table, continue.
-h, --host = ..
Export data from the MySQL server on the named host. The default host is Localhost.
-L, --Lock-Tables.
To start exporting the lock all tables.
-T, --NO-CREATE-INFO
Do not write table creation information (CREATE TABLE statement)
-d, --NO-DATA
Do not write any of the rows of information. If you just want to get the export of a table, it is very useful!
--opt
With --quick --add-drop-table --add-locks --extended-insert --lock-tables.
Should be given to the fastest possible export of a MySQL server.
-pyour_pass, --password [= Your_pass]
Passwords used when connecting to the server. If you don't specify a "= Your_Pass" section, MySQLDUMP needs a password from the terminal.
-P port_num, --port = port_num
The TCP / IP port number used in connection with a host. (This is used to connect to hosts other than localhost because it uses UNIX sockets.)
-q, --quick
Do not buffer query, export directly to stdout; do it using mysql_use_result (). -S / path / to / socket, - Socket = / path / to / socket
Socket files used when connecting to LocalHost (it is the default host).
-T, - Tab = path-to-some-directory
For each given table, create a table_name.sql file, which contains the SQL CREATE command, and a table_name.txt file, which contains data. Note: This only works when MySQLDUMP is running on the same machine in MySQLD daemon. The format of the .txt file is based on the Fields-XXX and --Lines - XXX options.
-U user_name, --user = user_name
Usernames used by MySQL when connecting to the server. The default is your UNIX login name.
-O var = option, --set-variable var = option Sets the value of a variable. Possible variables are listed below.
-v, --Verbose
Permutable mode. Print out more information made by the program.
-V, --version
Print version information and exit.
-w, --where = 'where-condition'
Only the recorded record is exported; pay attention to quotation marks forced!
"--where = user = 'jimf'" "" -wuserid> 1 "" -wuserid <1 "
1.2, one backup of the most common mysqldump:
Although Mysqldump support has a lot of orders, for most people, we only need to use -opt this command is enough, make a complete backup for your database:
MySQLDUMP --Opt Database> Backup-file.sql
But it is also useful to enrich another mysql database with information from a database:
MySQLDUMP --Opt Database | MySQL - Host = Remote-Host-C Database
1.3, file recovery databases exported using mysqldump
Since MySQLDUMP is exported is a complete SQL statement, it is easy to use the MySQL client to import the data:
Shell> mysqladmin create target_db_name
Shell> MySQL Target_DB_Name Be Shell> mysql library name Second, use the mysqldump timing backup database script 2.1, backup script Use scripts to perform database backup operations every day, it is necessary for people who use the MySQL database, there are a lot of online online, here you have a friend's script dbackup: This script is only available once a day, and only the last five days of backup is on the server. DBBACKUP code: #! / bin / bash #This is a shellscript for auto db backup #Powered by Aspbiz # 2004-09 #Setting # Set the database name, database login name, password, backup path, log path, data file location, and backup mode # By default, the backup method is TAR, but it can be mysqldump, mysqldotcopy # By default, log in to the mysql database with root (empty), back up to /Root/dbxxxxx.tgzdbname=MYSQL Dbuser = root Dbpasswd = Backuppath = / root / Logfile = / root / db.log Dbpath = / var / lib / mysql / # BackupMethod = mysqldump # BackupMethod = mysqlhotcopy # Backupmethod = tar #Setting end Newfile = "$ backuppath" DB $ (DATE % Y% M% D) .tgz DUMPFILE = "$ backuppath" DB $ (DATE % Y% M% D) Oldfile = "$ backuppath" DB $ (DATE % Y% M% D --date = '5 days ago'). TGZ echo "-----------------------------------------"> $ logfile Echo $ (DATE "% Y-% M-% D% H:% M:% S") >> $ logfile echo "--------------------------" >> $ logfile # Delete Old File IF [-f $ oldfile] THEN RM -F $ Oldfile >> $ logfile 2> & 1 echo "[$ oldfile] delete old file surcess!" >> $ logfile Else echo "[$ lyfile] no old backup file!" >> $ logfile Fi IF [-f $ newfile] THEN Echo "[$ newfile] The Backup File Is Exists, Can't Backup!" >> $ logfile Else Case $ backupmethod in MySQLDUMP) IF [-z $ dbpasswd] THEN MySQLDUMP -U $ DBUSER --Opt $ dbname> $ dumpfile Else MySQLDUMP -U $ dbuser -p $ dbpasswd --opt $ dbname> $ dumpfile Fi TAR CZVF $ NEWFILE $ dumpfile >> $ logfile 2> & 1 echo "[$ newfile] backup success!" >> $ logfile RM-RF $ DUMPFILE ; MySQLHOTCOPY) RM-RF $ DUMPFILE Mkdir $ dumpfile IF [-z $ dbpasswd] THEN MySQLHOTCOPY -U $ dbuser $ dbname $ dumpfile >> $ logfile 2> & 1 Else MySQLHOTCOPY -U $ dbuser -p $ dbpasswd $ dbname $ dumpfile >> $ logfile 2> & 1 Fi TAR CZVF $ NEWFILE $ dumpfile >> $ logfile 2> & 1 echo "[$ newfile] backup success!" >> $ logfile RM-RF $ dumpfile ;; *) /etc/init.d/mysqld stop> / dev / null 2> & 1 TAR CZVF $ NewFile $ dbpath $ dbname >> $ logfile 2> & 1 /etc/init.d/mysqld start> / dev / null 2> & 1 echo "[$ newfile] backup success!" >> $ logfile ; ESAC Fi echo "-----------------------------------------"> $ logfile 2.2, put the crontab periodically executed dbbackup Assume Dbbackup in the / root directory. By using the crontab command, we set up 0:10 to perform / root / dbbakup scripts at 0:00. 1. Use crontab -e edit crontab 2, join in crontab: #back for mysql database 10 0 * * * / root / dbbackup
