Category Archives: Backup

Dumping multiple mysql tables at once

Publicat la 20 January 2016 No comments

Could happen to have a mysql database with a lot of tables and we want dump all of them, but we also want to make the logical dump process faster. Some of you could say that mydumper is the solution. It could be. But what if we don't have the permission to install it or whatever?

Let's say we have these tables and we need to dump all of them:

[codesyntax lang="bash"]

MariaDB [database]> show tables like "objects_2015%";
+-----------------------------------------------+
| Tables_in_xxxxxxxxxx_logs (objects_2015%)     |
+-----------------------------------------------+
| objects_2015_12_01                            |
| objects_2015_12_02                            |
| objects_2015_12_03                            |
| objects_2015_12_04                            |
| objects_2015_12_05                            |
| objects_2015_12_06                            |
| objects_2015_12_07                            |
| objects_2015_12_08                            |
| objects_2015_12_09                            |
| objects_2015_12_10                            |
| objects_2015_12_11                            |
| objects_2015_12_12                            |
| objects_2015_12_13                            |
| objects_2015_12_14                            |
| objects_2015_12_15                            |
| objects_2015_12_16                            |
| objects_2015_12_17                            |
| objects_2015_12_18                            |
| objects_2015_12_19                            |
| objects_2015_12_20                            |
| objects_2015_12_21                            |
| objects_2015_12_22                            |
| objects_2015_12_23                            |
| objects_2015_12_24                            |
| objects_2015_12_25                            |
| objects_2015_12_26                            |
| objects_2015_12_27                            |
| objects_2015_12_28                            |
| objects_2015_12_29                            |
| objects_2015_12_30                            |
| objects_2015_12_31                            |
+-----------------------------------------------+
74 rows in set (0.00 sec)

[/codesyntax]

Dumping, let's say, 6 tables in parallel will speed up the dump process.
Note: Too many mysqldump instances could overload the server and make the dump process slower (if the server is a production server this could have quite an impact on the performance).

We need the list with all the commands we need to execute:

[codesyntax lang="bash"]

for i in $(mysql -h host -uuser -ppassword database -Bsqe "show tables like \"objects_2015%\""); do echo "mysqldump -h host -uuser -ppassword --add-drop-table --quick --quote-names --disable-keys --extended-insert database $i | gzip > /path/to/backup/$i.sql.gz"; done > /tmp/things_to_do/tables.txt

[/codesyntax]

Start the dump process:
[codesyntax lang="bash"]

parallel -j 6 < /tmp/things_to_do/tables.txt

[/codesyntax]

mysql hot backup with xtrabackup

Publicat la 15 September 2015 No comments

I want to backup my databases without downtime... Well, there are couple approaches to this. One of them is to add a standby slave and use LVM snapshot approach. The second approach is to use percona xtrabackup. My problem is that I have some huge databases here, so taking local backups is out of the question.

But just to remain written this is how to do local hot backups.

1. Take the backup
[codesyntax lang="bash"]

time innobackupex --user=user --password=password --no-timestamp --rsync --slave-info --safe-slave-backup /path/mysql

[/codesyntax]

2. At this point the data is not ready to be restored. There might be uncommitted transactions to be undone or transactions in the logs to be replayed. Doing those pending operations will make the data files consistent and it is the purpose of the prepare stage. Once this has been done, the data is ready to be used.

[codesyntax lang="bash"]

time innobackupex --apply-log --use-memory=12G /path/mysql

[/codesyntax]

Since in my case having storing the backup locally (not even temporary) is not an option I need to transport the whole thing to the storage server.
The lame solution is to use NFS... but c'mon... grrrr... no!

The right solution is to stream the data directly to the storage server. To achieve this I am using netcat. Also to gain more performance I am compressing the data on the fly.

1. On the target machine
[codesyntax lang="bash"]

nc -l -p 9999 | qpress -dio | xbstream -x -C /path/to/mysql

[/codesyntax]

2. On the source machine
[codesyntax lang="bash"]

time innobackupex --user=user --password=pass --no-timestamp --slave-info --parallel=$((`nproc`-2)) --safe-slave-backup --stream=xbstream /data/db/mysql | qpress -io something | nc destination 9999

[/codesyntax]

3. On the target machine
Apply the transaction log in order to make the backup consistent.

[codesyntax lang="bash"]

time innobackupex --apply-log --use-memory=120G /path/to/mysql

[/codesyntax]

Note: --use-memory option is used to speed up the process by using more memory (the default is 100MB which is a quite small value).

How to install the same debian packages on another system

Publicat la 21 February 2013 No comments

The approach is simple: generate a list of installed packages on Debian-based systems and install this list of packages on the new box. This approach is useful when you want to install the same packages on another fresh OS install for example.

1. To export the list of installed packages, proceed as follows:
[codesyntax lang="bash"]

dpkg --get-selections | grep -v deinstall > LIST_FILE

[/codesyntax]

The file LIST_FILE is small so it can be emailed to yourself by using:
[codesyntax lang="bash"]

dpkg --get-selections | grep -v deinstall > LIST_FILE && cat LIST_FILE | mailx -s "`hostname -f`: Package list" "user@test.org"

[/codesyntax]

2. Once you’ve got your server up and running with a fresh base install
[codesyntax lang="bash"]

apt-get update
apt-get dist-upgrade

[/codesyntax]

3. Move your LIST_FILE file into your home directory and run the following commands to recover the previous generated list:
[codesyntax lang="bash"]

dpkg --clear-selections
dpkg --set-selections < LIST_FILE

[/codesyntax]

4. Install the packages
[codesyntax lang="bash"]

aptitude install

[/codesyntax]

How to clone a running Linux system over network using netcat

Publicat la 11 December 2012 No comments

This is a short but potentially extremely handy guide to ghosting one Linux box to another (or simply making a full backup of a desktop/server).

On the target machine:
[codesyntax lang="bash"]

nc -p 2222 -l | bzip2 -d | dd of=/dev/hdb

[/codesyntax]

On the source machine:
[codesyntax lang="bash"]

bzip2 -c /dev/hda | nc 10.111.1.206 2222

[/codesyntax]

Note:

  • please be VERY careful about IPs, devices and ports.
  • after completion of dd command you have to run fsck on target partitions

Creating backups of running VMs in XenServer

Publicat la 22 November 2012 No comments

With XenServer it is possible to create backups of VMs, even if they are running. The process is as follows:

Search for the uuid of the VMs to backup

First look for the uuid of the VMs to backup. We don’t want to backup the control domain itself, so we add is-control-domain=false to the vm-list command:
[codesyntax lang="bash"]

xe vm-list is-control-domain=false

[/codesyntax]

Create a snapshot of each (running)

Now we create a snapshot of the VMs we want to backup, replacing the uuid one by one with the ones we found with the previous command. Also replace the name of the snapshot if desired:
[codesyntax lang="bash"]

xe vm-snapshot uuid=8d6f9d81-95b5-2ffb-4ecc-b5e442cc5c22 new-name-label=gb-r7n2-snapshot

[/codesyntax]

This command has a return value: the uuid of the created snapshot. Then we transform the snapshot into a VM to be able to save it to a file, replacing uuid with the return value of the previous command:
[codesyntax lang="bash"]

xe template-param-set is-a-template=false ha-always-run=false uuid=4efd0392-8881-176c-012e-a56e9cb2beed

[/codesyntax]

Save the snapshot to file

In the next step we save the snapshot to a file, replacing uuid with the snapshot uuid and providing a meaningful filename:
[codesyntax lang="bash"]

xe vm-export vm=4efd0392-8881-176c-012e-a56e9cb2beed filename=gb-r7n2-snapshot.xva

[/codesyntax]

Remove the created snapshot

In the final step we delete the snapshot:
[codesyntax lang="bash"]

xe vm-uninstall uuid=4efd0392-8881-176c-012e-a56e9cb2beed force=true

[/codesyntax]

Source: http://www.jansipke.nl/creating-backups-of-running-vms-in-xenserver/

xen vm gzip export import

Publicat la 10 August 2012 No comments

To export just leave the filename blank as in this example:
[codesyntax lang="bash"]

xe vm-export vm=VM-UUID filename= | gzip -c > /mnt/vm.xva.gz

[/codesyntax]

To import use /dev/stdin as filename:
[codesyntax lang="bash"]

gunzip -c /mnt/vm.xva.gz | xe vm-import sr-uuid=SR-UUID filename=/dev/stdin

[/codesyntax]