Veeam and MySQL: How to Perform Backup & Recovery

Speaker: Paul Parkin
Published : August, 2017
Veeam and MySQL: How to Perform Backup & Recovery

View slides

Rating: 5 / 5 (10 votes cast)

MySQL and the MariaDB variant are among the most popular and used databases in Linux environments and many critical applications support their data on them.

Getting a consistent database backup and performing effective recovery is vital to your business.
Follow this live webinar to find out how Veeam® Backup & Replication ™ allows you to get consistent backups of MySQL databases.

Video Transcript:


Good morning. Welcome to today's Veeam® webcast. And we are here today to talk about Veeam and MySQL. Welcome everybody who's attending on the call, well over a hundred of you so far and there may be some more joining later. So, this is about Veeam and MySQL and how to make a consistent backup and, more importantly, an effective recovery. My name's Paul Parkin and I'm a systems engineer. Fortunate enough to be a Systems Engineer here at Veeam Software and the UK and Ireland. So let's begin. So, our agenda today is essentially an introduction to MySQL and Maria DB. They a discussion on theme and scripts for application consistency and the three recommended backups to the three recommended methods, get a decent backup for, for the two databases, recovery of course. And then at the end we have to have plenty of time for questions so that you can field your questions and I will answer as many as I possibly can.

 So, Maria and MySQL has quite a history that from 1995 when the first version was released, MySQL was, was, was gaining popularity very, very quickly. And then 2008 of course it was acquired by Sun Microsystems later, Sun Microsystems were subsequently acquired by Oracle. Of course in the same year MariaDB was created from a fork of MySQL version 5.5 and MariaDB remains open source and remains compatible with the Oracle version and a lots of distributions will actually, will actually allow you to install MariaDB as part of the installation process center. Been one of those, one of my favorites as well. So at the bottom here you can see how popular my SQL is and these are up to date scores from August 17 so you can see that Oracle is still a very popular DBs database model and MySQL is not far behind at all. Okay, so this section is about a Veeam and scripts for application consistency.

Veeam scripts and application consistency


On the right-hand side of this screen, you'll see a job flow. This job flow is the process that Veeam follows when, when we're doing a backup of a Linux or Windows, if you want to use either of these databases on Windows, when we do a consistent backup these databases, so we as you, as you can see from the flow, it involves pre-three, pre-freeze and post-call script execution. But the key thing to remember here is that a Veeam doesn't leave those scripts lying about in temp after we've completed the backup, we actually clean that up. So in the, in the absence of any VSS, of course in that we use in Windows, Veeam needs to use these peripheries postal scripts methodologies in order to make sure we get a consistent backup, both not just these two databases but often many other databases too.

 So, to protect MySQL and MariaDB, we typically require this pre-freeze postal script, which is usually a dot I say shell script and this script can be placed anywhere on the Veeam Backup & Replication™ server. By convention most people choose to put that script in C script unless type database type directory structure. It just makes diagnostics a lot easier if everybody knows where they are. And bear in mind that if the DBA is needing to modify those scripts for some reason, for instance, they've changed the version of the database or they've changed the OS or they've changed the authentication methodology, then that will need to be changed. Of course, in the, in the backup job for authentication or in the script for, for any, any alterations and checks if commands have changed, ect, ect…. And there’s our options.

So, let's say let's talk first about a script modification position. So for those who are not Linux admins, Linux DBAs, ect, those who are Windows operators, please bear in mind that when you modify a script in most Windows tools, you will find that the end of line character messes up a Linux based file. OK. The best thing to do if you're using Windows is to use something I know, about plus, plus I use it and find it wonderful. However, my preferred method is actually to SSH onto the Linux box and modify it using VI VIM, Vaco nano, whatever your favorite editor is. And, and that way I know it's in the right format and then I just copy it back to the, to the Windows VBR server to the being Backup & Replication server. So those scripts are stored on the VBR server and typically we're using quotes 22 that's customizable so you can move it to a different board if you've chosen a custom quote for SSH and a, we typically copy that during the job execution to a ‘slash TMP you ID underscore a script name’ so you can easily identify it and diagnose where if you have issues during the back of the process.

So the, if, if there's no network connection, front-end network connection to the VM, then between the BNR on the VM, then typically we need to use the VIX communication channel and VMware and all the to be able to, in order to be able to communicate with a VM via VMware's internal channel to the virtual machine. And in that case, you'll find the temp files, you'll find it in a still NTMP slash VMware route slash script name instead of the directly under the slash TMP directory. Okay.

Veeam Backup & Replication:


So we need to talk about guest account management within Veeam and the jobs are very simple to set up the availability of those jobs and the simplicity with which you can search schedules and set protection tasks and a retention lens is very straightforward or less in the manual. Today I'd like to focus on what we've come here to talk about. So managing access to the Veeam system and running Veeam Backup & Replication script requires access credentials of course, and been best practices, probably better to make sure you use the description field so that all administrators were using the Veeam console know what this job is for and how it's authenticated, maybe as long you're not giving too much away under the other information as well. Now from version nine five VMware added the ability to be able to do Linux private key credentials. Okay, so we can access your Linux machine. You know us going to read out which, whichever flavor you like via a Linux private key. So, what we need to do obviously is create that private key and important at this stage within the Linux credentials. Doing so will allow you to maintain, of course a zero password challenge interface should you wish to do so. So, we can still back up those very, very secure Linux and Maria and bicycle database instances in most environments.

SSH key generation


So SSH key generation. This isn't a full tutorial by the way I, I'm considering doing one of those tutorials maybe on a recorded webinar or something. If you, if you're interested in not making a comment in the, in the, in the comments here within, within this go to webinar chat. So essentially the high-level process for this is typically you log in as the user, you are going to, you are going to use that for the authentication between the VBR server and they target machine and you will use something like SSH and keygen and there are options, additional arguments stuff. But typically that will do the job. It will prompt you for the username- the passphrase, sorry. And, and it will allow you to create a pair of keys within a dot SSH directory underneath your user home, your home user directory.

So, when that's requested by the job configuration, we need to point that to the credential store. So, you need to get the file, files off the home directory and onto the VBR server. And then of course you can remove them afterwards. Sensei, same potent to keep this key safe. So, we're storing these credentials within Veeam and this is a private key we're asking for. Since we need to keep this safe, if you are using this method of, of, of authentication, we would, we would recommend from a Veeam perspective that you keep your Veeam Backup & Replication configuration backup, which is a backup to protect the configuration of the overall VBR server, we would recommend that you take that in encrypted form. Now by default Veeam does not encrypt the configuration backup. However, this is a simple option to enable. You give a, a passphrase or secret and Veeam will from then on, start using encryption to encrypt all those authentication that that authentication database. Okay. And highly recommended and in fact is recommended whether you're going to use the a key base, pick key based authentication or not.

Guest account management


So, and once you get past, once you've assigned your, assigned your private key, you will see on this dialogue here that you had asked for your username. That's the username on the Linux machine that you want to connect with. You asked for a pass phrase, if you created one at a time that you created your product key, and then you're asked to browse to that private key in order for us to be able to use it. Now again, you can customize the SSH port to news accustomed one should you wish to. And if necessary, you can elevate the specific account to route. And of course this can optionally out the accounts as to do as in order to do that. Now if they, if the account you got assigned for this task is a level account, then you won't need to elevate to route. So it's, you know, it's, it's a question of security and what your policies are as far as the security of your Linux-based machines, whether, yeah, Linux based virtual machines. Okay. So are you at the private key and set the SSH port and if necessary, elevate the specific, specific account to route.

Job configuration


So, in job configuration you can see here that in the guest processing tabs we enable guests processing, we enable application-aware processing and we go to the scripts tab of the applications dialogue. So, once we’re in there we can enter up pre-freeze script, which is a fairly usually a fairly simple script. However, its customizable based on what you've got in your, in your, in your SQL and your bicycle DB, how that constructed and what you are backing up. So, the script is of course customizable to your tastes and post-thaw scripts is added as well at that place. So, we've now reached a stage where we're at the three recommended methods to get to consistent backup of Maria DB and MySQL DB.

Three methods for consistent MySQL backup


And these are our three methods. They hot backup, which is a database dump, a hot backup, which is a database freeze and a cold backup, which is a full database shutdown. So, we'll move onto that and the hot backup with a database online dump is, is essentially the workflow is the pre-free script dumps the database to a single file located in /TMP and the VM snapshot is, is then executed. So these are two steps but it's in the in between those, there's quite a bit of time in order to achieve that. So, a pause is often required in order to make sure that the database is, is, is actually completing the dump before we started taking the VM snapshot that the dump is them deleted by the post-thaw script. And this is an optional process so you can modify the script, but typically you'll want to delete the dump because the dump is the size of the database.

So, you're saving on disc space if you delete it. The pros of this, well, the database remains online and if that's the only option from an operational perspective, then this is your best option and you need to make sure you've left enough space on the virtual machine to be able to do that database dump. The read and write operations are permitted during the operation of the backroom because this is the hot backup. Okay. And the cones. I've mentioned a couple, its, it's a more complex method is takes a little bit of a trial and error to make sure that you are absolutely, definitely going to get this done and completed before before Veeam takes a VMware snapshot and it takes more time and canvas and can lengthen the backup window due to that dump creation. So and consider higher RTO discs if you're using this method of recovery.

Bear in mind that if you use what Veeam calls instant recovery, which we'll talk about in a minute, then being able to use that dump file in order to recover from is going to be disc intensive. So high RTO discs a flash, or something fast anyway, we'll improve the performance of that day at that recovery process. The next one was database freezing, so hot backup database freezing and the pre-freeze script dumps the database to a single file located in temp- TMP, sorry. The snapshot is executed. The dump is then deleted from the post- post-thaw script. And the pros of this, well the database remains, remains online but it's read only during the, during the time we're doing the backup, the dump and the snapshot and we don't need as much desk as during a hot backup online minimum RTO, yes, absolutely mini- minimum recovery time objective when we come to recover this particular dump file.

So, cons, the database is only available for reads during that time and it's necessary to introduce the time out to, in order to enter the read only state for the database of course, because that takes a little time. And the next one is cold backup to come back with the database totally shut down. Of course, the scripts can shut down the database based on your commands in the order you want. And then we take a VM snapshot and then after the backup the database is started and you can then process kick off some kickoffs, some internal scripts within the VM to make sure that that database is up and running after the backup. So, bear in mind the users got to have privileges for executing the start and stop of the service. And so, when you're creating that user on your Linux box, make sure that the user has DB rights to be able to stop and stop that. So, or, or OS rights to be able to start and stop that service. Pros of that; well it's easy to implement and share is there's no additional this space required because we're just a snapshotting the entire VM and it's a minimum many of them recovery time operation. So, it's really, really short and very fast to recover a VM from a backup that’s been made using this, using this technique, not the database is not available during backup is about the only con on this one. So if you do have a maintenance window throughout the, throughout the night, maybe overnight, then this will be a very practical option even if it's not necessarily palatable in all cases. We offer you the other two alternatives in that case.

MySQL Recovery


So, what about recovery? We've covered our backup. What about the recovery methodologies themselves? Typically, most Veeam customers will go, rather than restore a whole VM what they'll do is do an instant recovery. So instant recovery is essentially mounting the backup from the VBR server from this repository and connecting that via vPower into the hypervisor. vPower is our own proprietary technology and, and the hypervisor then boots the and from that data path right to our backup. So now once the VM is up and running, we can then either do some extract or we can make sure it's on a separate, private network so that we can do some other operations with it. But Veeam offers you other alternatives there as well. So rather than just using a, a separate VMware network for, for the recovery of this VM, if the original VM is no longer booting and it's down, then of course you can restore that. You can recover that VM via instant recovery straight into the hypervisor and straight into the production network. And the VM will operate as if it was working directly from the time we took that backup.

The other options are to use a virtual labs and that's the subject of another seminar. So, make sure you sign up for that one when it's alive. So the, if you've used the dump restore method, if you use the dump method, then the restore is basically relatively simple, but if the problem is not limited to database on availability, the entire VM must be restored by the backup so you can use, Veeam’s FLR to recover individual files, individual folders from your backups, your Linux backups and, but the best method is to actually recover the whole virtual machine if you like, and doing it via instant recovery is the fastest way of getting access to that data. If you don't need fast access, if that isn't a part of driven the process for us, if it's maybe for, for test and dev or something like that, then use the V lab option that I've already just mentioned. So, it's the database dump method was used. It's typical to, to use a command like that at the bottom of the screen, something like MySQL monitor you username and password and, and then to send that, to send that to the MySQL dump SQL file so you can inject, you can inject it into the database, relatively straightforwardly. Standard backup methodologies apply for Maria and MySQL database.

The next method is use Veeam U-air database restoration. And essentially what we do here is use an isolated network with our little virtual lab proxy appliance and, and the production guest, the recovery guest, is brought up in an isolated network and we're able to nat between the two being able to transfer files as a result of that process. So this is what you might call a stage recovery methodology. Veeam doesn't just offer you one method to get your databases by. We offer you many methods to get it by just not one method, doesn't cover all eventualities. So it's important that you have more tools in your kit bag when it comes to recovery and that you know how to use them.

So, the connectivity between the isolated environment and approach environment is insured by an automatic way generated virtual lab proxy VM and this is essentially a small Linux appliance and we make sure that traffic isn't route between the two of course other than between the production and virtual lab machines when we need to. So further reading on Veeam and Linux with MySQL and Maria DB and highly recommend this, this solution paper by my colleague Pascal Di Marco and I'll leave the URL on screen a couple of seconds just in case anybody wants to jot it down before you get the slides after this, after this seminar and then I'll move on.

Other links about MySQL Backup


A couple of little lea- useful links here and there's a good section within the V- within the Veeam manual for VMware using pre and post, post session scripts so that this the Veeam manuals, a lot of manuals from vendors are quite dry, included a lot of marketing information and are difficult to read. I'm got to say my experience with Veeam manuals is excellent. I think they are concise to the point and devoid of as much marketing material as we can manage. Okay, so the Veeam manual for VMware also has a section on Linux private key generation as well. And if you're a Windows based admin primarily then, then maybe this is something that you should go and have a look out. Okay, so I've got to the stage where I'm starting to ask for questions. Are there any questions from attendees? Is there anything in the chat?



Okay, I don't think we've got, there are a couple, I’ve seen one from Vincent. Well this one, does the Veeam job status show how long the dump takes so we can see if it is making the backup longer than it should be. Okay. Vincent, to be honest, that's something that we should be tracking within the OS itself. Of course, you, it's going to take maybe a little bit of trial and error in the first instance to make sure that the dump file is created. If you're using the hot backup online, then then you can, you can test this in your own, in your own environments and do it on prior to, to even initiating the Veeam backup and in doing so you will, you will be able to empirically test how long the dump is taking and therefore you can modify your script to allow for that timing within the script itself. Hope that answers your question.

Let’s have a look: is that script pre-saved by Veeam or do we have to get it online somewhere? Okay, so there are scripts within the document that I showed a few seconds ago. Let me go back to that to make sure you have the URL if you want to go and see that soon. So what I would say though is bear in mind to these scripts are generic, pretty generic and your specific implementation of Maria or MySQL may need some customization in order to make sure you are able to recover it in full. So please do take diligence over the script detail to make sure that you are covering everything that your MySQL, or SQL Maria DB, DBAs or application vendors have asked you to back up. Okay, thank you for that question. Let's have another look at another one. Does the Veeam job status show how long? No, we done that one.

Just make sure is a script, is script place on guest VM- DJ? Yes. There's a script placed on the VM itself. On the guest VM it’s in those and /TMP/folder and we do that during the initiation of the backup job. When, when we initiate the backup job, we push the script using the authentication you've given us into the TMP directory, but then that script is used to execute whichever methodology you've chosen, whichever one of the three methodologies you've chosen. And then most importantly after the backup we remove the script. Okay. So it's important to remember that changes to the script. If you want to change, they need to be made on that C script OS type DB type folder for your script. Okay. Alright, sure. As the recorded session, we will do that on my own time. I hope you were, okay. And I think we've got one there: are that many pre and post-scripts already made? Already answered, I believe. They, the documents on screen will show you. We'll show you the basics on then I would suggest going to some of the MySQL and Maria DB backup sites where there is a lot of discussion on backup scripts and how to use them. What Veeam does is give you full flexibility to be able to use those methodologies but call the scripts directly from the Veeam backup job. Okay. I believe that's it for the questions.

Let’s have a look: is there a method, to back up logs, like Oracle SQL transaction logs? I don't- to improve RPO. Ah, that's a great question. There is- at Veeam at the moment we don't have a separate log-based backup program, to back up Maria DB and MySQL logs and truncate them while we do with SQL server and Oracle databases. However, if the community was to get active on Veeam forums and make some suggestions, but I've no doubt our developers, as they usually listen to lots of numerous of people, will be more than welcome to the attention. Okay, so now we don't do that now, will we do it in the future? I can't honestly tell you, but bear in mind that with some of these methods of backup you could back up relatively frequently depending upon the impact to your VM at the time you taken the backup. So watch that. Okay. I think, I believe we are just about out of time. If there are any more questions, please feel free to contact me either by email or via your Veeam local office, and as upcoming events on product demos and the, and the resource library, definitely recommended. And in in these links here, you'll be sent the slides after the, after the webinar.

Just get one more slide. Oops. And, and this is me. So should you need to contact me, contact me on Twitter or directly by email. If you have any more questions about that, about, about Maria DB and MySQL backups, then please feel free to, to ping their message, ask me a question as soon as I've got time, I will reply. Thank you very much for your time today. I hope you have a smashing week and I look forward to hearing more from the community. All the best.

The best HTML CheatSheet has its own visual editor where you can apply your own CSS code as well. Web developers and designers love this free online resource!

Duration: 32:56

Register to watch

By registering, you agree that your personal data will be managed by Veeam in accordance with the Privacy Policy.