Bash : Auto Backup Mysql on Mandriva 2005

Based on my experience on maintenance project.
It's about to create auto backup mysql on Mandriva 2005 (Linux) server.

1. Create file backup.sh, you may using mc or pico

$ mcedit /backup.sh

File content :

#! /bin/sh
/usr/bin/mysqldump -u[username] -p[password] [database_name] > /[your_backup_directory]/[filename].sql
cd /backup
tar --remove-files -czf backup-`date '+%Y-%m-%d' `.tar.gz -R *.sql
sudo cp -f *.tar.gz /[another_backup_directory]

2. Edit your cron list

$ mcedit /etc/crontab

File content :

SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
HOME=/

# run-parts
00 7-14 * * 1-6 root /bin/sh /backup.sh

3. Reconfigure your crontab

$ crontab -u[username] /etc/crontab
$ crontab -l

MySQL auto backup scheduled every Monday through Friday beginning at 7 am till 2 pm :)

phpMyAdmin : How to Import Large CSV

Case : You have a big file size csv to import to mysql using phpmyadmin
Solution : Use mysql script instead of import form from phpmyadmin (that will save you a lot of time)
Script :

LOAD DATA LOCAL INFILE 'file_name.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(column1,column2,column3);

Note : place your csv file in your phpmyadmin directory

Source : http://www.phpfreaks.com/forums/index.php?topic=343906.0

MySQL : Merge two tables

using phpMyAdmin :

1. select database
2. create new table (note that the new table should have the same structure as the table that we want to merge)
3. go to the SQL menu and type in the sql box :
   
   

   INSERT IGNORE
   INTO table_1
   SELECT *
   FROM table_2
   ;

   
   

doh !

source : http://stackoverflow.com/questions/725556/how-can-i-merge-two-mysql-tables

Import mysql database from windows command prompt

1. open cmd
2. enter mysql directory
3. type :

mysql -uUSERNAME -pPASSWORD DATABASENAME < FILENAME.sql

doh !

source : http://www.ehow.com/how_2036644_import-sql-database.html