Okay, so you’ve gotten used to using mysqldump files for your MySQL databases, but have been concerned about how often you need to run these. Maybe this was fine when you only had a few changes a day, or the work you were doing on your database was not mission-critical. However, now your work has really started to pay off. Your database is running full speed all the time. You are starting to get concerned about the time in between your backups and what data might be lost in the course of a day.
As mentioned before, typically when running a backup of a MySQL database, we create a mysqldump file. Usually, we do this daily, using a cronjob to create a new file with a timestamp of the file. This way if the database becomes corrupt we can simply load the most recent file and recover most of our data.
However, as we now know there are limitations to this approach, being that you run the risk of losing any data changes since the last update. You could set this to run hourly, but your database is now receiving a lot of activity. You are concerned that any outage or server crash could still result in significant losses. You could set it to run every second, but then you would suffer serious performance issues, and you’d very rapidly fill up your server, especially if your database has any size at all.
Thankfully, MySQL has an extremely useful feature, namely the Binary logs, or binlogs. If configured, these will track any changes in the database and save a snapshot of the database as a binary file. In other words, in the event of something going wrong, we can have a record of the state of the database as of the last update; we lose virtually nothing.
So what do Binary Logs do?
Say you already have full backup of the database in your last mysqldump file as of yesterday afternoon. However several changes may have been made since then. If the binlogs are set, you can get each and every change that occurred since then, so that even after your last backup you have a record of all new transactions.
Using these together with your mysqldump file you can get a full backup that is 100% accurate.
Binary logs have a number of functions, and are particularly crucial in being able to replicate all of the content within your database.
Setting Up Binary Logs
First of all you will want to make sure the binary logs are enabled.. In most cases they are by default, but to check you simply run the following command in mysql:
If they are not turned on, you will see a message like this:
ERROR 1381 (HY000): You are not using binary login
If you get this error message, you can easily configure binlogs by placing the following into your my.conf (or my.ini if it’s windows) file under mysqld:
Actually even if it is running, you will want to verify the below for reasons that we will get into later, particularly the section setting up a master/slave. The lines you want to pay attention to for later are bind-address, which is the IP address of your server, server_id which a specific identifier for your server, and binlog_do_db which is the name of the database. Note: you can enter multiple rows for binlog_do_db if there are different databases being replicated; these have no effect on the master; they are references for the replicating database. You can enter multiple lines if necessary.
But I digress; you’ll see the use for these later. The binlogs will work without these lines, but you will need to enter these if you wish to use some of the more advanced features so better to do it now.
Once you have this information entered, stop and restart MySQL.
Run the following command again:
And you should get something that looks like this:
+————+———–+
| Log_name | File_size |
+————+———–+
| bin.000001 | 533 |
| bin.000002 | 675 |
| bin.000003 | 12973 |
+————+———–+
3 rows in set (0.00 sec)
First of all, of course you’re thinking, well where are these files on my system?
On a *nix system or on a Mac, the default location for them is at /var/lib/mysql/
On a Windows machine, they are typically in \mysql\data
Okay, you say, how do I see what’s in them? You can’t simply open these right away, as they are binary files that can’t be read or used with a text editor (if you try you’ll get a mess; you might be able to make sense of pieces of them, but … why?) . So you will need to use the following command (you will want to put these into a file as shown below, because these are likely to be very large):
Now if you open filename.txt, you’ll still something that looks like a mess! Just kidding, it actually it does make a more sense. What you will see is an account of everything that happened on this database in SQL format. Here’s an example portion:
SET INSERT_ID=13/*!*/;
# at 112761
#180711 20:56:22 server id 1 end_log_pos 113081 Query thread_id=52 exec_time=0 error_code=0
use `clients`/*!*/;
SET TIMESTAMP=1531356982.771806/*!*/;
SET @@session.time_zone=’SYSTEM’/*!*/;
INSERT INTO `contact` (`user_id`, `name`, `organization`, `email`, `phone`, `message`, `entered`, `time`, `notes`, `date`) VALUES (NULL, ‘Joe Smith’, ‘organization name’, ‘joesemail@smith.com’, NULL, NULL, NULL, NULL, NULL, CURRENT_TIMESTAMP)
/*!*/;
# at 113081
Okay, at this point you know you have binary logs enabled, and you know how to get at them, and you’re starting to get a hint about how these can be used.
Restoration using the binary logs
So now you have all this information, and you’re getting an idea of what’s happening, but let’s go back to your original goal; restoring your database with updated data.
Remember that you have a mysql dump file of your last replication? Let’s put that aside for right now (let’s assumed that it’s called dumpfile.sql).
Now you will want to get a copy of your binary logs using the following command:
Or if you are loading multiple binlogs use this:
Ready to restore your database? Let’s go!
Wait, hold up! One important thing you need to want to pay attention to: when you restore your database after a crash (or for whatever reason) your binary logs will actually record this transaction as well and add it to the binary logs; so the restore itself will create an endless loop where the restoration will continue until it is done, which won’t happen because it’s continually creating new binary logs!
To prevent this you will first want to disable your binary logs using the following command as root:
(The -D is case sensitive) Now load your original msyqldump file.
After that you can load your file like so
Alternately you can paste the file into your SQL window if you’re like me and like graphic interfaces.
It’s important to remember that you don’t need to load the entire binary log, you can manually remove pieces of the data from the sql files, or you can specify specific parts that you do or don’t want. For instance, if you know a specific start position and stop position you can load parts of the file like this:
Similarly, if you know a specific datetime that you wish to begin with you can do something like this:
These are, of course only a few examples, there are many more available to you
Flushing your binary logs
One thing you will want to do is to create a fresh log file after you’ve restored the database (you may wish to do this periodically anyway), so you will need to flush your binary logs, or those that are no longer relevant. So maybe you have 156 binlog files, but you only want to keep the last 5 of them. All you need to do is to run this command:
You can also specify these by date and time:
Master/Slave Backups
One very useful feature that using MySQL’s Binary logs is the ability to to set up master-slave replication. This is a process where you can have multiple copies of your databases running at the same time. This adds safety and security – you have already have a backup of everything all the time. It will also speed up the restoration process. Beyond that, you will also have another database that can be used for analysis without affecting the main database. It can help you the process of scaling up in the event that you need more than one instance running of the same database.
Let’s cover some of the basics. You already have a master database; this is what we’ve been using. You created this when you entered the files into the my.cfg file back at the beginning.
At this point we can set up the correct privileges for the slave or replication database:
Then use the following query to give the slave server access:
Then run the following query:
You’ll get a table that looks something like this:
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 | 198 | mydb; | |
+——————+———-+————–+——————+
Make a note of the position (in this case 198). Then enter
UNLOCK TABLES;
At this point we are going to need to set up the configuration for the slave.
Log in to a new session as root and then run the following:
mysql> CHANGE MASTER TO
-> MASTER_HOST=’12.34.56.7876′,
-> MASTER_USER=’slave‘,
-> MASTER_PASSWORD=’replication_password‘,
-> MASTER_LOG_FILE=’mysql-bin.000001 ‘,
-> MASTER_LOG_POS=198;
It’s now configured. Now you can turn on the replication database:
START SLAVE;
To check your replication database status, you can just type:
SHOW SLAVE STATUS\G;
You will then be shown information regarding your replication database. If this line exists
Slave_IO_State: Waiting for master to send event
And if Seconds_Behind_Master is any number (including 0) and it is not null, then everything is running as it should. Congratulations! You now have a master-slave database relation set up!
Conclusion
This is just a beginning to using the binlogs and replication in MySQL; and the basics to what you need to understand to get started using them to backup your databases. There’s a lot more but for now you have the basics and can start using these to manage your database backup process. As with anything getting really familiar with this means getting used to using it. In other words, practice makes perfect!
