The easiest way to backup MySql database is available online
- 2020-05-06 11:47:19
- OfStack
Database backup using MYSQL, and a very formal database backup method, and other database servers have the same concept, but have you ever thought that MySQL will have a more convenient use of file directory backup method, and quickly have good.
One, data backup shortcut
Since this method is not officially documented, let's call it an experiment.
Purpose: back up an mysql database TestA on the hostA host and restore to
on the hostB machine
Test environment:
Operating system: WinNT4. 0, Mysql3. 22.34, phpMyAdmin 2.1.0
Install the mysql database in hostA and set up the TestA database
hostB machine installs mysql database, not TestA database
Method steps:
Launch phpMyAdmin to view the list of databases in HostA and HostB. There is no TestA database
in HostB
Find the installation directory for mysql in HostA and the database directory data
In my test environment, this directory is
C: mysqldata
Find the subdirectory
for the database name
C: mysqldataTestA
Paste and copy to Data directory of HostB, HostA is the same as HostB Mysql data directory of
Refresh phpMyAdmin of HostB look at the database list, we can see that TestA has appeared, and make query modification and other operations are normal, backup restore and restore
successfully
Conclusion: the Mysql database can be saved, backed up and restored in the form of files as long as the corresponding file directory is restored, there is no need to use other tools to backup.
2. Formal method (official recommendation) :
Export using MySQL's mysqldump tool, the basic usage is:
mysqldump [OPTIONS] database [tables]
If you do not give any tables, the entire database will be exported.
By executing mysqldump --help, you can get the list of options that your version of mysqldump supports.
Note that if you run mysqldump without the --quick or --opt option, mysqldump will load the entire result set into memory before exporting the results, which could be a problem if you are exporting a large database.
mysqldump supports the following options:
- add - locks
Add LOCK TABLES before each table export and then UNLOCK TABLE. (for faster insertion to MySQL).
- add drop - table
Add an drop table statement before each create statement.
- allow - keywords
Allows you to create column names that are keywords. This is done by prefixing the column name with the table name.
- c - complete - insert
Use the full insert statement (with column names).
- C - compress
If both the client and the server support compression, compress all information between the two.
- delayed
Insert the line with the INSERT DELAYED command.
- e - extended - insert
Use the new multi-line INSERT syntax. (gives tighter and faster insert statements)
- #, - debug
[= option_string]
Trace program usage (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 choices are used with the -T choices and have the same meaning as the corresponding LOAD DATA INFILE clause.
LOAD DATA INFILE syntax.
- F - flush - logs
Before you start exporting, wash the log files from the MySQL server.
- f - force
Even if we get an SQL error during a table export, continue.
- h - host =..
Export data from the MySQL server on the named host. The default host is localhost.
- l -- lock - tables.
Locks all tables for the start export.
- t - no create - info
Do not write table creation information (CREATE TABLE statement)
- d - no - data
No row information is written to the table. This is useful if you only want to get an export of the structure of a table!
- opt
Same --quick drop table add locks extended lock-tables
Should give you the fastest possible export for reading into an MySQL server.
- pyour_pass - password
[= your_pass]
The password to use when connecting to the server. If you do not specify the "=your_pass" section, mysqldump requires a password from the terminal.
-P port_num, --port=port_num
The TCP/IP port number used when connecting to a host. (this is used to connect to a host other than localhost, because it USES Unix sockets.)
- q - quick
Unbuffered query, directly exported to stdout; Do it with mysql_use_result().
- S /path/to/socket, - socket = / path/to/socket
Socket file used when connecting to localhost, which is the default host.
- T - tab = path - to some - directory
For each given table, create an table_name.sql file, which contains the SQL CREATE command, and an table_name.txt file, which contains the data. Note: this only works if mysqldump runs 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 user name MySQL USES 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
Verbose mode. Print out more information about what the program did.
- V - version
Print version information and exit.
- w - where = 'where - condition
Export only selected records; Note that quotation marks are mandatory!
"-- where = user = 'jimf" "- wuserid> 1" "-wuserid < 1"
The most common mysqldump use makes it possible to make a backup of the entire database:
mysqldump --opt database > backup-file.sql
But it is also useful for populating another MySQL database with information from one database:
mysqldump host
Since mysqldump exports the full SQL statement, it is easy to import data with the mysql client:
mysqladmin create target_db_name
mysql target_db_name < backup-file.sql
One, data backup shortcut
Since this method is not officially documented, let's call it an experiment.
Purpose: back up an mysql database TestA on the hostA host and restore to
on the hostB machine
Test environment:
Operating system: WinNT4. 0, Mysql3. 22.34, phpMyAdmin 2.1.0
Install the mysql database in hostA and set up the TestA database
hostB machine installs mysql database, not TestA database
Method steps:
Launch phpMyAdmin to view the list of databases in HostA and HostB. There is no TestA database
in HostB
Find the installation directory for mysql in HostA and the database directory data
In my test environment, this directory is
C: mysqldata
Find the subdirectory
for the database name
C: mysqldataTestA
Paste and copy to Data directory of HostB, HostA is the same as HostB Mysql data directory of
Refresh phpMyAdmin of HostB look at the database list, we can see that TestA has appeared, and make query modification and other operations are normal, backup restore and restore
successfully
Conclusion: the Mysql database can be saved, backed up and restored in the form of files as long as the corresponding file directory is restored, there is no need to use other tools to backup.
2. Formal method (official recommendation) :
Export using MySQL's mysqldump tool, the basic usage is:
mysqldump [OPTIONS] database [tables]
If you do not give any tables, the entire database will be exported.
By executing mysqldump --help, you can get the list of options that your version of mysqldump supports.
Note that if you run mysqldump without the --quick or --opt option, mysqldump will load the entire result set into memory before exporting the results, which could be a problem if you are exporting a large database.
mysqldump supports the following options:
- add - locks
Add LOCK TABLES before each table export and then UNLOCK TABLE. (for faster insertion to MySQL).
- add drop - table
Add an drop table statement before each create statement.
- allow - keywords
Allows you to create column names that are keywords. This is done by prefixing the column name with the table name.
- c - complete - insert
Use the full insert statement (with column names).
- C - compress
If both the client and the server support compression, compress all information between the two.
- delayed
Insert the line with the INSERT DELAYED command.
- e - extended - insert
Use the new multi-line INSERT syntax. (gives tighter and faster insert statements)
- #, - debug
[= option_string]
Trace program usage (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 choices are used with the -T choices and have the same meaning as the corresponding LOAD DATA INFILE clause.
LOAD DATA INFILE syntax.
- F - flush - logs
Before you start exporting, wash the log files from the MySQL server.
- f - force
Even if we get an SQL error during a table export, continue.
- h - host =..
Export data from the MySQL server on the named host. The default host is localhost.
- l -- lock - tables.
Locks all tables for the start export.
- t - no create - info
Do not write table creation information (CREATE TABLE statement)
- d - no - data
No row information is written to the table. This is useful if you only want to get an export of the structure of a table!
- opt
Same --quick drop table add locks extended lock-tables
Should give you the fastest possible export for reading into an MySQL server.
- pyour_pass - password
[= your_pass]
The password to use when connecting to the server. If you do not specify the "=your_pass" section, mysqldump requires a password from the terminal.
-P port_num, --port=port_num
The TCP/IP port number used when connecting to a host. (this is used to connect to a host other than localhost, because it USES Unix sockets.)
- q - quick
Unbuffered query, directly exported to stdout; Do it with mysql_use_result().
- S /path/to/socket, - socket = / path/to/socket
Socket file used when connecting to localhost, which is the default host.
- T - tab = path - to some - directory
For each given table, create an table_name.sql file, which contains the SQL CREATE command, and an table_name.txt file, which contains the data. Note: this only works if mysqldump runs 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 user name MySQL USES 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
Verbose mode. Print out more information about what the program did.
- V - version
Print version information and exit.
- w - where = 'where - condition
Export only selected records; Note that quotation marks are mandatory!
"-- where = user = 'jimf" "- wuserid> 1" "-wuserid < 1"
The most common mysqldump use makes it possible to make a backup of the entire database:
mysqldump --opt database > backup-file.sql
But it is also useful for populating another MySQL database with information from one database:
mysqldump host
Since mysqldump exports the full SQL statement, it is easy to import data with the mysql client:
mysqladmin create target_db_name
mysql target_db_name < backup-file.sql