Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to backup and restore data tables in MySQL. Many people may not know much about it. In order to let everyone know more, Xiaobian summarizes the following contents for everyone. I hope you can gain something according to this article.

You can back up data using SELECT INTO OUTFILE and restore data using LOAD DATA INFILE. This method can only export the contents of the data, not the table structure. If the table structure file is damaged, you must first restore the original table structure.

Grammar:

SELECT * INTO {OUTFILE | DUMPFILE} 'file_name' FROM tbl_name

LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]

INTO TABLE tbl_name

SELECT ... SELECT statements of the format INTO OUTFILE 'file_name' write the selected rows to a file. Files are created on the server host and cannot be already present (which prevents database tables and files such as "/etc/passwd" from being corrupted, among other things). SELECT ... INTO OUTFILE is the inverse of LOAD DATA INTO FILE.

The LOAD DATA INFILE statement reads from a text file into a table at a high rate. If you specify LOCAL keyword, read file from client host. If LOCAL is not specified, the file must be located on the server. (LOCAL is available in MySQL 3.22.6 or later.)

For security reasons, when reading a text file located on a server, the file must be in the database directory or accessible to everyone. In addition, in order to use LOAD DATA INFILE for files on the server, you must have file permissions on the server host. To use this SELECT INTO OUTFILE statement, you must have FILE privileges on the server host.

To avoid duplicate records, you need a PRIMARY KEY or UNIQUE index in the table. When a new record duplicates an old record at a unique index value, the REPLACE keyword causes the old record to be replaced with a new record. If you specify IGNORE, skip entries for duplicate rows of existing rows with unique indexes. If you do not specify either option, an error occurs when duplicate index values are found and the rest of the text file is ignored.

If you specify the keyword LOW_PRIORITY, execution of the LOAD DATA statement is deferred until no other customers read the table.

Using LOCAL will be slower than letting the server access the file directly, because the contents of the file must be transferred from the client host to the server host. On the other hand, you do not need file permission to load local files. If you load data from a local file using the LOCAL keyword, the server has no way to stop the file transfer in the middle of the operation, so the default behavior is as if IGNORE was specified.

When looking for files on a server host, the server uses the following rules:

If an absolute pathname is given, the server uses that pathname.

Given a relative pathname with one or more predecessor parts, the server searches the file against the server's data directory.

If given a filename without a prefix, the server looks for the file in the database directory of the current database.

Assuming that table tbl_name has a PRIMARY KEY or UNIQUE index, the process for backing up a data table is as follows:

1. Lock the data table to avoid updating the table during the backup process.

mysql>LOCK TABLES READ tbl_name;

2. Export data

mysql>SELECT * INTO OUTFILE ‘tbl_name.bak’ FROM tbl_name;

3. Unlock table

mysql>UNLOCK TABLES;

The corresponding procedure for restoring backup data is as follows:

Add a write lock to the table:

mysql>LOCK TABLES tbl_name WRITE;

2. Recovery of data

mysql>LOAD DATA INFILE ‘tbl_name.bak’

->REPLACE INTO TABLE tbl_name;

If you specify a LOW_PRIORITY keyword, you do not have to lock the table as above, because the import of data will be delayed until no customers read the table:

mysql>LOAD DATA LOW_PRIORITY INFILE ‘tbl_name’

->REPLACE INTO TABLE tbl_name;

3. Unlock table

Recovery of data using mysqlimport

If you are only restoring data, then there is absolutely no need to execute SQL statements in the client, because you can simply use the mysqlimport program, which corresponds exactly to the LOAD DATA statement, by sending a LOAD DATA INFILE command to the server. Execute mysqlimport --help and look at the output carefully. You can get help here.

shell> mysqlimport [options] db_name filename ...

For each text file named on the command line, mysqlimport strips the file name extension and uses it to determine which table imports the file's contents. For example, the name "patient.txt",

"patient.text" and "patient" will all be imported into a table named patient.

Common options are:

-C, --compress If both client and server support compression, compress all information in between.

-d, --delete Empty tables before importing text files.

l, --lock-tables Write all tables specified before processing any text file. This ensures that all tables are synchronized on the server.

--low-priority, --local, --replace, --ignore correspond to LOW_PRIORITY, LOCAL, REPLACE, IGNORE keywords of LOAD DATA statements, respectively.

For example, restore the data of table tbl1 in database db1, and save the data file as tbl1.bak. Suppose you are on the server host:

shell>mysqlimport --lock-tables --replace db1 tbl1.bak

This locks the table before restoring the data. You can also use the--low-priority option:

shell>mysqlimport --low-priority --replace db1 tbl1.bak

If you are recovering data for a remote server, you can also do this:

shell>mysqlimport -C --lock-tables --replace db1 tbl1.bak

Of course, decompression consumes CPU time.

Like any client, you may need to provide-u, -p options to authenticate, or you can store these parameters in the options file my.cnf, just like any other client, but I won't go into detail here.

mysql->UNLOCAK TABLES;

Backup data with mysqldump

Like mysqlimport, there is a tool called mysqldump that backs up data, but it does more than SQL statements by including SQL statements in exported files, so you can back up the structure of database tables, and you can back up a database, or even an entire database system.

mysqldump [OPTIONS] database [tables]

mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

mysqldump [OPTIONS] --all-databases [OPTIONS]

If you don't give any tables, the entire database will be dumped.

By executing mysqldump --help, you can get a list of supported options for your version of mysqldump.

1. Method of backing up database

For example, suppose you back up database db_name on the server host

shell> mydqldump db_name

Of course, since mysqldump positions output to standard output by default, you need to redirect standard output.

For example, back up the database to bd_name.bak:

shell> mydqldump db_name>db_name.bak

You can back up multiple databases, note that this method will not specify tables:

shell> mydqldump --databases db1 db1>db.bak

You can also back up copies of the entire database system, but for a large system, there is no practical value in doing so:

shell> mydqldump --all-databases>db.bak

Although it is useful to export the structure of tables with mysqldump, when recovering large amounts of data, the large number of SQL statements makes recovery inefficient. You can separate data from SQL statements that create tables by using the--tab option.

-T, --tab= Create a data file with tab-separated column values and a file containing SQL statements to create table structures in the directory specified by the option, denoted by the extensions.txt and.sql, respectively. This option cannot be used with--databases or--all-databases, and mysqldump must be running on the server host.

For example, suppose database db contains tables tbl1, tbl2, and you are ready to back them up to/var/mysqldb

shell>mysqldump --tab=/var/mysqldb/ db

The effect is to generate four files in the directory/var/mysqldb, tbl1.txt, tbl1.sql, tbl2.txt, and tbl2.sql.

2, mysqldump utility authentication problems

As with any client, you must also provide a MySQL database account to export the database, if you are not using anonymous users, you may need to manually provide parameters or use options files:

If so:

shell>mysql -u root –pmypass db_name>db_name.sql

Or you can provide parameters in the options file like this:

[mysqldump]

user=root

password=mypass

then perform

shell>mysqldump db_name>db_name.sql

All goes well and there will be no problems, but be aware that the command history may leak passwords or make the options file inaccessible to anyone other than you. Since the database server also needs the options file, the options file can only be owned and accessed by the user who started the server (e.g. mysql), so as not to leak. Under Unix you have a workaround, which is to provide a personal options file (~/.my.cnf) in your own user directory, for example,/home/some_user/.my.cnf, and then add the above contents to the file to prevent leaks.

After reading the above, do you have any further understanding of how to backup and restore data tables in MySQL? If you still want to know more knowledge or related content, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.