Data recovery in MySQL EBS snapshot

This is a short guide might help someone who uses AWS (and, in particular, MySQL on the EC2 instance), to recover the data in the MySQL EBS snapshot (regular creation of which any prudent system administrator, of course, sets in advance — ec2‑consistent‑snapshot, for example)

First of all you need to open EC2 Management Console, under ELASTIC BLOCK STORE → Snapshots to find the (usually the last partition).

Next you need to click on the picture, right-click and select "Create Volume". In the Availability Zone you need to select the same region where the EC2 instance.

Then go to ELASTIC BLOCK STORE → Volumes and, again, right-click the mouse on the appeared section. In the menu select "Attach Volume", then in the opened modal window choose the EC2 instance and click "Yes Attach".

All — on the server there must be a new block device. You can now run dmesg | tail and see what ID was assigned to connected block devices. For example, it is xvdg. Then the FS can be, for example, /dev/xvdg1 (depends on the preferences of those who created the partition table).

Create a new directory and mounted it section:

the

mkdir /mnt/backup
mount /dev/xvdg1 /mnt/backup

In order to obtain the necessary data from backup, including the additional MySQL instance running with a separate data directory:

the

sudo -u mysql /usr/libexec/mysqld --basedir=/usr --datadir=/mnt/backup/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mysqld_backup.log --pid-file=/var/run/mysqld/mysqld_backup.pid --socket=/var/lib/mysql/mysql_backup.sock --port=5523

Now try to connect to a running instance of MySQL:

the

mysql -h 127.0.0.1-P 5523

If you can connect, you can start the data recovery process.

For example, consider a fairly simple scenario: administrator-trainee change in production value for a specific field of a specific object, but was distracted by contemplation of the eternal, and accidentally forgot to write in the SQL query WHERE clause. Well, okay — it happens to everyone.

So now we want to restore the value of the field sex in the website.profile. And not even for all records, and for the third (because the administrator was, of course, brooding, but not so much that you do not press Ctrl + C, realizing that the request is clearly running suspiciously long time). To do this in shell the primary database, create a file containing IDs:

the

select id from profile where sex= 'test' into outfile '/tmp/profile_id_list';

Accordingly, it will create the file /tmp/profile_id_list, which will be the IDs of those records, the field sex which you want to restore from a backup.

Then write here such script and save it under the name restore.py:

the

import MySQLdb

db = MySQLdb.connect(host="127.0.0.1",
port=5523,
user="user",
passwd="password"
db="website")

c = db.cursor()

f = open("/tmp/profile_id_list")

for profile_id in f.readlines():
c.execute(
"select sex from profile where id=%s",
(profile_id,)
)
print "update profile set sex=\"%s\" where id=%s;" % (
c.fetchone()[0],
profile_id[:-1]
)

And write the SQL file to restore sex

the

python restore.py > restore.sql

Check that the file is in order (for example, the number of rows can be viewed using wc-l restore.sql), and then performed SQL queries from a file:

the

mysql website < restore.sql

Check that all successfully recovered.

You can now delete /tmp/profile_id_list and other files, and therefore, shut down the MySQL server:

the

mysqladmin -u root -p-h 127.0.0.1-P 5523 shutdown

Then just admantium section and delete the directory that it is mounted:

the

umount /mnt/backup
rm-r /mnt/backup

In the AWS Management Console, respectively, go to the ELASTIC BLOCK STORE → Volumes and disable virtual block device (Detach Volume). Then it can be deleted (Delete Volume).
Else you can again go to the snapshot (ELASTIC BLOCK STORE → Snapshots) and somehow mark those images, where (judging by the time of the snapshot) has incorrect data (for example, to reflect this in the name of the picture). The alternative is to remove the. But this decision is worse from the point of view that this picture may need someone else (to recover other data that is in this picture may be just fine). Therefore, it is better by default, to assume that any modern (and especially the last one) the picture can contain valuable data, and in any case do not delete them for some time (e.g. a week).

And finally the Council. MySQL has a mode where you cannot perform DELETE query or UPDATE, if it is not specified WHERE condition, which is uniquely defined by a specific object. So, if you accidentally forgot to add WHERE you just get the error:

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

To enable this mode, simply add options to the mysql command, like --i‑am‑a‑dummy or --safe‑updates.

The same effect can be achieved by adding to the file ~/.my.cnf the line safe‑updates (which is useful, for example, if you run the mysql command without any options, and all will be automatically taken from ~/.my.cnf).

By the way, by default, this mode adds a couple of restrictions (which, however, can be disabled, but in practice this is rarely required): select_limit is set to 1000, and for max_join_size is 1000000.

Article based on information from habrahabr.ru