changing default my.cnf path in mysql
I am having two mysql instances on same machine. The installations are on /usr/loca/mysql1 and /usr/local/mysql2.
I m having separate my.cnf files located in /etc/mysql1 and /etc/mysql2. I installed the first instance of my sql using source distribution and with the --prefix=/usr/local/mysql1 option. The second one i got from copying and pastinf the same directory to /usr/local/mysql2.
When i start the mysql daemon on /usr.local/mysql/libexec it reads the my.cnf file in /etc/mysql1. And if i start the mysql daemon in /usr/local/mysql2 it reads the same my.cnf file. I have separate port numbers and .sock files defined in the .cnf file in those 2 locations.
I can read the my.cnf file in the second location by using --defaults-file=/etc/mysql2/my.cnf option on mysqld startup. I do not need to enter this each and every time i start the daemon.
If i am going to have more instances how can i point the correct my.cnf file to read to each and every mysql daemon. What is the retionale behind mysqld links with the my.cnf file.
how can i predefine the location of my.cnf file for each instance.
Answer
As you've already discovered, MySQL has a compiled-in search location for its configuration file. Although you could recompile yourself, changing this, you've also discovered the --defaults-file option to mysqld, which instructs it to use an entirely different configuration path. Coupled with --data-dir, this means you can start multiple instances of MySQL bound to different ports (and addresses, if liked) and working with entirely separate sets of data, while working off the same binaries and libraries.
Traditionally, most operating system distributions will bundle a single init script for starting the "default" instance of MySQL; that is, the one installed in the "usual" location, and with the standard configuration path. Although this is to cater for the commonest case, what you're after is a bit different, so you'll need to create separate scripts to launch the separate instances.
If you're planning on deploying a lot of MySQL instances on the same machine (and I'd have to ask why), then you may want to write a custom init script which has some way of "discovering" each of these (perhaps by inspecting some directory containing a "common" layout), and then loops over them, starting each one up. Of course, the same init script then needs to be capable of locating and properly shutting down each one.