Why would you need to run a second instance? Well, perhaps this is a redundant machine that replicates production systems and needs to run a writable database as well. Or maybe you need to replicate two masters to a single box.
There are many reasons to run multiple MySQL instances, and you probably have already identified that need. Otherwise, you wouldn't be reading this howto.
First, as a clarification, this post is going to assume that you already have one instance of MySQL running in a more-or-less default configuration under OpenBSD.
The first thing you'll need to do is create a data directory with proper permissions for the second instance:
# mkdir /var/mysql_two # chown _mysql:_mysql /var/mysql_two
Now populate the directory with a default database:
# mysql_install_db --datadir=/var/mysql_two --user=_mysql
If you're using my.cnf to set anything that shouldn't apply to both instances (such as replication or default engines), you'll need to create a second my.cnf file. We'll assume that this has been done as /etc/my2.cnf
Next, you need to edit /etc/rc.local in order to start both MySQL processes at boot . You probably already have something like this:
if [ -x /usr/local/bin/mysqld_safe ]; then /usr/local/bin/mysqld_safe --ssl-ca=/etc/ssl/ca-bundle.crt \ --user=_mysql \ --log-error=/var/log/mysql/mysqld-err.log & echo -n ' MySQL 5 Starting \n' fi
You'll need to add an additional startup command to that block. Note that if you are specifying a second my.cnf file, that it needs to be the first flag provided to mysqld_safe:
if [ -x /usr/local/bin/mysqld_safe ]; then ##Process One /usr/local/bin/mysqld_safe --ssl-ca=/etc/ssl/ca-bundle.crt \ --user=_mysql \ --log-error=/var/log/mysql/mysqld-err.log & echo -n ' First MySQL 5 process starting \n'
##Process Two /usr/local/bin/mysqld_safe --defaults-file=/etc/my2.cnf \ --datadir=/var/mysql_two \ --user=_mysql \ --log-error=/var/log/mysql/mysqld2-err.log \ --port=3307 & echo -n ' Second MySQL 5 process Starting \n' fi
You may want to try starting the process up manually instead of rebooting. To do this:
$ sudo /usr/local/bin/mysqld_safe --defaults-file=/etc/my2.cnf \ > --datadir=/var/mysql_two \ > --user=_mysql \ > --log-error=/var/log/mysql/mysqld2-err.log \ > --port=3307 &
You can test the server with:
$ mysql -u root --port=3307
Notice that it's a completely different set of schemas and table than your first instance. Remember to change the root password for this instance as they are logically separate.
That should do it.
Leave a comment