MySQL

Veeam – Linux VM: A place to back up MySQL

What does it take to back up MySQL on a Linux VM? This is a riddle we sometimes hear at Veeam: When running on a Linux VM, how does one quiesce MySQL databases? Unfortunately, there are not many new ways to answer this riddle, and the answers we currently have are already tried and tested!

The answers can be found in our popular white paper Consistent protection of MySQL/MariaDB with Veeam, written by Solutions Architect Pascal Di Marco. The paper is available for download on our website and describes three different methods for backing up MySQL/MariaDB on a Linux VM. Two hot backup methods running pre- and post-snapshot scripts, and cold backup using database shutdown. This makes use of VMware tools installed on the Linux VM. It is not straightforward like a Microsoft SQL quiescence, because Linux doesn’t have a VSS mechanism like Windows does.

Described are methods of backing up MySQL/MariaDB on a Linux VM using activated scripts local to the database. VMware can run a script to act before the snapshot is created, known as the pre-freeze script and can run a script to do things after the snapshot is created, known as the post-thaw script.

Here’s a quick summary:

Option 1: Hot backup — Database online dump

The mysqldump command copies a database to storage accessible from the MySQL server, taking an online dump of each database without disrupting the MySQL service. This method lets you take a transaction consistent backup of databases but more steps are needed to perform a restore. As with Option 1, the pre-freeze-script will only run if you have the VMware tools running.

Advantage: This allows for 100% uptime; the MySQL service does not stop and the dumped databases are in a transaction-consistent state.

Disadvantage: Depending on the size of your databases, the process may take a considerable amount of time to achieve. A second copy of the database means extra storage space is required to maintain it.

Option 2: Hot backup — Database freezing

Stop the MySQL service for a few moments while the snapshot is created, then start it again. The post-thaw script will not run until the snapshot is created. The pre-freeze script and post-thaw script will only be able to run if you have the VMware tools running in your MySQL server.

Advantage: This is quick and simple, allowing you to take a transaction of all databases with no additional disk usage local to the MySQL server.

Disadvantage: Databases running on the MySQL server will briefly be unavailable, and applications that need 100% uptime may not find this suitable.

Option 3: Cold Backup — Database shutdown

In this method, the application service will be stopped during snapshot creation and restarted once the VM snapshot has been created. It requires permission to start and stop application services but does not require MySQL user permissions. You can authenticate by either using the MySQL default configuration file or hardcoding the username and password in the script.

Advantage: This is easy to set up and doesn’t take extra space. It provides a short RTO, since no further action is required aside from booting the restored guest.

Disadvantage: The databases will be totally unavailable while the guest snapshot is created.

Recovery

Guest recovery: The cold backup and freeze method will leave the database consistent and able to start up without additional operation, so restoring the VM from the backup files is the only operation to perform. The guest recovery may benefit from Veeam’s Instant VM Recovery feature, which lets you boot up the guest directly from the Veeam Backup Repository in minutes.

Additional dump restoration: The extra task of injecting the dump file into the database using file redirection is necessary if the following is true: The issue is not limited to a database outage, the entire VM must be recovered from the Veeam Backup file and the database dump method has been used.

Veeam U-AIR database restoration: Whether it is a granular or a full database restoration, Veeam U-AIR wizard can be used in conjunction with any relevant database management tool such as MySQL Workbench to recover a database item.

 

meltdown spectre

Microsoft Releases More Patches for Meltdown & Spectre

Microsoft informed users on Tuesday that it released additional patches for the CPU vulnerabilities known as Meltdown and Spectre, and removed antivirus compatibility checks in Windows 10.

Meltdown and Spectre allow malicious applications to bypass memory isolation and access sensitive data. Meltdown attacks are possible due to CVE-2017-5754, while Spectre attacks are possible due to CVE-2017-5753 (Variant 1) and CVE-2017-5715 (Variant 2). Meltdown and Spectre Variant 1 can be resolved with software updates, but Spectre Variant 2 requires microcode patches.

In addition to software mitigations, Microsoft recently started providing microcode patches as well. It initially delivered Intel’s microcode updates to devices running Windows 10 Fall Creators Update and Windows Server 2016 (1709) with Skylake processors.

Now that Intel has developed and tested patches for many of its products, Microsoft has also expanded the list of processors covered by its Windows 10 and Windows Server 2016 updates. Devices with Skylake, Coffee Lake and Kaby Lake CPUs can now receive the microcode updates from Intel via the Microsoft Update Catalog.

Microsoft also informed customers on Tuesday that software patches for the Meltdown vulnerability are now available for x86 editions of Windows 7 and Windows 8.1.

The company has also decided to remove the antivirus compatibility checks in Windows 10. The decision to introduce these checks came after the tech giant noticed that some security products had created compatibility issues with the Meltdown patches. This resulted in users not receiving security updates unless their AV vendor made some changes.

Microsoft has determined that this is no longer an issue on Windows 10 so the checks have been removed. On other versions of the operating system, users will still not receive updates if their antivirus is incompatible.

Microsoft’s Patch Tuesday updates for March 2018 fix over 70 flaws, including more than a dozen critical bugs affecting the company’s Edge and Internet Explorer web browsers.

veeam

Getting started with Veeam Explorer for Microsoft SQL Server

Believe it or not, I used to work a lot with Microsoft SQL Server. While I did not call myself a database administrator (DBA), I did know my way around a database or two. Since I’ve been at Veeam, I have always enjoyed telling the Veeam story around using SQL Server as a critical application that needs the best Availability options.

That’s why I took particular interest in Veeam Explorer for Microsoft SQL Server that came in Veeam Backup & Replication. Veeam Explorer for Microsoft SQL Server allows application-specific restores of SQL databases, and also contents of tables, objects such as stored procedures, views and more. Additionally, you can also restore the databases to a specific transaction.

This is a great combination of functionality from the established application-aware image processing with a dedicated tool for database restores in Veeam Explorer for Microsoft SQL Server. Additionally, Veeam Backup & Replication and the Veeam Agent for Microsoft Windows also provide an image backup of the entire system.

For those who are not a DBA, sometimes dealing with low-level SQL Server topics can be a bit overwhelming. To help this process, I created a few scripts to help individuals learn this type of interaction with SQL Server. I put three (and a deleted script) up on the Veeam Github site. To use this script, only an S:\ drive is needed (the path can be changed) to create the sample database and put in a SQL Server Agent job to automatically run a few stored procedures that will insert and delete random data. This creates a database called SQLGREENDREAM.

After running the three scripts to create the database, implement the random number function and set the schedule to create the random data (2 records) and delete 1 record. The SQL Server Transaction Log Backup will show the new database being backed up after the next incremental backup:

Veeam Explorer for Microsoft SQL Server

Once the interval of the SQL Server Agent job runs (12 minutes in the GitHub script) and the Veeam Backup Job interval passes, the most selective restore point option can be selected in Veeam Explorer for Microsoft SQL Server. This selective option, to restore to a specific transaction, is shown in the figure below:

Veeam Explorer for Microsoft SQL Server

Once the interval of the SQL Server Agent job runs (12 minutes in the GitHub script) and the Veeam Backup copy interval process through a time when the test data has been run, the restore to a specific transaction option can be visible to the controlled scripting for the SQLGREENDREAM database in the GitHub repository. Then you can see the records in question being just as scripted, 2 records added then one record deleted. Those entries are done by the SQL Server Agent:

From there, the restores can be done with confidence to see how the SQL databases are restored with Veeam. With the sample scripts in the GitHub repository, one can become more comfortable with these restore situations when venturing out of normal comfort zones! If you are using Veeam Backup Free Edition and the SQL Server is a VM being backed up, you can still use Veeam Explorer for Microsoft SQL Server to restore the database to the time of the image-based backup; just no transaction rollback. You can use the NFR program for a fully functional installation also.


This article was reposted from Veeam.com

veeam

Best practices from Veeam support on using tape

When speaking about backup storage strategies, Veeam’s recommendation is to keep the initial backup chain short (7 – 14 points) and use general purpose disk that will allow you to recover data in the shortest amount of time. The long-term retention should come from secondary and tertiary storage, which typically boasts a much lower storage cost per TB, but at a trade-off, the RTO when restoring from such storage can take much longer time. Here’s the graphics, which illustrates this scenario:

veeam support

Additionally, with many new features of Veeam, the tape support now includes putting vSphere, Hyper-V, and Veeam Agents for Microsoft Windows and Linux backups on tape.

One of the most popular choices for backup archival is tape. It is cheap, reliable and offers protection against crypto viruses and hacker attacks. Additionally, it’s offline when not in a tape loader.

With Veeam, IT administrators can use flexible options to create copies of backups and store them on a different media, following the 3-2-1 Rule for the backup and disaster recovery. This blog post provides advice and considerations that will help you create a robust tape archival infrastructure.

How to deploy a tape library and use it with Veeam

When planning and implementing your deployment project, follow the recommendations below:

  1. It is recommended to configure the tape library for use exclusively by Veeam Backup & Replication. Using it together with any third-party tape-recording software (for example, in your evaluation lab) may prevent other software from recording.
  2. To streamline the workflow, use tapes with barcodes. Please check the integrity of the barcodes before you start using the tapes, and make sure the barcode reader is turned on. If you have multiple libraries, ensure that the barcodes are unique throughout the infrastructure.
  3. For increased capacity, use the latest LTO. Since Veeam Backup & Replication 9.5 Update 3, Veeam now supports the LTO-8 format.
  4. If you plan to use encryption for archived data, consider using hardware encryption (implemented in LTO-4 and later). Software encryption can decrease performance.
  5. Do not use hardware compression with compressed Veeam backups. Double compression will not give any benefits and can even increase the size of the file on tape.
  6. Install and check the following:
  • The latest drivers for your tape library. Remember that only the original (OEM) drivers are supported. Drivers supplied with Microsoft Windows are not recommended.
  • The latest changer and controller firmware. Changers working via SCSI are supported.

You will need a tape server that will perform most data transfer tasks during archiving to tape. Check the following prerequisites:

  • This should be a physical machine or a VM connected through iSCSI, since direct pass-through is not supported.
  • Using a Windows 2008 R2 machine for a tape server is not recommended due to the possibility of performance degradation. Instead, use Windows Server 2012 or later to achieve better performance and seamless operation.
  • Best practice is to provide a direct connection from tape server to the repository to improve the performance and specify this preferred repo in tape server connections.
  • If you plan to create synthetic backups, using a deduplication storage is not recommended.

One thing to also consider is to use GFS media pools with the tape support in Veeam. This feature allows longer-term retention to be set easily for tape backups as shown in the picture below:

veeam support

If you plan to perform file-to-tape archiving for a large number of files (more than 500,000 per job), consider using any commercial edition of SQL Server for Veeam configuration database to support these operations. Configuration database stores information about all files backed up by Veeam Backup & Replication, and using SQL Server Express edition (with its 10 GB limit for a database size) may lead to significant performance degradation. If database size reaches 10 GB, all Veeam operations will stop.

To load or get the tapes from the library, use the import-export slots. If you need to perform these operations manually, remember to stop tape jobs, stop tape server, perform manual operation, start server, rescan or run inventory for the library (to recognize the uploaded tapes) and then restart the tape job.

  • If the tapes have barcodes, then you can perform the rescan.
  • If the tapes do not have barcodes, then you should perform the inventory.

What to consider before starting the upgrade

If you are upgrading your Veeam deployment, then you should first upgrade the Veeam backup server.

The tape server will be upgraded after that, using the automated steps of the Upgrade wizard that opens after the first launch of Veeam Backup & Replication console. However, you can choose to upgrade it manually by starting the Upgrade wizard at any time from the main Veeam Backup & Replication menu.

If you are upgrading your tape library, consider the following:

  1. To streamline the process and skip the catalogue step, you can add the new library to the existing media pools, and after the old library is switched off, remove it from the media pools.
  2. After connecting the new library to Veeam server, you should load the existing tapes with their barcodes to that new library and perform the rescan. Then you can switch the old library to the offline state (detach it from Veeam server) and then delete it from Veeam Backup & Replication configuration.

What to consider when planning for tape jobs

Before you start configuring Veeam jobs for tape archiving, consider the following factors:

  1. What entities will you need to archive? Will these be files and folders, or VM backups? Do you need to archive full backups only, or both full and incremental backups?
  2. What is the estimated data size?
  3. How often will you need to archive data?
  4. What will be the retention policy for your data?
  5. How often will the tapes be changed? Will they be exported?
  6. What is the tape capacity?
  7. What tape device will be used for archiving?

After these considerations, it is recommended that you double your estimated number of tapes when planning for the resources.

Conclusion

In this blog post, we’ve talked mainly about tape infrastructure. We recognize that when setting up tape jobs, the learning curve can be quite steep. Instead of explaining all the concepts, we chose a different approach. We’ve prepared a list of settings and a well-defined result that will be achieved. You can choose to use them as they are or as a basis for your personal setup. Check out this Secondary Copy Best Practices Veeam guide for more details.


This article was provided by Veeam.com