msgbartop
A chronological documentation test project, nothing serious, really!
msgbarbottom

04 Jun 2007 Shell script to backup mySQL database

Shell script to backup MySql database

mysql-backup.bash

#!/bin/bash
# Shell script to backup MySql database
# To backup Nysql databases file to /backup dir and later pick up by your
# script. You can skip few databases from backup too.
# For more info please see (Installation info):
# http://www.cyberciti.biz/nixcraft/vivek/blogger/2005/01/mysql-backup-script.html
# Last updated: Aug - 2005
# --------------------------------------------------------------------
# This is a free shell script under GNU GPL version 2.0 or above
# Copyright (C) 2004, 2005 nixCraft project
# Feedback/comment/suggestions : http://cyberciti.biz/fb/
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit http://bash.cyberciti.biz/ for more information.
# -------------------------------------------------------------------------

MyUSER="SET-MYSQL-USER-NAME"     # USERNAME
MyPASS="SET-PASSWORD"       # PASSWORD
MyHOST="localhost"          # Hostname

# Linux bin paths, change this if it can't be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"

# Backup Dest directory, change this if you have someother location
DEST="/backup"

# Main directory where backup will be stored
MBD="$DEST/mysql"

# Get hostname
HOST="$(hostname)"

# Get data in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y")"

# File to store current backup file
FILE=""
# Store list of databases
DBS=""

# DO NOT BACKUP these databases
IGGY="test"

[ ! -d $MBD ] && mkdir -p $MBD || :

# Only root can access it!
$CHOWN 0.0 -R $DEST
$CHMOD 0600 $DEST

# Get all database list first
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"

for db in $DBS
do
    skipdb=-1
    if [ "$IGGY" != "" ];
    then
	for i in $IGGY
	do
	    [ "$db" == "$i" ] && skipdb=1 || :
	done
    fi

    if [ "$skipdb" == "-1" ] ; then
	FILE="$MBD/$db.$HOST.$NOW.gz"
	# do all inone job in pipe,
	# connect to mysql using mysqldump for select mysql database
	# and pipe it out to gz file in backup dir :)
        $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE
    fi
done

Tags: , , , ,

Posted by

04 Jun 2007 Howto: Connect MySQL server using C program API under Linux or UNIX

Many of the clients in the MySQL source distribution are written in C. If you are looking for examples that demonstrate how to use the C API, take a look at these clients. You can find these in the clients directory in the MySQL source distribution.
(more…)

Tags: , , ,

Posted by

07 May 2007 Import csv data to mysql

This is a short note about how to import comma separated data, CSV, from a file into a mysql database from a shell.

  1. Log in to your mysql database and choose the database you are going to import into.
  2. Type in the following in the mysql console to import from the CSV file
    load data infile '/home/username/data.csv' into table program fields terminated by ';' lines terminated by '\r\n';

Tags: , , ,

Posted by

07 May 2007 mySQL dump i batch mode

# mysql dbname -B -N -e "SELECT serial FROM serials" >/path/to/outfile

-B or –batch: prints rows tab-separated and turns off the borders
-N or –skip-column-names: leaves out the header row

Tags: ,

Posted by

07 May 2007 Export mysql data to a tab-separated file

# mysqldump -u root -p --no-create-info -T=/tmp --fields-terminated-by='\t' db tabell

Tags: , , ,

Posted by