Usually when we use the MySQL database, often because of operational errors caused by data loss, MySQL database backup can help us to avoid due to various reasons, data loss or other problems with the database.
First, data backup shortcut
Because this method has not been officially certified official document, we tentatively called the test.
Purpose: Back up a MySQL database backup hostA host TestA, and restored to the hostB machine
Test environment:
Operating System: WinNT4.0, Mysql3.22.34, phpMyAdmin 2.1.0
Install MySQL database backup in hostA and set up TestA database
HostB installed MySQL database backup, there is no TestA database
Method steps:
Start phpMyAdmin View HostA and HostB in the database list, there is no TestA database in HostB
Find HostA MySQL database backup installation directory, and find the database directory data
In my experimental environment, this directory is C: mysqldata
Find the corresponding database name subdirectory C: mysqldataTestA
Paste copy to HostB's Data directory, HostA HostB MySQL database backup data directory with the same file
Refresh HostB phpMyAdmin look at the list of databases, we see that TestA has emerged, and for query modification and other operations are normal, the backup and restore success
Test Conclusion: MySQL database can be saved as a file, backup, recovery as long as the corresponding file directory recovery can be, without using other tools to back up.
Second, the formal approach (official advice):
Export to use MySQL database backup mysqldump tool, the basic usage is:
mysqldump [OPTIONS] database [tables]
If you do not give any table, the entire database will be exported.
By executing mysqldump --help you can get the list of options supported by your version of mysqldump.
Note that if you run mysqldump without the --quick or --opt option, mysqldump will load the entire result set into memory before exporting the result, which can be a problem if you are exporting a large database.
mysqldump supports the following options:
--add-locks
Add LOCK TABLES before each table is exported and then UNLOCK TABLE. (In order to make it faster to insert into a MySQL database backup).
--add-drop-table
Add a drop table before each create statement.
--allow-keywords
Allows you to create column names that are keywords. This is done by adding a table name before the column name.
-c, --complete-insert
Use the complete insert statement (using the column name).
-C, --compress
If both client and server support compression, compress all information between the two.
--delayed
Insert a row with the INSERT DELAYED command.
-e, --extended-insert
Use the new multi-line INSERT syntax. (Gives tighter and faster insert statements)
- #, --debug [= option_string]
Track the use of the program (for debugging).
--help
Show a help message and exit.
--fields-terminated-by = ...
--fields-enclosed-by = ...
--fields-optionally-enclosed-by = ...
--fields-escaped-by = ...
--fields-terminated-by = ...
These selections are used with the -T option and have the same meaning as the corresponding LOAD DATA INFILE clause.
LOAD DATA INFILE syntax.
-F, --flush-logs
Before starting to export, wash away the log files in the MySQL database backup server.
-f, --force,
Even if we get an SQL error during a table export, continue.
-h, --host = ..
Export data from a MySQL database backup server on a named host. The default host is localhost.
-l, --lock-tables.
Lock all tables for export start.
-t, --no-create-info
Do not write table creation information (CREATE TABLE statement)
-d, --no-data
Any row information not written to the table. This is useful if you only want to export the structure of a table!
--opt
with:
--quick --add-drop-table --add-locks --extended-insert --lock-tables
You should give your fastest possible export for reading into a MySQL database backup server.
-pyour_pass, --password [= your_pass]
The password used when connecting to the server. If you do not specify the "= your_pass" section, mysqldump needs a password from the terminal.
-P port_num, --port = port_num
The TCP / IP port number to use when connecting to a host. (This is used to connect to a host other than localhost because it uses Unix sockets.)
-q, --quick
Do not buffer the query, export directly to stdout; do it using MySQL_use_result ().
-S / path / to / socket, --socket = / path / to / socket
The socket file used when connecting to localhost, which is the default host.
-T, --tab = path-to-some-directory
For each given table, create a table_name.sql file that contains the SQL CREATE command, and a table_name.txt file that contains the data. Note: This only works if mysqldump is running on the same machine as the mysqld daemon. The format of the .txt file is based on the --fields-xxx and --lines - xxx options.
-u user_name, --user = user_name
The username MySQL uses when connecting to the server. The default is your Unix login name.
-O var = option, --set-variable var = option
Set the value of a variable. The possible variables are listed below.
-v, --verbose
Long pattern. Print out the program to do more information.
-V, --version
Print version information and exit.
-w, --where = 'where-condition'
Export only the selected record; note the quotation marks are mandatory!
"--where = user = 'jimf'" "-wuserid> 1" "-wuserid1"
The most common mysqldump uses a backup that might make the entire database:
mysqldump --opt database> backup-file.sql
But it is also useful for enriching another MySQL database backup with information from one database:
mysqldump --opt database
MySQL - host = remote-host -C database