mysql backup program
Author: admin
November 11, 2011
First, the database backup and recovery strategy
a) directly
copy files directly to / usr / share / TSMIS / mysql / test folder to the other places.
mysqlhotcopy db_name / path / to / some / dir
only for MyISAM tables, the table is actually copied the file (. frm,, MYI,. MYD)
Disadvantages: will lock the table. Can only be restored to the status of the last backup.
advantages: speed. 450 000 data took 17s.
b) Mysqldump
i. mysqldump – tab = / path / to / some / dir – opt db_name
ii.
1. on the binary log, restart the service.
the mysqld section in my.cnf, add:
in the corresponding directory will have a mysql-bin.000001 mysql-bin.index file. This data update, insert, delete operation, the response of the operation will be recorded in mysql-bin.xxxxxx file.
2. using mysqldump backup, plus the – flush-logs – delete-master-log option, backup, will delete the previous binary log file.
3. recovery time, need to first mysqldump backup file recovery. , Use the mysqlbinlog tool to convert the binary log to re-import the sql.
mysqlbinlog hostname-bin. [0-9] *> test_bk.sql
advantages: you can completely backup.
Disadvantages: back up at a slower pace. 450 000 data 30s or so. Log space.
c) through the program to synchronize
i. Create a backup of the database test_bak, regularly perform a task, the test data dump test_bak in. When the test problems when the test_bak dump the data re-test in.
ii.
Second, the database table structure finishing
a) a car information table will be merged, deleted useless tables, delete unwanted stored procedure.
i. remove the brand, subbrand, uploadqueue related tables.
ii. merge several tables vehicleinfo, vehiclelicense, vehicletype, bodycolor, picture of ehicleinfo, delete them: colorname, add: colorcode
iii. delete the stored procedure, retaining only inserttrafficdata and deletehistorydata.
iv. view whether to keep the vehicle?
v. the other table: role-related tables are also deleted.
vi. increase the dictionary table, the global configuration table, history of the state table, the operation log table.
b) other places you want to modify
c) police power collated video problem
i.
10.67 .51.151
Tags: subbrand
Comments are closed.