Backup and Restore a MySQL DB

[omaggi]

Hi everyone.
I search in the forum but I didn't find anything really "perfect" for my problem...

In my project in Access 2007 I use a MySQL database.
In a form there are 2 buttons (one is backup and the other restore).
I want with the help of VBA to do a complete backup of all tables in my MySQL database and also have the opportunities to do a restore.

It will be perfect if the backup of the tables will create ONLY 1 file and the name has the current date...

Thanks a lot for all the possible suggestions...

 

[RainLover]

Do you have PHP MyAdmin.

If you do then that will write the code for you.

I have never done this but believe it should work.

 

[omaggi]

Yes I have PHPMyAdmin...

 

[RainLover]

I just did a test and the Code that PHP writes is far too complex and simply not suitable.

The other thing to do is to manually export the Tables and Data to a back up file.

But I don't know how to do this in Access.

I always use PHPMyAdmin to do my backups.

Sorry I have not been much help here.

I assume you do know how to back up using PHPMyAdmin.

 

[omaggi]

Yes, with PHPMyAdmin I don't have problems to backup but for my project I need to use VBA and make this backup automatically when I click on a button...
Thx for your help.

Any other suggestions?

UP...

 

[RainLover]

No other suggestions at the moment, but you problem does interest me.

Are you working on a Local Server or Remote.

 

[omaggi]

At the moment I work on my computer but at the end of my project it will be on a local server...

Greetings

 

[Banana]

It will be perfect if the backup of the tables will create ONLY 1 file and the name has the current date...

You didn't say where it will be backed up or what version MySQL it will be and whether the server is hosted.

If you intend to store the backup on the server (e.g. same machine where the MySQL is actually running), then you must realize that you cannot overwrite any file from within MySQL. This is a security precaution to prevent malicious user from using MySQL to randomly destroy files on the server. Thus MySQL will allow you only to write a new file if there are no file by that name.

You could write a { bash script and a cron job | batch file and Windows Task Scheduler } to automatically delete the old file and run the mysqldump or mysqlimport program at regular interval. You can also use Administrator GUI to do this for you.

If you intend that the backup be copied to different machine, then more details will be needed.

You also say you have PHPMyAdmin but you say it's not right... just exactly what make it not right for the job?

You also say you want to use VBA, in which the one option I can think of doing from VBA driving a server remotely is to run a SELECT ... INTO OUTFILE ... SQL command. For reasons of keeping it practical and straightforward, you'd have to run as many SELECT ... INTO OUTFILE as you have many tables. Note that this writes a backup into server's filesystem and you must have FILE privilege.

The other option is to have Access do a 'Import' if you want to get your backup into the client.

There are few more such as replication but it all depends on where you want the file to go, how much control you have over the server, and version of the MySQL.

 

[omaggi]

Thx I tried the "outfile" and goes well!
Thanks for the suggestions...

In fact I use MySQL 5.1.37...

Greetings