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

This article introduces the knowledge about "MySQL how to backup and restore table data with SQL statements". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

1. Backup and restore table data using SQL statements (mention, no details)

Users can export table data to a text file using the SELECT INTO…OUTFILE statement and recover the data using the LOAD DATA …INFILE statement. However, this method can only export or import the contents of the data, excluding the structure of the table. If the structure file of the table is damaged, the original table structure must be restored first.

SELECT INTO…OUTFILE Format:

SELECT * INTO OUTFILE 'file_name' export_options

|DUMPFILE 'file_name'

where export_options are:

[FIELDS

[terminated BY 'string']

[[optionally] ENCLOSED BY 'char']

[escaped BY 'char' ]

]

[LINES TERMINATED BY 'string' ]

Description:

The effect of this statement is to write the rows selected in the SELECT statement to a file, where file_name is the name of the file. Files are created on the server host by default, and the filename cannot be one that already exists (this may overwrite the original file). If you want to write the file to a specific location, prefix the file name with the specific path. In a file, data lines are stored in a certain form, and null values are represented by "\N".

When using OUTFILE, you can add the following two optional clauses to export_options, which determine the format of the data rows in the file:

Fields clause: There are three subclauses in the Fields clause: TERMINATED BY,[OPTIONALLY] ENCLOSED BY, and ESCAPED BY. If a FIELDS clause is specified, specify at least one of these three subclauses.

TERMINATED BY is used to specify the sign between field values; for example,"TERMINATED BY','specifies a comma as the sign between two field values.

(2) The ENCLOSED BY clause is used to specify the symbol that wraps the character values in the file. For example,"ENCLOSED BY ' ""means that the character values in the file are placed between double quotes. If the keyword OPTIONALLY is added, all values are placed between double quotes.

(3) The ESCAPED BY clause is used to specify escape characters, for example,"ESCAPED BY '*'" specifies "*" as an escape character instead of "\," such as spaces would be represented as "*N."

LINES clause: Use TERMINATED BY in the LINES clause to specify the end-of-line flag, such as "LINES TERMINATED BY '? "" means a line with "? "as an end sign.

If neither the FIELDS nor LINES clauses are specified, the following clauses are declared by default:

FIELDS TERMINATED BY '\t'ENCLOSED BY '' ESCAPED BY '\\'

LINES TERMINATED BY '\n'

If you use DUMPFILE instead of OUTFILE, all the lines in the exported file are placed next to each other, and there are no marks between the values and the lines, resulting in a long value.

5. The LOAD DATA …INFILE statement is the complement of the SELECT INTO…OUTFILE statement, which imports data from a file into.

LOAD DATA …INFILE Format:

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

[replace | ignore]

INTO TABLE tbl_name

[FIELDS

[TERMINATED BY 'string']

[[OPTIONALLY] ENCLOSED BY 'char']

[ESCAPED BY 'char' ]

]

[LINES

[STARTING BY 'string']

[TERMINATED BY 'string']

]

[IGNORE number LINES]

[(col_name_or_user_var,...)]

[SET col_name = expr,...)]

Description:

● LOW_PRIORITY |CONCURRENT: If LOW_PRIORITY is specified, execution of the statement is delayed. If CONCURRENT is specified, the table's data can be used by other threads while LOAD DATA is executing.

LOCAL: If LOCAL is specified, the file is read by the client on the client host and sent to the server. The file is given a full path name to specify the exact location. If a relative path name is given, the name is interpreted relative to the directory in which the client was started. If LOCAL is not specified, the file must be located on the server host and read directly by the server. Using LOCAL is slightly slower than having the server read the file directly because the contents of the file must be sent to the server via the client.

file_name: The name of the file to be loaded, in which the data lines to be stored in the database are saved. Input files can be created manually or using other programs. You can specify an absolute path to the file, such as "D:/file/myfile.txt," and the server searches for files based on that path. If you do not specify a path, such as "myfile.txt," the server reads in the database directory of the default database. If the file is ".../ myfile.txt", then the server reads directly from the data directory, that is, MySQL's data directory. For security reasons, when reading a text file located on the server, the file must either be located in the database directory or be readable in its entirety.

Note: Windows path names are specified here using forward slashes, not backslashes.

tb_name: The table name of the data to be imported. The table must exist in the database, and the table structure must be consistent with the data rows of the imported file.

● REPLACE |IGNORE: If REPLACE is specified, the input row replaces the original row when the same unique keyword value appears in the file as the original row. If IGNORE is specified, input rows with the same unique key value as the original row are skipped.

● FIELDS clause: FIELDS clause and SELECT here.. Similar in the INTO OUTFILE statement. Used to determine symbols between fields and between data lines.

LINES clause: TERMINATED BY subclause used to specify the end of a line flag. The STARTING BY subclause specifies a prefix that, when importing rows of data, ignores the prefix and everything that precedes it. If a row does not include the prefix, the entire row is skipped.

IGNORE number LINES: This option can be used to ignore the first few lines of the file. For example, IGNORE 1 LINES can be used to skip the first line.

www.2cto.com

col_name_or_user_var: If you want to load some columns of a table or fields in a file in a different order than the columns in the table, you must specify a list of columns, which can contain column names or user variables.

SET clause: The SET clause allows you to modify the values of columns in a table when importing data.

For example: backup the data in KC table in XSCJ database to FILE directory of D disk. If the field value is a character, mark it with double quotation marks. The field values are separated by commas. Each line is marked with "? "It's the end sign. Finally, the backup data is imported into an empty table COURSE table with the same structure as the KC table.

First export the data:

USE XSCJ;

SELECT * FROM KC

INTO OUTFILE'D:/FILE/myfile1.txt'

FIELDS TERMINATED BY ' , '

OPTIONALLYENCLOSED BY ' " '

LINES TERMINATED BY '? ';

After the file is backed up, you can import the data in the file into the COURSE table by using the following command:

LOAD DATA INFILE 'D:/FILE/myfile1.txt'

INTO TABLE COURSE

FIELDS TERMINATED BY ' , '

OPTIONALLYENCLOSED BY ' " '

LINES TERMINATED BY '? ';

www.2cto.com

Note: When importing data, you must specify the symbols to judge according to the format of the data lines in the file. For example, in myfile1.txt file, field values are separated by commas. When importing data, be sure to use the "TERMINATED BY ','" clause to specify commas separators between field values, corresponding to SELECT…INTOOUTFILE statement.

Because MySQL tables are saved as files, backups are easy. However, in the case of multiple users using MySQL, in order to get a consistent backup, a read lock on the associated table is required to prevent the table from being updated during the backup process; when restoring data, a write lock is required to avoid conflicts. After backup or restore, unlock the table.

2. log-enabled

Binary logging can be enabled at server startup by modifying the my.ini options file in the C:\Program Files\MySQL folder. Open the file, find the line [d], and add a line of the following format to the end of that line:

log-bin[=filename]

Note: When this option is added, it is loaded at server startup, enabling binary logging. If filename contains an extension, the extension is ignored. MySQL Server adds a numeric extension to each binary log name. This number increases by 1 each time the server is started or the log is refreshed. If filename is not given, it defaults to the host name. Let's say filename is named bin_log. If no directory is specified, binary log files are automatically created under MySQL's data directory. Since the log must be in the bin directory when the mysqlbinlog tool is used to process the log below, the path of the log is specified as the bin directory, and the added line is changed to the following line:

log-bin=C:/Program Files/MySQL/MySQLServer 5.1/bin/bin_log

Save and restart the server. Restarting the server can be:

Shut down the servers,

net stop mysql

Restart the server:

net start mysql

At this point, there are two more files in the bin directory of the MySQL installation directory: bin_log.000001 and bin_log.index. bin_log.000001 is a binary log file, stored in binary form, used to save database update information. When the log file size reaches its maximum, MySQL also automatically creates a new binary file. bin_log.index is a binary log index file automatically created by the server and contains the file names of all binary log files used.

Binary log files can be examined using the mysqlbinlog utility. Command format: mysqlbinlog[options] log-files... www.2cto.com

Log-files is the file name of the binary log.

For example, run the following command to view the contents of bin_log.000001:

mysqlbinlog bin_log.000001

Since binary data can be very large and cannot be stretched on the screen, it can be saved to a text file:

mysqlbinlogbin_log.000001>D:/FILE/lbin-log000001.txt

The command format for recovering data using logs is as follows:

mysqlbinlog [options] log-files… |mysql [options]

Example: Suppose the user uses the mysqldump tool to make a full backup of database XSCJ at 1 p.m. on Monday, and the backup file is file.sql. From Monday at 1 p.m. User Enable Log, bin_log.000001 file holds all changes from Monday at 1 p.m. to Tuesday at 1 p.m. Run a SQL statement on Tuesday at 1 p.m.:

Flush logs;

The bin_log.000002 file was created and the database crashed at 1 p.m. on Wednesday. We are now going to restore the database to its state at 1 p.m. on Wednesday. To restore the database to Monday at 1 p.m., enter the following command in a DOS window:

mysqldump -uroot -p123456 XSCJ

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.