In most cases you should manage mysqld options through option files. Section 4.1.2, “my.cnf Option Files ”.
mysqld and mysqld.server read options from the mysqld and server groups. mysqld_safe read options from the mysqld, server, mysqld_safe and safe_mysqld groups. An embedded MySQL server usually reads options from the server, embedded and xxxxx_SERVER, where xxxxx is the name of the application.
mysqld accepts a lot of command-line options. Here follows some of the most common ones. For a full list execute mysqld -help. Options used for replication are listed in a separate section, see Section 4.10.6, “Replication Options in my.cnf”.
Use SQL-99 syntax instead of MySQL syntax. Section 1.7.2, “Running MySQL in ANSI Mode ”.
Path to installation directory. All paths are usually resolved relative to this.
Allow big result sets by saving all temporary sets on file. It solves most 'table full' errors, but also slows down the queries where in-memory tables would suffice. Since Version 3.23.2, MySQL is able to solve it automatically by using memory for small temporary tables and switching to disk tables where necessary.
IP address to bind to.
Write the error log messages to stderr/stdout even if -log-error is specified. On Windows, mysqld will not close the console screen if this option is used.
Directory where character sets are. Section 4.6.1, “The Character Set Used for Data and Sorting ”.
Put mysqld daemon in chroot environment at startup. Recommended security measure since MySQL 4.0 (MySQL 3.23 is not able to provide 100% closed chroot jail). It somewhat limits LOAD DATA INFILE and SELECT ... INTO OUTFILE though.
Write a core file if mysqld dies. For some systems you must also specify -core-file-size to mysqld_safe. Section 4.7.2, “mysqld_safe, The Wrapper Around mysqld”. Note that on some systems, like Solaris, you will not get a core file if you are also using the -user option.
Path to the database root.
If MySQL is configured with -with-debug, you can use this option to get a trace file of what mysqld is doing. Section E.1.2, “Creating Trace Files ”.
Set the default character set. Section 4.6.1, “The Character Set Used for Data and Sorting ”.
Set the default table type for tables. Chapter 7, MySQL Table Types .
How MyISAM DELAYED KEYS should be used. Section 5.5.2, “Tuning Server Parameters ”.
Don't flush key buffers between writes for any MyISAM table. Section 5.5.2, “Tuning Server Parameters ”.
Read the default keys used by DES_ENCRYPT() and DES_DECRYPT() from this file.
Enable system locking. Note that if you use this option on a system on which lockd does not fully work (as on Linux), you will easily get mysqld to deadlock.
Enable support for named pipes (only on NT/Win2000/XP).
This is a bit mask of different flags one can use for debugging the mysqld server; one should not use this option if one doesn't know exactly what it does!
Flush all changes to disk after each SQL command. Normally MySQL only does a write of all changes to disk after each SQL command and lets the operating system handle the syncing to disk. Section A.4.1, “What To Do If MySQL Keeps Crashing ”.
Display short help and exit.
Read SQL commands from this file at startup.
Client error messages in given language. May be given as a full path. Section 4.6.2, “Non-English Error Messages ”.
Log connections and queries to file. Section 4.9.2, “The General Query Log ”.
Log all queries that change data to the file. Used for backup and replication. Section 4.9.4, “The Binary Log ”.
Index file for binary log file names. Section 4.9.4, “The Binary Log ”.
Log errors and startup messages to this file. Section 4.9.1, “The Error Log ”.
Log all ISAM/MyISAM changes to file (only used when debugging ISAM/MyISAM).
Log all queries that have taken more than long_query_time seconds to execute to file. Section 4.9.5, “The Slow Query Log ”.
Log updates to file.# where # is a unique number if not given. Section 4.9.3, “The Update Log ”. The update log is deprecated and will be removed in MySQL 5.0; you should use the binary log instead (-log-bin). Section 4.9.4, “The Binary Log ”. Starting from version 5.0, using -log-update will just turn on the binlog instead.
Log some extra information to the update log. If you are using -log-slow-queries then queries that are not using indexes are logged to the slow query log.
Table-modifying operations (INSERT/DELETE/UPDATE) will have lower priority than selects. It can also be done via {INSERT | REPLACE | UPDATE | DELETE} LOW_PRIORITY ... to lower the priority of only one query, or by SET LOW_PRIORITY_UPDATES=1 to change the priority in one thread. Section 5.3.2, “Table Locking Issues ”.
Lock the mysqld process in memory. This works only if your system supports the mlockall() system call (like Solaris). This may help if you have a problem where the operating system is causing mysqld to swap on disk. Note that use of this option requires that you run the server as root, which is normally not a good idea for security reasons.
Option is any combination of DEFAULT, BACKUP, FORCE or QUICK. You can also set this explicitly to "" if you want to disable this option. If this option is used, mysqld will on open check if the table is marked as crashed or if the table wasn't closed properly. (The last option only works if you are running with -skip-external-locking.) If this is the case mysqld will run check on the table. If the table was corrupted, mysqld will attempt to repair it.
The following options affects how the repair works.
| Option | Description |
| DEFAULT | The same as not giving any option to -myisam-recover. |
| BACKUP | If the data table was changed during recover, save a backup of the table_name.MYD datafile as table_name-datetime.BAK. |
| FORCE | Run recover even if we will lose more than one row from the .MYD file. |
| QUICK | Don't check the rows in the table if there aren't any delete blocks. |
Before a table is automatically repaired, MySQL will add a note about this in the error log. If you want to be able to recover from most things without user intervention, you should use the options BACKUP,FORCE. This will force a repair of a table even if some rows would be deleted, but it will keep the old datafile as a backup so that you can later examine what happened.
From version 4.0.12, the -new option can be used to make the server behave as 4.1 in certain aspects, easing a 4.0 to 4.1 upgrade:
TIMESTAMP is returned as a string with the format 'YYYY-MM-DD HH:MM:SS'. Section 6.2, “Column Types ”.
Path to pid file used by mysqld_safe.
Port number to listen for TCP/IP connections.
Use the 3.20 protocol for compatibility with some very old clients. Section 2.5.5, “Upgrading from Version 3.20 to 3.21 ”.
Only use one thread (for debugging under Linux). Section E.1, “Debugging a MySQL server ”.
To change the number of file descriptors available to mysqld. If this is not set or set to 0, then mysqld will use this value to reserve file descriptors to use with setrlimit(). If this value is 0 then mysqld will reserve max_connections*5 or max_connections + table_cache*2 (whichever is larger) number of files. You should try increasing this if mysqld gives you the error 'Too many open files'.
Give a variable a value. -help lists variables. You can find a full description for all variables in the SHOW VARIABLES section in this manual. Section 4.5.7.4, “SHOW VARIABLES”. The tuning server parameters section includes information of how to optimise these. Please note that -set-variable is deprecated since MySQL 4.0, just use -var=option on its own. Section 5.5.2, “Tuning Server Parameters ”.
In MySQL 4.0.2 one can set a variable directly with -variable-name=option and set-variable is no longer needed in option files.
If you want to restrict the maximum value a startup option can be set to with SET, you can define this by using the -maximum-variable-name command line option. Section 5.5.6, “SET Syntax ”.
Note that when setting a variable to a value, MySQL may automatically correct it to stay within a given range and also adjusts the value a little to fix for the used algorithm.
Skip some optimise stages.
With this option, the SHOW DATABASES command returns only those databases for which the user has some kind of privilege. From version 4.0.2 this option is deprecated and doesn't do anything (the option is enabled by default) as we now have the SHOW DATABASES privilege. Section 4.3.1, “GRANT and REVOKE Syntax ”.
If this is enabled, a user can't create new users with the GRANT command, if the user doesn't have INSERT privilege to the mysql.user table or any column in this table.
Disable usage of BDB tables. This will save memory and may speed up some things.
Turn off the ability to select and insert at the same time on MyISAM tables. (This is only to be used if you think you have found a bug in this feature.)
In MySQL 4.0.3 you should use -delay-key-write=OFF instead. Ignore the DELAY_KEY_WRITE option for all tables. Section 5.5.2, “Tuning Server Parameters ”.
This option causes the server not to use the privilege system at all. This gives everyone full access to all databases! (You can tell a running server to start using the grant tables again by executing mysqladmin flush-privileges or mysqladmin reload.)
Never use host name cache for faster name-ip resolution, but query DNS server on every connect instead. Section 5.5.5, “How MySQL uses DNS ”.
Disable usage of Innodb tables. This will save memory and disk space and speed up some things.
Don't use system locking. To use isamchk or myisamchk you must shut down the server. Section 1.2.3, “How Stable Is MySQL? ”. Note that in MySQL Version 3.23 you can use REPAIR and CHECK to repair/check MyISAM tables.
Hostnames are not resolved. All Host column values in the grant tables must be IP numbers or localhost. Section 5.5.5, “How MySQL uses DNS ”.
Don't listen for TCP/IP connections at all. All interaction with mysqld must be made via named pipes or Unix sockets. This option is highly recommended for systems where only local requests are allowed. Section 5.5.5, “How MySQL uses DNS ”.
Don't use new, possibly wrong routines.
Deprecated option in 4.0.13; use -skip-symbolic-links instead.
Enable or disable symbolic link support. This option has different effects on Windows and Unix.
On Windows, enabling symbolic links allows you to establish a symbolic link to a database directory by creating a directory.sym file that contains the path to the real directory. Section 2.6.1.5, “Distributing Data Across Different Disks on Windows ”.
On Unix, enabling symbolic links means that you can link a MyISAM index file or datafile to another directory with the INDEX DIRECTORY or DATA DIRECTORY options of the CREATE TABLE statement. If you delete or rename the table, the files that its symbolic links point to also will be deleted or renamed.
If MySQL is configured with -with-debug=full, all programs check memory for overruns for every memory allocation and memory freeing operations. This checking is very slow, so for the server you can avoid it when you don't need it by using the -skip-safemalloc option.
Don't allow the SHOW DATABASES command, unless the user has the SHOW DATABASES privilege.
Don't write stack traces. This option is useful when you are running mysqld under a debugger. On some systems, you also must use this option to get a core file. Section E.1, “Debugging a MySQL server ”.
Disable using thread priorities for faster response time.
On Unix, the socket file to use for local connections (default /tmp/mysql.sock). On Windows, the pipe name to use for local connections that use a named pipe (default MySql).
The option values can be any combination of: REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ONLY_FULL_GROUP_BY, NO_UNSIGNED_SUBTRACTION, NO_AUTO_VALUE_ON_ZERO, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_DIR_IN_CREATE, MYSQL323, MYSQL40, DB2, MSSQL, ORACLE, POSTGRESQL, SAPDB, or ANSI. The value also can be empty (-sql-mode="") if you want to reset it.
NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number. This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column (this is not recommended, by the way). For example, if you dump the table with mysqldump and then reload it, normally MySQL will generate new sequence numbers when it encounters the 0 values, resulting in a table with different contents than the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem.
Several of the option values are used for compatibility with other servers. If specified, they cause the server to omit from the output of SHOW CREATE TABLE those parts of the statement that are not understood by earlier versions of MySQL or other database servers. Using these option values results in CREATE TABLE statements that are more portable for use with other servers:
The NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_DIR_IN_CREATE, and NO_KEY_OPTIONS values cause omission of table options, or options pertaining to column or index definitions.
The values MYSQL323 and MYSQL40 are for compatibility with MySQL 3.23 and MySQL 4.0.
The values used for compatibility with other servers are DB2, MSSQL, ORACLE, POSTGRESQL, and SAPDB.
These options also affect the output of mysqldump, because that program uses SHOW CREATE TABLE to obtain the table-creation statements that it includes in its own output.
Several of the option values have a complex effect because they are shorthand for a group or set of values. For example, you can tell the server to run in ANSI mode by using the -sql-mode=ANSI (or -ansi) option, which is equivalent to specifying both of the following command-line options:
--sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY --transaction-isolation=SERIALIZABLE
Note that specifying ANSI mode in this way also has the effect of setting the transaction isolation level. For more information about running the server in ANSI mode, see Section 1.7.2, “Running MySQL in ANSI Mode ”.
Other "group" values are DB2, MSSQL, ORACLE, POSTGRESQL, and SAPDB. Specifying any of them turns on the PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, and NO_KEY_OPTIONS values.
The -sql-mode option was added in MySQL 3.23.41. The NO_UNSIGNED_SUBTRACTION value was added in 4.0.0. NO_DIR_IN_CREATE was added in 4.0.15. NO_AUTO_VALUE_ON_ZERO, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, MYSQL323, MYSQL40, DB2, MSSQL, ORACLE, POSTGRESQL, SAPDB, and ANSI were added in 4.1.1.
Using this option will cause most temporary files created by the server to use a small set of names, rather than a unique name for each new file. This is to work around a problem in the Linux kernel dealing with creating many new files with different names. With the old behaviour, Linux seems to "leak" memory, as it's being allocated to the directory entry cache rather than to the disk cache.
Sets the default transaction isolation level. Section 6.7.4, “SET TRANSACTION Syntax ”.
Path of the directory to use for creating temporary files. It may be useful if your default /tmp directory resides on a partition that is too small to hold temporary tables. Starting from MySQL 4.1, this option accepts several paths that are used in round-robin fashion. Paths should be separated by colon characters (:) on Unix and semicolon characters (;) on Windows.
Run the mysqld server as the user having the name user_name or numeric user ID user_id. ("User" in this context refers to a system login account, not a MySQL user listed in the grant tables.)
This option is mandatory when starting mysqld as root. The server will change its user ID during its startup sequence, causing it to run as that particular user rather than as root. Section 4.2.2, “How to Make MySQL Secure Against Crackers ”.
Starting from MySQL 3.23.56 and 4.0.12: To avoid a possible security hole where a user adds a -user=root option to some my.cnf file (thus causing the server to run as root), mysqld uses only the first -user option specified and produces a warning if there are multiple -user options. Options in /etc/my.cnf and datadir/my.cnf are processed before command-line options, so it is recommended that you put a -user option in /etc/my.cnf and specify a value other than root. The option in /etc/my.cnf will be found before any other -user options, which ensures that the server runs as a user other than root, and that a warning results if any other -user option is found.
Display version information and exit.
Print out warnings like Aborted connection... to the .err file. Enabling this option is recommended, for example, if you use replication (you will get more information about what is happening, such as messages about network failures and reconnections). Section A.2.10, “Communication Errors / Aborted Connection ”.
This option used to be called -warnings.
You can change most values for a running server with the SET command. Section 5.5.6, “SET Syntax ”.
MySQL can, since Version 3.22, read default startup options for the server and for clients from option files.
On Windows, MySQL reads default options from the following files:
| Filename | Purpose |
| windows-directory\my.ini | Global options |
| C:\my.cnf | Global options |
windows-directory is the location of your Windows directory.
On Unix, MySQL reads default options from the following files:
| Filename | Purpose |
| /etc/my.cnf | Global options |
| DATADIR/my.cnf | Server-specific options |
| defaults-extra-file | The file specified with -defaults-extra-file=path |
| ~/.my.cnf | User-specific options |
DATADIR is the MySQL data directory (typically /usr/local/mysql/data for a binary installation or /usr/local/var for a source installation). Note that this is the directory that was specified at configuration time, not the one specified with -datadir when mysqld starts up! (-datadir has no effect on where the server looks for option files, because the server looks for files before it processes any command-line arguments.)
Note that on Windows you should specify all paths in option files with / instead of \. If you use \, you need to specify it twice, because \ is the escape character in MySQL.
MySQL tries to read option files in the order listed above. If multiple option files exist, an option specified in a file read later takes precedence over the same option specified in a file read earlier. Options specified on the command-line take precedence over options specified in any option file. Some options can be specified using environment variables. Options specified on the command-line or in option files take precedence over environment variable values. Appendix F, Environment Variables .
The following programs support option files: mysql, mysqladmin, mysqld, mysqld_safe, mysql.server, mysqldump, mysqlimport, mysqlshow, mysqlcheck, myisamchk, and myisampack.
Since Version 4.0.2, you can use the loose prefix for command-line options (or options in my.cnf). If an option is prefixed by loose, the program reading it will not exit with an error if an option is unknown, but will rather only issue a warning:
shell mysql --loose-no-such-option
Any long option that may be given on the command-line when running a MySQL program can be given in an option file as well (without the leading double dash). Run the program with the -help option to get a list of available options.
An option file can contain lines of the following forms:
Comment lines start with # or ;. Empty lines are ignored.
group is the name of the program or group for which you want to set options. After a group line, any option or set-variable lines apply to the named group until the end of the option file or another group line is given.
This is equivalent to -option on the command-line.
This is equivalent to -option=value on the command-line.
This is equivalent to -set-variable variable=value on the command-line. Please note that -set-variable is deprecated since MySQL 4.0; as of that version, program variable names can be used as option names. On the command line, just use -variable=value. In an option file, use variable=value.
The [client] group allows you to specify options that apply to all MySQL clients (not mysqld). This is the perfect group to use to specify the password that you use to connect to the server. (But make sure the option file is readable and writable only by yourself.)
Note that for options and values, all leading and trailing blanks are automatically deleted. You may use the escape sequences \b, \t, \n, \r, \\, and \s in your value string (\s == blank).
Here is a typical global option file:
[client] port=3306 socket=/tmp/mysql.sock [mysqld] port=3306 socket=/tmp/mysql.sock set-variable = key_buffer_size=16M set-variable = max_allowed_packet=1M [mysqldump] quick
Here is typical user option file:
[client] # The following password will be sent to all standard MySQL clients password=my_password [mysql] no-auto-rehash set-variable = connect_timeout=2 [mysqlhotcopy] interactive-timeout
If you have a source distribution, you will find sample configuration files named my-xxxx.cnf in the support-files directory. If you have a binary distribution, look in the DIR/support-files directory, where DIR is the pathname to the MySQL installation directory (typically C:\mysql or /usr/local/mysql). Currently there are sample configuration files for small, medium, large, and very large systems. You can copy my-xxxx.cnf to your home directory (rename the copy to .my.cnf) to experiment with this.
All MySQL programs that support option files support the following options:
| Option | Description |
| -no-defaults | Don't read any option files. |
| -print-defaults | Print the program name and all options that it will get. |
| -defaults-file=full-path-to-default-file | Only use the given configuration file. |
| -defaults-extra-file=full-path-to-default-file | Read this configuration file after the global configuration file but before the user configuration file. |
Note that the options just shown must be first on the command line to work, with the exception that -print-defaults may be used immediately after -defaults-file or -defaults-extra-file.
Note for developers: Option file handling is implemented simply by processing all matching options (that is, options in the appropriate group) before any command-line arguments. This works nicely for programs that use the last instance of an option that is specified multiple times. If you have an old program that handles multiply specified options this way but doesn't read option files, you need add only two lines to give it that capability. Check the source code of any of the standard MySQL clients to see how to do this.
In shell scripts, you can use the my_print_defaults command to parse the option files. The following example shows the output that my_print_defaults might produce when asked to show the options found in the [client] and [mysql] groups:
shell my_print_defaults client mysql --port=3306 --socket=/tmp/mysql.sock --no-auto-rehash
In some cases you might want to run multiple mysqld servers on the same machine. For example, you might want to test a new MySQL release while leaving your existing production setup undisturbed. Or you may want to give different users access to different mysqld servers that they manage themselves. (For example, you might be an Internet service provider that wants to provide independent MySQL installations for different customers.)
When you run multiple servers on a single machine, each server must have unique values for several operating parameters. At least the following options must be different for each server:
-port=port_num
-socket=path
-shared-memory-base-name=name (Windows only; new in MySQL 4.1)
-pid-file=path (Unix only)
-port controls the port number for TCP/IP connections. -socket controls the socket file path on Unix and the name of the named pipe on Windows. (It's necessary to specify distinct pipe names on Windows only for those servers that support named pipe connections.) -shared-memory-base-name designates the shared memory name used by a Windows server to allow clients to connect via shared memory. -pid-file indicates the name of the file in which a Unix server writes its process ID.
If you use the following options, they must be different for each server:
-log=path
-log-bin=path
-log-update=path
-log-error=path
-log-isam=path
-bdb-logdir=path
If you want more performance, you can also specify the following options differently for each server, to spread load between several physical disks:
-tmpdir=path
-bdb-tmpdir=path
Section 4.1.1, “mysqld Command-line Options ”.
Generally, each server should also use a different data directory, which is specified using the -datadir=path option.
If you have multiple MySQL installations in different locations, normally you can specify the base installation directory for each server with the -basedir=path option to cause each server to use a different data directory, log files, and PID file. (The defaults for all these values are determined relative to the base directory.) In that case, the only other options you need to specify are the -socket and -port options. For example, suppose you install binary MySQL versions (.tar files) in different locations and start them using the command ./bin/mysqld_safe under the corresponding base directory of each installation. mysqld_safe will determine the proper -basedir option to pass to mysqld, and you need specify only the -socket and -port options to mysqld_safe.
As discussed in the following sections, it is possible to start additional servers by setting environment variables or by specifying appropriate command-line options. However, if you need to run multiple servers on a more permanent basis, it will be more convenient to use option files to specify for each server those option values that must be unique to it. Section 4.1.2, “my.cnf Option Files ”.
Warning: Normally you should never have two servers that update data in the same databases! If your OS doesn't support fault-free system locking, this may lead to unpleasant surprises! If (despite this warning) you run multiple servers using the same data directory and they have logging enabled, you must specify the names of the log files using the appropriate options. Otherwise, the servers may try to log to the same files.
This warning against sharing a data directory among servers also applies in an NFS environment. Allowing several MySQL servers to access a common data directory over NFS is a bad idea!
The primary problem is that NFS will become the speed bottleneck. It is not meant for such use.
You also will have to come up with a solution how to make sure that two or more servers do not interfere with each other. At the moment there is no platform that will 100% reliably do the file locking in every situation (usually this is handled by the lockd daemon). Yet there would be one more possible risk with NFS; it would make the work even more complicated for lockd daemon to handle.
Make it easy for yourself: Forget about sharing a data directory among servers over NFS. A better solution is to have one computer with an operating system that efficiently handles threads and have several CPUs in it.
You can run multiple servers on Windows by starting them manually from the command line, each with appropriate operating parameters. On Windows NT-based systems, you also have the option of installing several servers as services and running them that way. General instructions for running MySQL servers from the command line or as services are given in Section 2.6.1, “Windows Notes ”. This section describes how to make sure you start each server with different values for those startup options that must be unique per server, such as the data directory. (These options are described in Section 4.1.3, “Running Multiple MySQL Servers on the Same Machine ”.)
To start multiple servers manually from the command line, you can specify the appropriate options on the command line or in an option file. It's more convenient to place the options in an option file, but it's necessary to make sure that each server gets its own set of options. To do this, create an option file for each server and tell the server the filename with a -defaults-file option when you run it.
Suppose you want to run mysqld on port 3307 with a data directory of C:\mydata1, and mysqld-max on port 3308 with a data directory of C:\mydata2. To accomplish this, create two option files. For example, create one file C:\my-opts1.cnf that looks like this:
[mysqld] datadir = C:/mydata1 port = 3307
Create a second file C:\my-opts2.cnf that looks like this:
[mysqld] datadir = C:/mydata2 port = 3308
Then start each server with its own option file:
shell mysqld --defaults-file=C:\my-opts1.cnf shell mysqld-max --defaults-file=C:\my-opts2.cnf
(On NT, the servers will start in the foreground, so you'll need to issue those two commands in separate console windows.)
To shut down the servers, you must connect to the appropriate port number:
shell mysqladmin --port=3307 shutdown shell mysqladmin --port=3308 shutdown
If you want to allow named pipe connections in addition to TCP/IP connections, use the mysqld-nt or mysqld-max-nt servers and specify options that enable the named pipe and specify its name. (Each server must have a unique pipe name.) For example, the C:\my-opts1.cnf file might be written like this:
[mysqld] datadir = C:/mydata1 port = 3307 enable-named-pipe socket = mypipe1
Then start the server this way:
shell mysqld-nt --defaults-file=C:\my-opts1.cnf
C:\my-opts2.cnf would be modified similarly.
On NT-based systems, you can install multiple servers as services. (This is possible as of MySQL 4.0.2.) In this case, you must make sure that each server uses a different service name in addition to all the other parameters that must be unique per server.
For the following instructions, assume that you want to run mysqld-nt servers from two different versions of MySQL that are installed at C:\mysql-4.0.8 and C:\mysql-4.0.14, respectively. (This might be the case if you're running 4.0.8 as your production server, but want to test 4.0.14 before upgrading to it.)
The following principles are relevant when installing a MySQL service with the -install (or -install-manual) option:
If you specify no service name, the server uses the default service name of MySql and the server reads options from the [mysqld] group in the standard option files.
If you specify a service name after the -install option, the server ignores the [mysqld] option group and instead reads options from the group that has the same name as the service.
If you specify a -defaults-file option after the service name, the server ignores the standard option files and reads options only from the [mysqld] group of the named file.
These principles give you several ways to set up multiple services. The following instructions describe some examples. Before trying any of them, be sure you shut down and remove any existing MySQL services first.
Specify the options for all services in one of the standard option files. To do this, use the [mysqld] group for the server that is installed under the default service name (MySql). For other servers, use a group name that is the same as the service name. Suppose you want to run the 4.0.8 mysqld-nt using the default service name and the 4.0.14 mysqld-nt using the service name mysqld2. In this case, you can use the [mysqld] group for 4.0.8 and the [mysqld2] group for 4.0.14. For example, you can set up C:\my.cnf like this:
# options for default service (MySql) [mysqld] basedir = C:/mysql-4.0.8 port = 3307 enable-named-pipe socket = mypipe1 # options for mysqld2 service [mysqld2] basedir = C:/mysql-4.0.14 port = 3308 enable-named-pipe socket = mypipe2
Install the services like this:
shell C:\mysql-4.0.8\bin\mysqld-nt --install shell C:\mysql-4.0.14\bin\mysqld-nt --install mysqld2
To start the services, use the services manager, or use NET START with the appropriate service names:
shell NET START MySql shell NET START mysqld2
To stop the services, use the services manager, or use NET STOP with the same service names.
Specify options for each server in separate files and use -defaults-file when you install the services to tell each server what file to use. In this case, each file should list options using a [mysqld] group.
With this approach, to specify options for the 4.0.8 mysqld-nt, create a file C:\my-opts1.cnf that looks like this:
[mysqld] basedir = C:/mysql-4.0.8 port = 3307 enable-named-pipe socket = mypipe1
For the 4.0.14 mysqld-nt, create a file C:\my-opts2.cnf that looks like this:
[mysqld] basedir = C:/mysql-4.0.14 port = 3308 enable-named-pipe socket = mypipe2
Install the services as follows (enter each command on a single line):
shell C:\mysql-4.0.8\bin\mysqld-nt --install MySql
--defaults-file=C:\my-opts1.cnf
shell C:\mysql-4.0.14\bin\mysqld-nt --install mysqld2
--defaults-file=C:\my-opts2.cnf
To use a -defaults-file option when you install a MySQL server as a service, you must precede the option with the service name. That is why the first command names the MySql service explicitly, even though that is the default service name.
Start and stop the services the same way as in the preceding example.
To remove multiple services, use mysqld -remove for each one, specifying a service name following the -remove option if the service to remove has a name different than the default.
The easiest way is to run multiple servers on Unix is to compile them with different TCP/IP ports and socket files so that each one is listening on different network interfaces. Also, by compiling in different base directories for each installation, that automatically results in different compiled-in data directory, log file, and PID file locations for each of your servers.
Assume an existing server is configured for the default port number and socket file. To configure a new server to have different operating parameters, use a configure command something like this:
shell ./configure --with-tcp-port=port_number \
--with-unix-socket-path=file_name \
--prefix=/usr/local/mysql-4.0.14
Here port_number and file_name should be different from the default port number and socket file pathname, and the -prefix value should specify an installation directory different than the one under which the existing MySQL installation is located.
If you have a MySQL server listening on a given port number, you can use the following command to find out what operating parameters it is using for several important configurable variables, including the base directory and socket name:
shell mysqladmin --host=host_name --port=port_number variables
With the information displayed by that command, you can tell what option values not to use when configuring an additional server.
Note that if you specify "localhost" as a hostname, mysqladmin will default to using a Unix socket connection rather than TCP/IP. In MySQL 4.1, you can explicitly specify the connection protocol to use by using the -protocol={TCP | SOCKET | PIPE | MEMORY} option.
You don't have to compile a new MySQL server just to start with a different socket file and TCP/IP port number. It is also possible to specify those values at runtime. One way to do so is by using command-line options:
shell /path/to/mysqld_safe --socket=file_name --port=port_number
To use another database directory for the second server, pass a -datadir=path option to mysqld_safe.
Another way to achieve a similar effect is to use environment variables to set the socket name and port number:
shell MYSQL_UNIX_PORT=/tmp/mysqld-new.sock shell MYSQL_TCP_PORT=3307 shell export MYSQL_UNIX_PORT MYSQL_TCP_PORT shell scripts/mysql_install_db shell bin/mysqld_safe
This is a quick and dirty method for starting a second server to use for testing. The nice thing about this method is that the environment variable settings will apply to any client programs that you invoke from the above shell. Thus, connections for those clients automatically will be directed to the second server!
Appendix F, Environment Variables includes a list of other environment variables you can use to affect mysqld.
For automatic server execution, your startup script that is executed at boot time should execute the following command once for each server with an appropriate option file path for each command:
mysqld_safe --defaults-file=path-to-option-file
Each option file should contain option values specific to a given server.
On Unix, the mysqld_multi script is another way to start multiple servers. Section 4.7.3, “mysqld_multi, A Program for Managing Multiple MySQL Servers ”.
When you want to connect with a client program to a MySQL server that is listening to different network interfaces than those compiled into your client, you can use one of the following methods:
Start the client with -host=host_name -port=port_number to connect via TCP/IP to a remote host, or with -host=localhost -socket=file_name to connect to a local host via a Unix socket or a Windows named pipe.
As of MySQL 4.1, start the client with -protocol=tcp to connect via TCP/IP, -protocol=socket to connect via a Unix socket, -protocol=pipe to connect via a named pipe, or -protocol=memory to connect via shared memory. For TCP/IP connections, you may also need to specify -host and -port options. For the other types of connections, you may need to specify a -socket option to specify a socket or named pipe name, or a -shared-memory-base-name option to specify the shared memory name.
On Unix, set the MYSQL_UNIX_PORT and MYSQL_TCP_PORT environment variables to point to the Unix socket and TCP/IP port before you start your clients. If you normally use a specific socket or port, you can place commands to set these environment variables in your .login file so that they apply each time you log in. Appendix F, Environment Variables .
Specify the default socket and TCP/IP port in the [client] group of an option file. Foe example, you can use C:\my.cnf on Windows, or the .my.cnf file in your home directory on Unix. Section 4.1.2, “my.cnf Option Files ”.
In a C program, you can specify the port or socket arguments in the mysql_real_connect() call. You can also have the program read option files by calling mysql_options(). Section 9.1.3, “C API Function Descriptions ”.
If you are using the Perl DBD::mysql module, you can read the options from the MySQL option files. For example:
$dsn = "DBI:mysql:test;mysql_read_default_group=client;"
. "mysql_read_default_file=/usr/local/mysql/data/my.cnf";
$dbh = DBI-connect($dsn, $user, $password);