Setting up Simple MySQL Database Replication

ABOUT THE SETUP
I operate a busy OpenStack environment that is used by lots of people making changes everyday.  A typical day will see over 100 images spawned and terminates, but some of these last longer than a day.  Because OpenStack maintains a stateDB mapping the OpenStack layers to the KVM/Libvirt layers it’s critical to have not only backups, but mysql replication in place.  A failure 18 hours after my last backup could leave me scrambling through over 100 instances looking and performing some horrible manual fixes.

With that in mind I’m building a second controller for OpenStack which will keep a replicated copy of the database.  The plan is to add an HAProxy in to the mix to manage all connections from the Compute Nodes to the Controllers as well as incoming https connections from my users into the Controllers.

In the end, that’s where I’m going.  This article is just about the first part.  MySQL Replication.  BTW: This is Ubuntu 12.04

REFERENCE: https://dev.mysql.com/doc/refman/5.0/en/replication-howto.html

Like all of the documentation from MySQL it’s written at a pretty high-level and is 100% factually correct while also being slightly unclear and missing steps.   What is wrong with examples?  Why not add some?

Continue reading

Tuning mySQL – Because by default it’s not even close to tuned.

Basic tuning of the mySQL is accomplished in the /etc/my.cnf file. If you want to get all geeky and into this reference the seminal document over on the mysql dev site. This should result in a speed increase in your system.  It certainly has in my system running mySQL 5.x.

The information below is expressed as a set of ratios that begins with your system RAM and then works from there.

innodb_buffer_pool_size = $SYSTEMRAM/2
innodb_additional_mem_pool_size = $innodb_buffer_pool_size/20
innodb_log_file_size = $innodb_buffer_pool_size/4
innodb_log_buffer_size = $innodb_buffer_pool_size/50 or a minimum value of 8MB

Note bene: Changing your log file size can results in a mySQL refusing to start.  Simply remove these files from you mysql data directory and they will be created on the next startup.

Script to Move Database Location – mySQL

Don’t run this script.  It’s a concept that I haven’t tested and running it is pretty well guaranteed to crash your mysql server.  It’s designed to make the relocation of data faster, but I don’t have time to finish it today.

You should probably use this fellow link because it works… it’s just slower and manual.  Oh, and if you do get a scripting urge, please make this script work properly for me and post it in a comment.  Thanks.

 

USER=root
PASSWORD=yourpassword
DBS="$(mysql --user=$USER --password=$PASSWORD -Bse 'show databases')"
OLDDATA_DIR="/var/lib/mysql"
NEWDATA_DIR="/database/lib/mysql"

mkdir -pv $NEWDATA_DIR

for FILE in ${DBS[@]}; do
        DATABASE=`basename $FILE`
        echo cp -R $OLDDATA_DIR/$DATABASE $NEWDATA_DIR/$DATABASE
done

# Set permissions
chown -R mysql:mysql $NEWDATA_DIR

# Archive the old & link it to the new
mv $OLDDATA_DIR OLDDATA_DIR-old
ln -s $NEWDATA_DIR/$DATABASE $OLDDATA_DIR/$DATABASE

#get_mysql_option mysqld datadir "/database/lib/mysql"
sed -i  's|$OLDDATA_DIR|$NEWDATA_DIR|' /etc/init.d/mysqld
sed -i  's|$OLDDATA_DIR|$NEWDATA_DIR|' /etc/my.cnf

Reset mySQL password

Simple stuff assuming you haven’t forgotten your password.  If you have lost the password then read on below after the two methods

mysql -uroot
USE mysql;
UPDATE user
SET password = password
WHERE user = “root";
flush privileges;
exit;
# or is you have a password - change it
 mysqladmin -h localhost -u root password newpassword

I LOST MY PASSWORD
Easy enough, you have to start in safe mode and then use the first method above.  Safe mode is invoked by skipping the grant table load:

/usr/bin/mysqld_safe –skip-grant-tables&

About Jay Farschman - Jay currently works as a Senior Systems Administrator for an asset management company in Colorado where he works with companies that produce hardware, telecommunications software and financial services.  Jay previously owned a consulting company and provided training and consulting services for three Fortune 500 companies and numerous small businesses where he leveraged Linux to provided exceptional value.