MySQL database maintenance and performance improvements

In order to effectively prevent data loss and minimize the loss, the database server should be maintained. Database maintenance, including data backup, restore, export and import operations.

1. mysql Database backup

The so-called database maintenance, mainly includes backup data, restore data and database migration, for MySQL, also includes database object table export and import.

1.1 Data backup by copying data files

There are many reasons for data loss, including the following:

A. Storage media failure: The disk device that saved the database file is corrupted, and the user does not have a database backup resulting in a complete loss of data.

B. User error actions: If you mistakenly delete some important data, or even the entire database.

C. The server is completely paralyzed and the system needs to be rebuilt.

Since the data files in the MySQL server are disk-based text files, the simplest and most direct backup operation is to copy the database files directly. This approach is inappropriate for INNODB storage engine tables and is suitable only for tables that store engine MyISAM.

1.2 Using command mysqldump for data backup

There are three forms: backing up a database, backing up multiple databases, and backing up all databases.

1.2.1 Backing up a database

- -  > backupname.sql

Examples are as follows:

Backup Database Company under the table t_dept;

- - > d:\t_dept_back.sql

1.2.2 Backing up multiple databases

- - -- databases dbname1 dbname2 ... dbname3 > Backupname.sql

Examples are as follows:

Back up the database company database and Companynew database:

- - -- databases company Companynew> D:\database_company_back.sql

1.2.3 Backing up all databases

- - -- all  > Backupname.sql

Examples are as follows:

Back Up all databases:

- - -- all  > C:\all_database_back.sql

2. mysql Data restore

2.1 Data restore by copying data files

When restoring data by copying data files, it is necessary to ensure that the major version numbers of the two MySQL databases are identical, because only the major version number of the MySQL database is consistent, the file types of the two MySQL databases are guaranteed to be the same. Because data backups are made by replicating data files, tables that have a storage engine type of I:NNODB are not available and are only valid for tables with the storage engine type MyISAM. Therefore, when data is restored by copying a data file, it is only valid for tables that store type MyISAM.

Note: The version number of the MySQL database server, the first number represents the major version number.

2.2 Data restore by command MySQL

- - - [dbname] < Backname.sql

Examples are as follows:

Restore the database in the company table t_dept:

- - - < T_dept_back.sql

3.MySQL database tables exported to a text file

3.1 Execute Select ... into OUTFILE implement export to text file

SELECT [file_name]  from [where Condition]  into ' file_name ' [OPTION]

Examples are as follows:

The implementation will export all the data in the table T_dept to the file t_dept:

SELECT *  fromT_dept intoOUTFILE'C:/t_dept_1.txt'Fields TERMINATED by '\.'Optionally enclosed by '\"'LINES starting by '\>'TERMINATED by '\ r \ n';

The above implementation of the table t_dept all the data exported to the file T_dept_1.txt file, but also set the corresponding display format.

3.2 Execute Command mysqldump implement export to text file

- - - T file_directory dbname table_name[option];

Examples are as follows:

Export the corresponding data to the text file T_dept.txt:

- - - T C:\Company t_dept;

The above implementation exports all the data in the table T_dept to the file t_dept.

3.3 Execute command MySQL implementation export to text file

- - -E "select[file_name]from>file_name;

Examples are as follows:

- - -E "select* from T_dept"  company> C:/T_ Dept.txt;

4. mysql implementation text file import into database table

4.1 Implementing the "LOAD DATA INFILE" command to import a text file into a database table

LOAD DATA[LOCAL]file_nameintoTABLE[OPTION ];

Examples are as follows:

LOAD ' C:/t_dept.txt '  into TABLE '\.' \ "'\ r \ n';       

4.1 Execute Command mysqlimport implement import text file

- -ppassword[--local]file_name[OPTION]

Examples are as follows:

- - '\.' \ "'\>'\ r \ n';        

5. Database Migration

5.1 Migration between MySQL databases of the same version

This kind of database migration is easiest to implement.

5.2 Migrations between different versions of MySQL databases

Two ways: The lower version to the higher version and the higher version to the lower version.

5.3 Migration between different databases

There is no universally applicable approach due to the differences before different databases.

6. Data performance optimization

MySQL database maintenance and performance improvements