MySQL x 2

Grim... · **Posted:** Mon Jun 29, 2009 11:24

Hello!
I have the jolly task of trying to create a second database server on one of our production boxes, which already has a MySQL server running on it.
The second database will use a different socket and datafile to the first, and I'd rather not shut the existing database down if I can help it.

However, I'm drawing a blank. I've tried using mysqld_multi but it makes my head hurt (and didn't seem to work). I've also read this page but I'm still having no success. Does anyone have any advice?

MaliA · **Posted:** Mon Jun 29, 2009 11:27

Grim... wrote:

Hello!
I have the jolly task of trying to create a second database server on one of our production boxes, which already has a MySQL server running on it.
The second database will use a different socket and datafile to the first, and I'd rather not shut the existing database down if I can help it.

However, I'm drawing a blank. I've tried using mysqld_multi but it makes my head hurt (and didn't seem to work). I've also read this page but I'm still having no success. Does anyone have any advice?

You'll need an old priest, and a young priest.

failing that, it's probably the index tables of the database sending looking for things in the wrong place. So fix that, first.

Grim... · **Posted:** Mon Jun 29, 2009 11:34

It's not worth me hitting the "Display this post" link on that one, is it?

Doctor Glyndwr · **Joined:** 30th Mar, 2008 **Posts:** 32624

Grim... wrote:

It's not worth me hitting the "Display this post" link on that one, is it?

Nope.

myp · **Posted:** Mon Jun 29, 2009 11:36

Grim... wrote:

It's not worth me hitting the "Display this post" link on that one, is it?

Is it ever?

GazChap · **Posted:** Mon Jun 29, 2009 11:45

Well, according to that page you can just pass a different socket to the new server when you invoke it through the shell, e.g. mysqld --socket=mysql2.sock or whatever.

Or is it the datafiles that're causing the problem?

You should be able to specify --datadir=xxx to specify different datafiles too.

Grim... · **Posted:** Mon Jun 29, 2009 11:51

GazChap wrote:

Or is it the datafiles that're causing the problem?

Maybe - I thought it created them if they weren't there? Maybe that's where I'm going wrong.

Code:

Starting mysqld daemon with databases from /home/matt/sqltest
mysqld_safe[9698]: started
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[9703]: ended

GazChap · **Posted:** Mon Jun 29, 2009 11:54

It should do, yeah. Can you have a gander in the directory and see if the files are there after the server's stopped?

Grim... · **Posted:** Mon Jun 29, 2009 12:00

Yeah, they're there. Here's the (unhelpful) log file:

Code:

/usr/sbin/mysqld, Version: 5.0.32-Debian_7etch8-log (Debian etch distribution). started with:
Tcp port: 3310  Unix socket: /var/run/mysqld/mysqld2.sock
Time                 Id Command    Argument

Bah.

Grim... · **Posted:** Mon Jun 29, 2009 12:01

Aha, hang on. Now I'm getting somewhere - it's the socket that's at fault.

Code:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld2.sock' (2)

Do I have to create the socket myself? Again, I thought MySQL dealt with that.

GazChap · **Posted:** Mon Jun 29, 2009 12:05

Yeah, you can't create a socket manually, MySQL (or the host OS) should create it for you.

Permissions issue on /var/run/mysqld/, perhaps?

Grim... · **Posted:** Mon Jun 29, 2009 12:08

Nope - 0777 (this is a dev box, so no worries there).

Grim... · **Posted:** Mon Jun 29, 2009 12:11

Here are the important bits of the .cnf file that I'm telling it to run:

Code:

[client]
port            = 3310
socket          = /var/run/mysqld/mysqld2.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket          = /var/run/mysqld/mysqld2.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = root
pid-file        = /var/run/mysqld/mysqld2.pid
socket          = /var/run/mysqld/mysqld2.sock
port            = 3310
basedir         = /usr
datadir         = /home/matt/sqltest
tmpdir          = /tmp
language        = /usr/share/mysql/english

You think I should change the basedir?

I'm shit at this stuff

/goes off to program something

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49244

The port is different to the original database, right?

Grim... · **Posted:** Mon Jun 29, 2009 12:14

Craster wrote:

The port is different to the original database, right?

Yeah, original uses 3306. Changing the basedir made no difference.

GazChap · **Posted:** Mon Jun 29, 2009 12:16

Isn't a software/hardware firewall of some sort blocking connections to 3310 is there?

Grim... · **Posted:** Mon Jun 29, 2009 12:17

GazChap wrote:

Isn't a software/hardware firewall of some sort blocking connections to 3310 is there?

Hmm, don't think so.
I'll check.

myp · **Posted:** Mon Jun 29, 2009 12:19

Sounds stupid (probably is), but have you tried stopping and starting mysqld?

GazChap · **Posted:** Mon Jun 29, 2009 12:21

Try this in the shell:

Code:

mysql_install_db --user=xxx --basedir=yyy --datadir=zzz

And then try starting the server.

I'd backup your existing datadir/basedir beforehand though, not sure if this is intelligent enough to not wipe out existing datafiles.

GazChap · **Posted:** Mon Jun 29, 2009 12:22

myp wrote:

Sounds stupid (probably is), but have you tried stopping and starting mysqld?

Grim... doesn't want to shut down the existing server if he can help it, I think.

Shutting down mysqld and restarting it would only affect the server currently running, may not have any effect on any new server(s) he wants to start up.

myp · **Posted:** Mon Jun 29, 2009 12:28

Hrmmm, yes, I'd forgotten about that.

Grim... · **Posted:** Mon Jun 29, 2009 12:35

GazChap wrote:

Try this in the shell:

Code:

mysql_install_db --user=xxx --basedir=yyy --datadir=zzz

And then try starting the server.

Hmm, that spat out the error

Code:

090629 12:33:03 [ERROR] /usr/sbin/mysqld: Can't find file: './mysql/help_relation.frm' (errno: 13)

about 1,000 times but seemed to complete anyway.
But! No difference, as far as I can tell.

Grim... · **Posted:** Mon Jun 29, 2009 13:48

Yay verbose output!

Code:

090629 13:47:06 [ERROR] /usr/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13)
090629 13:47:06 [ERROR] /usr/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13)
090629 13:47:06 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: './mysql/host.frm' (errno: 13)

would seem to be the problem. Why the hell is it looking there for stuff? Ooh, basedir!

Grim... · **Posted:** Tue Jun 30, 2009 13:38

AND LO, THE CLOUDS DID PART, AND THE LIGHT DID COME SHINING DOWN, AND THE TERMINAL, IT SAID:

Attachment:

PRAISEBE.gif

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH!

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49244

Hurrah! I think.

Grim... · **Posted:** Tue Jun 30, 2009 13:42

Code:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.01 sec)

Hurrah indeed.

myp · **Posted:** Tue Jun 30, 2009 13:46

Kudos on your leet skillz, soldier.

itsallwater · **Posted:** Tue Jun 30, 2009 14:06

So what was the problem in the end?

Grim... · **Posted:** Tue Jun 30, 2009 14:20

I have no fucking clue. It's actually a really simple thing to do - make a new config file and then just start MySQL with

Code:

/usr/bin/mysqld_safe --defaults-file=/my/new/file.cnf

I think it was just the strange way it was installed onto the server in the first place that was fucking everything up.

GazChap · **Posted:** Tue Jun 30, 2009 14:42

No root password? Tut tut

And do you have another server called hunky?

Grim... · **Posted:** Tue Jun 30, 2009 14:53

GazChap wrote:

No root password? Tut tut

And do you have another server called hunky?

Afraid not. This one is called Dory because of its tendency to forget things

We have Hindenburg, Titanic and United93, though.
"Titanic's gone down again!"
Much joy

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49244

Fish actually have a decent memory - many are quite capable of being trained.

Grim... · **Posted:** Tue Jun 30, 2009 15:09

Craster wrote:

Fish actually have a decent memory - many are quite capable of being trained.

Yes indeed. But we didn't call it "fish".

kalmar · **Posted:** Thu Jan 07, 2010 17:21

Mind if I hijack this thread?

I was toying with the idea of converting some of my websites to WordPress. To do this I need to use MySQL. I've installed that.

Now what? I'm at a bit of a loss what to do with it - no idea how to address it from the command line, and got horribly confused with user names and so on when trying to run phpmysqladmin.

Can anyone give me some basic pointers to how to create a user, set up databases so that Wordpress can use it, and so on?

GazChap · **Posted:** Thu Jan 07, 2010 18:19

Are you on Windows?

kalmar · **Posted:** Thu Jan 07, 2010 18:27

No, mac...

Grim... · **Posted:** Thu Jan 07, 2010 18:43

When you set it up, did you specify a root password?

If so, at the prompt:

Code:

mysql -uroot -p

Then type your password. This should get you to your MySQL command prompt.

Try typing

Code:

show databases;

to see if everything is working (note the semicolon).

If this works, install Wordpress following the instructions, and it'll create its own database once you tell it the username (root) and password.

Grim... · **Posted:** Thu Jan 07, 2010 18:46

Oh, you might need to start it first. Use

Code:

/etc/init.d/mysqld start

at the prompt.

kalmar · **Posted:** Fri Jan 08, 2010 0:13

Woo, awesome, thanks Grim...
I know it's running, there's a thinger in the control panel for that.

I think there is no password on the root.. user.

Code:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| test               | 
+--------------------+
2 rows in set (0.18 sec)

The WP setup is asking for

Database Name The name of the database you want to run WP in.
User Name Your MySQL username
Password ...and MySQL password.
Database Host 99% chance you won't need to change this value.
Table Prefix

So I need to make those first, right? OK, it actually has instructions for doing it from the command line, I missed that last time.

To be continued...

kalmar · **Posted:** Fri Jan 08, 2010 0:33

Oh, that was easy. Why did nobody say wordpress was so handy?

Be Excellent To Each Other

MySQL x 2
Bah. Stoopid databases

Who is online

Be Excellent To Each Other

MySQL x 2 Bah. Stoopid databases

Who is online

MySQL x 2
Bah. Stoopid databases