MySQL experience 8-2-use SQL statements to back up and restore table data

MySQL experience 8-2-use SQL statements to back up and restore table data 1. Use SQL statements to back up and restore table data (for more information, see this article). You can use SELECT... The OUTFILE statement exports table DATA to a text file and uses the load data... The INFILE statement restores data. However, this method can only export or import data content, excluding the table structure. If the structure file of the table is damaged, the original table structure must be restored first. Select... OUTFILE format: SELECT * into outfile 'file _ name' export_options | DUMPFILE 'file _ name' where export_options is: [FIELDS www.2cto.com [terminated BY 'string'] [[optionally] enclosed by 'Char '] [escaped BY 'Char'] [lines terminated by 'string'] Description: this statement is used to write the row selected by the SELECT statement in the table to a file. file_name is the name of the file. Files are created on the server host by default, and the file name cannot already exist (this may overwrite the original file ). If you want to write the file to a specific location, you must add a specific path before the file name. In the file, data rows are stored in a certain form, and the null value is expressed as "\ N. When using OUTFILE, you can add the following two optional clauses to export_options to determine the format of data rows in the file: ● fields clause: the FIELDS clause contains three sub-sentences: terminated by, [OPTIONALLY] enclosed by, and escaped. If the FIELDS clause is specified, at least one of the three sub-statements must be specified. (1) terminated by is used to specify the symbols between field values. For example, "terminated by", "specifies a comma as a sign between two field values. (2) The enclosed by clause is used to specify the character value symbols in the package file. For example, "enclosed by '"' "indicates that the character value in the file is placed between double quotation marks, if the keyword OPTIONALLY is added, all values are placed between double quotation marks. (3) The escaped by clause is used to specify escape characters. For example, "escaped by '*'" specifies "*" as an escape character, replacing "\". if a space is used, it indicates "* N ". ● LINES clause: In the LINES clause, use terminated by to specify the end flag of a row, for example, "lines terminated '? '"Indicates that a row uses"?" As the end sign. If neither FIELDS nor LINES clause is specified, the following clause is declared by default: fields terminated by '\ t' enclosed by ''escaped by' \ 'lines terminated by' \ n' if DUMPFILE is used instead of OUTFILE, all the rows in the exported file are placed closely with each other, and there is no mark between the value and the row, forming a long value. 5. load data... The INFILE statement is select... OUTFILE statement, which can import data from a file to a database. 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,...)] www.: www.2cto.com ● LOW_PRIORITY | CONCURRENT: If LOW_PRIORIT is specified Y, then the execution of the statement is delayed. If CONCURRENT is specified, when load data is being executed, other threads can use the table DATA at the same time. ● LOCAL: if LOCAL is specified, the file will be read by the client on the client host and sent to the server. The file will be given a complete path name to specify the exact location. If a relative path name is given, the name is considered as relative to the directory where the client is started. If LOCAL is not specified, the file must be located on the server host and be directly read by the server. Compared to allowing the server to directly read files, the LOCAL speed is slightly slower, because the file content must be sent to the server through the client. ● File_name: name of the file to be loaded. The file stores the data rows to be stored in the database. The input file can be created manually or by using other programs. You can specify the absolute path of the file, such as "D:/file/myfile.txt". Then, the server searches for the file based on the path. If this parameter is not specified, for example, "myfile.txt", the server reads data from the database directory of the default database. If the file is "./myfile.txt", the server reads the file directly in the data directory, that is, the MySQL data DIRECTORY. For security reasons, when reading text files on the server, the files must be in the database directory or all readable. Note: A forward slash is used to specify the Windows path name, instead of a backslash. ● Tb_name: name of the table to be imported. the table must exist in the database and the table structure must be consistent with the data row in the import file. ● REPLACE | IGNORE: If REPLACE is specified, the input row replaces the original row when a unique keyword value identical to the original row appears in the file. If IGNORE is specified, the input row with the same unique keyword value as the original row is skipped. ● FIELDS clause: The FIELDS clause here is similar to the SELECT .. into outfile statement. Used to determine the symbols between fields and between data rows. ● LINES clause: The terminated by clause is used to specify the end mark of a row. The starting by sub-statement specifies a prefix. When importing data, ignore the prefix and the content before the prefix in the row. If a row does not include this prefix, the entire row is skipped. ● IGNORE number LINES: This option can be used to IGNORE the first few LINES of a file. For example, you can use IGNORE 1 LINES to skip the first line. Www.2cto.com ● col_name_or_user_var: If you want to load some columns in a table or the order of Field Values in the file is different from that in the table, you must specify a column list, which can contain column names or user variables. SET clause: The SET clause can modify the values of columns in the table when importing data. For example, to back up data in the KC table in the XSCJ database to the FILE directory on disk D, double quotation marks are required for field values if they are characters. Field Values are separated by commas (,). Is the end mark. Finally, import the backup data to a COURSE table with the same structure as the KC table. First export data: use xscj; SELECT * from kc into outfile 'd:/FILE/myfile1.txt 'fields TERMINATED by', 'optionallyenclosed by' "'Lines TERMINATED '? '; After the FILE is backed up, you can import the DATA in the FILE to the course table. Run the following command: load data infile 'd:/FILE/myfile1.txt 'into table course fields terminated ', 'optionallyenclosed BY '"'Lines TERMINATED '? '; Www.2cto.com Note: When importing data, you must specify the judgment symbol based on the format of the data row in the file. For example, in the myfile1.txt file, the field values are separated BY commas. When importing data, you must use the "terminated by ','" clause to specify commas as the delimiter between the Field Values and SELECT... Corresponds to the export OUTFILE statement. Because MySQL tables are saved as files, it is easy to back up. However, when multiple users use MySQL, a read lock is required for the related table to obtain a consistent backup to prevent the table from being updated during the backup process; when data is restored, a write lock is required to avoid conflicts. The table must be unlocked after the backup or recovery is complete. 2. Enable log binary logs can be enabled when the server is started. You need to modify the my. ini option file in the C: \ Program Files \ MySQL folder. Open the file, find the row where [mysqld] is located, and add the following format to the row: log-bin [= filename] Description: after this option is added, this option is loaded when the server is started to enable binary logs. If filename contains an extension, the extension is ignored. The MySQL Server adds a digital extension after each binary log name. This number is increased by 1 each time you start the server or refresh the log. If filename is not provided, the default host name is used. Assume that filename is named bin_log. If no directory is specified, the binary log file is automatically created under the MySQL data DIRECTORY. Because the following mysqlbinlog tool is used to process logs, the log must be in the bin directory, so the log Path is specified as the bin directory, and the added rows are changed to the following line: log-bin = C: /Program Files/MySQL/MySQLServer 5.1/bin/bin_log save and restart the server. You can restart the server by disabling the server first, and then starting the server: net start mysql. At this time, 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, which is stored in binary form and used to save database update information. When the log file size reaches the maximum, MySQL will automatically create a new binary file. Bin_log.index is a binary log index file automatically created by the server, which contains the names of all binary log files used. Use the mysqlbinlog utility to check binary log files. Command Format: mysqlbinlog [options] log-files... www.2cto.com Description: log-files is the binary log file name. For example, run the following command to view the bin_log.000001 content: mysqlbinlog bin_log.000001 because the binary data may be very large and cannot be extended on the screen, it can be saved to a text file: mysqlbinlogbin_log.000001> D: /FILE/lbin-log000001.txt command format for restoring data using logs: mysqlbinlog [options] log-files... | Mysql [options] example: assume that you use mysqldump to completely back up database XSCJ at one o'clock P.M. on Monday, and the backup file is file. SQL. Enable logs from one o'clock P.M. on Monday. The bin_log.000001 file stores all the changes from one o'clock P.M. on Monday to one o'clock P.M. on Tuesday, and runs an SQL statement: Flush logs at one o'clock P.M. on Tuesday. The bin_log.000002 file is created at this time, the database crashed at one o'clock P.M. on Wednesday. Now we want to restore the database to the status at one o'clock P.M. on Wednesday. First, restore the database to the State at one o'clock P.M. on Monday. In the DOS window, enter the following command: mysqldump-uroot-p123456 XSCJ <file. SQL uses the following command to restore the database to the State at noon on Tuesday: mysqlbinlog bin_log.000001 | mysql-uroot-p123456 and then use the following command to restore the database to the State at one o'clock P.M. on Wednesday: mysqlbinlog bin_log.000002 | mysql-uroot-p123456 because log files occupy a large amount of hard disk resources, it is necessary to clear useless log files in time. Use the following SQL statement to clear all log files: www.2cto.com Reset master. to delete some log files, use the purge master logs statement. Syntax format: PURGE {MASTER | BINARY} logs to 'Log _ name' or: PURGE {MASTER | BINARY} logs before 'date' description: the first statement is used to delete a specific log file. log_name is the file name. The second statement is used to delete all log files before date. Both MASTER and BINARY are synonyms. Author tianyazaiheruan