Archive for MySQL

Drop Column If Exists in MySQL

IF EXISTS ( SELECT * FROM information_schema.columns WHERE table_name = 'country' AND column_name = 'created_at' AND table_schema = DATABASE() ) THEN
    ALTER TABLE `country` DROP COLUMN `created_at`;
END IF;
Because DROP COLUMN IF EXISTS isn't supported by MySQL.

Comments

Show All Columns on a MySQL Table

SELECT * FROM information_schema.columns WHERE table_name = ‘my_super_table_name’ AND table_schema = ‘my_super_database_name’;

Comments

Show All Indexes on a MySQL Database

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, CARDINALITY, INDEX_TYPE FROM information_schema.statistics WHERE INDEX_NAME != ‘PRIMARY’ AND table_schema = ‘my_super_database’;

Comments

Create your own linux/mysql/php development box for Windows or Mac OS X

Download iso of ubuntu server edition 11
	http://www.ubuntu.com/download/server/download
Download VirtualBox
	http://www.virtualbox.org/wiki/Downloads


Install VirtualBox
	
Create a new VM with 1000mb ram and 20GB of storage
	- bridge the network connection
	- default install (enable openssh server)
	
$ sudo su -
$ visudo
$ vim /etc/network/interfaces
	auto eth0
	iface eth0 inet static
			address 192.168.7.55
			netmask 255.255.255.0
			gateway 192.168.7.1

$ /etc/init.d/networking restart
$ ifconfig
$ ping google.com  //confirms internet connectivity
$ apt-get update  //update package manager sources
$ apt-get install mysql-server  //install mysql
$ apt-get install apache2
$ apt-get install php5
$ php -v  //confirm php 5.3.5
$ apt-get install samba  //install samba
$ vim /etc/samba/smb.conf
	- enable (uncomment) [homes]
	- read only = no
	- create mask = 0775
	- directory mask = 0775
$ sudo smbpasswd myusername
$ /etc/init.d/smbd restart //restart samba service to apply changes
	- should be able to connect to your smb share via windows or mac (http://support.apple.com/kb/HT1568)
$ apt-get install git-core

Comments

Export to CSV with MySQL

On Ubuntu, this will create a file in /var/lib/mysql/[DB_NAME]/output.csv

I had to run the query as MySQL root. Didn’t try to find out what permission actually permitted the user to write to the file. It’s probably create table though.


SELECT
v.id, v.title, vc.name, v.slug, v.description
FROM
video v
INNER JOIN
video_category vc ON v.video_category_id = vc.id
INTO
OUTFILE 'output.csv'
FIELDS ESCAPED BY '"'
TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

Comments

Croned MySQL Database Backup Script and Transfer to an Offsite Location

This bash script regularly (from a cron) backs up a production database to an offsite server into a date-stamped compressed (gzip) format.

It is intended to be run from the production server. You need to generate a public key (ssh-keygen) for the production server and add it to the offsite server’s authorized keys file (/home/username/.ssh/authorized_keys). Be sure to chmod 600 the public and private key after creating them.

Also make sure that you use the same username for the key generation, the connection string key location and the cron entry.


#
# http://codingsnippets.com/croned-mysql-database-backup-script
#
# This bash script regularly (from a cron) backs up a production
# database to an offsite server into a date-stamped compressed
# (gzip) format.
#
# It is intended to be run from the production server.
#
#
# Please report any comments, bugs or improvements at
# http://codingsnippets.com/croned-mysql-database-backup-script
# and please leave this message intact so others can
# do the same, improve the script, or download the
# most current version. Thanks.
#

LOCALFILE_PATH="/home/www/backups"
FILENAME="livedb-`date "+%Y-%m-%d"`.sql.gz"
SSH="$(which ssh)"
GZIP="$(which gzip)"
SCP="$(which scp)"
MYSQL_DUMP="$(which mysqldump)"

DB_HOSTNAME="localhost"
DB_USERNAME="USERNAME"
DB_PASSWORD="password"
DATABASE="database_name"

#Offsite location
SSH_PORT="22"
SSH_HOSTNAME="yourhostname.com"
SSH_USERNAME="yourusername"
SSH_KEY_LOCATION="~/.ssh/id_rsa"
BACKUP_LOCATION="/home/user/backups"

echo $FILENAME
$MYSQL_DUMP -u$DB_USERNAME -p$DB_PASSWORD -h$DB_HOSTNAME $DATABASE | $GZIP -9 > $LOCALFILE_PATH/$FILENAME
$SCP -i $SSH_KEY_LOCATION -P $SSH_PORT $LOCALFILE_PATH/$FILENAME $SSH_USERNAME@$SSH_HOSTNAME:$BACKUP_LOCATION/$FILENAME

Comments (1)

Drop All Tables in a MySQL Database

This BASH Script drops all tables in a MySQL Database without having to drop the database.

This is courtesy of http://www.cyberciti.biz/faq/how-do-i-empty-mysql-database/


#!/bin/bash
MUSER="$1"
MPASS="$2"
MDB="$3"

# Detect paths
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)

if [ $# -ne 3 ]
then
echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name}"
echo "Drops all tables from a MySQL"
exit 1
fi

TABLES=$($MYSQL -u $MUSER -p$MPASS $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )

for t in $TABLES
do
echo "Deleting $t table from $MDB database..."
$MYSQL -u $MUSER -p$MPASS $MDB -e "drop table $t"
done

Comments