MySQL

Creating a User

A pair out of user and host is considered as a username, so always use them together

create user 'user'@'host' identified by 'passwd';       # create entry in user table
grant all privileges on db.* to 'user'@'host';          # create entry in db table for db
show grants for 'user'@'host';

In the grant command, all privileges can be replaced with a subset of select, insert, update, delete, create, drop.

Use an empty host (""), if you want to use the host table, i.e. username of 'user'@' '

grant all privileges on *.* to ... instead of on db.* is only suitable for superusers, because it sets all privileges to 'Y' in the user table.

Host Table

Must contain some of

• host = 'host.example.com'
• db = database name or '%'
• privileges as wanted (or'ed with privileges in db table)

And finally one

• host = '%'
• db = '%'
• all privileges = 'N'

Query Cache

show variables like '%query_cache%';
show status like 'Qcache%';
show status like 'com_select';    # selects without cache

The Qcache_hits/Qcache_inserts ratio must be high for an efficient cache.

Connections

show status like '%conn%';
show variables like '%conn%';
set global max_connections=100;

Timeouts

show variables like '%timeout%';
set global wait_timeout=300;

Binary Logging and Replication

show master status;
show slave status;
show binary logs;
purge master logs before '2007-01-01'
purge master logs to 'host-bin.999'   # to means "up to"

/etc/my.cnf:

# master
[mysqld]
log-bin=mysql-bin
server-id=1

# slave
[mysqld]
server-id=slave_id

Optimize+Analyze Script

# this shell script finds all the tables for a database and run a
command against it
# @usage "mysql_tables.sh --optimize MyDatabaseABC"
# @date 6/14/2006
# @version 1.2 - 2/02/2008 - add analyze
# @version 1.1 - 1/28/2007 - add repair
# @version 1.0 - 6/14/2006 - first release
# @author Son Nguyen and Mike Liebsch

#Change Me
DBUSER=root
DBPASS=fooo

DBNAME=$2

printUsage() {
        echo "Usage: $0"
        echo " --analyze <dbname"
        echo " --optimize <dbname>"
        echo " --repair <dbname>"
        return
}


doAllTables() {
        # get the table names
        TABLENAMES=`mysql -u $DBUSER --password="$DBPASS" -D $DBNAME -e
"SHOW TABLES\G;"|grep 'Tables_in_'|sed -n 's/.*Tables_in_.*:
\([_0-9A-Za-z]*\).*/\1/p'`
        # loop through the tables and optimize them
        for TABLENAME in $TABLENAMES
                do
                mysql -u $DBUSER --password="$DBPASS" -D $DBNAME -e
"$DBCMD TABLE $TABLENAME;"
        done
}

if [ $# -eq 0 ] ; then
        printUsage
        exit 1
fi

case $1 in
        --optimize) DBCMD=OPTIMIZE; doAllTables;;
        --repair) DBCMD=REPAIR; doAllTables;;
        --help) printUsage; exit 1;;
        *) printUsage; exit 1;;
esac

Dump Script

PATH="/usr/local/bin:$PATH"
export PATH

dumpdir="/var/lib/mysql/dumps"
mkdir -p $dumpdir 2> /dev/null
cd $dumpdir

databases="--all-databases"
#databases="DB1 DB2 DB3"

for db in $databases; do
        if [ $db = "--all-databases" ]; then
                dumpfile=all-databases.sql.bz2
        else
                dumpfile=$db.sql.bz2
        fi

        mv $dumpfile $dumpfile.old 2> /dev/null
        nice mysqldump -h localhost --user=root --password=PASSWORD $db | bzip2 -c > $dumpfile
done

Joins Cheat Sheet

Move a database

@source_server
mysqldump -u root -p --opt -Q DBNAME > ~/dump.sql
scp dump.sql dest_server:

@dest_server
mysqldump -u root -p --opt -Q DBNAME > backup.sql
mysql -u root -p DBNAME < dump.sql
mysqlcheck --check-upgrade DBNAME
mysqlcheck --analyze DBNAME
mysqlcheck --optimize DBNAME
mysqlcheck --repair DBNAME
mysqlcheck --check --extended DBNAME