Nov03
2007

CoolStack is a collection of some of the most commonly used open source applications optimized for the Sun Solaris OS platform. I’ve been tracking the release of the next version, CoolStack 1.2, for some time and see that it has just been released. Here is a brief overview of what it gives you:

  • Apache 2.2.6 with mod_jk-1.2.25, mod_proxy and SMF support.
  • PHP 5.2.4 with FastCGI support.
  • APC 3.0.14.
  • MySQL 5.0.45 with ndbcluster and SMF support.
  • Memcached 1.2.2.
  • Squid 2.6.16 with SMF support.
  • Tomcat 5.5.23 with SMF support.
  • Ruby 1.8.6 with RubyGems and Rails 1.2.3.
  • lighttpd 1.4.18.
  • Perl 5.8.8 with DBI-1.59, DBD-mysql-4.005 and Sys-Syslog-0.18 extensions

As you can see, CoolStack is now a pretty serious software bundle and there isn’t much it doesn’t give you (although DTrace support for Ruby would be a useful addition). The addition of Tomcat and SMF support (SMF allows easier management of Solaris services) along with the additional Apache modules and updated revisions of Apache, MySQL, Ruby and Rails tick a large number of boxes in our organisation as we previously had to build several Apache modules by hand, struggled with lack of Perl DBI/DBD extensions and spent far too long constructing SMF manifests for many of the applications.

So, well done to Shanti and his team for providing almost exactly everything we were hoping for in this release! CoolStack has now become an integral part of our software infrastructure, reducing our service deployment times considerably.

Oct09
2007

I spent some time looking at the mysqldump command in detail this afternoon with a view to backing up some in-house MySQL databases which were configured in a simple replicated environment (single master, single slave). I knew that executing something like:

# mysqldump -u user --password=xxxx dbname > dumpfile.sql

on the replication slave would do most of what I needed but I wanted to be sure that I was getting a consistent backup of the data nonetheless. At first, I thought I might have to stop MySQL before and after the dump but then I recalled of course that MySQL actually needs to be running to do a dump. My Subversion hat was duly removed for the afternoon!

Then I started to think about locking and flushing databases and tables, a simple database lock/unlock (read) before/after the dump should do the trick. Fortunately, this is taken care of for me by the mysqldump utility through the --opt parameter. This parameter is a shorthand way of enabling several other options, most notably the --lock-tables option, which locks the tables in the database being dumped for the duration of the dump itself.

Interestingly though, the replication process is also suspended by mysqldump and this actually lead me to falsely believe (for a short time) that all databases had been locked instead of just the one being dumped.

So, in essence, the simple command I started out with above actually does everything I need.

Jul19
2007

The following notes were taken during an exercise to configure MySQL database replication across two SunFire T2000 servers running Solaris 10, each of which also has a single ZFS file system mounted in /storage/xxxx (where the respective MySQL data files are located). The snapshot capabilities of ZFS were hugely beneficial in this scenario as they allowed mere seconds of database downtime (as opposed to potentially several minutes or even hours otherwise).

The process of replication setup is already well documented on the Internet, particularly here on the MySQL website. However, as usual, I have chosen to share my experiences and observations in the hope that may prove useful to others.

Introduction

Each of the steps below is precluded by the system on which the commands are to be carried out, MASTER or SLAVE.

MASTER

The following commands will affect the availability of the database so prepare a number of login sessions with the commands ready to be executed and execute them as quickly as possible in the correct order so as to minimize the down time of the database.

1. Lock the Database
master$ mysql -u root -p
mysql> FLUSH TABLES WITH READ LOCK;

2. Create a snapshot of the database
master# zfs snapshot tank/masterzfs@mysql-2007-07-18

3. Record the File and Position information from Master Status (to be used on slave later on)
mysql> SHOW MASTER STATUS;
Typical values include File: mysql-bin.000008 and Position: 420414560

4. Unlock the database again
mysql> UNLOCK TABLES;

5. Verify the successful creation of the ZFS snapshot
master# zfs list

6. Compress/pack the required contents of the ZFS snapshot
master# cd /storage/masterzfs/.zfs/snapshot/masterzfs-mysql-2007-07-18/mysql
master# tar -cf /storage/masterzfs/masterzfs-mysql-2007-07-13.tar mysql/

[NOTE] The .zfs directory above is a hidden directory and may not be visible (even via “ls -a”). However, it is there and you can access it!

SLAVE

7. Copy the database archive produced on the master to the slave (using SSH compression)
slave# mkdir /storage/slavezfs/tmp
slave# cd /storage/slavezfs/tmp
slave# scp -C etadev@db219:/storage/masterzfs/masterzfs-mysql-2007-07-13.tar .

[NOTE] This took approximately 75 minutes for a 4.7GB file over a 100Mb connection

8. Unpack the database archive at the slave
slave# cd /storage/slavezfs/tmp
slave# tar –xvf masterzfs-mysql-2007-07-13.tar

MASTER

9. Grant database access to the slave
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'xx.xx.xx.xx' IDENTIFIED BY 'replpass';
Clearly, you will need to substitute your own username, slave IP Address and replication password here

10. Update the Master MySQL Configuration File(s) and restart MySQL (if required)
master# vi /etc/my.cnf
server-id=1
log-bin=mysql-bin (uncomment)
innodb_flush_log_at_trx_commit = 1 (uncomment if using InnoDB tables)

SLAVE

11. Move the unpacked files from the Master into place (ensure that MySQL and all services that use it are stopped)
slave# cd /storage/slavezfs
slave# mv mysql mysql.pre-repl
slave# mv tmp/mysql .

12. Update the Slave MySQL Configuration File(s) and restart MySQL (if required)
slave# vi /etc/my.cnf
server-id=2
log-bin=mysql-bin (uncomment)
innodb_flush_log_at_trx_commit = 1 (uncomment if using InnoDB tables)

13. Configure and Start Replication
mysql> CHANGE MASTER TO MASTER_HOST='xx.xx.xx.xx', MASTER_PORT=XXXX, MASTER_USER='repl', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=420414560;
mysql> START SLAVE;

Clearly, you will need to substitute your own username, master IP Address, Port and replication password here. Also MASTER_LOG_FILE and MASTER_LOG_POS are the values recorded from the SHOW MASTER STATUS in Step 3. Be careful when entering the CHANGE MASTER command above as the inclusion of white spaces in the log file setting can prevent replication from working correctly. Also, you should be aware that you are now accessing a different database than might have previously been on this system and so, the users/passwords may be different.

14. Verify Replication is Operating correctly
mysql> SHOW SLAVE STATUS;
and examine the Seconds_Behind_Master field. When the slave is fully synchronised with the master, this will be 0.

Best of luck!

Calendar

April 2014
M T W T F S S
« Feb    
 123456
78910111213
14151617181920
21222324252627
282930