mysql备份两个表_MySQL日常备份从一个表到另一个表

If I have 2 tables with the same definition, how would I backup data from it daily? Can I use mySQL Administrator to perform something like this

At 12:00am everyday, copy all the rows from main_table to backup_table

It will be preferable if it is incremental backup as some changes will be made to the reccords in backup_table and I don't want a new backup to wipe out those changes.

Thanks

解决方案

Let's start with this: Copying data from one table to another on the same server IS NOT a backup.

Now, if you have MySQL 5.1.6 or newer, you can use event scheduler, to schedule such actions.

It should be as simple as running a query like this

INSERT INTO

secondarydatabase.tableName

SELECT pr.* FROM

primarydatabase.tableName AS pr

LEFT JOIN

secondarydatabase.tableName AS sec

USING(primaryKeyColumn)

WHERE sec.primaryKeyColumn IS NULL

(this will copy any new rows, changes in already existing rows will not be copied over)

As far as actual backups go, please read this: