MySQL database backup basics

Source: Internet
Author: User
Keywords Network programming Mysql tutorial
Tags backup backup server basics connecting create data data backup database backup

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.