MySQL
De gacq wiki
Contenido
Documentation
Tools
- PHPMyAdmin
- mytop
- mtop
- innotop
Performance
Syntax
Select
CONCAT(first_name, ' ', last_name)
Integration
Authentication using LDAP, kerberos, etc is not ready yet
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
