Topics

MYSQL

Simple MySQL migration bash script

#!/usr/bin/env bash

set -e


mkdir -p /root/tmp

#migrate "original db params" "new db params" "new db name"
function migrate(){
    mysqldump --skip-lock-tables --single-transaction --add-drop-table $1 > /root/tmp/${3}.sql
    echo "CREATE DATABASE IF NOT EXISTS ${3};" | mysql $2
    mysql --max_allowed_packet=1000M $2  $3 < /root/tmp/${3}.sql
    rm -f /root/tmp/${3}.sql
}


migrate "-uolduser -poldpassword -h oldhost olddbname" "-unewuser -pnewpassword -h newhost" "newdbname"

#more migrates here ...

Fork here

Use mysqldump to create separate files and directories for databases and tables

The script creates separate directory for every database and bz2 files for every table inside that database.

#!/bin/sh

#edit these
USER=""
PASSWORD=""
MYSQLDIR="/path/to/backupdir"

MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"

echo - Dumping DB structure "$MYSQLDIR"/all.bz2 
$MYSQLDUMP --user=$USER --password=$PASSWORD -d --all-databases | bzip2 > "$MYSQLDIR"/all.bz2 

echo - Dumping tables for each DB
databases=`$MYSQL --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`
for db in $databases; do
    echo - Creating "$db" DB
	mkdir $MYSQLDIR/$db
	chmod -R 777 $MYSQLDIR/$db
	for tb in `$MYSQL  --user=$USER --password=$PASSWORD -N -B -e "use $db ;show tables"`
		do 
			echo -- Creating table $tb
			$MYSQLDUMP --opt  --delayed-insert --insert-ignore --user=$USER --password=$PASSWORD $db $tb | bzip2 -c > $MYSQLDIR/$db/$tb.sql.bz2
	done
	echo
done

Install Sphinx 2.0.4 on CentOS 6.2

– Make sure to check CentOS, PHP-FPM, Nginx, memcached and MYSQL posts.

– Get fresh RPM from sphinxsearch.com

– Install Sphinx

rpm -Uhv http://sphinxsearch.com/files/sphinx-2.0.4-1.rhel6.x86_64.rpm

– Create sample MySQL database

Watch log files on Linux server with Swatch 3.2.3

– Make sure to check CentOS, PHP-FPM, memcached and MYSQL posts.

– get a fresh package from Swatch project

wget "http://downloads.sourceforge.net/project/swatch/swatch/3.2.3/swatch-3.2.3.tar.gz?r=http%3A%2F%2Fsourceforge.net%2Fprojects%2Fswatch%2F&ts=1330806322&use_mirror=garr"
tar xfz swatch-3.2.3.tar.gz 
cd swatch-3.2.3
cpan -i Date::Format File::Tail
cpan -i Date::Manip Date::Calc
cpan -i Test::Inter Test::Pod Test::Pod::Coverage Module::Build
cp /root/.cpan/sources/authors/id/S/SB/SBECK/Date-Manip-* .
tar xfz Date-Manip-*.tar.gz
cd Date-Manip-*
perl Build.PL 
./Build install
cd ..
perl Makefile.PL
make install

– configure swatch

install memcached 1.4.13 on CentOS 6.2

– Make sure to check PHP-FPM and MYSQL posts.

– One of the dependencies of memcache is libevent, get latest version from libevent.org

mkdir libevent
cd libevent
wget https://github.com/downloads/libevent/libevent/libevent-2.0.17-stable.tar.gz
tar xfz libevent-2.0.17-stable.tar.gz
cd libevent-2.0.17-stable

– get latest version from memcached.org