Recovering data in MySQL from an EBS snapshot

This short tutorial may help someone using AWS (and, in particular, MySQL on an EC2 instance), to restore data to MySQL from an EBS snapshot (which, of course, any prudent system administrator regularly sets up in advance - ec2 ‑ consistent ‑ snapshot , for example)

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

Next you need to click on the picture with the right mouse button and select “Create Volume”. In the Availability Zone, you need to select the same region in which the EC2 instance is located.
')
After that, go to the section ELASTIC BLOCK STORE → Volumes and, again, right-click on the section that appears. In the menu, select the “Attach Volume” item, then in the modal window that appears, select the EC2 instance and click “Yes, Attach”.

Everything - a new block device should appear on the server. Now you can run dmesg | tail dmesg | tail and see which identifier was assigned to the connected block device. Suppose this is xvdg. Then the file system 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 into it:

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

In order to get the necessary data from the backup, we include an additional MySQL instance that works 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 let's try connecting to the running instance of MySQL:

 mysql -h 127.0.0.1 -P 5523 

If the connection is successful, you can start the data recovery process.

For example, consider a fairly simple scenario: the trainee administrator changed the production value for a specific field of a particular object, but was distracted by thinking about the eternal, and accidentally forgot to write a WHERE in the SQL query. Well, do not worry - who does not happen.

So, now we want to restore the sex field values ​​to website.profile. And not even for all the records, but somewhere for the third (because the administrator was, of course, thoughtful, but not so much as not to press Ctrl + C, realizing that the request was obviously running suspiciously long). To do this, in the shell of the main database, create a file containing the required 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 here 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 using 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 

Then just 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 section ELASTIC BLOCK STORE → Volumes and disable the virtual block device (Detach Volume). After that you can delete it (Delete Volume).

You can also go back to the snapshot section (ELASTIC BLOCK STORE → Snapshots) and somehow mark those snapshots where (judging by the time the snapshot was taken) there is incorrect data (for example, reflect this in the snapshot name). The alternative is to delete the snapshot altogether. But this solution is worse from the point of view that this particular snapshot may be needed by someone else (to recover completely different data, which in this particular snapshot may be in perfect order). Therefore, it is better to assume by default that any modern (and especially the most recent) snapshot may contain valuable data, and just in case not to delete them for some time (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 the WHERE condition, in which a specific object is uniquely specified. Therefore, if you accidentally forgot to add a WHERE, then you just get an error:

ERROR 1175 (HY000): you are using the KEY column

To enable this mode, it is enough to add to the options of the mysql command, to taste: ‑‑i‑am‑a‑dummy or ‑‑safe‑updates .

A similar effect can be achieved by adding the safe‑updates line to the ~ / .my.cnf file (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 it is rarely required): select_limit is set to 1000, and for max_join_size - 1000000.