I would like to make the loading of data into MySQL database (Ubuntu 12.04) a cron job. I have am able to do it manually using the code:

LOAD LOCAL DATA
INFILE re.txt
INTO table rasss
LINES TERMINATED BY '|'


I also know how to add a job to the crontab -e file, but I have only done so for single line commands

the best solution would be to make a short script of this, and call that script from cron -- you could do it as a one-liner, but it would be ugly and hard to maintain, and there's no advantage to be had. (let me know if you need an example)

edit:

try something along these lines, off the top of my head:

#!/bin/bash

DB_USER='my_user';
DB_PASSWD='passwd';

DB_NAME='db_name';
TABLE='rass';

INPUT_FILE='/full/path/to/re.txt';

SQL="USE $DB_NAME; LOAD LOCAL DATA INFILE '$INPUT_FILE' REPLACE INTO TABLE $TABLE LINES TERMINATED BY '|' CHARACTER SET utf8;" mysql --user=$DB_USER --password=$DB_PASSWD --default_character_set utf8$DB_NAME -e "\$SQL"


As for where to put it, well, that's up to you. Bear in mind that if you use the root crontab then the script will execute with root permissions, and must be owned by root (and not be world-writable). For this reason, I sometimes have scripts of this kind in /root.

Hope this helps :)

• yes i would be grateful for an example, if i make a script, where do i store it – sosytee Aug 26 '13 at 9:53
• @sosytee -- see update :) – simon Aug 26 '13 at 10:07
• I have the idea, now will work on the coding – sosytee Aug 26 '13 at 11:26