Diferencia entre revisiones de «MySQL»
De gacq wiki
(→Tools) |
|||
| (No se muestran 9 ediciones intermedias de 2 usuarios) | |||
| Línea 5: | Línea 5: | ||
=Tools= | =Tools= | ||
*PHPMyAdmin | *PHPMyAdmin | ||
| + | *mytop | ||
| + | *mtop | ||
*[http://www.xaprb.com/innotop innotop] | *[http://www.xaprb.com/innotop innotop] | ||
| + | **[http://www.xaprb.com/blog/2006/07/02/innotop-mysql-innodb-monitor/ innotop Article] | ||
=Performance= | =Performance= | ||
*[http://www.mysqlperformanceblog.com MySQL Performance Blog] | *[http://www.mysqlperformanceblog.com MySQL Performance Blog] | ||
| + | **[http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ What to tune in MySQL Server after installation] | ||
| + | |||
| + | =Syntax= | ||
| + | ==Select== | ||
| + | CONCAT(first_name, ' ', last_name) | ||
=Integration= | =Integration= | ||
| Línea 18: | Línea 26: | ||
set password = password("yournewpassword"); | set password = password("yournewpassword"); | ||
| − | = | + | =Database Backups= |
| + | ==Hot backup - One database== | ||
| + | <pre> | ||
| + | #!/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 {} \; | ||
| + | </pre> | ||
| + | |||
==Hot backup - All databases== | ==Hot backup - All databases== | ||
#!/bin/sh | #!/bin/sh | ||
| Línea 36: | Línea 64: | ||
find $OUTPUTDIR -type f -name "all-databases.*.sql" -ctime $DAYS -exec rm -f {} \; | find $OUTPUTDIR -type f -name "all-databases.*.sql" -ctime $DAYS -exec rm -f {} \; | ||
| − | ==Hot backup - | + | ==Hot backup - All databases, each database in a separated file== |
#!/bin/sh | #!/bin/sh | ||
# by gacq | # by gacq | ||
# | # | ||
| + | # MySQL root password | ||
PASSWORD=$1 | PASSWORD=$1 | ||
| − | + | BASEDIR="/srv/backup/mysql" | |
| − | |||
# Delete backups older than the number of days specified in DAYS | # Delete backups older than the number of days specified in DAYS | ||
| Línea 48: | Línea 76: | ||
date=`date +%F_%T | tr \: _` | date=`date +%F_%T | tr \: _` | ||
| − | mysqldump --password=$PASSWORD $ | + | |
| + | 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 | # Delete older backups | ||
| − | find $ | + | find $BASEDIR -type d -name "mySQLbackup*" -ctime $DAYS -exec rm -fr {} \; |
==Selected databases== | ==Selected databases== | ||
| Línea 57: | Línea 93: | ||
for db in `echo $databases` | for db in `echo $databases` | ||
do | do | ||
| − | mysqldump -uroot -p$passwd $db | gzip > $dir/mySQL/mySQLdump-$db.dump.gz | + | mysqldump --databases -uroot -p$passwd $db | gzip > $dir/mySQL/mySQLdump-$db.dump.gz |
done | done | ||
| + | |||
| + | ==Backup using LVM snapshots== | ||
| + | *[http://mike.kruckenberg.com/archives/2006/04/limits_in_mysql.html Limits in mysqldump?] | ||
| + | *[http://mike.kruckenberg.com/archives/2006/05/mysql_backups_u.html MySQL Backups using LVM Snapshots] | ||
| + | *[http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/ Using LVM for MySQL Backup and Replication Setup] | ||
| + | |||
| + | = Errors = | ||
| + | == Error 1044 on mysqldump == | ||
| + | <pre> | ||
| + | mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES | ||
| + | </pre> | ||
| + | |||
| + | Resolved using the '--single-transaction' option of mysqldump | ||
| + | |||
| + | * http://forums.mysql.com/read.php?10,108835,112951#msg-112951 | ||
Revisión actual del 09:05 21 oct 2010
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
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
