MySQL experience 8-1-use a client program to back up and restore _ MySQL

MySQL experience 8-1-use a client program to back up and restore bitsCN. comMySQL experience 8-1-use the client program to back up and restore 1. the loss or destruction of data in the backup and recovery requirements analysis database may be due to the following reasons: (1) Computer hardware failure. Due to improper use or product quality, computer hardware may fail and cannot be used. If the hard disk is damaged, data stored on it will be lost. (2) software faults. Data may be damaged by misoperations in the software system due to software design errors or improper use by users. (3) virus. Destructive viruses damage system software, hardware, and data. (4) misoperation. If the user mistakenly uses commands such as DELETE and UPDATE, the data may be lost or damaged. (5) natural disasters. Such as fire, flood, or earthquake, they can cause great damage and destroy computer systems and their data. (6) theft. Some important data may be stolen. Therefore, you must create a copy of the database, that is, back up the database. when the database is damaged, you can restore the database, restoring a database is to restore the database from the error State to a correct state. Backup and recovery can also be used for other purposes. for example, you can move the database from one server or copy it to another server through backup and recovery. 2. data recovery methods may lead to data TABLE loss or server crash. a simple drop table or drop database statement will render the data TABLE useless. The more dangerous thing is that DELETE * FROM table_name can easily clear the data table, and such an error is very easy to happen. Therefore, it is very important for a database system to have data that can be recovered. MySQL has three methods to ensure data security. (1) database backup: protects data by exporting data or copying table files. (2) binary log file: All statements for saving updated data. (3) database replication: the MySQL internal replication function is implemented between two or more servers by setting the master-slave relationship between them. One of them serves as the master server and the other as the slave server. This section describes the first two methods. Database recovery is to load the backed up database to the system when the database fails, so that the database is restored to the correct state during backup. Recovery refers to system maintenance and management operations corresponding to backup. when the system performs recovery operations, it first performs some system security checks, this includes checking whether the database to be restored exists, whether the database is changed, and whether the database files are compatible, and then taking corresponding recovery measures based on the database backup type used. 3. using the client program to back up and restore MySQL provides many free client programs and utilities. different MySQL client programs can connect to the server to access the database or execute different management tasks. These programs do not communicate with the server, but can perform MySQL-related operations. These client programs are stored in the BIN subdirectory under the MySQL directory. Here we will briefly introduce the mysqldump program and mysqlimport program. Use the client method: Open the DOS terminal and enter the BIN directory. The path is C:/Program Files/MySQL/MySQLServer 5.1/bin. all the client commands described later are entered here, 1 ). using mysqldump to back up data mysqldump client can also be used to back up data. it does more work than SQL statements to include SQL statements of table structure in exported files. Therefore, you can back up the structure of database tables, you can also back up a database or even the entire database system. (1_1) backup table command format: mysqldump [options] db_name [tables]> filename description: options is an option supported by the mysqldump command, you can run the mysqldump-help command to obtain the mysqldump option table and help information, which is not listed in detail here. Db_name is the database name, followed by the name of the table to be backed up. Filename is the name of the last backup file. if there are multiple tables in this statement, the files are saved in this file. the default file storage address is the bin directory of MySQL. If you want to save it to a specific location, you can specify its path. Note that the file name cannot already exist in the directory. Otherwise, the new backup file will overwrite the original file, causing unnecessary trouble. Like other client programs, you need to use a user account to connect to the server when backing up data. This requires you to manually provide parameters or modify related values in the option file. The parameter format is-h [hostname]-u [username]-p [password] where-h is the host name,-u is the user name, and-p is the user password, no space is allowed between the-p option and the password. For example, use mysqldump to back up the XS and KC tables. The specific command is as follows: mysqldump-h localhost-u root-p123456XSCJ xs kc> twotables. SQL description: if it is a local server, The-h option can be omitted. In the bin directory of MySQL, you can see that a. SQL file has been saved, which stores a series of SQL statements for creating XS and KC tables. Note: If the table name is not in the command, the whole database is backed up. (1_2) the mysqldump program can also back up one or more databases to one file. Command format: mysqldump [options] -- databases DB1 [DB2 DB3. ..]> filename example: back up the XSCJ database and mysql database to the FILE folder on the D disk. Command: mysqldump-uroot-p123456 -- databases XSCJmysql> D:/FILE/data. SQL description: data in the FILE folder after the command is executed. the SQL file is created, and all SQL statements of the XSCJ database and mysql database are stored. Mysql can also back up the entire database system, that is, all databases in the system. For example, back up all databases on the MySQL server. Run the following command: mysqldump-uroot-p123456 -- all-databases> all. although SQL uses mysqldump to export the table structure, if the data volume is large during data recovery, a large number of SQL statements will reduce the recovery efficiency. You can use the -- tab = Option to separate data from the SQL statement used to create a table. --Tablename creates a. txt format file that stores data and a. SQL format file that contains the SQL statements used to create table structures. This option cannot be used with -- databases or -- all-databases, and mysqldump must run on the server host. For example, back up the table structure and data of all tables in the XSCJ database to the FILE folder on disk D. Command: mysqldump-uroot-p123456 -- tab = D:/FILE/XSCJ syntax and xs_kc. SQL. (1_3) the files backed up by mysqldump store a collection of SQL statements. you can restore these statements to the server to restore a corrupted database. For example, if the XSCJ database is damaged, use the backup file to restore it. Command for backing up XSCJ database: mysqldump-uroot-p123456 XSCJ> XSCJ. SQL recovery command: mysql-uroot-p123456 XSCJ Author tianyazaiheruanbitsCN.com