Re: mysql5 options file location
Hi Alex. It seems that mysqld and all the client programs insist on reading /etc/my.cnf first. To overide this behaviour for a particular instance of mysqld you need to pass the --defaults-file option as the FIRST parameter to mysqld_safe or mysqld if you are not using mysqld_safe. --defaults-extra-file=path The name of an option file to be read in addition to the usual option files. If given, this option must be first. --defaults-file=path The name of an option file to be read instead of the usual option files. If given, this option must be first. --no-defaults Do not read any option files. If given, this option must be first. More options are found in /usr/local/mysql-5.0.18/man/man1/mysqld_safe.1 I found this behaviour very annoying at first. But I now use this to my advantage. I have split my my.cnf file like this: /etc/my.cnf only has parameters used by mysql client programs. the my.cnf that lives in the mysqld installation directory only contains directives pertinent to that particular version of mysqld, and nothing there for the client programs. That way you have total control over all the parameters passed to all client programs in one central place, /etc/my.cnf. You do not have to worry about mysqld reading the wrong parameters for its invocation, because you have a seperate my.cnf just for that version of mysqld. I have written the following script to start a particular version of mysqld. This lives in /usr/local/mysql-version/bin/ #! /bin/sh # # start the MySQL database server /usr/local/mysql-5.0.18/bin/mysqld \ --defaults-file=/usr/local/mysql-5.0.18/my.cnf \ --port= \ --socket=/var/lib/mysql/mysql.sock \ --pid=/var/lib/mysql/laptop.pid \ --user=mysql \ --datadir=/var/lib/mysql And this script gets called from /etc/init.d/boot.local when the machine boots up. /etc/init.d/halt.local calls the following script to shutdown the mysqld server gracefully. #! /bin/sh # # stop the MySQL database server /usr/local/mysql-5.0.18/bin/mysqladmin shutdown \ -usqlsuperuser -p \ --socket=/var/lib/mysql/mysql.sock The other advantage of passing parameters on the command-line to mysqld is that you can actually see, using a visual process manager like http://www.student.nada.kth.se/~f91-men/qps/ * if mysqld is running OK * what parameters you passed to mysqld, eg the port, socket, datadir etc, each mysqld is using * how many different versions of mysqld you have running I find this very helpfull when running two versions of mysqld at a time, eg testing a newer version against an already installed version, before removing the older version. HTH Keith In theory, theory and practice are the same; In practice they are not. On Mon, 13 Mar 2006, Alex Moore wrote: To: mysql@lists.mysql.com From: Alex Moore [EMAIL PROTECTED] Subject: mysql5 options file location I am building mysql5 latest from source on Solaris. The location of the options file is very confusing and does not work according to the online documentation. For example, I have --basedir of /opt/csw/mysql5 and --datadir of /opt/csw/mysql5/var. If I put my.cnf in datadir or in basedir, the file is not used. I am using mysqld_safe to start mysqld. my.cnf options will only work if I put the file in /etc/ './libexec/mysqld --verbose --help' returns: Default options are read from the following files in the given order: /etc/my.cnf ~/.my.cnf This is very different from my mysql4 builds from source, which include the documented server-specific file listed after /etc/my.cnf How can I get mysql5 to use a server-specfic options file? Am I missing a configure option or defines for mysql5? Thanks, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql5 options file location
On Mon, 13 Mar 2006 16:52:03 + (GMT) [EMAIL PROTECTED] wrote: Hi Alex. It seems that mysqld and all the client programs insist on reading /etc/my.cnf first. To overide this behaviour for a particular instance of mysqld you need to pass the --defaults-file option as the FIRST parameter to mysqld_safe or mysqld if you are not using mysqld_safe. Yes, I understand everything that you said Keith and have used the options file logic to my advantage as well. Thanks for the full description. What is not working for me is that the server-specific file does not appear to be built into all of the objects like it was in 4.1. For example, my.cnf is in basedir. my.cnf has a [mysqld] group that defines many options like various logging selections and tuning. I do not want those options in a global file since I am not supposed to write to /etc/. I am using a directory structure similar to /usr/local/mysql5, but it is actually /opt/csw/mysql5. The server-specific options are read on mysqld_safe at startup, as evidenced by `mysqladmin variables`. Without a /etc/my.cnf file, 'my_print_defaults mysqld' prints nothing. With a /etc/my.cnf file, 'my_print_defaults mysqld' prints only the options in /etc/my.cnf. This is very different from 4.1 and causing me a lot of headaches and is probably my biggest issue with 5.0. I could go on about the utilities that do not work without a global options file. Another clue about this change from 4.1 is the `mysqld --verbose --help` output not listing a server-specific options file on 5.0 I just wondered if I did something wrong or if I can just not use some utilities in 5.0 The situation with 5.0 and the options file is really much bigger than what I have described. I have big problems with the 5.0 location of the server-specific options file moving from datadir to basedir. But that is another discussion. Thanks, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql5 options file location
In your global /etc/my.cnf fle, you should be able to split that into sections for each client program that you want to set specific options for. Eg. # /etc/my.cnf # MySQL client program configuration file # last updated 2006-03-05 # mysqld server configuration file lives in # /usr/local/mysql-version/my.cnf #--- # mysql client program configuration options #--- [mysql] no-auto-rehash # needed for security - to stops multiple deletes/updates # without a where clause safe-updates # # The following options will be passed to all MySQL clients [client] socket = /var/lib/mysql/mysql.sock port = 7000 # [myisamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M # [mysqldump] quick set-variable = max_allowed_packet=16M # # available programs/scripts are: (from 4.0.21 version) #my_print_defaults mysqladmin #myisamchk mysqlbinlog #myisamlog mysqlbug - n/a #myisampack mysqlcheck #mysql mysqld #mysql_convert_table_format - .plmysqld_multi #mysql_find_rows mysqldump #mysql_fix_privilege_tables n/a mysqlhotcopy - .pl #mysql_install_dbmysqlimport #mysql_setpermission - .pl mysqlshow #mysql_zap mysqltest #mysqlaccess - .pl safe_mysqld # # end of mysql client program configurations # /etc/my.cnf Take a look at the man pages for the client programs, as well as mysqld_safe and mysqld, in /opt/csw/mysql5/man/man1/ Most of the options for client programs are listed there. Exactly what are the problems you are having with the server specific my.cnf file? AFAIK it should be in basedir, and not normally in datadir. The log files are OK to have in datadir - that's the default place SuSE Linux puts them. HTH Keith In theory, theory and practice are the same; In practice they are not. On Mon, 13 Mar 2006, Alex Moore wrote: To: mysql@lists.mysql.com From: Alex Moore [EMAIL PROTECTED] Subject: Re: mysql5 options file location On Mon, 13 Mar 2006 16:52:03 + (GMT) [EMAIL PROTECTED] wrote: Hi Alex. It seems that mysqld and all the client programs insist on reading /etc/my.cnf first. To overide this behaviour for a particular instance of mysqld you need to pass the --defaults-file option as the FIRST parameter to mysqld_safe or mysqld if you are not using mysqld_safe. Yes, I understand everything that you said Keith and have used the options file logic to my advantage as well. Thanks for the full description. What is not working for me is that the server-specific file does not appear to be built into all of the objects like it was in 4.1. For example, my.cnf is in basedir. my.cnf has a [mysqld] group that defines many options like various logging selections and tuning. I do not want those options in a global file since I am not supposed to write to /etc/. I am using a directory structure similar to /usr/local/mysql5, but it is actually /opt/csw/mysql5. The server-specific options are read on mysqld_safe at startup, as evidenced by `mysqladmin variables`. Without a /etc/my.cnf file, 'my_print_defaults mysqld' prints nothing. With a /etc/my.cnf file, 'my_print_defaults mysqld' prints only the options in /etc/my.cnf. This is very different from 4.1 and causing me a lot of headaches and is probably my biggest issue with 5.0. I could go on about the utilities that do not work without a global options file. Another clue about this change from 4.1 is the `mysqld --verbose --help` output not listing a server-specific options file on 5.0 I just wondered if I did something wrong or if I can just not use some utilities in 5.0 The situation with 5.0 and the options file is really much bigger than what I have described. I have big problems with the 5.0 location of the server-specific options file moving from datadir to basedir. But that is another discussion. Thanks, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql5 options file location
On Mon, 13 Mar 2006 23:05:30 + (GMT) [EMAIL PROTECTED] wrote: Exactly what are the problems you are having with the server specific my.cnf file? Sorry, I thought that I had described the problem. A quick example was 'my_print_defaults mysqld' returning only the options defined in the global file. None of the options from the server-specific my.cnf are returned. This is not the way 4.1, and probably earlier, works. Thanks, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql5 options file location
I get the same results as you do using this: karsites:/usr/local/mysql-5.0.18/bin # ./my_print_defaults mysqld karsites:/usr/local/mysql-5.0.18/bin # ./my_print_defaults client --socket=/var/lib/mysql/mysql.sock --port= karsites:/usr/local/mysql-5.0.18/bin # It seems like the last parameter passed to my_print_defaults tells my_print_defaults to get that particular section from the/etc/my.cnf file and print it out. However, you can pass a parameter to my_print_defaults, to tell it which my.cnf file to check. So doing this: karsites:/usr/local/mysql-5.0.18/bin # ./my_print_defaults --defaults-file=/usr/local/mysql-5.0.18/my.cnf mysqld --basedir=/usr/local/mysql-5.0.18 --server-id=1 --skip-name-resolve --skip-locking --set-variable=key_buffer=16M --set-variable=max_allowed_packet=1M --set-variable=table_cache=64 --set-variable=sort_buffer=512K --set-variable=net_buffer_length=8K --set-variable=myisam_sort_buffer_size=8M --log=5-0-18.log --log-bin=laptop-bin --log-error=5-0-18.error-log --log-queries-not-using-indexes --log-slow-admin-statements --log-slow-queries=5-0-18.slow-log --log-warnings karsites:/usr/local/mysql-5.0.18/bin # Does actually return the correct my.cnf file details. You can also use the -c /usr/local... shorthand option to tell my_print_defaults which my.cnf to look at. 'my_print_defaults --help' will return all the available options you can use. This is actually a Linux ELF file, and not a shell script Alex. That's something I have also just learned, so I'm pleased you mentioned it, because I was having the same problem, until I checked the options available, using --help. Are there any other problems you have encountered using this type of my.cnf setup? Keith In theory, theory and practice are the same; In practice they are not. On Mon, 13 Mar 2006, Alex Moore wrote: To: mysql@lists.mysql.com From: Alex Moore [EMAIL PROTECTED] Subject: Re: mysql5 options file location On Mon, 13 Mar 2006 23:05:30 + (GMT) [EMAIL PROTECTED] wrote: Exactly what are the problems you are having with the server specific my.cnf file? Sorry, I thought that I had described the problem. A quick example was 'my_print_defaults mysqld' returning only the options defined in the global file. None of the options from the server-specific my.cnf are returned. This is not the way 4.1, and probably earlier, works. Thanks, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
