The only problem I face with this set up, though, is that I have multiple production servers out there. So this only works if this backup server could be a slave for multiple machines.
This is not possible, though, because, of course, no slave can serve two masters. Fortunately, a server can have multiple instances of MySQL running on it! So, in a sense, we have a server with multiple MySQL instances, to which a master can replicate. More about that set up in an upcoming post.
A how to on this blog, shows how this can be done. I’ll replicate the process below.
We’ll be working with CentOS 5.8, but this could really apply for any OS. First we’ll need to install MySQL like normal.
yum install mysql mysql-server
There are plenty of good tutorials out there on how to install the specific version of MySQL you want on the specific OS you’re running.
You’ll need to have a different folder for each of the MySQL instances, say /dbases/master-a/
, /dbases/master-b/
, and /dbases/master-c/
.
mkdir -p /dbases/{master-a,master-b,master-c}
This is the default MySQL config file, it may be named differently on other OSes.
cp /etc/my.cnf /etc/master-a.cnf; cp /etc/my.cnf /etc/master-b.cnf; cp /etc/my.cnf /etc/master-c.cnf
For each new config file, you’ll need to specify some unique variables.
[mysqld] port=3307 datadir=/dbases/master-a socket=/dbases/master-a/mysql.sock user=mysql server_id=3307 log-bin=/dbases/master/mysql-bin.log # Disabling symbolic-links is recommended to prevent assorted security risks; # to do so, uncomment this line: symbolic-links=0 [mysqld_safe] log-error=/dbases/master-a/mysqld.log pid-file=/dbases/master-a/mysqld.pid
The port
option sets this MySQL instance on a different port than the default 3306. The datadir
, socket
, log-bin
, log-error
, and pid-file
options make sure the necessary files are not using the default files.
The init script allows the server to start and stop the service at boot time, and allows for easy start up and shutdown (on CentOS/RedHat, at least – with an easy service mysqld start
).
cp /etc/init.d/mysqld /etc/init.d/mysqld-master-a
Just do one for now. We’ll copy the new one to create the others, then just do a quick search and replace in those files to change the master-a to master-b and master-c.
#!/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/master-a.cnf # pidfile: /dbases/master-a/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 config files # Usage: get_mysql_option SECTION VARNAME DEFAULT # result is returned in $result # We use my_print_defaults which prints all options from multiple files, # with the more specific ones later; hence take the last match. get_mysql_option(){ result=/usr/bin/my_print_defaults "$1" | sed -n "s/^--$2=//p" | tail -n 1
if [ -z "$result" ]; then # not found, use default result="$3" fi } get_mysql_option mysqld datadir "/dbases/master-a" datadir="/dbases/master-a" get_mysql_option mysqld socket "/dbases/master-a/mysql.sock" socketfile="/dbases/master-a/mysql.sock" get_mysql_option mysqld_safe log-error "/dbases/master-a/mysqld.log" errlogfile="/dbases/master-a/mysqld.log" get_mysql_option mysqld_safe pid-file "/dbases/master-a/mysqld.pid" mypidfile="/dbases/master-a/mysqld.pid" defaultfile="/etc/master-a.cnf" 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_db --datadir="$datadir" --user=mysql ret=$? chown -R mysql:mysql "$datadir" if [ $ret -ne 0 ] ; then return $ret fi fi chown mysql:mysql "$datadir" chmod 0755 "$datadir" # Pass all the options determined above, to ensure consistent behavior. # In many cases mysqld_safe would arrive at the same conclusions anyway # but we need to be sure. /usr/bin/mysqld_safe --defaults-file="$defaultfile" --datadir="$datadir" --socket="$socketfile" \ --log-error="$errlogfile" --pid-file="$mypidfile" \ --user=mysql >/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=30 while [ $STARTTIMEOUT -gt 0 ]; do RESPONSE=/usr/bin/mysqladmin --socket="$socketfile" --user=UNKNOWN_MYSQL_USER ping 2>&1
&& break echo "$RESPONSE" | grep -q "Access denied for user" && break sleep 1 let STARTTIMEOUT=${STARTTIMEOUT}-1 done if [ $STARTTIMEOUT -eq 0 ]; then echo "Timeout error occurred trying to start MySQL Daemon." action $"Starting $prog: " /bin/false ret=1 else action $"Starting $prog: " /bin/true fi else action $"Starting $prog: " /bin/false fi [ $ret -eq 0 ] && touch /dbases/master-a/mysqld return $ret } stop(){ MYSQLPID=cat "$mypidfile" 2>/dev/null
if [ -n "$MYSQLPID" ]; then /bin/kill "$MYSQLPID" >/dev/null 2>&1 ret=$? if [ $ret -eq 0 ]; then STOPTIMEOUT=60 while [ $STOPTIMEOUT -gt 0 ]; do /bin/kill -0 "$MYSQLPID" >/dev/null 2>&1 || break sleep 1 let STOPTIMEOUT=${STOPTIMEOUT}-1 done if [ $STOPTIMEOUT -eq 0 ]; then echo "Timeout error occurred trying to stop MySQL Daemon." ret=1 action $"Stopping $prog: " /bin/false else rm -f /dbases/master-a/mysqld rm -f "$socketfile" action $"Stopping $prog: " /bin/true fi else action $"Stopping $prog: " /bin/false fi else ret=1 action $"Stopping $prog: " /bin/false fi return $ret } restart(){ stop start } condrestart(){ [ -e /dbases/master-a/mysqld ] && restart || : } # See how we were called. case "$1" in start) start ;; stop) stop ;; status) status mysqld ;; restart) restart ;; condrestart) condrestart ;; *) echo $"Usage: $0 {start|stop|status|condrestart|restart}" exit 1 esac exit $?
Now you can start each instance with the handy service
command.
service mysqld-master-a start
Now, to connect to each MySQL instance, you’ll need to specify the port and/or socket file.
mysql -P3307 --socket="/dbases/mysql-master-a/mysql.sock"]]>
If you have a live production MySQL server, stopping it to make a backup is not really an option. Fortunately there are a few options. Before you decide on which option to choose, here is a list of things to keep in mind when choosing a backup solution (from the MySQL gurus at Percona):
There are a few MySQL backup products out there as well. I have used the first two on this list.
There’s probably a gazillion more out there. Google’s your friend in finding things you need.
There are several options. You could use a script above, or create a slave of the database (basically an exact copy of the production MySQL server – all changes that occur in the master are sent to the slave), or some combination. I’ll use a combination. I’ll replicate the production server onto the backup server, then run the incremental backups from there. This first part will walk through the process of setting up MySQL replication.
To give proper credit, here are several other how to’s I found helpful.
Step 1. Edit the my.cnf file to include at least the following (if needed) lines. Note: you will have to restart MySQL for these changes to take affect.
[mysqld] server_id=1 innodb_flush_log_at_trx_commit=1 log_bin=mysql-bin.log sync_binlog=1
Step 2. Make a MySQL user for the slave to use.
GRANT REPLICATION SLAVE ON *.* TO 'rep_user'@'localhost' IDENTIFIED BY 'passwordhere';
Step 3. Open a terminal session and log in to a MySQL prompt. Type the following command and hit enter.
FLUSH TABLES WITH READ LOCK
Note: This will lock your database so that no changes can be made from any web applications or other programs. This session should remain open, and the database locked for the next few steps.
Step 4. After the FLUSH TABLES command finishes, run the following command and press enter.
SHOW MASTER STATUS
Record the information under “File Name” and “Position”.
Step 5. Make a copy of the database files.
5.1 LVM Snapshot:
In another terminal session, run the following command to make an LVM snapshot of the database.
lvcreate -L10G -s -n mysql-backup /dev/mapper/dbases
This creates a snapshot of the database files very quickly. We can use the snapshot later to copy the data to the backup server without interfering with the original database files.
After this command finishes, you can unlock the database as shown in the next step. Then you can mount the new LVM partition and copy the files to the backup server.
mkdir -p /mnt/mysql-backup
mount -o nouuid /dev/mapper/mysql-backup /mnt/mysql-backup
rsync -avz -e "ssh -c blowfish" /mnt/mysql-backup [email protected]:/backup/location
5.2 RSYNC:
If you don’t have your database files on an LVM partition, you can just copy the files to the backup server now using rsync, scp or what have you. This will take significantly longer (depending on the size of your database), leaving the database in a locked state.
rsync -avz -e "ssh -c blowfish" /dbases/mysql [email protected]:/backup/location
5.3 MySQL Dump:
You could also take a mysqldump of the database and copy that SQL file to the other server.
mysqldump -uuser -p --all-databases > mysql-backup.sql
scp mysql-backup.sql [email protected]:/backup/location
Step 6. Once the lvcreate command has finished, you can unlock the database.
UNLOCK TABLES
Step 7. If you haven’t already, copy the copy of the database files to the backup server.
Step 1. Edit the my.cnf file to include at least the following (if needed) lines. Note: you will have to restart MySQL for these changes to take affect.
[mysqld] server_id=2
Step 2. Start MySQL and run the following commands in a mysql session to start the MySQL slave.
CHANGE MASTER TO MASTER_HOST = "master.server.com", MASTER_USER = "rep_user", MASTER_PASSWORD = "passwordhere", MASTER_LOG_FILE = "mysql-bin.log", MASTER_LOG_POS = 2341234;
The MASTER_HOST is the domain name or IP address of the master server. MASTER_USER, MASTER_PASSWORD were created on the master server in Step 2. MASTER_LOG_FILE and MASTER_LOG_POS were gathered in Step 4.Then, finally, to start the slave, issue the following command in mysql.
START SLAVE;