Tag Archives: mysql

Linux / Windows : Automatically backup mysql (on linux) and tranfer backup file to windows (via FTP)

Automatically backup mysql (on linux) and tranfer backup file to windows (via FTP).
This task will involve 2 types of environment (OS), Windows and Linux.

[Windows Section]
1. Setup FTP server on your windows environment using windows own software or third party software.
2. Create an account and allow connection only from the server(Linux) IP address.

[Linux Section]
1. Create an account on mysql with backup privileges ONLY (normally all read privileges).
2. Create an folder in your home directory. Example : /home/anas/mysql_backup_scheduler
3. Create an empty shell file inside /home/anas/mysql_backup_scheduler. Example: backup_and_ftp.sh
4. Inside backup_and_ftp.sh , put the content below and replace accordingly : –

---- BOF -----

#!/bin/bash

DATEC="/bin/date"
DATE=`${DATEC} +%Y%m%d_%H%M`
DATEM=`${DATEC} +%Y-%m-%d`
DBNAME=""
DBBACKUP="$DBNAME"-${DATE}.sql
HOST=''
USER=''
PASSWD=''

cd /home/anas/mysql_backup_scheduler/
/usr/bin/mysqldump -u -p --databases "$DBNAME" > ${DBBACKUP}

echo "Done Backup ..." ${DATE}

ftp -n $HOST <<END_SCRIPT
quote USER $USER
quote PASS $PASSWD
put ${DBBACKUP}
quit
END_SCRIPT

echo "Done Upload ..." ${DATE}

#rm ${DBBACKUP} # uncomment only when you want to delete old files
exit 0

---- EOF ----

Finally, add this script to your cron.

MySQL : drop table with prefix

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) 
    AS statement FROM information_schema.tables 
    WHERE table_schema = 'nama_pengkalandata' AND table_name LIKE 'namaprefix_%';

run those SQL in mysq terminal or phpmyadmin or any mysql client. It will generate ‘drop table’ syntax.

ref: http://stackoverflow.com/questions/1589278/sql-deleting-tables-with-prefix

Mysql show only column names

2 type of sql to show only column name

1.   SELECT column_name
FROM information_schema.columns
WHERE table_schema = ‘DBNAME’
AND table_name = ‘TABLENAME’;

 

2.  SELECT column_name
FROM information_schema.columns
WHERE table_name = ‘TABLENAME’

 

Ref: stackoverflow.com

Mysql Mass Kill

This method I use to do mass kill on mysql process. After run “show processlist” and notice something.

mysql> select concat('KILL ',id,';') from information_schema.processlist
 where Host like '%oss%' and time > 200 into outfile '/tmp/lock.txt';
mysql> source /tmp/lock.txt;

done.. all the process where host like oss and time more than 200.

References:
mysqlperformanceblog, stackoverflow
tested  on centos 6.5 mysql 5.5