{"id":216,"date":"2013-04-01T19:47:52","date_gmt":"2013-04-01T19:47:52","guid":{"rendered":"http:\/\/www.reades.com\/?p=216"},"modified":"2013-04-01T19:47:52","modified_gmt":"2013-04-01T19:47:52","slug":"multiple-mysql-servers-on-a-single-machine","status":"publish","type":"post","link":"http:\/\/www.reades.com\/wp\/?p=216","title":{"rendered":"Multiple MySQL Servers on a Single Machine"},"content":{"rendered":"

\t\t\t\tNote: this was previously posted at\u00a0simulacra.info<\/a>, but I am in the process of (re)organising my technical notes and tutorials.<\/i><\/p>\n

A bit of a dry post here, but I thought I’d share my experience of trying to get two instances of MySQL (and two different versions, to boot) running simultaneously on a single piece of hardware as I’ve spent the past two days tearing my hear out and swearing profusely (mostly) under my breath.<\/p>\n

So the background for this post is that we’ve recently received a new\u00a0toy\u00a0server for data crunching in short, sharp bursts. The idea is that when we’re working with sensitive data that can’t be stored ‘in the cloud’ we can use this machine, with its 32GB of RAM, to whiz through aggregation and querying. However, because we’ve got multiple users of the system and some of us need different levels of security\u00a0as well as<\/em>\u00a0different versions of the software, there really was no alternative to installing two sets of binaries with two very different configurations.<\/p>\n

We installed the first using Ubuntu’s handy\u00a0apt-get<\/code>\u00a0utility, but the second one proved the problem. First, it needs to be installed somewhere else, and because it’s Ubuntu we can’t install from RPM. So custom configuration here we come… which is where the fun begins because it turns out that the configuration process has changed\u00a0utterly<\/em>\u00a0since I last did anything like this.<\/p>\n

Apparently,\u00a0cmake<\/code>\u00a0has now replaced\u00a0.\/configure<\/code>\u00a0as the ‘right way’ to do these things, and after a great deal of huffing and puffing this got me a configurable system:<\/p>\n


\ncmake . -DCMAKE_INSTALL_PREFIX=\/usr\/local\/mysql-secure -DMYSQL_DATADIR=\/var\/mysql-secure\/ -DWITH_SSL=yes\u00a0-DCURSES_LIBRARY=\/usr\/lib\/libncurses.a\u00a0-DWITH_DEBUG=OFF -DMYSQL_MAINTAINER_MODE=OFF -DENABLED_PROFILING=ON -DINSTALL_LAYOUT=STANDALONE
\n<\/code><\/p>\n

Now you go back to the old habit of:<\/p>\n


\nmake
\nsudo make install
\n<\/code><\/p>\n

If you’re very, very lucky then this will have compiled smoothly. If you’re not then, in all probability you don’t have curses or libaio1 installed and will have to\u00a0make clean<\/code>\u00a0before you can reach this point without an error.<\/p>\n

You’re now ready to finish the installation, so head on over to your base directory for the install:<\/p>\n


\ncd \/usr\/local\/mysql-secure\/
\n<\/code><\/p>\n

I’m assuming here that you still want to\u00a0run<\/em>\u00a0MySQL as the\u00a0mysql<\/code>\u00a0user (who should have their login shell set to\u00a0\/dev\/null<\/code>), but you could quite easily run it as another user if you want:<\/p>\n


\nsudo .\/scripts\/mysql_install_db --user=mysql --basedir=\/usr\/local\/mysql-secure\/ --datadir=\/var\/lib\/mysql-secure
\n<\/code><\/p>\n

You should then go through the normal steps to set up a\u00a0root<\/code>\u00a0password and generally lock down MySQL as best you can. You will also want to set up a separate configuration file for your ‘secure’ MySQL installation because, at the very least, you need it to run on a different port (here specified as\u00a0XXXX<\/code>) and with a different socket. Something like this:<\/p>\n


\n[client]
\nport = XXXX
\nsocket = \/var\/run\/mysqld\/mysqld-secure.sock<\/code><\/p>\n

user = mysql
\nsocket = \/var\/run\/mysqld\/mysqld-secure.sock
\nport = XXXX
\nbasedir = \/usr\/local\/mysql-secure
\ndatadir = \/var\/lib\/mysql-secure
\ntmpdir = \/tmp<\/p>\n

You’re nearly there, and from here on out the server can be started by simply running:<\/p>\n


\nsudo .\/bin\/mysqld_safe --defaults-file=\/etc\/mysql\/secure.cnf --user=mysql &
\n<\/code><\/p>\n

I’m still trying to figure out what isn’t\u00a0quite<\/em>\u00a0right about the configuration, but here’s how to connect locally and remotely to a database called\u00a0db<\/code>\u00a0on the\u00a0second<\/em>\u00a0instance of MySQL server listening on port XXX:<\/p>\n


\nmysql --socket=\/var\/run\/mysqld\/mysqld-secure.sock -P XXX\u00a0-u root -p db
\nmysql -P XXX -u foo -p db
\n<\/code><\/p>\n

Hope this helps anyone trying to do anything similar.<\/p>\n

Next up: figuring out how to configure SSL with MySQL for additional security.<\/p>\n

Configuring a Third Server<\/h3>\n

As with all things computer related, you can always have more. In this case, I wanted access to the OpenQuery Graph engine for MySQL\/MariaDB. Well, it turns out that MySQL isn’t so keen on the engine and MariaDB (a drop-in replacement for MySQL) is the only way to go. So for posterity’s sake here’s how I got a third database up and running on the\u00a0same<\/em>\u00a0machine.<\/p>\n

I grabbed the source distribution for MariaDB (this is version 5.3, by the time you’re reading this you might want to check for a 5.x) from here:\u00a0http:\/\/downloads.askmonty.org\/mariadb\/5.3\/#file_type=source<\/a>
\ntar -xzvf maria...
\ncd .\/maria...
\n.\/configure --prefix=\/usr\/local\/mariadb-5.3 --datadir=\/cosmic\/...
\nmake && sudo make install
\nsudo chown -R XXX \/cosmic\/...
\ncd \/usr\/local\/mariadb-5.3
\nsudo .\/bin\/mysql_install_db --user=XXX --defaults-file=\/etc\/my-openquery.cnf --datadir=\/cosmic\/...<\/code>
\nThat last command obviously relies on you having created a configuration file for the OpenQuery-enabled MariaDB installation. Here’s a minimal working copy:<\/p>\n


\n[client]
\nport = XXX
\nsocket = \/tmp\/mariadb-secure.sock<\/code><\/p>\n

[mysql.server]
\nuser=mysql
\nbasedir=\/mnt\/cosmic\/…<\/p>\n

[mysqld]
\nuser = XXX
\nport = XXX
\nsocket = \/tmp\/mariadb-secure.sock
\ndatadir=\/mnt\/cosmic\/…
\nskip-external-locking
\nkey_buffer = 16M
\nmax_allowed_packet = 1M
\ntable_cache = 64
\nsort_buffer_size = 512K
\nnet_buffer_length = 8K
\nread_buffer_size = 256K
\nread_rnd_buffer_size = 512K
\nmyisam_sort_buffer_size = 8M<\/p>\n

… [There’s more in here, but a basic MySQL.cnf file will give you all you need] …<\/p>\n

Starting up is as simple as:
\n
\nsudo \/usr\/local\/mariadb-5.3\/bin\/mysqld_safe --defaults-file=\/etc\/mysql\/my-opengraph.cnf --user=mysql &
\n<\/code><\/p>\n

But to enable the graph plug-in engine for Maria there’s one last step — you need to connect to the database an issue the following command (as root):<\/p>\n


\nINSTALL PLUGIN oqgraph SONAME 'ha_oqgraph.so';
\nSHOW ENGINES;<\/code>\t\t<\/p>\n","protected":false},"excerpt":{"rendered":"

Note: this was previously posted at\u00a0simulacra.info, but I am in the process of (re)organising my technical notes and tutorials. A bit of a dry post here, but I thought I’d share my experience of trying to get two instances of MySQL (and two different versions, to boot) running simultaneously on a single piece of hardware […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,12],"tags":[36,37,72],"class_list":["post-216","post","type-post","status-publish","format-standard","hentry","category-big-data","category-tutorials","tag-data-2","tag-database","tag-mysql"],"_links":{"self":[{"href":"http:\/\/www.reades.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/216","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.reades.com\/wp\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.reades.com\/wp\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.reades.com\/wp\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.reades.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=216"}],"version-history":[{"count":0,"href":"http:\/\/www.reades.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/216\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.reades.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=216"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.reades.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=216"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.reades.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=216"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}