Web lists-archives.com

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.


-----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.

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.

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:

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.

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).

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