Topic: Running Multiple MySQL Instances on SME

[LANMonkey]

I would like to be able to install another server instance of MySQL that does not interfere with the MySQL installation that runs SME Server.

I broached this subject once before here:

http://forums.contribs.org/index.php/topic,46327.msg227041.html

I looked into this again and found some information here:

http://dev.mysql.com/doc/refman/5.5/en/multiple-servers.html

http://lasanthals.blogspot.com/2012/09/running-multiple-instances-of-mysql-on.html

http://www.howtoforge.com/running-mysql4-and-mysql5-concurrently

There is a New Feature Request posted here that I saw in response to my first inquiry on the subject:

http://bugs.contribs.org/show_bug.cgi?id=2301

Has anyone ever managed to do this?  Has anyone ever tried and failed?  Can anyone say whether this is possible on SME or not and what would be the best way to approach it on SME?

[Stefano]

I have no solution but a question: why?

[LANMonkey]

Good question.

For a website on my SME server, I need the features of a later version of MySQL.  The version that SME distributes with does not have those features and an upgrade pathway for that version does not exist.  If you try to upgrade, the Hoard mail website breaks down and other things too, if I recall.  I tried once and it was a total disaster.

[Stefano]

in this case you could install a plain VM with Centos or another distro, install there mysql in the desired/needed version, use it as db backend.. you're done..

[LANMonkey]

in this case you could install a plain VM with Centos or another distro, install there mysql in the desired/needed version, use it as db backend.. you're done..

The idea of getting into virtually installed operating systems makes my blood run cold.  Perhaps you could give me a brief sketch of how to make such an installation and how I would apply the MySQL database in such an installation.

[Stefano]

virtualization is simple and you can test almost everything..

I don't know which version of mysql do you need, but you can follow any of the hundreds howto out there to virtualize your favourite O.S.

start from here: http://www.howtoforge.com

[LANMonkey]

"Your hardware must support virtualization".

On this thread, let us consider virtualization off topic.

Again, I am interested in how to set up multiple instances of MySQL on SME.  Anyone have any clues about doing this on SME?

[mmccarn]

I have installed 'mariadb' using their 'alongside' installation instructions.  So far, so good.

My notes are here:
http://wiki.contribs.org/User_talk:Mmccarn#Install_mariadb_.27alongside.27_mysql


I've got Moodle 2.6 running using this version of mariadb.

[LANMonkey]

I have installed 'mariadb' using their 'alongside' installation instructions.  So far, so good.

My notes are here:
http://wiki.contribs.org/User_talk:Mmccarn#Install_mariadb_.27alongside.27_mysql


I've got Moodle 2.6 running using this version of mariadb.

That looks encouraging.  I'll check it out.  Moodle looks interesting too.

Thanks for posting.

[TerryF]

I have installed 'mariadb' using their 'alongside' installation instructions.  So far, so good.

The way things are going Mariadb will replace MySQL in more than a few open source distros..

[LANMonkey]

I have installed 'mariadb' using their 'alongside' installation instructions.  So far, so good.

My notes are here:
http://wiki.contribs.org/User_talk:Mmccarn#Install_mariadb_.27alongside.27_mysql

I am happy to make this move to mariadb, especially if SME intends to migrate to mariadb sometime in the future.  But, as a point of reference, since mariadb is a fork of MySQL, couldn't this 'alongside' procedure also be applied to some other version of MySQL?

[mmccarn]

Well, dev.mysql.com has a page for 'multiple mysql instances on Unix':
http://dev.mysql.com/doc/refman/5.7/en/multiple-unix-servers.html

...But the instructions seem to start with "./configure", which always puts me off.

I setup mariadb because their website had easy(ish) instructions that didn't require compiling from source.

[LANMonkey]

Well, dev.mysql.com has a page for 'multiple mysql instances on Unix':
http://dev.mysql.com/doc/refman/5.7/en/multiple-unix-servers.html

...But the instructions seem to start with "./configure", which always puts me off.

I setup mariadb because their website had easy(ish) instructions that didn't require compiling from source.

Might be worth a try.  But I'm going to try mariadb first.

[janet]

LANMonkey

This thread
http://forums.contribs.org/index.php/topic,50081.0.html
has details re how to install a later version of mysql & Moodle.

[LANMonkey]

LANMonkey

This thread
http://forums.contribs.org/index.php/topic,50081.0.html
has details re how to install a later version of mysql & Moodle.

I'll try that too.

But meanwhile, I managed to follow mccarn's instruction at the link provided and successfully display,

Code: [Select]

+--------------------+
| VERSION()          |
+--------------------+
| 5.5.34-MariaDB-log |
+--------------------+

I just copied and pasted the instructions, line by line from the "Raw notes on mariadb installation" into my PuTTY command line and enter.

I saw some differences.

First, /etc/init.rd in my SME 9 beta 3 is a link to /etc/rc.d/init.d.  That was a little disorienting, but the file to change was there.

I didn't see any instructions to add a group "mariadb".  When I tried, it rejected mariadb as being already used as a user name and I had to use a different name and make the appropriate changes in the instructions.

Also, testing access with,

Code: [Select]

mysql -e "SELECT VERSION();" --port=3307 --protocol=TCP -p
didn't work until I did a reboot.  And, it did not need an actual password to work, you just a get a prompt for a password and hit "enter" and that should do it.  Same for the example version check.  My SME did not ship with a  password for MySQL's root and we are not advised anywhere to add one.

Now to applications to see how MariaDB works with my databases.

[TerryF]

My SME did not ship with a  password for MySQL's root and we are not advised anywhere to add one.

Have you read this?  http://wiki.contribs.org/MySQL#MySQL_root_password

[CharlieBrady]

Here's some reading for you:

https://access.redhat.com/site/documentation/en-US/Red_Hat_Developer_Toolset/1/html/Software_Collections_Guide/

http://developerblog.redhat.com/2013/08/06/using-drupal-7-with-the-php-5-4-and-mysql-software-collections/

https://www.google.ca/webhp?q=mysql55%20el5%20rpm

[LANMonkey]

Thanks all.  I always reset root outside of SME, but was unsure how that would effect MySQL in root.

[mmccarn]

First, /etc/init.rd in my SME 9 beta 3 is a link to /etc/rc.d/init.d.  That was a little disorienting, but the file to change was there.

I didn't see any instructions to add a group "mariadb".  When I tried, it rejected mariadb as being already used as a user name and I had to use a different name and make the appropriate changes in the instructions.

Also, testing access with,

Code: [Select]

mysql -e "SELECT VERSION();" --port=3307 --protocol=TCP -p
didn't work until I did a reboot.  And, it did not need an actual password to work, you just a get a prompt for a password and hit "enter" and that should do it.  Same for the example version check.  My SME did not ship with a  password for MySQL's root and we are not advised anywhere to add one.

Thanks for testing things out!

/etc/init.rd vs etc/rc.d/init.d:
Since they're symlinked, it doesn't really matter which one you create your new file in.

Group 'mariadb':
When you create a user in server-manager, SME also creates a group of the same name.

MySQL root password:
SME doesn't make you enter a password for mysql, but it does use a root password for mysql that was generated randomly on your server during installation and can be found in /var/service/mysqld/set.password .  You don't need to enter the password when running 'mysql' or 'mysqladmin' at the command line because SME uses some sort of magic to enter the password for you.

When connecting to mariadb, this randomly generated password is also supplied-- but mariadb really does have an empty root password by default.  Until I added "-p" to my mariadb mysql command after first installing mariadb I got an authentication error.

You can see your randomly generated mysql root password in this file: /var/service/mysqld/set.password

Reboot:
I somehow managed to shutdown mysql while trying to get mariadb to run before I figured out that I was having permission issues with the the pid file creation in /var/run/mariadb.  I rebooted to fix mysql, but I may have needed to reboot for some other reason.

[edits]
root password section edited slightly.

[LANMonkey]

I ran the commands to install MariaDB from the User Talk again on another machine, my SME  8.

There is a typo,

chown mariadb:maridb /var/run/mariadb

In order to customize the execution line in /etc/init.d/mariadb, you have to find it first.  I used the search function in my Notepad++ to find "$bindir/mysqld_safe", making sure to use a Normal search so that I pick up the dollar sign.

Applying "mysql -e "SELECT VERSION();" --port=3307 --protocol=TCP -p" returns a password prompt.  I enter nothing and hit return, and I get back:

Code: [Select]

ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (111)
And so before that command is where you should do a reboot.  After a reboot, I am able to get the version report.

I'm not sure I see the point of,

mysql --port=3307 --protocol=TCP -p < /var/service/mysqld/set.password

I enter that and get a prompt for a password.  Should I have set the password before entering that?  What does "set.password" do?  I don't have a password for my MySQL root user, maybe that was the problem.

The last command works anyway for me.

[mmccarn]

maridb (typo):
Corrected (twice).  Thanks.

/etc/init.d/mariadb:
Yes.  I ended up searching for 'mysqld_safe' to find this line.  If there's a lot of interest in mariadb I would either script the edits or create downloadable versions of the various customized files somewhere or hope that someone builds a smeserver-mariadb contrib.

reboot required:
I always assumed that the database initialization script started the database server.  If it doesn't we'd need to insert a command in the process to start mariadb:

Code: [Select]

/etc/init.d/mariadb startIt seems possible to me that starting the database like this would remove the need for a reboot.

mysql --port=3307 --protocol=TCP -p < /var/service/mysqld/set.password
Unless you have gone to great lengths to UNSET it you DO have a password for root on your mysql database.  You don't need to enter a password when you run mysql because the SME devs put it into ~/.my.cnf for you.

However, since there is a password in ~/.my.cnf, it is going to be used to connect to mariadb, too, unless you specify "-p" every time you run mysql --port=3307 --protocol=TCP.

The point of this command is to prompt you for a password once, then SET the password for 'root' on the mariadb database to the same value used for 'root' in mysql, since this password is supplied automatically by the mysql client.

My thought process here is:
1) I don't want to have to remember a new root password
2) The SME devs make very conservative security choices:
  2a) leaving the mariadb root password blank cannot be secure.
  2b) using the SME mysql security mechanisms for the root password in mariadb should be a good security stance.

[LANMonkey]

I installed this MariaDB on my SME 9.0 test server before I tried it on SME 8.0.  This problem is on SME 8.0, it is not a problem on my test server, SME 9.0 alpha 2.

When I boot up SME 8.0, as I see things starting up withe [OK] prompts, the start for MySQL hangs with the prompt "starting MariaDB" followed by ticks popping up every second for about 10 lines on the screen, or about 10 to 15 minutes.  Eventually, SME does boot up.

I might add I also have upgraded the main MySQL server according to the suggestions posted above and explained at this link here:

http://forums.contribs.org/index.php/topic,50081.0.html

But it is also installed on my 9.0 server alongside MariaDB with no problems.

What is going on?  Why does it take so long for MariaDB to start up on 8.0?  What can I do to fix this or look into it further?

[janet]

LANMonkey

I installed this MariaDB on my SME 9.0 test server before I tried it on SME 8.0.  This problem is on SME 8.0, it is not a problem on my test server, SME 9.0 alpha 2.

You should be testing big changes like this on the same system version, ie on a sme 8 test server, if you are going to install on sme 8 production server.

As for using your stated use of SME 9.0 alpha 2
there are at least 2 more updates, a beta 1 & beta 2 releases, which are much more advanced that alpha 3 was, & there is also a iso for beta 3 available for download & tesing (not officially released yet), see devinfo mail list. You should be testing with the latest release.
See
http://forums.contribs.org/index.php/board,17.0.html

What can I do to fix this or look into it further?

Look at the log files, especially start up logs

[LANMonkey]

....

Look at the log files, especially start up logs

Which logs are the start up logs?  I checked the boot logs in the logs part of server-manager and it says they are all empty.  I checked the mysqld logs, but the only ones not empty are pretty large.  If that's where to look, what do I look for?  I searched "mariadb" and found nothing.

[janet]

LANMonkey

The standard log to look at for many things is
messages

Also see
dmesg

[mmccarn]

Which logs are the start up logs?  I checked the boot logs in the logs part of server-manager and it says they are all empty.  I checked the mysqld logs, but the only ones not empty are pretty large.  If that's where to look, what do I look for?  I searched "mariadb" and found nothing.

The mariadb logs should be in /opt/mariadb-data/<smeservername>.err

[LANMonkey]

Here are the contents of <myserver>.err

Code: [Select]

131216 15:53:22 mysqld_safe Starting mysqld daemon with databases from /opt/mariadb-data
131216 15:53:24 InnoDB: The InnoDB memory heap is disabled
131216 15:53:24 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131216 15:53:24 InnoDB: Compressed tables use zlib 1.2.3
131216 15:53:24 InnoDB: Using Linux native AIO
131216 15:53:24 InnoDB: Initializing buffer pool, size = 128.0M
131216 15:53:24 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
131216 15:53:24  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
131216 15:53:25  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
131216 15:53:25  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
131216 15:53:26  InnoDB: Waiting for the background threads to start
131216 15:53:27 Percona XtraDB (http://www.percona.com) 5.5.34-MariaDB-31.1 started; log sequence number 0
131216 15:53:27 [Note] Plugin 'FEEDBACK' is disabled.
131216 15:53:28 [Note] Server socket created on IP: '0.0.0.0'.
131216 15:53:28 [Note] Event Scheduler: Loaded 0 events
131216 15:53:28 [Note] /opt/mariadb/bin/mysqld: ready for connections.
Version: '5.5.34-MariaDB-log'  socket: '/opt/mariadb-data/mariadb.sock'  port: 3307  MariaDB Server
131219 10:37:16 mysqld_safe Starting mysqld daemon with databases from /opt/mariadb-data
131219 10:37:17 InnoDB: The InnoDB memory heap is disabled
131219 10:37:17 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131219 10:37:17 InnoDB: Compressed tables use zlib 1.2.3
131219 10:37:17 InnoDB: Using Linux native AIO
131219 10:37:17 InnoDB: Initializing buffer pool, size = 128.0M
131219 10:37:17 InnoDB: Completed initialization of buffer pool
131219 10:37:17 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
131219 10:37:17  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
131219 10:37:18  InnoDB: Waiting for the background threads to start
131219 10:37:19 Percona XtraDB (http://www.percona.com) 5.5.34-MariaDB-31.1 started; log sequence number 1597945
131219 10:37:19 [Note] Plugin 'FEEDBACK' is disabled.
131219 10:37:19 [Note] Recovering after a crash using mysql-bin
131219 10:37:19 [Note] Starting crash recovery...
131219 10:37:19 [Note] Crash recovery finished.
131219 10:37:19 [Note] Server socket created on IP: '0.0.0.0'.
131219 10:37:19 [ERROR] mysqld: Table './mysql/user' is marked as crashed and should be repaired
131219 10:37:19 [Warning] Checking table:   './mysql/user'
131219 10:37:19 [ERROR] mysql.user: 1 client is using or hasn't closed the table properly
131219 10:37:19 [Note] Event Scheduler: Loaded 0 events
131219 10:37:19 [Note] /opt/mariadb/bin/mysqld: ready for connections.
Version: '5.5.34-MariaDB-log'  socket: '/opt/mariadb-data/mariadb.sock'  port: 3307  MariaDB Server
131221 09:28:25 mysqld_safe Starting mysqld daemon with databases from /opt/mariadb-data
131221  9:28:27 InnoDB: The InnoDB memory heap is disabled
131221  9:28:27 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131221  9:28:27 InnoDB: Compressed tables use zlib 1.2.3
131221  9:28:27 InnoDB: Using Linux native AIO
131221  9:28:27 InnoDB: Initializing buffer pool, size = 128.0M
131221  9:28:27 InnoDB: Completed initialization of buffer pool
131221  9:28:27 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
131221  9:28:27  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
131221  9:28:27  InnoDB: Waiting for the background threads to start
131221  9:28:28 Percona XtraDB (http://www.percona.com) 5.5.34-MariaDB-31.1 started; log sequence number 1597945
131221  9:28:29 [Note] Plugin 'FEEDBACK' is disabled.
131221  9:28:29 [Note] Recovering after a crash using mysql-bin
131221  9:28:29 [Note] Starting crash recovery...
131221  9:28:29 [Note] Crash recovery finished.
131221  9:28:29 [Note] Server socket created on IP: '0.0.0.0'.
131221  9:28:29 [Note] Event Scheduler: Loaded 0 events
131221  9:28:29 [Note] /opt/mariadb/bin/mysqld: ready for connections.
Version: '5.5.34-MariaDB-log'  socket: '/opt/mariadb-data/mariadb.sock'  port: 3307  MariaDB Server

The first thing I looked for was lines with huge differences in time to account for the extremely long time for MySQL to start up in the boot sequence.  I don't see anything.  I suppose it might have helped if I had marked the time the MySQL start line appeared.  I don't want to take my server off line long enough to see what that's like.

I also have MariaDB installed in SME 9 beta 2 (I think) and here is the last day of that log from that server.  I don't see any difference and this server boots up with no delays and no notifications that MariaDB is starting up in the boot sequence.

Code: [Select]

131221 09:45:06 mysqld_safe Starting mysqld daemon with databases from /opt/mariadb-data
131221  9:45:07 InnoDB: The InnoDB memory heap is disabled
131221  9:45:07 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131221  9:45:07 InnoDB: Compressed tables use zlib 1.2.3
131221  9:45:07 InnoDB: Using Linux native AIO
131221  9:45:07 InnoDB: Initializing buffer pool, size = 128.0M
131221  9:45:07 InnoDB: Completed initialization of buffer pool
131221  9:45:07 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
131221  9:45:08  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 48164780, file name ./mysql-bin.000004
131221  9:45:10  InnoDB: Waiting for the background threads to start
131221  9:45:11 Percona XtraDB (http://www.percona.com) 5.5.34-MariaDB-31.1 started; log sequence number 19776695
131221  9:45:12 [Note] Plugin 'FEEDBACK' is disabled.
131221  9:45:13 [Note] Recovering after a crash using mysql-bin
131221  9:45:13 [Note] Starting crash recovery...
131221  9:45:13 [Note] Crash recovery finished.
131221  9:45:13 [Note] Server socket created on IP: '0.0.0.0'.
131221  9:45:16 [Note] Event Scheduler: Loaded 0 events
131221  9:45:16 [Note] /opt/mariadb/bin/mysqld: ready for connections.
Version: '5.5.34-MariaDB-log'  socket: '/opt/mariadb-data/mariadb.sock'  port: 3307  MariaDB Server


[idp_qbn]

LANMonkey

STOP - every post you do, you change the title of the thread.  Leave them as they are so others can follow what is happening. Stop it now  or the Bad Fairy may take down your particulars......

Good luck with what you are trying to do, though. Sounds useful .

Cheers and Merry Christmas
Ian

[LANMonkey]

LANMonkey

STOP - every post you do, you change the title of the thread.  Leave them as they are so others can follow what is happening. Stop it now  or the Bad Fairy may take down your particulars......

Good luck with what you are trying to do, though. Sounds useful .

Cheers and Merry Christmas
Ian

OK,  I thought actually titling a post in the subject was good for brownie points from the Good Fairy.

Why do they leave it blank?

[idp_qbn]

Hmm - when I hit "Reply" the window that opens already has the subject filled in.
Why is yours different? Are you using the "Reply" button or something else?

Cheers
Ian
"Reply" is just under the last posting, on the right bottom corner of the screen.

[Stefano]

just don't use "reply" but "Quick reply"

[LANMonkey]

Hmm - when I hit "Reply" the window that opens already has the subject filled in.
Why is yours different? Are you using the "Reply" button or something else?

Cheers
Ian
"Reply" is just under the last posting, on the right bottom corner of the screen.

Right.  It isn't blank, but it isn't permanent.  You can replace what is in there with anything you want.  If you don't want anyone to change what the subject is, that entry should not be editable.

[LANMonkey]

I continue to have problems booting up with MariaDB.  I have posted logs from MariaDB above.  If anyone can provide clues as to what is going I would be much obliged.  The problem again:

I installed this MariaDB on my SME 9.0 test server before I tried it on SME 8.0.  This problem is on SME 8.0, it is not a problem on my test server, SME 9.0 alpha 2.

When I boot up SME 8.0, as I see things starting up withe [OK] prompts, the start for MySQL hangs with the prompt "starting MariaDB" followed by ticks popping up every second for about 10 lines on the screen, or about 10 to 15 minutes.  Eventually, SME does boot up.

I might add I also have upgraded the main MySQL server according to the suggestions posted above and explained at this link here:

http://forums.contribs.org/index.php/topic,50081.0.html

But it is also installed on my 9.0 server alongside MariaDB with no problems.

What is going on?  Why does it take so long for MariaDB to start up on 8.0?  What can I do to fix this or look into it further?