MySQL backup solution

I. database backup and recovery policies

A) Directly copy the object

Directly copy the/usr/share/TSMIS/MySQL/test folder to another place.

Mysqlhotcopy db_name/path/to/Some/Dir

Only suitable for MyISAM tables. In fact, it is used to copy table files (. frm, myi,. MYD)

Disadvantage: The table is locked. Only the status of the last backup can be restored.

Advantage: high speed. 0.45 million pieces of data took 17 s.

B) mysqldump

I. mysqldump -- tab =/path/to/Some/dir -- opt db_name

II.

1. Enable binary logs and restart the service.

In mysqld section of my. CNF, add:

A mysql-bin.000001 mysql-bin.index file is generated under the appropriate directory. When you perform update, insert, or delete operations on the data, the response is recorded in the mysql-bin.xxxxxx file.

2. When mysqldump is used for backup, The -- flush-logs -- delete-master-log option is added. After backup, the previous binary log files will be deleted.

3. Restore the file backed up by mysqldump. Then, use the mysqlbinlog tool to convert binary logs into SQL and re-import them.

Mysqlbinlog hostname-bin. [0-9] *> test_bk. SQL

Advantage: Full backup is supported.

Disadvantage: the backup speed is a little slow. About 30 seconds of data. Log space.

C) PassProgramSynchronize

I. Create a backup database test_bak, regularly execute a task, and transfer the data in test to test_bak. When the test fails, the data in test_bak is re-transferred to test.

II.

Ii. Database Table Structure Arrangement

A) Merge the vehicle information table, delete useless tables, and delete useless stored procedures.

I. Delete tables related to brand, subbrand, and uploadqueue.

Ii. Merge the vehicleinfo, vehiclelicense, vehicletype, bodycolor, and picture tables into ehicleinfo tables. Delete the colorname and add the colorcode

Iii. Delete the stored procedure and only retain inserttrafficdata and deletehistorydata.

Iv. Is the view vehicle retained?

V. Other Tables: tables related to role are also deleted.

Vi. Added dictionary tables, global configuration tables, historical status tables, and Operation Log tables.

B) other places to be modified

C) video and electric warning table sorting

I.

10.67.51.151