Hot Backup of MySQL on a Linux VM by gqz18849

VIEWS: 227 PAGES: 10

									Hot Backup of MySQL on a Linux VM
From a Linux guest virtual machine



Written by:
Ricky El-Qasem
Senior Systems Engineer
Veeam Software
5 Ways VMware vSphere Improves Backup and Recovery
    Hot Backup of MySQL on a Linux VM



    CONTENTS

                EXECUTIVE SUMMARY .....................................................................................................................................3
                CONCEPT ......................................................................................................................................................................4
                    OPTION 1. SUSPEND MYSQL SERVICE ...................................................................................... 4
                    OPTION 2. USE ONLINE DUMP .................................................................................................. 4
                WALKTHROUGH.....................................................................................................................................................5
                    ASSUMPTIONS ............................................................................................................................ 5
                    SUSPEND MYSQL SERVICE......................................................................................................... 5
                    USE ONLINE DUMP .................................................................................................................... 7
                ABOUT VEEAM ..................................................................................................................................................... 10




                                                                                                                                                                                              Page | 2

             
5 Ways VMware vSphere Improves Backup and Recovery
    Hot Backup of MySQL on a Linux VM



    EXECUTIVE SUMMARY
                As more organisations move to virtualisation to facilitate core infrastructure, they
                look to the eco-system vendors to provide necessary core services, such as backup.
                Who better to help them meet those requirements than the virtualisation vendors
                themselves? This document describes the process required to take a consistent
                backup of MySQL running in a Linux virtual machine guest.
                VMware enhanced the ability to take offline snapshots for the backup of virtual
                machines with the advent of VCB (VMware Consolidated Backup) and more
                recently the vStorage API for data protection. These tools allow third-party vendors
                to make use of API calls to produce LAN-free consistent snapshots of virtual
                machines. This is achieved by providing a block list to the backup proxy allowing
                for access of the virtual machine’s disk subsystem. The block list is presented across
                a Storage Area Network (SAN)/ Network Attached Storage (NAS), Fibre channel,
                ISCSI or NAS, allowing backup I/O to occur away from the production LAN
                orientated traffic. The result of these API calls is an Agentless live image-level copy
                of the virtual disks, or an Agentless file-level mount of the virtual disks. With
                Microsoft database centric applications like Active directory, SQL and Exchange a
                transaction consistent backup of these applications is possible with the use of
                Microsoft Volume Shadow copy Services aka VSS. During backup and at the point
                the snapshot is created, backup vendors are able to interface with VSS to place
                these applications in a temporary consistent state just for the period of the
                snapshot creation. Unfortunately MySQL is not VSS aware especially in a non-
                Microsoft operating system like Linux so a new approach has to be taken to
                produce a transaction consistent backup of MySQL databases.




                                                                                                          Page | 3

             
5 Ways VMware vSphere Improves Backup and Recovery
    Hot Backup of MySQL on a Linux VM



    CONCEPT
                The approach described here entails the use of activated scripts local to the MySQL
                database. VMware has the ability to run a script to perform actions before the
                snapshot is created which is known as the pre-freeze-script and the ability to run a
                script to perform actions after the snapshot is created which is known as the post-
                thaw-script. Virtual machine snapshots are used to release the file lock on the
                virtual disk which enables backup vendors to take backups of the virtual disk even
                while the virtual machine is powered on and running. The snapshot is created very
                quickly so the time required between the pre-freeze-script running and the post-
                thaw-script running is minimal, a few minutes even a few seconds. We have 2
                options to consider to achieve database consistency:

                Option 1. Suspend MySQL service
                The MySQL service is stopped for a few seconds while the snapshot is created then
                started again and is described in Method 1.
                       Advantage: It's quick and easy and will allow you to take a transaction of
                       all databases without additional disk usage local to the MySQL server.
                       Disadvantage: For a brief period of time no databases running on the
                       MySQL server will be available which may not be suitable for applications
                       that need 100% uptime.

                Option 2. Use Online Dump
                An online dump of each database is taken using the mysqldump command which
                copies a database to storage accessible from the MySQL server and is described in
                Method 2.
                       Advantage: The dumped databases are in a transaction consistent state
                       and this is achieved without stopping the MySQL service and allows for
                       100% uptime.
                       Disadvantage: Extra storage space is required to maintain a second copy
                       of the database and process may take a considerable amount of time to
                       achieve depending on the size of your databases.




                                                                                                       Page | 4

             
5 Ways VMware vSphere Improves Backup and Recovery
    Hot Backup of MySQL on a Linux VM



    WALKTHROUGH
                Assumptions
                To find this information useful it is assumed you meet the following prerequisites:
                        You are conversant with Linux & MySQL command-line
                        You are happy with the concept of server virtualisation
                        You understand the workings of the VCB framework & the vStorage API for
                        data protection
                        A Windows version of this document will be outlined in a separate
                        document
                Also please note that the information used in this document was retrieved by
                testing the concept with the follow software:
                        Virtual Machine running on vSphere 4.0
                        The guest operating system is Ubuntu 9.10
                        Linux version of MySQL 5.1.x

                Suspend MySQL service
                When using this method we'll stop the MySQL service by executing a command in
                the pre-freeze-script and then start it again with a command in the post-thaw-
                script. 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 so the first steps is to ensure the tools are
                installed and running properly. Remember the assumption listed above and please
                bear in mind that the steps list next may differ depending on your environment but
                should apply to must situations. If you already have the VMware tools up and
                running you could jump to Part2.

                Install the VMware tools
                Install the VMware tools on Ubuntu server 9.10

                Step1
                The VMware tools installation will need a C compiler part way through installation
                so we need to pre-stage this by executing the following commands:
                sudo apt-get install build-essential
                sudo apt-get install linux-headers-`uname -r`

                Please note ` symbol surrounding `uname –r` is the symbol usually next to the 1
                key on your keyboard.




                                                                                                      Page | 5

             
5 Ways VMware vSphere Improves Backup and Recovery
    Hot Backup of MySQL on a Linux VM


                Step 2
                Next install the VMware tools on to the virtual machine by clicking "Install /Upgrade
                VMware tools" under the virtual machines Guest menu:




                Step 3
                Next we have to mount the VMware tools CDrom and copy the appropriate install
                file in the tmp folder. Then we have to expand (unzip) the install files needed. We
                achieve all this by running the following commands:
                cd /
                mount cdrom
                cp /cdrom/*.gz /tmp/
                cd /tmp
                tar xvzf VM*.gz
                cd vmware*

                Step 4
                Now we are ready to install the VMware tools. Unless you have a specific reason I
                would default all the questions by hitting the "enter " key making sure that where
                asked for a yes/no answer first that yes is typed. To start the install run the
                following command:
                sudo ./vmware-install.pl

                Creating scripts
                Creating the pre-freeze-script and post-thaw-script.

                Step 5
                Next we have to create the pre-freeze-script and post-thaw-script files as they are
                not created by default. To do this run the "touch" command in the following way:
                sudo touch /usr/sbin/pre-freeze-script
                sudo touch /usr/sbin/post-thaw-script

                Step 6
                Next we have to edit the pre-freeze-script and insert the "service mysql stop"
                command. To do this you can use your favorite Linux text editor, I use nano for
                simplicity:
                sudo nano /usr/sbin/pre-freeze-script

                From here type service mysql stop and save&exit by typing ctrl-X and
                choosing to save the file by typing Y and hitting "enter "




                                                                                                        Page | 6

             
5 Ways VMware vSphere Improves Backup and Recovery
    Hot Backup of MySQL on a Linux VM


                Step 7
                Next we have to edit the post-thaw-script and insert the "service mysql start"
                command.
                sudo nano /usr/sbin/post-thaw-script

                From here type service mysql start and save&exit by typing ctrl-X and
                choosing to save the file by typing Y and hitting "enter "

                Step 8
                At this point the scripts are not quite ready to run as one last thing step to happen
                before they can be used. The scripts are not executable as the execute bit needs
                setting on the file permissions.
                To do this run the following command on both files:
                sudo chmod 777 /usr/sbin/pre-freeze-script
                sudo chmod 777 /usr/sbin/post-thaw-script
                Note: The "chmod" command is used to set the execute bit but the parameter "777" used also
                relaxes the file security permission on these files so you may want to review this and use
                something more restrictive settings.

                Finally
                Now you are ready to perform your backup and Veeam backup will initiate the
                VMware snapshot component which will in-turn process the scripts as described
                early.

                Use Online Dump
                Using this method a pre-freeze-script is used to issue the mysqldump command to
                dump a copy of a database to disk without disrupting the MySQL service. Whilst
                this method provides a way of taking a transaction consistent backup of databases
                and 100% uptime additional steps are needed when performing a restore. A
                consistent copy of the database may need to be restored from database dump you
                create with the pre-freeze-script. The pre-freeze-script will only be able to run if you
                have the VMware tools running in your MySQL server so the first steps is to ensure
                the tools are installed and running properly. Remember the assumption listed
                above and please bear in mind that the steps list next may differ depending on
                your environment but should apply to must situations. If you already have the
                VMware tools up and running you could jump to Part2.

                Install the VMware tools
                Install the VMware tools on Ubuntu server 9.10

                Step1
                The VMware tools installation will need a C compiler part way through installation
                so we need to pre-stage this by executing the following commands:
                sudo apt-get install build-essential
                sudo apt-get install linux-headers-`uname -r`

                Please note ` symbol surrounding `uname –r` is the symbol usually next to the 1
                key on your keyboard.

                                                                                                             Page | 7

             
5 Ways VMware vSphere Improves Backup and Recovery
    Hot Backup of MySQL on a Linux VM


                Step 2
                Next install the VMware tools on to the virtual machine by clicking "Install /Upgrade
                VMware tools" under the virtual machines Guest menu:




                Step 3
                Next we have to mount the VMware tools CDrom and copy the appropriate install
                file in the tmp folder. Then we have to expand (unzip) the install files needed. We
                achieve all this by running the following commands:
                cd /
                mount cdrom
                cp /cdrom/*.gz /tmp/
                cd /tmp
                tar xvzf VM*.gz
                cd vmware*

                Step 4
                Now we are ready to install the VMware tools. Unless you have a specific reason I
                would default all the questions by hitting the "enter " key making sure that where
                asked for a yes/no answer first that yes is typed. To start the install run the
                following command:
                sudo ./vmware-install.pl

                Creating scripts
                Creating the pre-freeze-script and post-thaw-script.

                Step 5
                Next we have to create the pre-freeze-script and post-thaw-script files as they are
                not created by default. To do this run the touch command in the following way:
                sudo touch /usr/sbin/pre-freeze-script
                sudo touch /usr/sbin/post-thaw-script




                                                                                                        Page | 8

             
5 Ways VMware vSphere Improves Backup and Recovery
    Hot Backup of MySQL on a Linux VM


                Step 6
                Next we have to edit the pre-freeze-script and insert the "service mysql stop"
                command. To do this you can use your favorite Linux text editor, I use nano for
                simplicity:
                sudo nano /usr/sbin/pre-freeze-script

                From here type sudo mysqldump –uroot –pveeam –h localhost test >
                test.sql and save&exit by typing ctrl-X and choosing to save the file by typing Y
                and hitting "enter "
                Note: I used the word "test" to represent the name of my database whilst –uroot is the user
                prompt and -pveeam is the password. The test.sql is the dump of the database so you could use
                an appropriate path and file name.

                Step 7
                At this point the scripts are not quite ready to run as one last step needs to happen
                before they can be used. The scripts are not executable as the execute bit needs
                setting on the file permissions.
                To do this run the following command on both files:
                sudo chmod 777 /usr/sbin/pre-freeze-script
                sudo chmod 777 /usr/sbin/post-thaw-script
                Note: The "chmod" command is used to set the execute bit but the parameter "777" used also
                relaxes the file security permission on these files so you may want to review this and use
                something more restrictive settings.

                Finally
                Now you are ready to perform your backup and Veeam backup will initiate the
                VMware snapshot component which will in-turn process the scripts as described
                early.




                                                                                                                Page | 9

             
5 Ways VMware vSphere Improves Backup and Recovery
    Hot Backup of MySQL on a Linux VM



    ABOUT VEEAM
                Veeam Software, a VMware Technology Alliance Premier Partner, provides innova-
                tive software for managing VMware infrastructure. Veeam offers an award winning
                suite of tools to assist the VMware administrator, including Veeam Backup, the
                2 in 1 backup and replication solution; Veeam Reporter Enterprise, to document
                virtual environments for capacity planning and chargeback; Veeam Configurator,
                offering Host Profiles today; and Veeam Monitor, for performance monitoring and
                alerting across multiple VirtualCenters. With its acquisition of nworks, Veeam’s
                products include connectors that incorporate VMware data into Microsoft System
                Center Operations Manager and HP Software Operations Manager. Learn more
                about Veeam Software by visiting www.veeam.com.




                                                                                                   Page | 10

             

								
To top