mysql linux

8/3/2019 Mysql Linux

1/14

Guidelines for using MySQL

with Double-Take for Linux

Double-Take, GeoCluster, Double-Take for Virtual Systems, and NSI are registered trademarks of Double-Take Software, Inc. Balance andDouble-Take ShadowCaster are trademarks of Double-Take Software, Inc. TimeData is a registered trademark of Double-Take Software Cannc. TimeData logo is a trademark of Double-Take Software Canada, Inc. Linux is the registered trademark of Linus Torvalds in the U.S. ather countries. Red Hat and Red Hat Enterprise Linux are registered trademarks of Red Hat, Inc. in the United States and other countries

MySQL is a registered trademark of MySQL AB in the United States, the European Union and other countries. Microsoft, Windows, and theWindows logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. VMware is aegistered trademark or trademark of VMware, Inc. in the United States and/or other jurisdictions. All other trademarks are the property of espective companies.

2008 Double-Take Software, Inc. All rights reserved.Revision 1.0.0 published June 2008


2/14

Double-Take Support for Application Failover

The Double-Take file system replication process is application independent and replicates any file system changes (includingpermissions and attributes) written to NTFS, FAT or FAT32 file systems by any application or process, subject to specificexceptions called out in the Users Guide or readme file. Maintaining point-in-time consistent file system replicas and providingserver monitoring and automatic or manual failover of the server name and IP address are the primary functions ofDouble-Take, and Double-Take Software offers support to qualified customers should these functions fail to operate inaccordance with our published documentation, regardless of what application or process is manipulating the data.

Double-Take Software may provide application notes and other documents that provide implementation guidelines on how touse Double-Take functions and replicas to manually or automatically failover or recover many popular third-party applicationsand a general process to accomplish failover or recovery of many other third-party applications. While these steps are believed

to be accurate for the specific configuration, Double-Take version, and application versions originally tested, due to the numberof possible configurations and variables, Double-Take Software can only test selected combinations and may provide onlylimited support for the operation and configuration of third-party applications or the behavior of those applications before,during, or after failover, in its discretion. In cases where Double-Take Software has no direct access to or experience with aparticular application or configuration, Double-Take Software support may also be limited to only the actual replication of thefile system data and failover (name and IP address) of the server.

For assistance in validating, implementing or troubleshooting these or other possible configurations with third-partyapplications, Double-Take Software and its partners may offer professional services on a fee basis to apply best practices forassisting with third-party applications to recover automatically or manually using replicated data. This, and any other,application note is provided solely for the convenience of our customers and is not intended to bind Double-Take Software toany obligation. Although we try to provide quality information, Double-Take Software makes no claims, promises or guaranteesabout the accuracy, completeness, or adequacy of the information contained in this document.


3/14

Table of Contents

Document Overview ............................................................................ 1Audience ................................................................................................................ 1Expectations ........................................................................................................... 1

Related documentation ............................................................................................. 1Getting help ............................................................................................................ 2

Solution Overview ............................................................................... 3Configuration .......................................................................................................... 3Requirements .......................................................................................................... 4Permissions ............................................................................................................ 4Modifying the sample script files ................................................................................ 4Configuring the replication set ................................................................................... 4Managing MySQL services ......................................................................................... 5

Sample Implementation ...................................................................... 6Install software on the source .................................................................................... 6Install and configure software on the target ................................................................ 6

Configure and begin mirroring and replication .............................................................. 6Configure failover and begin failure monitoring ............................................................ 7Restore your data .................................................................................................. 10


4/14

1

Document OverviewThis document is a Double-Take application note. An application note provides guidelines on the use

of Double-Take in a specific environment.

This document contains:

Document OverviewExplains what an application note contains, how it should be used, whatyou need to know before trying to use the application note, and where you can go for moreinformation.

Solution OverviewExplains how the application works with Double-Take and describes theconsiderations that you must weigh when implementing your Double-Take solution. Review thissection to make sure that you understand the theory involved with using Double-Take and yourapplication. Includes both basics, such as system requirements, as well as configuration andenvironment-specific topics, such as interactions with specific clients or special considerations forWAN (Wide Area Network) environments. Pay special attention to those topics that are directlyrelated to your environment.

Sample ImplementationDescribes a specific example of how to use Double-Take for thissolution. Use these procedures as a guideline for creating your own implementation.

AudienceThis document is written for network and application administrators who have a workingunderstanding of the applications and environments where the Double-Take solution is to bedeployed. You may need to expand on the documented information in order to customize the solutionto fit your environment.

Before you use this application note, you should have an understanding of:

Double-Take

MySQL

ExpectationsApplication notes are intended to provide a framework for configuring a Double-Take solution in aspecific environment and to draw attention to decisions you will need to make when configuring yoursolution.

Because there are an infinite number of possible configuration, network, and environment scenarios,application notes contain general configuration guidelines as well as an example configurationprocedure that has been tested for a specific environment.

This document assumes that you are comfortable working with your operating system, Double-Take,and MySQL.

Related documentationBefore you begin to configure your solution, make sure that you have complete documentation foryour operating system, application, and Double-Take. This application note does not providestep-by-step instructions for using standard operating system, application, and Double-Takefunctionality.

The following documents contain additional information that you may need while setting up thissolution:

Double-Take Users Guide or online documentation

Reference guides or documentation for your version of MySQL

NOTE: Because no two environments or configurations are exactly the same, you will probablyneed to implement additional or different steps than what is documented here in order tomake the solution work in your environment.


5/14

2

Getting helpDouble-Take Software has application notes that describe how to configure Double-Take with avariety of popular third-party applications. These application notes are available on the ApplicationNotes page of the Double-Take Software support web site (http://support.doubletake.com).

Double-Take Software offers professional services on a fee basis to assist you in identifying the bestpractices for implementing a solution in your environment. Visithttp://www.doubletake.com/what-we-offer/services/ for more information.


6/14

3

Solution OverviewMySQL is a scalable, reliable, flexible, and high-performance relational database management systemfor many server-based operating systems.

Double-Take provides real-time enterprise data protection and replication. Double-Take can be usedto provide high availability for MySQL.

This document describes the steps necessary to configure Double-Take to provide high availability for

Linux servers running MySQL. These procedures allow a secondary server to assume the identity

and role of a failed primary MySQL server while maintaining the availability of MySQL services withminimal disruption or data loss.

To complete these instructions, you will install MySQL and Double-Take, and configure Double-Takefor replication and failover.

ConfigurationIn this solution, a single server is configured to act as the primary MySQL server, and a second serveris configured to act as the backup target server. Double-Take is used to replicate and failover the

MySQL server database to a Double-Take target machine. In the event of a source failure, the clientsare directed to the target server.

NOTE: Due to the complexities of these applications, this document is intended for networkadministrators with experience installing, configuring, and maintaining networkapplications including Double-Take and MySQL.

NOTE: Double-Take allows you to configure one target to monitor and failover for one or moresource machines. In a one-to-one configuration, you will want to replicate your MySQLdata to the same location on the target so that failover is automatic. In a many-to-oneconfiguration, the data will need to be replicated to a unique location and then renamedto the corresponding MySQL directory on the source before failover occurs.

This application note focuses on a single MySQL server being replicated to a single target.

Source Name: Indy

Failed Source (Offline)

Target Name: Jersey and Indy

Target

Target IP: 125.7.82.67 and 125.7.82.43Original source IP: 125.7.82.43

User and application requests are sent tothe target by locating the sources original

IP address or hostname

MySQLDouble-Take

MySQLDouble-Take

MySQL client


7/14


8/14

5

Managing MySQL servicesYou will need to start any MySQL services when failover is initiated, and stop them on the targetserver prior to initiating failback to the source.

MySQL_

The MySQL service can be scripted to start and stop at the appropriate times by using failover andfailback scripts. Because no two environments are exactly the same, you will need to make sure thatyou are addressing all services relevant for your environment.

Ib_logfile# /var/lib/mysql/

Ibdata# /var/lib/mysql/

NOTE: The Listener service will not start on the target ifmysql.sock is replicated from the

source, but the target is not in a failed over state. This file contains configurationinformation that is not valid on the target unless it is in a failed over state. If you needto start MySQL on the target server without failing over, do not include mysql.sock inthe replication set.

File Name Default Location


9/14

6

Sample ImplementationThis section describes an example of how to configure Double-Take and MySQL. Use these proceduresas a guideline for creating your own implementation.

Because no two environments or configurations are exactly the same, you will probably need toimplement additional or different steps than what is documented here in order to make the solutionwork in your environment.

Install software on the source1. Install MySQL on the source, if it is not already installed.

2. Install Double-Take on the source using the installation defaults. See the Double-Take GettingStarted guide for details.

Install and configure software on the target1. Install MySQL on the target. The MySQL installation must be identical on the source and target.

2. Stop the MySQL service.

a. In the application bar, select Appl ications, System Settings, Server Settings, Services .

b. Select the Mysql service, then click Stop.

3. Set the Mysql service to manual startup so that the failover and failback scripts that you will becreating will control the stopping and starting of the Mysql service. Make sure to set theservices on the target to manual startup.

Create an /etc/init.d/mysqld script to start and stop the mysql service manually. An exampleinit.d script is included in Con f igu r e f a i l ove r and beg in f a i l u r e m on i t o r i ng . Once configuredproperly in a Linux server, the Mysql service can be started or stopped by executing thecommand.

# /etc/init.d/mysqld start / stop

4. The command /etc/init.d/mysqld start/stop can be used inside a post-failover andpre-failback script to start and stop Mysql services on the target server.

5. Install Double-Take on the target server using the installation defaults. See the Double-TakeGetting Started guide for details.

Configure and begin mirroring and replication1. From the machine where the Double-Take Management Console is installed, select Start,

Programs, Double-Take, Linux Management Console.

2. Double-click your source machine to log on.

3. Right-click your source machine and select New , Replication Set and enter the desired namefor the replication set.

4. Select the MySQL data you wish to protect. For more information about what you should includein your replication set, see Conf igu r ing th e rep l ica t ion se t on page 4.

5. Right-click the replication set name and select Save to save the replication set.

6. Right-click on the source and select Properties .

7. On the Source tab, enable Block Checksum All Files on a Difference Mirror and click OK .8. Drag and drop the replication set onto the target. The Connection Manager dialog box opens.

9. The Source Server, Target Server, Replication Set, and Route fields will automatically bepopulated. If you have multiple IP addresses on your target, verify the Route field is set to thecorrect network path. (For detailed information on connecting a source and target, see theDouble-Take User's Guide.)

10 . Select One to One to map the replication set data from the source to an identicalvolume/directory structure on the target.


10/14

7

11 . Click Connect to start the mirror and replication processes.

Configure failover and begin failure monitoring1. If a failure occurs, you will want to have the MySQL services start on the target machine

automatically. To do this, create a shell script file called POST_FAILOVER.SH and save the fileto the /usr/bin/ directory where the Double-Take commands are installed. ThePOST_FAILOVER.SH script should include the following contents

********************************************************

#!/bin/bash

/etc/init.d/mysqld start

********************************************************

2. The following example shows the /etc/init.d/mysqld script and SQL script to start and stopMySQL services.

NOTE: If you start MySQL and mount the replicated databases on the target, or if the data onthe target is otherwise modified, the data on the source and target will no longer match.If the updated data on the target is not needed, perform a full or difference with blockchecksum mirror from the source to the target. If the updated data on the target isneeded, restore the data from the target to the source.


11/14

8

Sample_Init.d

############***** BEGINING OF INIT.D SCRIPT FOR MySQL SERVICE ***** ##############!/bin/bash## mysqld This shell script takes care of starting and stopping# the MySQL subsystem (mysqld).## chkconfig: - 64 36# description:MySQL database server.# processname: mysqld

# config: /etc/my.cnf# pidfile: /var/run/mysqld/mysqld.pid

# Source function library.. /etc/rc.d/init.d/functions

# Source networking configuration.. /etc/sysconfig/network

prog="MySQL"

# extract value of a MySQL option from /etc/my.cnf# Usage: get_mysql_option FILE VARNAME DEFAULT# result is returned in $resultget_mysql_option(){

result=`sed -n "s/^[ \t]*$2[ \t]*=[ \t]*//p" "$1" 2>/dev/null | tail -n 1ìf [ -z "$result" ]; then

# not found, use defaultresult="$3"

else# found, still have to deal with quoting and end-of-line commentsdequoted=ècho "$result" | sed "s/^'$[^']*$'.*$/\1/"ìf [ x"$dequoted" != x"$result" ]; then

result="$dequoted"else

dequoted=ècho "$result" | sed 's/^"$[^"]*$".*$/\1/'ìf [ x"$dequoted" != x"$result" ]; then

result="$dequoted"else

result=ècho "$result" | sed 's/^$[^ \t#]*$.*$/\1/'`fi

fifi

}

get_mysql_option /etc/my.cnf datadir "/var/lib/mysql"datadir="$result"get_mysql_option /etc/my.cnf socket "$datadir/mysql.sock"socketfile="$result"get_mysql_option /etc/my.cnf err-log "/var/log/mysqld.log"errlogfile="$result"get_mysql_option /etc/my.cnf pid-file "/var/run/mysqld/mysqld.pid"mypidfile="$result"

start(){touch "$errlogfile"chown mysql:mysql "$errlogfile"chmod 0640 "$errlogfile"[ -x /sbin/restorecon ] && /sbin/restorecon "$errlogfile"if [ ! -d "$datadir/mysql" ] ; then

action $"Initializing MySQL database: " /usr/bin/mysql_install_dbret=$?chown -R mysql:mysql "$datadir"if [ $ret -ne 0 ] ; then

return $retfi

fichown -R mysql:mysql "$datadir"

chmod 0755 "$datadir"# The reason for explicitly specifying --pid-file is that there may# be no such entry in my.cnf, and the default behavior will be to not# create it at all.../usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file="$mypidfile" >/dev/null 2>&1 &ret=$?# Spin for a maximum of N seconds waiting for the server to come up.# Rather than assuming we know a valid username, accept an "access# denied" response as meaning the server is functioning.if [ $ret -eq 0 ]; then

STARTTIMEOUT=30while [ $STARTTIMEOUT -gt 0 ]; do

RESPONSE=`/usr/bin/mysqladmin -uUNKNOWN_MYSQL_USER ping 2>&1` && break


12/14

9

echo "$RESPONSE" | grep -q "Access denied for user" && breaksleep 1let STARTTIMEOUT=${STARTTIMEOUT}-1

doneif [ $STARTTIMEOUT -eq 0 ]; then

echo "Timeout error occurred trying to start MySQL Daemon."action $"Starting $prog: " /bin/false

elseaction $"Starting $prog: " /bin/true

fielse

action $"Starting $prog: " /bin/falsefi[ $ret -eq 0 ] && touch /var/lock/subsys/mysqldreturn $ret

}

stop(){MYSQLPID=`cat "$mypidfile" 2>/dev/null `if [ -n "$MYSQLPID" ]; then

/bin/kill "$MYSQLPID" >/dev/null 2>&1ret=$?if [ $ret -eq 0 ]; then

STOPTIMEOUT=60while [ $STOPTIMEOUT -gt 0 ]; do

/bin/kill -0 "$MYSQLPID" >/dev/null 2>&1 || breaksleep 1let STOPTIMEOUT=${STOPTIMEOUT}-1

doneif [ $STOPTIMEOUT -eq 0 ]; then

echo "Timeout error occurred trying to stop MySQL Daemon."ret=1action $"Stopping $prog: " /bin/false

elserm -f /var/lock/subsys/mysqldrm -f "$socketfile"action $"Stopping $prog: " /bin/true

fielse

action $"Stopping $prog: " /bin/falsefi

elseret=1action $"Stopping $prog: " /bin/false

fireturn $ret

}restart(){

stopstart

}

condrestart(){[ -e /var/lock/subsys/mysqld ] && restart || :

}

# See how we were called.case "$1" instart)start;;

stop)stop;;

status)status mysqld;;

restart)

restart;;

condrestart)condrestart;;

*)echo $"Usage: $0 {start|stop|status|condrestart|restart}"exit 1

esac

exit $?

############# ***** END OF INIT.D SCRIPT FOR MYSQL SERVICE ***** #############


13/14

10

3. After a failure is resolved, you will be ready to bring your source back online. At this time, youwill want to stop the MySQL services on the target automatically. To do this, create a shell scriptfile called PREFAILBACK.SH and save the script file to the /usr/bin/ directory where theDouble-Take commands are installed. The PREFAILBACK.SH script should include the following:

******************************************************************

#!/bin/bash

/etc/init.d/mysqld stop

******************************************************************

4. Select Start, Programs, Double-Take, Failover Control Center.

5. Select the target machine from the list of available machines. If the target you need is notdisplayed, click Add Target, enter the machine name, and click OK .

6. To add a monitor for the selected target, click Add Monitor. Type the name of the sourcemachine and click OK . The Monitor Settings window will open.

7. In the Monitor Settings window, mark the IP address that is going to failover.

8. Click Scripts and specify the scripts that were created previously, using POST_FAILOVER.SH for thetarget post-failover script and PREFAILBACK.SH for the target pre-failback script.

9. Click OK to go back to the Monitor Settings dialog box.

10 . Click OK to begin monitoring the source machine.

In the event of a source machine failure, your target machine is now ready to stand in for your source.For details on monitoring failover, see the Double-Take Users Guide.

Restore your dataIf your source experiences a failure, such as a power, network, or disk failure, your target machinewill stand in for the source while you resolve the source machine issues. During the source machinedowntime, data is updated on the target machine. When your source machine is ready to come backonline, the data is no longer current and must be updated with the new data on the target machine.

1. Verify that your source machine is not connected to the network. If it is, disconnect it.

2. Resolve the source machine problem that caused the failure.

3. Install your operating system. Since your source machine is not connected to the network, goahead and use the sources original name and IP address.

4. Install Double-Take using the installation defaults.

5. Install MySQL using the same installation settings as you did originally.

6. On the source, stop the MySQL service so that all MySQL files are closed on the source and theDouble-Take target can restore the changes back to the source. Depending on the type of failure,your services may be set to manual startup but could still be running. Stop any SQL servicesthat are running.

7. On the target, select Start, Programs, Double-Take, Failover Control Center.

*********************************************************************************************************NOTE :--- Copy this script to the /etc/init.d/ directory of the linux server, and name it as "mysqld".

chkconfig mysql on

Please note that you can just use script directly, To start Mysql type command:$ /etc/init.d/mysqld startTo stop Mysql type command:$ /etc/init.d/mysqld stop

NOTE: The scripts are processed using the same account running the Double-Take service.

NOTE: If you must rebuild your hard drive, continue with step 3.

If you do not need to rebuild your hard drive, verify that the Double-Take connectionon the source has been disconnected (right-click the connection in the Double-TakeManagement Console and select Disconnect) and then continue with step 6.


14/14

8. Select the target machine that is currently standing in for the failed source.

9. Select the failed source and click Failback. The pre-failback script entered during the failoverconfiguration stops the MySQL services on the target so that no additional changes can be made.

10 . You will be prompted to determine if you want to continue monitoring the source server. Do notchoose Continue or Stop at this time.

11 . Stop the MySQL service on the source.

12 . Delete the .db and .sts files from the /var/lib/DT directory.

13 . Restart the Double-Take service (#service DT restart).14 . Connect the source machine to the network.

15 . After the source is back online, select whether or not you want to continue monitoring thissource machine (Continue or Stop).

16 . Since the replication sets were deleted, you will need to re-create those replication sets.

17 . To begin the restoration process, open the Double-Take Management Console and select Tools,Restoration Manager.

18 . Complete the appropriate fields as described below.

Original SourceThe name of the source machine where the data originally resided.

Restore FromThe name of the target machine that contains the replicated data.

Replication SetThe name of the replication set to be restored.

Restore ToThe name of the machine where the data will be restored. This may or may notbe the same as the original source machine.

19 . Identify the correct drive mappings for the data and any other restoration options necessary. Fordetailed information on the restoration options, see the Double-Take Users Guide.

20 . Verify that the selections you have made are correct and click Restore. The restorationprocedure time will vary depending on the amount of data that you have to restore.

21 . After the restoration is complete, start the MySQL services on the source machine.

22 . Reestablish the Double-Take MySQL replication set connection.

At this time, your data is restored back to your source machine, the source machine is again theprimary MySQL server, and, if you selected to continue failover monitoring, the target is available tostand in for the source in the event of a failure.

NOTE: Verify that the Double-Take connection on the source has been disconnected. If it isnot, right-click the connection in the Double-Take Management Console and selectDisconnect.

NOTE: You can also run the Double-Take DTCL automated restoration script, which can befound in the Double-Take Users Guide, to complete the remaining steps in thissection.

mysql linux

Documents