[Andhy] Zero Zone

Load MySQL Database from Spreadsheet using Shell Script

When we have a spreadsheet file with huge content which should be loaded into MySQL database, then what will we do?
For me, i prefer use shell script. So, how?

Well, before we do it, we need to understand the format of the spreadsheet first. The spreadsheet file consists of rows and columns, so what we need is to find how to identify each row and column. I don’t know how to identify rows and columns in shell script, therefore, in order to load the data correctly, I export the spreadsheet into plain text file by copy the entire content of the spreadsheet file and then paste it into plain text file.
If we copy a spreadsheet excel file and paste it into text file, each column in excel will be identified by tab and row will be identified by line in text file. From this, we can use symbol \t for tab and symbol \n for new line.
Before attempting to convert any spreadsheet into text file, it is best to review the data for consistency. For example, make sure that every row has the same number of columns and check that all dates are formatted in the same way.
After that, we can use shell script to load the plain text file into MySQL database using command LOAD DATA LOCAL INFILE.
The term of LOCAL is used because my MySQL server is remote server. That means my MySQL server is located in different server. See my previous post to know my server configuration.

Here is my simple script :

#! /bin/sh
# filename : updatedb.sh
# Load data from file into MySQL
mysql -u USER -pPASSWORD -D mydatabase -h 10.1.1.10 -e
"LOAD DATA LOCAL INFILE 'mydata' INTO TABLE mytable
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';"

By that script my file is successfully loaded into MySQL database.

Related Post :

• Backup MySQL Database Regularly using Shell Script
• Export dari Database ke Excel
• Catatan
• Fussion Charts
• Shell scripting sh for fun
• New Aggregator EL ITB 2003

Backup MySQL Database Regularly using Shell Script

Sometime, we need to backup our database regularly in order to avoid something bad ( paranoid mode)
Bellow is the simple shell sh script for backing up MySQL database by dumped the database and then put it into gzip file with the date of backup as the name file.

Here is the condition of my server :

• My MySQL server is located in IP Address 10.1.1.10 with username = USER and password = PASSWORD
• The name of database which should be dumped regularly is mydatabase
• My backup server is located in IP 10.1.1.15
• The path of mysqldump in my backup server is located in /apps/local/mysql/bin/mysqldump
• I want to put the gzip backup files in folder /backup/BACKUPDB in my backup server
• My backup script is located in /script folder in my backup server

Here is the simple script :

#! /bin/sh
# filename : backupdb.sh
# Automated database datestamp backup
/apps/local/mysql/bin/mysqldump --opt -Q -h 10.1.1.10 -u USER
-pPASSWORD mydatabase > /backup/BACKUPDB/`date +"%d-%m-%y"`.sql
gzip /backup/BACKUPDB/`date +"%d-%m-%y"`.sql

Then, in order to run automatically that script at specific time, we need to set up cron job by adding the crontab list in backup server.
Here is example crontab for running backupdb.sh every day at 2 am.

0 2 * * * /script/backupdb.sh

Before add the script into cron job, it will be better if you check whether that script is running well or not. Do it at your own risk

Related Post :

• Load MySQL Database from Spreadsheet using Shell Script
• Export dari Database ke Excel
• Catatan
• Fussion Charts
• Shell scripting sh for fun
• New Aggregator EL ITB 2003