Mysqldump command
MySQL mysqldump is used for MySQL database export. The basic usage is as follows:
| Shell> mysqldump [Options] database [Tables] |
If you do not specify any tables, the entire database will be exported. Run mysqldump -- help to obtain the option table supported by your mysqldump version. Note: If you run mysqldump without the -- quick or -- opt option, mysqldump loads the entire result set to the memory before the export result. If you are exporting a large database, this may be a problem.
1.1. mysqldump supports the following options:-- Add-locks adds lock tables before each table is exported and then unlock table. (To make it faster to insert data to MySQL ). -- Add-drop-table: Add a drop table before each create statement. -- Allow-keywords allows the creation of column names that are keywords. This is done by the table name prefix on each column name. -C, -- complete-insert use the complete insert Statement (with the column name ). -C, -- compress: if both the client and server support compression, compress all information between the two. -- Delayed use the insert delayed command to insert rows. -E, -- extended-insert use the new multiline insert syntax. (A more compact and faster insert statement is provided)-#, -- debug [= option_string] use of the tracing program (for debugging ). -- Help: displays a help message and exits. -- Fields-terminated-by =... -- fields-enclosed-by =... -- fields-optionally-enclosed-by =... -- fields-escaped-by =... -- fields-terminated-by =... these options are used with-T options and have the same meaning as the load data infile clause. Load data infile syntax. -F, -- flush-logs: wash the log files on the MySQL server before export. -F, -- force, even if we get an SQL error during the export of a table, continue. -H, -- Host = .. export data from the MySQL server on the named host. The default host is localhost. -L, -- lock-tables. Lock all tables for export start. -T, -- no-create-Info does not write the table creation Information (create table statement)-D, -- no-data does not write any row information of the table. If you only want to export the structure of a table, this is very useful! -- OPT is the same as -- quick -- add-drop-table -- add-Locks -- extended-insert -- lock-tables. You should be given the fastest export possible for reading a MySQL server. -Pyour_pass, -- password [= your_pass] indicates the password used to connect to the server. If you do not specify "= your_pass", mysqldump requires a password from the terminal. -P port_num, -- Port = port_num indicates the TCP/IP Port number used to connect to a host. (This is used to connect to a host other than localhost because it uses UNIX sockets .) -Q, -- quick does not buffer queries and is exported directly to stdout. Use mysql_use_result () to do it. -S/path/to/socket, -- socket =/path/to/socket the socket file used when connecting to localhost (it is the default host. -T, -- tab = path-to-some-directory create a table_name. SQL file for each given table, which contains the SQL CREATE command and a table_name.txt file, which contains data. Note: This only works when mysqldump runs on the same machine where the mysqld daemon is running .. The format of the TXT file is determined by the options -- fields-xxx and -- lines -- XXX. -U user_name, -- user = user_name: username used by MySQL when connecting to the server. The default value is your UNIX login name. -O Var = option, -- Set-variable VAR = option to set the value of a variable. Possible variables are listed below. -V, -- verbose mode. Print out more information about the program. -V, -- version: print the version information and exit. -W, -- where = ''' where-condition ''' only export selected records. Note that the quotation marks are mandatory! "-- Where = user = ''' jimf '''" "-wuserid> 1" "-wuserid <1"
1.2. One of the most common mysqldump backups:Although mysqldump supports many commands, for most people, we only need to use the-opt command to make a complete backup for your database:
| Mysqldump -- opt database> backup-file. SQL |
However, it is also useful for enriching another MySQL database with information from one database:
| Mysqldump -- opt database | MySQL -- Host = remote-host-C Database |
1.3 Use mysqldump to export files to restore the databaseMysqldump exports complete SQL statements, so it is easy to import data into MYSQL client programs:
| Shell> mysqladmin create target_db_name shell> MySQL target_db_name <backup-file. SQL |
Is shell> MySQL database name <file name
Ii. Use mysqldump to regularly back up the database script
2.1 backup scriptThe script regularly performs database backup operations every day. It is necessary for everyone who uses the MySQL database. There are many such scripts on the Internet. Here we will extract a friend's script dbbackup: this script can be executed only once a day, and only the backups of the last five days are retained 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, and Log Path, data File Location and backup mode # by default, the backup mode is tar, mysqldump, and mysqldotcopy # by default, use root (empty) to log on to the MySQL database, back up to/root/dbxxxxx. tgz dbname = MySQL dbuser = root dbpasswd = backuppath =/root/logfile =/root/DB. log dbpath =/var/lib/MySQL/# backupmethod = mysqldump # backupmethod = mysqlhotcopy # backupmethod = tar # setting end new File = "$ 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 success! ">>$ Logfile else echo" [$ oldfile] 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 add crontab to regularly execute dbbackupAssume that dbbackup is in the/root directory. Run the crontab command to run the/root/dbbakup script at 00:10 every day. 1. Use crontab-e to edit crontab2 and add the following to crontab:
| # Back For jabber database10 0 ***/root/dbbackup |
