Игорь Олемской — практические заметки по системному администрированию Linux CentOS

MongoDB Backup types and strategies are neatly explained in its documentation, which you can check here. In case you are not familiar with MongoDB backup types and strategies, please have a look at its documentation.

What I am describing here is a simple script which we are using since months to take MongoDB backup and transfer it over to our Backup server. Here are few things its doing:

• As we have multiple MongoDB Replica Sets, the script identify current replica set and check whether current server is Master or Slave, exit if its Master. We take backup only from Slave host.
• Take Backup using mongodump command.
• Upon successful completion of dump, transfer that to our Backup server. Ensure that ssh key based authentication is setup between both servers to implement seamless and secure transfer. It creates new directory based on current timestamp under replicaset directory in specified path at Backup server and transfer dump there.
• Log each steps described above, send alert mail if any step fail with description or send confirmation mail upon successful execution with essential details.
• Sample confirmation mail is below:

Subject: Backup for  done on  at 09/May/12 08:00:01 AM
Body: Mongo Backup Status for  on 09/May/12 08:00:01 AM.
 
08:00:01 AM:  is slave and looks OK.
08:00:01 AM: Starting Dump, executing /usr/local/mongodb/mongo/bin/mongodump --out /databases/dump --host ...
08:34:06 AM: Mongodump command completed. Backup size is 25G.
08:34:10 AM: Directory created on backup server, copying data using scp...
08:57:00 AM: Copied dump to backup server in directory /home/backup/Mongodump/replicaset/datestamp/.
08:57:00 AM: Mongo Backup process completed successfully.

Here is the full bash script, please note that you need to update variables properly and have to check and update it to run in your environment which can be entirely different from mine. Its just an idea to automate MongoDB backup:

##
# Script to take mongo backup using mongodump and store it in Backup Server
##
 
#!/bin/bash
 
## Set variables
TodayDate=`date +"%d/%b/%g %r"`
DateStamp=`date +%d%m%y%H%M%S`
CurrentTime=`date +"%r"`
 
MongoBinPath="/usr/local/mongodb/mongo/bin"
ReplicaSet=`echo 'rs.status()' | $MongoBinPath/mongo | egrep "set" | awk -F \" '{print $4}'`
MongoHost=`hostname`
LocalBackupPath="/databases/dump"
 
LogFile="/var/log/mongo-backup.log"
IsOK=0
CmdStatus=""
 
BackupHost=xx.xx.xx.xx
BackupHostPath="/home/backup/mongobackup"
BackupHostPort=22
 
MailNotification="admin@domain.com anotheradmin@domain.com"
 
echo -e "Mongo Backup Status for $ReplicaSet on $TodayDate. \n" > $LogFile
 
## Check whether host is slave and in good state for backup
for i in `echo "rs.status()" | $MongoBinPath/mongo | egrep "name" | awk -F \" '{print $4}'| cut -f 1 -d :`; do
 IsMaster=`echo "db.isMaster()"| $MongoBinPath/mongo --host $i | grep ismaster|awk -F ":" '{print $2}' | cut -f 1 -d ,`;
 TheState=`echo "rs.status()"| $MongoBinPath/mongo --host $i | grep -i mystate | awk -F ":" '{print $2}' | cut -f 1 -d ,`;
 if [ $IsMaster == "false" -a $TheState -eq 2  ]; then
  MongoHost=$i
  IsOK=1
  echo "$CurrentTime: $MongoHost is slave and looks OK." >> $LogFile
  break
 fi
done
 
CurrentTime=`date +"%r"`
 
## Exit if not good
if [ $IsOK -eq 0 ]; then
   echo "$CurrentTime: Error: Either $MongoHost is not slave or not in good state. Aborting Backup, Please check!" >> $LogFile
   mail -s "Backup error for $ReplicaSet from $MongoHost on $TodayDate" $MailNotification < $LogFile    exit 1; fi ## Remove earlier backup CmdStatus=$(rm -rf $LocalBackupPath/*) ## Start backup process echo "$CurrentTime: Starting Dump, executing $MongoBinPath/mongodump --out $LocalBackupPath --host $MongoHost..." >> $LogFile
 
CmdStatus=`$MongoBinPath/mongodump --out $LocalBackupPath --host $MongoHost`
if [ $? -ne 0 ]; then
  echo "$CurrentTime: There is an issue while trying to take dump in $MongoHost. Aborting dump process, please check! " >> $LogFile
  cat $CmdStatus >> $LogFile
  mail -s "Backup error for $ReplicaSet from $MongoHost on $TodayDate" $MailNotification < $LogFile   exit fi CurrentTime=`date +"%r"` BackupSize=$(du -sh $LocalBackupPath/. | awk '{ print $1 }') echo "$CurrentTime: Mongodump command completed. Backup size is $BackupSize. " >> $LogFile
 
## dump is fine then scp it to backup server
## create directory first
CmdStatus=$(ssh -p $BackupHostPort $BackupHost "mkdir -p  $BackupHostPath/$ReplicaSet/$DateStamp")
 
if [ $? -ne 0 ]; then
  echo "$CurrentTime: Either failing to connect Backup server using ssh or destination directory already exist!" >> $LogFile
  cat $CmdStatus >> $LogFile
  mail -s "Backup error for $ReplicaSet from $MongoHost on $TodayDate" $MailNotification < $LogFile   exit fi CurrentTime=`date +"%r"` echo "$CurrentTime: Directory created on backup server, copying data using scp..." >> $LogFile
 
CmdStatus=`scp -P $BackupHostPort -r $LocalBackupPath/* $BackupHost:/$BackupHostPath/$ReplicaSet/$DateStamp/`
if [ $? -ne 0 ]; then
  echo "$CurrentTime: Unable to scp dump to $BackupHost:/$BackupHostPath/$ReplicaSet/$DateStamp using port $BackupHostPort. " >> $LogFile
  cat $CmdStatus >> $LogFile
  mail -s "Backup error for $ReplicaSet from $MongoHost on $TodayDate" $MailNotification < $LogFile   exit fi CurrentTime=`date +"%r"` echo "$CurrentTime: Copied dump to backup server in directory $BackupHost$BackupHostPath/$ReplicaSet/$DateStamp/." >> $LogFile
echo "$CurrentTime: Mongo Backup process completed successfully." >> $LogFile
mail -s "Backup for $ReplicaSet done on $MongoHost at $TodayDate" $MailRecipients $MailNotification < $LogFile

Its just a basic script and may needs further enhancements. In case you have suggestion/queries, please put it below in comments.

More Related and helpful articles:

• Setup multiple MySQL database servers in a single Linux host
• Optimize MySQL on a large Database Server
• Recover or reset root password of MySQL and PostgreSQL Servers 
• Optimize and fix MySQL Server running slow without any load
• How to find out the clients connecting to your MySQL server
• Quickly repair huge corrupted or crashed table in MySQL
• Install and configure PhpMyAdmin to manage multiple MySQL Servers

Replication just stopped in one Slave server with error: The table xtable is full which means no more records are permitted to insert in this table by MySQL and hence this has broken the replication.

I checked that xtable is having storage engine as Memory. In such tables, the max. no. of records you can insert is controlled by variable max_heap_table_size. When checking the size of this variable, I found that this is having default value:

mysql> show variables like 'max_heap_table_size';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+
1 row in set (0.00 sec)

So we need to increase the value of this variable and then issue Alter Table command to make it effective. Also do not forget to add variable with new value in your my.cnf.

Change the variable value:

mysql> set  max_heap_table_size=268435456;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like 'max_heap_table_size';
+---------------------+-----------+
| Variable_name       | Value     |
+---------------------+-----------+
| max_heap_table_size | 268435456 |
+---------------------+-----------+
1 row in set (0.00 sec)

Now let’s truncate the table and issue Alter table on it to make the value effective for this table:

mysql> truncate table xtable;
Query OK, 0 rows affected (0.00 sec)
 
mysql> alter table xtable ENGINE=MEMORY;
Query OK, 88 rows affected (0.06 sec)
Records: 88  Duplicates: 0  Warnings: 0

After this fix, the issue has been resolved. Make sure that you do not run the truncate command on table which have important data. I have issued truncate because it contains temporary/transactional data so we are fine with removing all records here.

In general, MySQL is quite fast at restoring data, but I observed that while restoring 20gb of backup its taking more than the usual time. This can happen when you don’t have enough memory or if key_buffer_size is not set high enough, then it can take very long time to re-index the data. In CentOS [...]

If you push play, the video should scoot out to about the 14m40s mark where MySQLTuner appears on one of the slides. Thanks to Trent Hornibrook for letting me know!

MySQLTuner mentioned at the O'Reilly MySQL Conference is a post from: Major Hayden's Racker Hacker blog.

Thanks for following the blog via the RSS feed. Please don't copy my posts or quote portions of them without attribution.

This is a basic PHP script to check replication status of single or multiple MySQL servers. You have to update variables in the script to run in browser like user, password, ip of server etc. Here is the script code: <? ########## # # dbrepstatus.php by Jagbir Singh (jags@jagbir.com) # # ######### ?>   <html><head> [...]

If you have read and implemented my previous article: how to run multiple MySQL services on a single box, you may like to manage such multiple installation of MySQL or multiple separate MySQL hosts by using a single PhpMyAdmin installation. This is easy enough to do quickly. I assume you have Apache (httpd) and PHP [...]

The pv command is one that I really enjoy using but it's also one that I often forget about. You can't get a much more concise definition of what pv does than this one:

pv allows a user to see the progress of data through a pipeline, by giving information such as time elapsed, percentage completed (with progress bar), current throughput rate, total data transferred, and ETA.

The usage certainly isn't complicated:

To use it, insert it in a pipeline between two processes, with the appropriate options. Its standard input will be passed through to its standard output and progress will be shown on standard error.

A great application of pv is when you're restoring large amounts of data into MySQL, especially if you're restoring data under duress due to an accidentally-dropped table or database. (Who hasn't been there before?) The standard way of restoring data is something we're all familiar with:

# mysql my_database < database_backup.sql

The downside of this method is that you have no idea how quickly your restore is working or when it might be done. You could always open another terminal to monitor the tables and databases as they're created, but that can be hard to follow.

# pv database_backup.sql | mysql my_database
96.8MB 0:00:17 [5.51MB/s] [==>                                ] 11% ETA 0:02:10

When it comes to MySQL, your restore rate is going to be different based on some different factors, so the ETA might not be entirely accurate.

Monitor MySQL restore progress with pv is a post from: Major Hayden's Racker Hacker blog.

c0b6ad7e-f251-11df-b20b-4040336e00ef

Here is the situation: We are doing replication of only 1 table from a database (live) to other host (backup). so we expect that updates which are on others DBs/tables except on this table should not replicate. Wrong! they got replicated. We shifted one DB say DB1 from live env to Backup DB host and [...]

Today, on my 28th birthday, I'm finally delivering on a promise to my readers which I made about two months ago. I've written a guide on how to host a web application redundantly in a cloud environment. While it's still a bit of a rough draft, it should be a good starting point for those who haven't worked in virtualized environments before. Also, it may show some of the more experienced systems administrators a new way to do things.

The guide: Redundant Cloud Hosting Guide

As always, if you find anything in the guide that needs improvement, I'm all ears.

A simple guide to redundant cloud hosting is a post from: Major Hayden's Racker Hacker blog.

c0b6ad7e-f251-11df-b20b-4040336e00ef

MySQL stopped default inclusion of InnoDB in latest 5.1.x, so if you need InnoDB, you have to compile it from source. I have done following steps in CentOS 5.4 server to compile MySQL and optimize it later for a heavy site: 1. Remove earlier installation of MySQL, if any and download source rpm from MySQL [...]