Diferencia entre revisiones de «MySQL»

De gacq wiki
Saltar a: navegación, buscar
(→‎Tools)
 
(No se muestran 8 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]
 
**[http://www.xaprb.com/blog/2006/07/02/innotop-mysql-innodb-monitor/ innotop Article]
Línea 10: Línea 12:
 
=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 19: Línea 26:
 
  set password = password("yournewpassword");
 
  set password = password("yournewpassword");
  
−
=Scripts=
+
=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 37: 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 - One database==
+
==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
−
  DATABASE=$2
+
  BASEDIR="/srv/backup/mysql"
−
OUTPUTDIR="/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 49: Línea 76:
 
   
 
   
 
  date=`date +%F_%T | tr \: _`
 
  date=`date +%F_%T | tr \: _`
−
  mysqldump --password=$PASSWORD $DATABASE | gzip > $OUTPUTDIR/$DATABASE-${date}.sql.gz
+
   
 +
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 $OUTPUTDIR -type f -name "all-databases.*.sql" -ctime $DAYS -exec rm -f {} \;
+
  find $BASEDIR -type d -name "mySQLbackup*" -ctime $DAYS -exec rm -fr {} \;
  
 
==Selected databases==
 
==Selected databases==
Línea 58: 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

Documentation

Tools

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

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
Obtenido de «https://wiki.gacq.com/index.php?title=MySQL&oldid=3037»