RE: mysqld_multi

Thank you all for the tips. I've read the manual multiple times and it is NOT clear what options are necessary in a multi environment. The missing element was provided by Oracle support: I need to use --defaults-file=my.cnf as in:
# mysqld --defaults-file=cba.cnf --initialize --user=cba
CBA.CNF contents:
[mysqld] # No instance number, NOT documented!
socket = /MySQLdb/cba/mysql.sock
port = 3317
pid-file = /MySQLdb/cba/mysqld.pid
datadir = /MySQLdb/cba
lc-messages-dir = /usr/share/mysql/English
log-error = /MySQLdb/cba/mysql.error
user = cba
# mysql -uroot -p --socket=/MySQLdb/cba/mysql.sock
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
mysql> quit
I was simply seeking those three commands, but the reference manual did not provide a real-world example.
matthew
-----Original Message-----
From: shawn l.green [mailto:shawn.l.green@xxxxxxxxxx]
Sent: Saturday, May 20, 2017 10:57 AM
To: mysql@xxxxxxxxxxxxxxx
Subject: Re: mysqld_multi
Hello Matthew,
On 5/19/2017 12:19 PM, Matthew Black wrote:
> I just installed MySQL Enterprise Edition 5.7 on RHEL 6.8 to replace an aging 5.1 system running on RHEL 5. We run mysqld_multi with multiple instances, each database on its own TCP Port 33xx. I'm having trouble creating a database on the new server in a multi environment.
>
>
>
> Can anyone provide a simple example of how to edit /etc/my.cnf file and command line steps necessary for creating a new database running on, for example, port 3311?
>
>
>
> Thanks in advance.
>
>
>
> matthew
>
How to use mysql_multi is covered in the Manual. This includes a sample
my.cnf file demonstrating how to define your separate instances.
https://dev.mysql.com/doc/refman/5.7/en/mysqld-multi.html
However, before you setup an instance to be managed by mysqld_multi, you
will need to instantiate a set of datafiles for that 5.7 instance of the
mysqld daemon to manage. You do that following the directions here (by
hand) the first time.
https://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization.html
This means you need to setup at least two folders (one for --datadir and
one for --tmpdir) for each separate instance you want to create and
assign ownership and privileges to those folders appropriate to the user
your mysqld daemon will be executing as when it runs. There are other
things you must also keep unique between instances when they share a
common host machine. Those are described here:
https://dev.mysql.com/doc/refman/5.7/en/multiple-servers.html
An example of setting up the folders and assigning privileges to them is
located in the instructions to installing a set of mysqld binaries using
a .zip or .tar archive. Please note, you do not need a separate mysqld
installation for each instance you want to create. Several daemons (each
operating on their own port, socket, folders, data files,... ) can be
started using just one set of binary files.
https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html
So... the general process would look like this (presuming you have
already installed mysqld and setup at least one instance)
==
1) Decide where you want a second (or later) instance to store its
files. Choose port numbers and unix socket names for this new instance
that are unique from any other instances that will be running on this host.
2) Setup any new folders you need to create (including assigning privileges)
3) Document those names and any other settings you want this additional
instance to use in a configuration file specific for this instance
4) Use that special configuration file to bootstrap (initialize) the
data files used to manage that instance (the --initialize instructions
were linked to earlier in this reply)
5) Once you have this instance setup the way you want. Shut it down.
6) Copy the elements that are unique to this instance into an
appropriately-named section of your common configuration file (the one
that mysqld_multi will read)
7) Test that you can start/stop this new instance using mysqld_multi
As you can tell, it takes a bit of planning and effort to establish a
non-default setup of hosting multiple MySQL instances on the same host
machine. There is no simple one-line command to tell mysqld_multi to
create a new instance as there are things it cannot do (like create
folders in your file system).
Regards,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN
Become certified in MySQL! Visit https://www.mysql.com/certification/
for details.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql