MySQL Data Recovery from EBS Snapshot

This short guide will probably help someone who uses AWS (and, in particular, MySQL on the EC2 instance) to recover data in MySQL from an EBS snapshot (which any prudent system administrator regularly creates, of course, in advance - using ec2 ‑ consistent ‑ snapshot , for example)

First of all, open the EC2 Management Console , and in the ELASTIC BLOCK STORE → Snapshots section find the appropriate snapshot (usually this is the last snapshot of the section).

Next, right-click on the picture and select “Create Volume”. In the Availability Zone, you must choose the same region where the EC2 instance is located.

After that, go to the ELASTIC BLOCK STORE → Volumes section and, again, right-click on the section that appears. In the menu, select the “Attach Volume” item, and then in the resulting modal window select the EC2 instance and click “Yes, Attach”.

That's it - a new block device should appear on the server. Now you can start dmesg | tailand see what identifier was assigned for the connected block device. Let's say this is xvdg. Then the FS can be located, for example, on / dev / xvdg1 (depending on the preferences of the person who created the partition table).

Create a new directory and mount the section in it:

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

In order to get the necessary data from the backup, we will include an additional instance of MySQL working with a separate data directory:

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:

mysql -h 127.0.0.1 -P 5523

If you can connect, you can begin the process of data recovery.

For example, consider a fairly simple scenario: the trainee administrator in production changed the value for a specific field of a particular object, but was distracted by thinking about the eternal, and accidentally forgot to write WHERE in the SQL query. Well, it’s okay - with whom it doesn’t happen.

So now we want to restore the values ​​of the sex field in website.profile. And not even for all the records, but somewhere for a third (because the administrator was, of course, thoughtful, but not so much as not to press Ctrl + C, realizing that the request was obviously being executed suspiciously long). To do this, in the shell of the main database, create a file containing the necessary identifiers:

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

Accordingly, the file / tmp / profile_id_list will be created, where there will be identifiers of those records whose sex field needs to be restored from the backup.

Next, we write such a script, and save it under the name restore.py:

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:

python restore.py > restore.sql

We check that the file is in order (for example, the number of lines can be viewed with wc -l restore.sql), and then we execute SQL queries from the file:

mysql website < restore.sql

We check that everything was successfully restored.

Now you can delete / tmp / profile_id_list and other files, and, accordingly, turn off the MySQL server:

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

Next, simply unmount the partition and delete the directory in which it was mounted:

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

And in the AWS Management Console, respectively, go to the ELASTIC BLOCK STORE → Volumes section and turn off the virtual block device (Detach Volume). After that, it can be deleted (Delete Volume).

You can also go back to the section with pictures (ELASTIC BLOCK STORE → Snapshots) and somehow mark those pictures where (judging by the time the picture was taken) there is incorrect data (for example, reflect this in the picture name). An alternative is to delete the snapshot altogether. But this decision is worse from the point of view that it is this image that may be needed by someone else (to restore completely different data, which in this image may be exactly in perfect order). Therefore, it is better to assume by default that any modern (and especially the latest) snapshot can contain valuable data, and just in case, do not delete them for a while (for example, a week).

And finally, advice. MySQL has a mode in which you cannot execute a DELETE or UPDATE query if it does not specify a WHERE clause in which a specific object is uniquely specified. Therefore, if you accidentally forgot to add WHERE, then 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, just add mysql commands to the options, to taste: ‑‑i‑am‑a‑dummyor ‑‑safe‑updates.

A similar effect can be achieved by adding a line to the ~ / .my.cnf file safe‑updates(which is convenient, for example, if you run the mysql command without any options at all, and everything is automatically taken from ~ / .my.cnf).

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