Hacer un backup mysql con CRON en Linux

Es siempre bueno realizar un backup de nuestras bases de datos en caso de algún fallo, perdida o error humano (que es lo que más suele ocurrir). Como ya muchos saben Linux posee una herramienta llamada CRON el cual permite ejecutar tareas repetitivas periodicamente.

En este tutorial les mostraré como hacer un pequeño script que permite realizar un backup de la base de datos que tengamos en mysql.

Estoy suponiendo que tienes instalado y configurado un servidor mysql funcionando en tu computadora o en un servidor remoto y que tienes los privilegios necesarios para accesar a la base de datos y ejecutar scripts en tu computadora o en el servidor remoto. Aclarado esto podemos seguir con el tutorial.

Script de Backup Mysql

Primero vamos a crear una carpeta para guardar el backup que vamos a generar, esta carpeta la puedes crear donde quieras, yo personalmente la creé en mi directorio raíz pero lo puedes crear donde quieras:

mkdir /respaldosql

En nuestro editor de texto favorito escribimos el script que hará el backup de la bases de datos que tengamos en nuestro servidor de MySQL:

 #!/bin/sh
 mysqldump -uroot -ppwd --opt db1.sql > /respaldosql/db1.sql
 mysqldump -uroot -ppwd --opt db2.sql > /respaldosql/db2.sql
 cd /home/usuario/respaldosql/
 tar -zcvf respaldosql_$(date +%d%m%y).tgz *.sql
 find -name '*.tgz' -type f -mtime +2 -exec rm -f {} \;

Guarda este script con el nombre mysqlrespaldo.sh (o el nombre que más te parezca) en donde quieras, yo tengo una carpeta llamada scripts en mi directorio home, es a gusto de cada quien.

Vamos a analizar el script línea por línea: la primera línea #!/bin/sh simplemente le indica al bash que es un script ejecutable y donde va a buscar el bash que va a utilizar para ejecutar este script en este caso /bin/sh.

La segunda línea es la que hace todo el trabajo, mysqldump es un comando que “vuelca” o copia todos los datos que estén dentro de la base de datos que le indiquemos en la línea de comandos, las opciones son las siguientes:

• -u es para indicar el usuario en este caso es root pero puede ser cualquier otro usuario que tenga privilegios sobre la base de datos
• -p es para indicarle el password aquí tienes que colocar el password del usuario que estás utilizando para conectarte en la base de datos.
• –opt db1 aquí indicamos cual es base de datos que queremos que copie en este caso db1
• el operador > redirecciona la salida del comando hacia un archivo llamado db1.sql en una carpeta llamada respaldosql.

La línea que sigue hace exactamente lo mismo que la primera pero con otra base de datos llamada db2, puedes agregar tantas base de datos como quieras a este script.

Después cambiamos al directorio de respaldo donde acabamos de crear los archivos db1.sql y db2.sql con cd /respaldosql/ y comprimimos todos los archivos en uno solo con su respectiva fecha con el comando date, esto es para tener un respaldo anterior y saber cuando lo hicimos, es útil en caso de que por alguna razón queramos un respaldo viejo.

La última línea es opcional, lo que hace es buscar todos los archivos comprimidos que tengan más de 2 días y los elimina, esto es para evitar que se nos acumulen archivos antiguos, puedes cambiar con que frecuencia quieres borrar estos archivos puedes utilizar man find para mayor información.

Muy bien ahora tenemos nuestro script listo lo primero es hacerlo ejecutable:

En Ubuntu:

 sudo chmod 700 mysqlrespaldo.sh

En cualquier otra distribución tenemos que tener privilegios de administrador o root:

 chmod 700 mysqlrespaldo.sh

Añadiendo el Script a CRON

Como ya mencioné anteriormente CRON es una herramienta que posee Linux para ejecutar programas o scripts en forma repetitiva. Para modificar la tabla de cron ejecutamos el siguiente comando en nuestro shell:

 crontab -e

Se abrirá el crontab en nuestro editor de texto por defecto (en Ubuntu casi siempre se abre en el nano) aquí agregamos la siguiente línea y guardamos nuestro archivo:

 0 1 * * * /home/usuario/scripts/mysqlrespaldo.sh

Guardamos nuestro archivo y ya está listo para ejecutarse diariamente, vamos a explicar un poco como funciona (recuerda colocar en /home/usuario/scripts la ruta donde guardaste tu script).

El archivo de crontab tiene la siguiente estructura de izquierda a derecha:

• Minutos (rango de 0-59)
• Horas (0-23)
• Día del mes (1-31)
• Mes (1-12)
• Día de la semana (0-6 siendo 0=Domingo)
• Path completo al script o programa que queramos ejecutar

Cualquier campo con * quiere decir que se corre a cualquier posible valor de ese campo. Así que en nuestro caso el script correrá todos los días a la 1:00 de la madrugada.

Bueno eso fue todo espero que les sirva, como siempre cualquier duda o comentario son bienvenidos.

Saludos

Creating a Backup Xtrabackup

Creating a Backup

To create a backup, run xtrabackup with the –backup option. You also need to specify a --target_dir option, which is where the backup will be stored, and a --datadir option, which is where the MySQL data is stored. If the InnoDB data or log files aren’t stored in the same directory, you might need to specify the location of those, too. If the target directory does not exist, xtrabackup creates it. If the directory does exist and is empty, xtrabackup will succeed. xtrabackup will not overwrite existing files, it will fail with operating system error 17, file exists.

The tool changes its working directory to the data directory and performs two primary tasks to complete the backup:

• It starts a log-copying thread in the background. This thread watches the InnoDB log files, and when they change, it copies the changed blocks to a file called xtrabackup_logfile in the backup target directory. This is necessary because the backup might take a long time, and the recovery process needs all of the log file entries from the beginning to the end of the backup.
• It copies the InnoDB data files to the target directory. This is not a simple file copy; it opens and reads the files similarly to the way InnoDB does, by reading the data dictionary and copying them a page at a time.

When the data files are finished copying, xtrabackup stops the log-copying thread, and creates a files in the target directory called xtrabackup_checkpoints, which contains the type of backup performed, the log sequence number at the beginning, and the log sequence number at the end.

An example command to perform a backup follows:

$ xtrabackup --backup --datadir=/var/lib/mysql/ --target-dir=/data/backups/mysql/

This takes a backup of /var/lib/mysql and stores it at /data/backups/mysql/. If you specify a relative path, the target directory will be relative to the current directory.

During the backup process, you should see a lot of output showing the data files being copied, as well as the log file thread repeatedly scanning the log files and copying from it. Here is an example that shows the log thread scanning the log in the background, and a file copying thread working on the ibdata1 file:

>> log scanned up to (3646475465483)
>> log scanned up to (3646475517369)
>> log scanned up to (3646475581716)
>> log scanned up to (3646475636841)
>> log scanned up to (3646475718082)
>> log scanned up to (3646475988095)
>> log scanned up to (3646476048286)
>> log scanned up to (3646476102877)
>> log scanned up to (3646476140854)
[01] Copying /usr/local/mysql/var/ibdata1
     to /usr/local/mysql/Backups/2011-04-18_21-11-15/ibdata1
[01]        ...done

The last thing you should see is something like the following, where the value of the  will be a number that depends on your system:

xtrabackup: Transaction log of lsn () to () was copied.

Note

 

Log copying thread checks the transactional log every second to see if there were any new log records written that need to be copied, but there is a chance that the log copying thread might not be able to keep up with the amount of writes that go to the transactional logs, and will hit an error when the log records are overwritten before they could be read.

After the backup is finished, the target directory will contain files such as the following, assuming you have a single InnoDB table test.tbl1 and you are using MySQL’s innodb_file_per_table option:

/data/backups/mysql/ibdata1
/data/backups/mysql/test
/data/backups/mysql/test/tbl1.ibd
/data/backups/mysql/xtrabackup_checkpoints
/data/backups/mysql/xtrabackup_logfile

The backup can take a long time, depending on how large the database is. It is safe to cancel at any time, because it does not modify the database.

The next step is getting your backup ready to restored: Preparing the backup.

MySQL hot backup with XtraBackup on CentOS

If you are in a need for hot backup solution for MySQL RDBMS, you should try out Percona's XtraBackup. Now, as you can read on the official website, XtraBackup is the world's only open-source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases. XtraBackup also supports MyISAM backups, although not without table locks.

XtraBackup consists of three tools - innobackupex, xtrabackup and tar4ibd.

Innobackupex is a wrapper script written in Perl that allows you to backup a complete MySQL instance with InnoDB, MyISAM and XtraDB tables. XtraBackup is a tool compiled in C which main job is to copy only InnoDB and XtraDB data. Of course, there is tar4ibd - a tool that safely stores InnoDB data in tar archive.

Although you can use XtraBackup tool directly, the preferred way to backup your databases is with innobackupex, because it'll execute XtraBackup for you. If you use MyISAM in some or all of your database tables, you won't have a choice but to use XtraBackup tool.

Installation

The easiest way to install XtraBackup on CentOS 6 (it should also work without problems on CentOS 5) is to add Percona's repository. To do that, you'll need to create yum repo file in /etc/yum.repos.d/Percona.repo with the following content:

1

2

3

4

5

6


[percona]

name = CentOS $releasever - Percona

baseurl=http://repo.percona.com/centos/$releasever/os/$basearch/

enabled = 1

gpgkey = file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona

gpgcheck = 1

Afterwards you'll need to collect the GPG key and add it to /etc/pki/rpm-gpg/RPM-GPG-KEY-percona. You can use wget, curl or manually copy and paste the key into appropriate file. Below is the example for wget and curl:

1

2


# wget -O /etc/pki/rpm-gpg/RPM-GPG-KEY-percona http://www.percona.com/downloads/RPM-GPG-KEY-percona

# curl http://www.percona.com/downloads/RPM-GPG-KEY-percona -o /etc/pki/rpm-gpg/RPM-GPG-KEY-percona

By this point, you'll be ready to install xtrabackup package with yum:

1	# yum install xtrabackup

Creating full backup

To create a full backup, you can simply execute innobackupex with path to your backup directory as a single argument:

1	# innobackupex /path/to/backupdir/

Innobackupex will create a complete backup of MySQL datadir in defined location within a time stamped directory (e.g. /path/to/backupdir/2012-01-22_12-35-34/). If you don't like this behavior, you can tell innobackupex not to create a time stamped directory by using --no-timestamp switch.

Restoring full backup

To restore full backup created with innobackupex tool (i.e. all databases), you must first prepare full backup for restore:

1	# innobackupex --use-memory=4G --apply-log /path/to/backupdir/some_timestamp/

Basically, with this command innobackupex replays commited transactions in the log files and rolls back uncommited ones. The most important thing is that in the end innobackupex returns to you a message like "100313 02:43:07 innobackupex: completed OK!". You should be careful using --use-memory switch. By default, innobackupex uses 100 MB of RAM for this operation, but to speed things up, you can always assign more memory. Just be careful not to assign more memory then you have to spare.

After you prepared your backup, you can restore it. I recommend that you stop MySQL...

1	# service mysqld stop

...move current MySQL datadir to a safe location...

1	# mv /var/lib/mysql/ /var/lib/mysql-old

...and restore the backup you prepared earlier with:

1	# innobackupex --copy-back /path/to/backupdir/some_timestamp/

After you restore the backup, be sure to chown MySQL datadir and start MySQL server again:

1	# chown mysql. /var/lib/mysql # service mysqld start

Restoring partial backup

XtraBackup doesn't support partial backup restore. But if you need to restore a specific database(s) or table(s), there is a solution - restore trough alternate MySQL server. Just like with full backup restore, you'll start with preparing the backup:

1	# innobackupex --use-memory=4G --apply-log /path/to/backupdir/some_timestamp/

Afterwards, you should chown backup directory so that you don't bump into problems when you start alternate MySQL server:

1	# chown mysql. /path/to/backup/some_timestamp/

And now you are now set to start alternate MySQL instance on different port, so that your production instance can work right beside it:

1 2 3

# mysqld --basedir=/usr --user=mysql --log-error=/path/to/backup/mysqld.log \ --open-files-limit=4096 --pid-file=/path/to/backup/mysqld.pid \ --socket=/path/to/backup/mysql.sock --port 3307 --datadir=/path/to/backup/some_timestamp

In the command above you should replace log-error, pid-file, socket and datadir paths. If you already have a service running on port 3307, you should change it too.

Now when you have alternate MySQL instance up and running, you can use mysqldump utility to backup a specific database(s) or table(s) For example, to backup a whole database to a .sql file you'd use something like:

1	# mysqldump -P 3307 db_name > db_name.sql

To restore a database from .sql file to production MySQL instance you can simply execute:

1	# mysql db_name < db_name.sql

Even faster way to restore a database from alternate to production MySQL instance would be:

1	# mysqldump -P 3307 db_name | mysql db_name

- See more at: http://www.tekovic.com/mysql-hot-backup-with-xtrabackup-on-centos#sthash.7WiMtTEn.dpuf