theDaveTheRave
February 4th, 2009, 09:35 AM
Dear All.
I am trying to set up multiple MySQL servers on my terminal.
ultimately I want to convert this to a master / slave setup between 2 machines, but for now I have some questions.
I have successfully got mutliple servers running. but how do I access them independently of one another??
here is the important part of the </etc/mysql/my.cnf> file
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log = /var/lib/mysql/mysqld_multi.log
user = root
password = EjdZmfAcer
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
relay-log = mysql-relay-bin
err-log = /var/lib/mysql/mysql_safe_nostart.log
log-error = /var/lib/mysql/mysql_safe_err.log
user = mysql
[mysqld2]
server-id = 2
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
#socket = /var/run/mysqld/mysqld.sock # allready set on line 44.
port = 3308
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
default-storage-engine = innodb
[mysqld5]
server-id = 5
user = mysql
pid-file = /var/run/mysqld/mysqld.pid5
#socket = All servers will use the same local socket file for connecting
port = 3309
basedir = /usr
datadir = /var/lib/mysql2
tmpdir = /tmp
language = /usr/share/mysql/english
default-storage-engine = innodb
[mysqld357]
server-id = 357
mysqld = /usr/bin/mysqld_safe
#socket = All servers will use the same local socket file for connecting
port = 1357
pid-file = /var/lib/mysqldTest/mysqld.pid
basedir = /usr
datadir = /var/lib/mysqlTest/mysql
language = /usr/share/mysql/english
tmpdir = /tmp
user = mysql
bind-address = 'testDB'
default-storage-engine = innodb
You are probably wondering why I set up 3 instances, this is because at first I couldn't get the [mysqld357] server to run, but I have now solved that problem.
so I know that they are running from
davem@Dartagnon:~$ mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld5 is running
MySQL server from group: mysqld357 is running
davem@Dartagnon:~$
which would all seem rather good to me... however
if I log into MySQL with the following
davem@Dartagnon:~$ mysql -u davem -p -P 1357
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.0.51a-3ubuntu5.4-log (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
and then call the following function...
mysql> show variables like "%port%";
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_support_xa | ON |
| large_files_support | ON |
| port | 3308 |
+---------------------+-------+
3 rows in set (0.01 sec)
mysql>show variables like "%server%";
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| character_set_server | latin1 |
| collation_server | latin1_swedish_ci |
| server_id | 2 |
+----------------------+-------------------+
3 rows in set (0.00 sec)
which would seem to suggest I am in fact looking at the first server available in the list.
In a way this isn't a surprise, as they are all using the same <base directory> and hence are all looking at and retrieving data from the same <information_schema> tables.
But here is my problem.
I can select and add data to any table on any of the servers, from any server
any help would be appreciated, so as I can separate all the server instances.
thanks in advance
David
Edit #1 ...
So I've been thinking about all this.... and I've just had the thought that maybee I need to physically install a second MySQL server onto my terminal in a separate place?
would this sound correct??
Another thing I suddenly realised... I have the servers set up with different <datadir = /var/~...> settings. So I checked to see if there was a sub folder for my new <test357> database. And there was, but only in the data ddirectory for the the server on [mysqld2] not in the data directory of < /var/lib/mysql/mysqlTest > which is what I was hoping to see!
So now I have tried a third access method,
davem@Dartagnon:~$ mysql -u davem -p -P3358
which I assumed would not allow me to gain access to the MySQL server as I don't have a server on port 3358.... but
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.0.51a-3ubuntu5.4 (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| MGI_Mouse |
| fastaFishing |
| genefocus |
| identitylinks |
| mysql |
| mysqlTest |
| old_to_new |
| test357 |
+--------------------+
9 rows in set (0.16 sec)
mysql>
As you can see I can get in, and view all my databases!
I am really scratching my head on this one.... I guess I am going to have to install a second instance of MySQL.... but I'm not sure how to do this.... I will report back shortly.
Edit #2....
Ok so now I've been really "geeky" I've downloaded and installed the newest version of MySQL (ie version 6- alpha.
I used the install procedure to send it to a separate place on my HDD (inside the home/MySQL/MySQL6 folder), and the /home is on a separate partition, so if I refresh my pc I won't have to do a complete re-install of all of this....I hope....
but now I have another issue.
I want both mysql5.### and mysql6 to start up on boot.
they both want to look at the same config file (ie the one above) that lives in </etc/mysql/my.cnf> so now my issue is how do I make them look at different my.cnf files??
I'm sure I can work out 2 little scripts to run the mysqld_safe deamon's at startup (obviously the one for the new MySQL6 version sits in </home/mysql/MySQL6/bin> and the general one sits on <usr/bin/> (according to the <locate mysqld_safe> that I have just done). Which all seems fair enough.
In fact I have just decided to check some things.
davem@Dartagnon:~$ ps aux |grep mysql
root 5372 0.0 0.1 1772 524 ? S 08:47 0:00 /bin/sh /home/msql/MySQL6/bin/mysqld_safe --datadir=/home/msql/MySQL6/var --pid-file=/home/msql/MySQL6/var/Dartagnon.pid
mysql 5450 0.0 2.7 45136 14044 ? Sl 08:47 0:00 /home/msql/MySQL6/libexec/mysqld --basedir=/home/msql/MySQL6 --datadir=/home/msql/MySQL6/var --user=mysql --log-error=/var/lib/mysql/mysql_safe_err.log --pid-file=/home/msql/MySQL6/var/Dartagnon.pid --socket=/var/run/mysqld/mysqld.sock
davem 8655 0.0 0.1 3004 744 pts/0 D+ 09:14 0:00 grep mysql
Which would certainly suggest that there is a server running using the MySQL6 install on the correct data directory, and when I log into MySQL it just shows the test and information schema databases... which is cool
so now I start my "other" mysql5, and then check the ps aux
davem@Dartagnon:~$ sudo mysqld_multi start
[sudo] password for davem:
davem@Dartagnon:~$ ps aux |grep mysql
root 5372 0.0 0.1 1772 524 ? S 08:47 0:00 /bin/sh /home/msql/MySQL6/bin/mysqld_safe --datadir=/home/msql/MySQL6/var --pid-file=/home/msql/MySQL6/var/Dartagnon.pid
mysql 5450 0.0 2.5 45424 13084 ? Sl 08:47 0:00 /home/msql/MySQL6/libexec/mysqld --basedir=/home/msql/MySQL6 --datadir=/home/msql/MySQL6/var --user=mysql --log-error=/var/lib/mysql/mysql_safe_err.log --pid-file=/home/msql/MySQL6/var/Dartagnon.pid --socket=/var/run/mysqld/mysqld.sock
davem 8660 2.3 4.8 49304 24572 ? Sl 09:15 0:01 /usr/bin/mysql-query-browser
davem 8669 2.4 4.3 47292 21908 ? S 09:15 0:01 /usr/bin/mysql-query-browser
root 8682 0.0 0.1 1772 520 pts/0 S 09:16 0:00 /bin/sh /usr/bin/mysqld_safe --server-id=2 --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --port=3308 --basedir=/usr --datadir=/var/lib/mysql --tmpdir=/tmp --language=/usr/share/mysql/english --default-storage-engine=innodb --log-error=/var/lib/mysql/mysqlServer2.err --log-long --bind-address=Dartagnon --key_buffer_size=64M --max_allowed_packet=16M --thread_stack=128 --thread_cache_size=8 --query_cache_limit=1M --query_cache_size=16M --log_bin=/var/log/mysql/mysql-bin.log --expire_logs_days=21 --max_binlog_size=100M --skip-bdb
mysql 8914 0.3 4.2 176012 21284 pts/0 Sl 09:16 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3308 --socket=/var/run/mysqld/mysqld.sock --server-id=2 --tmpdir=/tmp --language=/usr/share/mysql/english --default-storage-engine=innodb --log-error=/var/lib/mysql/mysqlServer2.err --log-long --bind-address=Dartagnon --key_buffer_size=64M --max_allowed_packet=16M --thread_stack=128 --thread_cache_size=8 --query_cache_limit=1M --query_cache_size=16M --log_bin=/var/log/mysql/mysql-bin.log --expire_logs_days=21 --max_binlog_size=100M --skip-bdb
davem 8970 0.0 0.1 3012 780 pts/0 R+ 09:16 0:00 grep mysql
davem@Dartagnon:~$
[code]
now all I need to do is get them both to run as start u!
so a shell script is going to be required I guess!?
A quick question... should I write down how / why I have done this?? and put it here as an extended howto!?? what do you all think?
David.
Edit Feb.10th.
Ok so I thought that this was all working hapily. I haven't changed anything anywhere in my set up and I can't access any of instances of MySQL?
If i try to access anything I get the same message about the server being unavailable.
I've tried a reboot, but doesn't seem to help.
I can't even use the mysqld_multi tool...
[code]
davem@Dartagnon:~$ sudo mysqld_multi report
[sudo] password for davem:
Unknown option: port
Error with an option, see mysqld_multi --help for more info.
davem@Dartagnon:~$ sudo mysqld_multi stop
Unknown option: port
Error with an option, see mysqld_multi --help for more info.
davem@Dartagnon:~$ sudo mysqld_multi start
Unknown option: port
Error with an option, see mysqld_multi --help for more info.
davem@Dartagnon:~$
so where do I go now?
is anyone out there thinking that i have a curious problem?
I am trying to set up multiple MySQL servers on my terminal.
ultimately I want to convert this to a master / slave setup between 2 machines, but for now I have some questions.
I have successfully got mutliple servers running. but how do I access them independently of one another??
here is the important part of the </etc/mysql/my.cnf> file
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log = /var/lib/mysql/mysqld_multi.log
user = root
password = EjdZmfAcer
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
relay-log = mysql-relay-bin
err-log = /var/lib/mysql/mysql_safe_nostart.log
log-error = /var/lib/mysql/mysql_safe_err.log
user = mysql
[mysqld2]
server-id = 2
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
#socket = /var/run/mysqld/mysqld.sock # allready set on line 44.
port = 3308
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
default-storage-engine = innodb
[mysqld5]
server-id = 5
user = mysql
pid-file = /var/run/mysqld/mysqld.pid5
#socket = All servers will use the same local socket file for connecting
port = 3309
basedir = /usr
datadir = /var/lib/mysql2
tmpdir = /tmp
language = /usr/share/mysql/english
default-storage-engine = innodb
[mysqld357]
server-id = 357
mysqld = /usr/bin/mysqld_safe
#socket = All servers will use the same local socket file for connecting
port = 1357
pid-file = /var/lib/mysqldTest/mysqld.pid
basedir = /usr
datadir = /var/lib/mysqlTest/mysql
language = /usr/share/mysql/english
tmpdir = /tmp
user = mysql
bind-address = 'testDB'
default-storage-engine = innodb
You are probably wondering why I set up 3 instances, this is because at first I couldn't get the [mysqld357] server to run, but I have now solved that problem.
so I know that they are running from
davem@Dartagnon:~$ mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld5 is running
MySQL server from group: mysqld357 is running
davem@Dartagnon:~$
which would all seem rather good to me... however
if I log into MySQL with the following
davem@Dartagnon:~$ mysql -u davem -p -P 1357
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.0.51a-3ubuntu5.4-log (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
and then call the following function...
mysql> show variables like "%port%";
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_support_xa | ON |
| large_files_support | ON |
| port | 3308 |
+---------------------+-------+
3 rows in set (0.01 sec)
mysql>show variables like "%server%";
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| character_set_server | latin1 |
| collation_server | latin1_swedish_ci |
| server_id | 2 |
+----------------------+-------------------+
3 rows in set (0.00 sec)
which would seem to suggest I am in fact looking at the first server available in the list.
In a way this isn't a surprise, as they are all using the same <base directory> and hence are all looking at and retrieving data from the same <information_schema> tables.
But here is my problem.
I can select and add data to any table on any of the servers, from any server
any help would be appreciated, so as I can separate all the server instances.
thanks in advance
David
Edit #1 ...
So I've been thinking about all this.... and I've just had the thought that maybee I need to physically install a second MySQL server onto my terminal in a separate place?
would this sound correct??
Another thing I suddenly realised... I have the servers set up with different <datadir = /var/~...> settings. So I checked to see if there was a sub folder for my new <test357> database. And there was, but only in the data ddirectory for the the server on [mysqld2] not in the data directory of < /var/lib/mysql/mysqlTest > which is what I was hoping to see!
So now I have tried a third access method,
davem@Dartagnon:~$ mysql -u davem -p -P3358
which I assumed would not allow me to gain access to the MySQL server as I don't have a server on port 3358.... but
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.0.51a-3ubuntu5.4 (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| MGI_Mouse |
| fastaFishing |
| genefocus |
| identitylinks |
| mysql |
| mysqlTest |
| old_to_new |
| test357 |
+--------------------+
9 rows in set (0.16 sec)
mysql>
As you can see I can get in, and view all my databases!
I am really scratching my head on this one.... I guess I am going to have to install a second instance of MySQL.... but I'm not sure how to do this.... I will report back shortly.
Edit #2....
Ok so now I've been really "geeky" I've downloaded and installed the newest version of MySQL (ie version 6- alpha.
I used the install procedure to send it to a separate place on my HDD (inside the home/MySQL/MySQL6 folder), and the /home is on a separate partition, so if I refresh my pc I won't have to do a complete re-install of all of this....I hope....
but now I have another issue.
I want both mysql5.### and mysql6 to start up on boot.
they both want to look at the same config file (ie the one above) that lives in </etc/mysql/my.cnf> so now my issue is how do I make them look at different my.cnf files??
I'm sure I can work out 2 little scripts to run the mysqld_safe deamon's at startup (obviously the one for the new MySQL6 version sits in </home/mysql/MySQL6/bin> and the general one sits on <usr/bin/> (according to the <locate mysqld_safe> that I have just done). Which all seems fair enough.
In fact I have just decided to check some things.
davem@Dartagnon:~$ ps aux |grep mysql
root 5372 0.0 0.1 1772 524 ? S 08:47 0:00 /bin/sh /home/msql/MySQL6/bin/mysqld_safe --datadir=/home/msql/MySQL6/var --pid-file=/home/msql/MySQL6/var/Dartagnon.pid
mysql 5450 0.0 2.7 45136 14044 ? Sl 08:47 0:00 /home/msql/MySQL6/libexec/mysqld --basedir=/home/msql/MySQL6 --datadir=/home/msql/MySQL6/var --user=mysql --log-error=/var/lib/mysql/mysql_safe_err.log --pid-file=/home/msql/MySQL6/var/Dartagnon.pid --socket=/var/run/mysqld/mysqld.sock
davem 8655 0.0 0.1 3004 744 pts/0 D+ 09:14 0:00 grep mysql
Which would certainly suggest that there is a server running using the MySQL6 install on the correct data directory, and when I log into MySQL it just shows the test and information schema databases... which is cool
so now I start my "other" mysql5, and then check the ps aux
davem@Dartagnon:~$ sudo mysqld_multi start
[sudo] password for davem:
davem@Dartagnon:~$ ps aux |grep mysql
root 5372 0.0 0.1 1772 524 ? S 08:47 0:00 /bin/sh /home/msql/MySQL6/bin/mysqld_safe --datadir=/home/msql/MySQL6/var --pid-file=/home/msql/MySQL6/var/Dartagnon.pid
mysql 5450 0.0 2.5 45424 13084 ? Sl 08:47 0:00 /home/msql/MySQL6/libexec/mysqld --basedir=/home/msql/MySQL6 --datadir=/home/msql/MySQL6/var --user=mysql --log-error=/var/lib/mysql/mysql_safe_err.log --pid-file=/home/msql/MySQL6/var/Dartagnon.pid --socket=/var/run/mysqld/mysqld.sock
davem 8660 2.3 4.8 49304 24572 ? Sl 09:15 0:01 /usr/bin/mysql-query-browser
davem 8669 2.4 4.3 47292 21908 ? S 09:15 0:01 /usr/bin/mysql-query-browser
root 8682 0.0 0.1 1772 520 pts/0 S 09:16 0:00 /bin/sh /usr/bin/mysqld_safe --server-id=2 --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --port=3308 --basedir=/usr --datadir=/var/lib/mysql --tmpdir=/tmp --language=/usr/share/mysql/english --default-storage-engine=innodb --log-error=/var/lib/mysql/mysqlServer2.err --log-long --bind-address=Dartagnon --key_buffer_size=64M --max_allowed_packet=16M --thread_stack=128 --thread_cache_size=8 --query_cache_limit=1M --query_cache_size=16M --log_bin=/var/log/mysql/mysql-bin.log --expire_logs_days=21 --max_binlog_size=100M --skip-bdb
mysql 8914 0.3 4.2 176012 21284 pts/0 Sl 09:16 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3308 --socket=/var/run/mysqld/mysqld.sock --server-id=2 --tmpdir=/tmp --language=/usr/share/mysql/english --default-storage-engine=innodb --log-error=/var/lib/mysql/mysqlServer2.err --log-long --bind-address=Dartagnon --key_buffer_size=64M --max_allowed_packet=16M --thread_stack=128 --thread_cache_size=8 --query_cache_limit=1M --query_cache_size=16M --log_bin=/var/log/mysql/mysql-bin.log --expire_logs_days=21 --max_binlog_size=100M --skip-bdb
davem 8970 0.0 0.1 3012 780 pts/0 R+ 09:16 0:00 grep mysql
davem@Dartagnon:~$
[code]
now all I need to do is get them both to run as start u!
so a shell script is going to be required I guess!?
A quick question... should I write down how / why I have done this?? and put it here as an extended howto!?? what do you all think?
David.
Edit Feb.10th.
Ok so I thought that this was all working hapily. I haven't changed anything anywhere in my set up and I can't access any of instances of MySQL?
If i try to access anything I get the same message about the server being unavailable.
I've tried a reboot, but doesn't seem to help.
I can't even use the mysqld_multi tool...
[code]
davem@Dartagnon:~$ sudo mysqld_multi report
[sudo] password for davem:
Unknown option: port
Error with an option, see mysqld_multi --help for more info.
davem@Dartagnon:~$ sudo mysqld_multi stop
Unknown option: port
Error with an option, see mysqld_multi --help for more info.
davem@Dartagnon:~$ sudo mysqld_multi start
Unknown option: port
Error with an option, see mysqld_multi --help for more info.
davem@Dartagnon:~$
so where do I go now?
is anyone out there thinking that i have a curious problem?