MySQL: Automatically download tables

[Gene]

I have a MySQL database containing many tables. Two of those tables need to be downloaded on a daily basis at 9am. How can I go about automating this process? I simply need to download the two tables in CSV format to a directory on the server. Does this require a 'cron job'? Or is there a procedure that is stored in the database somehow? Thanks in advance for any advice!

 

[stscac]

Looking for a server based solution or a paid desktop solution?

A quick google search shows http://www.swordsky.com/

A server-based one is http://www.dagondesign.com/articles/automatic-mysql-backup-script/

I'm sure someone can modify it to fit your needs.

-Steve

 

[Gene]

Thanks Steve, Server-based is likely what I need, but I don't know enough about this to know what to look for. Is specialized software really necessary? I thought a simple command line somewhere would achieve the goal of downloading the CSV files.

 

[stscac]

Thanks Steve, Server-based is likely what I need, but I don't know enough about this to know what to look for. Is specialized software really necessary? I thought a simple command line somewhere would achieve the goal of downloading the CSV files.

Its a mixture of a crontab, a mysql query to extract the database, and then use php (or language of choice) to put it on the server.

I'll see what I can come up with.

Edit: Looks like this might be what you're looking for. With a little modification, it can be added to the server.

:tu:

-Steve

 

[Gene]

Thanks for finding that, but I don't think it will do what I need. I need to download two of the 'tables' that reside in one of my databases, and save these two tables as a CSV file to a folder on the server on a daily basis. No other database files should be included. Just two CSV files. No backup rotation. The same files should be overwritten every day.

 

[stscac]

Thanks for finding that, but I don't think it will do what I need. I need to download two of the 'tables' that reside in one of my databases, and save these two tables as a CSV file to a folder on the server on a daily basis. No other database files should be included. Just two CSV files. No backup rotation. The same files should be overwritten every day.

If you can't find your fit, PM me and I can try to come up with a custom solution.

-Steve

 

[Kate]

I have a MySQL database containing many tables. Two of those tables need to be downloaded on a daily basis at 9am. How can I go about automating this process? I simply need to download the two tables in CSV format to a directory on the server. Does this require a 'cron job'? Or is there a procedure that is stored in the database somehow? Thanks in advance for any advice!

For bulk import/export in mySQL you have LOAD DATA INFILE (import) and SELECT INTO (export).

Step 1: prepare your SQL statement with the fields you want exported.
Example:

USE your_database; # select DB

SELECT field1, field2 INTO OUTFILE '/destination/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM your_table;

Step 2:
When you are OK with the SQL put the code into a file and save it somewhere on your server

Step 3:
Test the script from the command line (login via telnet/SSH):

/usr/local/mysql -uroot -ppassword < /scripts/mysqlcsv

In this example I assume your DB user name is root and password is password (highlighed).
I also assume that the file containing the SQL statement is saved as /scripts/mysqlcsv. The command above will instruct mySQL to process the instructions in the file.
Please note that the mySQL path may differ on your server. Use the following shell command to determine the mysql path if needed:

whereis mysql

Step 4:
When your script is working from command line as per above then you just add a cronjob entry with that command line. And the job is now automated.

Shoot me a PM if you require further assistance.