MySQL

Documentation

• MySQL official documentation page
  • MySQL 5.1 Reference Manual

Tools

• PHPMyAdmin
• mytop
• mtop
• innotop
  • innotop Article

Performance

• MySQL Performance Blog
  • What to tune in MySQL Server after installation

Syntax

Select

CONCAT(first_name, ' ', last_name)

Integration

Authentication using LDAP, kerberos, etc is not ready yet

• This page describes the MySQL plan for Pluggable Authentication Support, which is currently under consideration - PluggableAuthenticationSupport

Common task

Change password

set password = password("yournewpassword");

Database Backups

Hot backup - One database

#!/bin/sh
# by gacq
#
USERNAME=$1
PASSWORD=$2
DATABASE=$3
OUTPUTDIR="/srv/backup/mysql"

# Delete backups older than the number of days specified in DAYS
DAYS=8

date=`date +%F_%T | tr \: _`
mysqldump --user=$USERNAME --password=$PASSWORD $DATABASE | gzip > $OUTPUTDIR/dbbackup_$DATABASE-${date}.sql.gz

# Delete older backups
find $OUTPUTDIR -type f -name "dbbackup_$DATABASE*" -ctime $DAYS -exec rm -f {} \;

Hot backup - All databases

#!/bin/sh
# by gacq
#
# MySQL root password
PASSWORD=$1
OUTPUTDIR="/srv/backup/mysql"

# Delete backups older than the number of days specified in DAYS
DAYS=8

date=`date +%F_%T | tr \: _`
mysqldump --all-databases --password=$PASSWORD | gzip > $OUTPUTDIR/all-databases-${date}.sql.gz

# Delete older backups
find $OUTPUTDIR -type f -name "all-databases.*.sql" -ctime $DAYS -exec rm -f {} \;

Hot backup - All databases, each database in a separated file

#!/bin/sh
# by gacq
#
# MySQL root password
PASSWORD=$1
BASEDIR="/srv/backup/mysql"

# Delete backups older than the number of days specified in DAYS
DAYS=8

date=`date +%F_%T | tr \: _`

OUTPUTDIR="$BASEDIR/mySQLbackup_$date"
mkdir -p $OUTPUTDIR

databases=`echo "show databases;" | mysql -Bs -uroot -p$PASSWORD`
for db in `echo $databases`
do
  mysqldump --databases --password=$PASSWORD $db | gzip > $OUTPUTDIR/mySQLdump-$db-${date}.sql.gz
done

# Delete older backups
find $BASEDIR -type d -name "mySQLbackup*" -ctime $DAYS -exec rm -fr {} \;

Selected databases

databases="db1 db2 db3"
for db in `echo $databases`
do
  mysqldump --databases  -uroot -p$passwd $db | gzip > $dir/mySQL/mySQLdump-$db.dump.gz
done

Backup using LVM snapshots

• Limits in mysqldump?
• MySQL Backups using LVM Snapshots
• Using LVM for MySQL Backup and Replication Setup

Errors

Error 1044 on mysqldump

mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES

Resolved using the '--single-transaction' option of mysqldump

* http://forums.mysql.com/read.php?10,108835,112951#msg-112951