2
6 mysqlmanager - the MySQL Instance Manager
7
9 mysqlmanager [options]
10
12 mysqlmanager is the MySQL Instance Manager (IM). This program monitors
13 and manages MySQL Database Server instances. MySQL Instance Manager is
14 available for Unix-like operating systems, and also on Windows as of
15 MySQL 5.0.13. It runs as a daemon that listens on a TCP/IP port. On
16 Unix, it also listens on a Unix socket file.
17 MySQL Instance Manager is included in MySQL distributions from version
19 5.0.3, and can be used in place of the mysqld_safe script to start and
20 stop one or more instances of MySQL Server. Because Instance Manager
21 can manage multiple server instances, it can also be used in place of
22 the mysqld_multi script. Instance Manager offers these capabilities:
23 · Instance Manager can start and stop instances, and report on the
25 status of instances.
26 · Server instances can be treated as guarded or unguarded:
28 · When Instance Manager starts, it starts each guarded instance. If
30 the instance crashes, Instance Manager detects this and restarts
31 it. When Instance Manager stops, it stops the instance.
32 · A nonguarded instance is not started when Instance Manager starts
34 or monitored by it. If the instance crashes after being started,
35 Instance Manager does not restart it. When Instance Manager
36 exits, it does not stop the instance if it is running.
37 Instances are guarded by default. An instance can be designated as
39 nonguarded by including the nonguarded option in the configuration
40 file.
41 · Instance Manager provides an interactive interface for configuring
43 instances, so that the need to edit the configuration file manually
44 is reduced or eliminated.
45 · Instance Manager provides remote instance management. That is, it
47 runs on the host where you want to control MySQL Server instances,
48 but you can connect to it from a remote host to perform
49 instance-management operations.
50 The following sections describe MySQL Instance Manager operation in
53 more detail.
54
56 The MySQL Instance Manager supports a number of command options. For a
57 brief listing, invoke mysqlmanager with the --help option. Options may
58 be given on the command line or in the Instance Manager configuration
59 file. On Windows, the standard configuration file is my.ini in the
60 directory where Instance Manager is installed. On Unix, the standard
61 file is /etc/my.cnf. To specify a different configuration file, start
62 Instance Manager with the --defaults-file option.
63 mysqlmanager supports the following options:
65 · --help, -?
67 Display a help message and exit.
69 · --angel-pid-file=file_name
71 The file in which the angel process records its process ID when
73 mysqlmanager runs in daemon mode (that is, when the --run-as-service
74 option is given). The default filename is mysqlmanager.angel.pid.
75 If the --angel-pid-file option is not given, the default angel PID
77 file has the same name as the PID file except that any PID file
78 extension is replaced with an extension of .angel.pid. (For example,
79 mysqlmanager.pid becomes mysqlmanager.angel.pid.)
80 This option was added in MySQL 5.0.23.
82 · --bind-address=IP
84 The IP address to bind to.
86 · --default-mysqld-path=path
88 The pathname of the MySQL Server binary. This pathname is used for
90 all server instance sections in the configuration file for which no
91 mysqld-path option is present. The default value of this option is
92 the compiled-in pathname, which depends on how the MySQL
93 distribution was configured. Example:
94 --default-mysqld-path=/usr/sbin/mysqld
95 · --defaults-file=file_name
97 Read Instance Manager and MySQL Server settings from the given file.
99 All configuration changes made by the Instance Manager will be
100 written to this file. This must be the first option on the command
101 line if it is used, and the file must exist.
102 If this option is not given, Instance Manager uses its standard
104 configuration file. On Windows, the standard file is my.ini in the
105 directory where Instance Manager is installed. On Unix, the standard
106 file is /etc/my.cnf.
107 · --install
109 On Windows, install Instance Manager as a Windows service. The
111 service name is MySQL Manager. This option was added in MySQL
112 5.0.11.
113 · --log=file_name
115 The path to the Instance Manager log file. This option has no effect
117 unless the --run-as-service option is also given. If the filename
118 specified for the option is a relative name, the log file is created
119 under the directory from which Instance Manager is started. To
120 ensure that the file is created in a specific directory, specify it
121 as a full pathname.
122 If --run-as-service is given without --log, the log file is
124 mysqlmanager.log in the data directory.
125 If --run-as-service is not given, log messages go to the standard
127 output. To capture log output, you can redirect Instance Manager
128 output to a file:
129 mysqlmanager > im.log
131 · --monitoring-interval=seconds
133 The interval in seconds for monitoring server instances. The default
135 value is 20 seconds. Instance Manager tries to connect to each
136 monitored (guarded) instance using the non-existing
137 MySQL_Instance_Manager user account to check whether it is alive/not
138 hanging. If the result of the connection attempt indicates that the
139 instance is unavailable, Instance Manager performs several attempts
140 to restart the instance.
141 Normally, the MySQL_Instance_Manager account does not exist, so the
143 connection attempts by Instance Manager cause the monitored instance
144 to produce messages in its general query log similar to the
145 following:
146 Access denied for user 'MySQL_Instance_M'@'localhost' (using password: YES)
148 The nonguarded option in the appropriate server instance section
151 disables monitoring for a particular instance. If the instance dies
152 after being started, Instance Manager will not restart it. Instance
153 Manager tries to connect to a nonguarded instance only when you request
154 the instance's status (for example, with the SHOW INSTANCES status.
155 See the section called “MYSQL SERVER INSTANCE STATUS MONITORING”, for
157 more information.
158 · --passwd, -P
160 Prepare an entry for the password file, print it to the standard
162 output, and exit. You can redirect the output from Instance Manager
163 to a file to save the entry in the file.
164 · --password-file=file_name
166 The name of the file where the Instance Manager looks for users and
168 passwords. On Windows, the default is mysqlmanager.passwd in the
169 directory where Instance Manager is installed. On Unix, the default
170 file is /etc/mysqlmanager.passwd.
171 · --pid-file=file_name
173 The process ID file to use. On Windows, the default file is
175 mysqlmanager.pid in the directory where Instance Manager is
176 installed. On Unix, the default is mysqlmanager.pid in the data
177 directory.
178 · --port=port_num
180 The port number to use when listening for TCP/IP connections from
182 clients. The default port number (assigned by IANA) is 2273.
183 · --print-defaults
185 Print the current defaults and exit. This must be the first option
187 on the command line if it is used.
188 · --remove
190 On Windows, removes Instance Manager as a Windows service. This
192 assumes that Instance Manager has been run with --install
193 previously. This option was added in MySQL 5.0.11.
194 · --run-as-service
196 On Unix, daemonize and start an angel process. The angel process
198 monitors Instance Manager and restarts it if it crashes. (The angel
199 process itself is simple and unlikely to crash.)
200 · --socket=path
202 On Unix, the socket file to use for incoming connections. The
204 default file is named /tmp/mysqlmanager.sock. This option has no
205 meaning on Windows.
206 · --standalone
208 This option is used on Windows to run Instance Manager in standalone
210 mode. You should specify it when you start Instance Manager from the
211 command line. This option was added in MySQL 5.0.13.
212 · --user=user_name
214 On Unix, the username of the system account to use for starting and
216 running mysqlmanager. This option generates a warning and has no
217 effect unless you start mysqlmanager as root (so that it can change
218 its effective user ID), or as the named user. It is recommended that
219 you configure mysqlmanager to run using the same account used to run
220 the mysqld server. (“User” in this context refers to a system login
221 account, not a MySQL user listed in the grant tables.)
222 · --version, -V
224 Display version information and exit.
226 · --wait-timeout=N
228 The number of seconds to wait for activity on an incoming connection
230 before closing it. The default is 28800 seconds (8 hours).
231 This option was added in MySQL 5.0.19. Before that, the timeout is
233 30 seconds and cannot be changed.
234
236 Instance Manager uses its standard configuration file unless it is
237 started with a --defaults-file option that specifies a different file.
238 On Windows, the standard file is my.ini in the directory where Instance
239 Manager is installed. On Unix, the standard file is /etc/my.cnf. (Prior
240 to MySQL 5.0.10, the MySQL Instance Manager read the same configuration
241 files as the MySQL Server, including /etc/my.cnf, ~/.my.cnf, and so
242 forth.)
243 Instance Manager reads options for itself from the [manager] section of
245 the configuration file, and options for server instances from [mysqld]
246 or [mysqldN] sections. The [manager] section contains any of the
247 options listed in the section called “MYSQL INSTANCE MANAGER COMMAND
248 OPTIONS”, except for those specified as having to be given as the first
249 option on the command line. Here is a sample [manager] section:
250 # MySQL Instance Manager options section
252 [manager]
253 default-mysqld-path = /usr/local/mysql/libexec/mysqld
254 socket=/tmp/manager.sock
255 pid-file=/tmp/manager.pid
256 password-file = /home/cps/.mysqlmanager.passwd
257 monitoring-interval = 2
258 port = 1999
259 bind-address = 192.168.1.5
260 Each [mysqld] or [mysqldN] instance section specifies options given by
262 Instance Manager to a server instance at startup. These are mainly
263 common MySQL Server options (see the section called “COMMAND OPTIONS”).
264 In addition, a [mysqldN] section can contain the options in the
265 following list, which are specific to Instance Manager. These options
266 are interpreted by Instance Manager itself; it does not pass them to
267 the server when it attempts to start that server.
268 Warning
270 The Instance Manager-specific options must not be used in a [mysqld]
271 section. If a server is started without using Instance Manager, it will
272 not recognize these options and will fail to start properly.
273 · mysqld-path = path
275 The pathname of the mysqld server binary to use for the server
277 instance.
278 · nonguarded
280 This option disables Instance Manager monitoring functionality for
282 the server instance. By default, an instance is guarded: At Instance
283 Manager start time, it starts the instance. It also monitors the
284 instance status and attempts to restart it if it fails. At Instance
285 Manager exit time, it stops the instance. None of these things
286 happen for nonguarded instances.
287 · shutdown-delay = seconds
289 The number of seconds Instance Manager should wait for the server
291 instance to shut down. The default value is 35 seconds. After the
292 delay expires, Instance Manager assumes that the instance is hanging
293 and attempts to terminate it. If you use InnoDB with large tables,
294 you should increase this value.
295 Here are some sample instance sections:
298 [mysqld1]
300 mysqld-path=/usr/local/mysql/libexec/mysqld
301 socket=/tmp/mysql.sock
302 port=3307
303 server_id=1
304 skip-stack-trace
305 core-file
306 skip-bdb
307 log-bin
308 log-error
309 log=mylog
310 log-slow-queries
311 [mysqld2]
312 nonguarded
313 port=3308
314 server_id=2
315 mysqld-path= /home/cps/mysql/trees/mysql-5.0/sql/mysqld
316 socket = /tmp/mysql.sock5
317 pid-file = /tmp/hostname.pid5
318 datadir= /home/cps/mysql_data/data_dir1
319 language=/home/cps/mysql/trees/mysql-5.0/sql/share/english
320 log-bin
321 log=/tmp/fordel.log
322
324 This section discusses how Instance Manager starts server instances
325 when it starts. However, before you start Instance Manager, you should
326 set up a password file for it. Otherwise, you will not be able to
327 connect to Instance Manager to control it after it starts. For details
328 about creating Instance Manager accounts, see the section called
329 “INSTANCE MANAGER USER AND PASSWORD MANAGEMENT”.
330 On Unix, the mysqld MySQL database server normally is started with the
332 mysql.server script, which usually resides in the /etc/init.d/
333 directory. In MySQL 5.0.3, this script invokes mysqlmanager (the MySQL
334 Instance Manager binary) to start MySQL. (In prior versions of MySQL
335 the mysqld_safe script is used for this purpose.) Starting from MySQL
336 5.0.4, the behavior of the startup script was changed again to
337 incorporate both setup schemes. In version 5.0.4, the startup script
338 uses the old scheme (invoking mysqld_safe) by default, but one can set
339 the use_mysqld_safe variable in the script to 0 (zero) to use the MySQL
340 Instance Manager to start a server.
341 Starting with MySQL 5.0.19, you can use Instance Manager if you modify
343 the my.cnf configuration file by adding use-manager to the
344 [mysql.server] section:
345 [mysql.server]
347 use-manager
348 When Instance Manager starts, it reads its configuration file if it
350 exists to find server instance sections and prepare a list of
351 instances. Instance sections have names of the form [mysqld] or
352 [mysqldN], where N is an unsigned integer (for example, [mysqld1],
353 [mysqld2], and so forth).
354 After preparing the list of instances, Instance Manager starts the
356 guarded instances in the list. If there are no instances, Instance
357 Manager creates an instance named mysqld and attempts to start it with
358 default (compiled-in) configuration values. This means that the
359 Instance Manager cannot find the mysqld program if it is not installed
360 in the default location. (Section 4.6, “Installation Layouts”,
361 describes default locations for components of MySQL distributions.) If
362 you have installed the MySQL server in a non-standard location, you
363 should create the Instance Manager configuration file.
364 Instance Manager also stops all guarded server instances when it shuts
366 down.
367 The allowable options for [mysqldN] server instance sections are
369 described in the section called “MYSQL INSTANCE MANAGER CONFIGURATION
370 FILES”. In these sections, you can use a special
371 mysqld-path=path-to-mysqld-binary option that is recognized only by
372 Instance Manager. Use this option to let Instance Manager know where
373 the mysqld binary resides. If there are multiple instances, it may also
374 be necessary to set other options such as datadir and port, to ensure
375 that each instance has a different data directory and TCP/IP port
376 number. Section 10, “Running Multiple MySQL Servers on the Same
377 Machine”, discusses the configuration values that must differ for each
378 instance when you run multiple instance on the same machine.
379 Warning
381 The [mysqld] instance section, if it exists, must not contain any
382 Instance Manager-specific options.
383 The typical Unix startup/shutdown cycle for a MySQL server with the
385 MySQL Instance Manager enabled is as follows:
386 1. The /etc/init.d/mysql script starts MySQL Instance Manager.
388 2. Instance Manager starts the guarded server instances and monitors
390 them.
391 3. If a server instance fails, Instance Manager restarts it.
393 4. If Instance Manager is shut down (for example, with the
395 /etc/init.d/mysql stop command), it shuts down all server instances.
396
398 The Instance Manager stores its user information in a password file. On
399 Windows, the default is mysqlmanager.passwd in the directory where
400 Instance Manager is installed. On Unix, the default file is
401 /etc/mysqlmanager.passwd. To specify a different location for the
402 password file, use the --password-file option.
403 If the password file does not exist or contains no password entries,
405 you cannot connect to the Instance Manager.
406 Note
408 Any Instance Manager process that is running to monitor server
409 instances does not notice changes to the password file. You must stop
410 it and restart it after making password entry changes.
411 Entries in the password file have the following format, where the two
413 fields are the account username and encrypted password, separated by a
414 colon:
415 petr:*35110DC9B4D8140F5DE667E28C72DD2597B5C848
417 Instance Manager password encryption is the same as that used by MySQL
419 Server. It is a one-way operation; no means are provided for decrypting
420 encrypted passwords.
421 Instance Manager accounts differ somewhat from MySQL Server accounts:
423 · MySQL Server accounts are associated with a hostname, username, and
425 password (see Section 6.1, “MySQL Usernames and Passwords”).
426 · Instance Manager accounts are associated with a username and
428 password only.
429 This means that a client can connect to Instance Manager with a given
432 username from any host. To limit connections so that clients can
433 connect only from the local host, start Instance Manager with the
434 --bind-address=127.0.0.1 option so that it listens only to the local
435 network interface. Remote clients will not be able to connect. Local
436 clients can connect like this:
437 shell> mysql -h 127.0.0.1 -P 2273
439 To generate a new entry, invoke Instance Manager with the --passwd
441 option and append the output to the /etc/mysqlmanager.passwd file. Here
442 is an example:
443 shell> mysqlmanager --passwd >> /etc/mysqlmanager.passwd
445 Creating record for new user.
446 Enter user name: mike
447 Enter password: mikepass
448 Re-type password: mikepass
449 At the prompts, enter the username and password for the new Instance
451 Manager user. You must enter the password twice. It does not echo to
452 the screen, so double entry guards against entering a different
453 password than you intend (if the two passwords do not match, no entry
454 is generated).
455 The preceding command causes the following line to be added to
457 /etc/mysqlmanager.passwd:
458 mike:*BBF1F551DD9DD96A01E66EC7DDC073911BAD17BA
460
462 To monitor the status of each guarded server instance, the MySQL
463 Instance Manager attempts to connect to the instance at regular
464 intervals using the MySQL_Instance_Manager@localhost user account with
465 a password of check_connection.
466 You are not required to create this account for MySQL Server; in fact,
468 it is expected that it will not exist. Instance Manager can tell that a
469 server is operational if the server accepts the connection attempt but
470 refuses access for the account by returning a login error. However,
471 these failed connection attempts are logged by the server to its
472 general query log (see Section 9.2, “The General Query Log”).
473 Instance Manager also attempts a connection to nonguarded server
475 instances when you use the SHOW INSTANCES or SHOW INSTANCE STATUS
476 command. This is the only status monitoring done for nonguarded
477 instances.
478 Instance Manager knows if a server instance fails at startup because it
480 receives a status from the attempt. For an instance that starts but
481 later crashes, Instance Manager receives a signal because it is the
482 parent process of the instance.
483
485 After you set up a password file for the MySQL Instance Manager and
486 Instance Manager is running, you can connect to it. The MySQL
487 client-server protocol is used to communicate with the Instance
488 Manager. For example, you can connect to it using the standard mysql
489 client program:
490 shell> mysql --port=2273 --host=im.example.org --user=mysql --password
492 Instance Manager supports the version of the MySQL client-server
494 protocol used by the client tools and libraries distributed with MySQL
495 4.1 or later, so other programs that use the MySQL C API also can
496 connect to it.
497
499 After you connect to MySQL Instance Manager, you can issue commands.
500 The following general principles apply to Instance Manager command
501 execution:
502 · Commands that take an instance name fail if the name is not a valid
504 instance name.
505 · Commands that take an instance name fail if the instance does not
507 exist.
508 · Instance Manager maintains information about instance configuration
510 in an internal (in-memory) cache. Initially, this information comes
511 from the configuration file if it exists, but some commands change
512 the configuration of an instance. Commands that modify the
513 configuration file fail if the file does not exist or is not
514 accessible to Instance Manager.
515 · On Windows, the standard file is my.ini in the directory where
517 Instance Manager is installed. On Unix, the standard configuration
518 file is /etc/my.cnf. To specify a different configuration file,
519 start Instance Manager with the --defaults-file option.
520 · If a [mysqld] instance section exists in the configuration file, it
522 must not contain any Instance Manager-specific options (see the
523 section called “MYSQL INSTANCE MANAGER CONFIGURATION FILES”).
524 Therefore, you must not add any of these options if you change the
525 configuration for an instance named mysqld.
526 The following list describes the commands that Instance Manager
529 accepts, with examples.
530 · START INSTANCE instance_name
532 This command attempts to start an offline instance. The command is
534 asynchronous; it does not wait for the instance to start.
535 mysql> START INSTANCE mysqld4;
537 Query OK, 0 rows affected (0,00 sec)
538 · STOP INSTANCE instance_name
540 This command attempts to stop an instance. The command is
542 synchronous; it waits for the instance to stop.
543 mysql> STOP INSTANCE mysqld4;
545 Query OK, 0 rows affected (0,00 sec)
546 · SHOW INSTANCES
548 Shows the names and status of all loaded instances.
550 mysql> SHOW INSTANCES;
552 +---------------+---------+
553 | instance_name | status |
554 +---------------+---------+
555 | mysqld3 | offline |
556 | mysqld4 | online |
557 | mysqld2 | offline |
558 +---------------+---------+
559 · SHOW INSTANCE STATUS instance_name
561 Shows status and version information for an instance.
563 mysql> SHOW INSTANCE STATUS mysqld3;
565 +---------------+--------+---------+
566 | instance_name | status | version |
567 +---------------+--------+---------+
568 | mysqld3 | online | unknown |
569 +---------------+--------+---------+
570 · SHOW INSTANCE OPTIONS instance_name
572 Shows the options used by an instance.
574 mysql> SHOW INSTANCE OPTIONS mysqld3;
576 +---------------+---------------------------------------------------+
577 | option_name | value |
578 +---------------+---------------------------------------------------+
579 | instance_name | mysqld3 |
580 | mysqld-path | /home/cps/mysql/trees/mysql-4.1/sql/mysqld |
581 | port | 3309 |
582 | socket | /tmp/mysql.sock3 |
583 | pid-file | hostname.pid3 |
584 | datadir | /home/cps/mysql_data/data_dir1/ |
585 | language | /home/cps/mysql/trees/mysql-4.1/sql/share/english |
586 +---------------+---------------------------------------------------+
587 · SHOW instance_name LOG FILES
589 The command lists all log files used by the instance. The result set
591 contains the path to the log file and the log file size. If no log
592 file path is specified in the instance section of the configuration
593 file (for example, log=/var/mysql.log), the Instance Manager tries
594 to guess its placement. If Instance Manager is unable to guess the
595 log file placement you should specify the log file location
596 explicitly by using a log option in the appropriate instance section
597 of the configuration file.
598 mysql> SHOW mysqld LOG FILES;
600 +-------------+------------------------------------+----------+
601 | Logfile | Path | Filesize |
602 +-------------+------------------------------------+----------+
603 | ERROR LOG | /home/cps/var/mysql/owlet.err | 9186 |
604 | GENERAL LOG | /home/cps/var/mysql/owlet.log | 471503 |
605 | SLOW LOG | /home/cps/var/mysql/owlet-slow.log | 4463 |
606 +-------------+------------------------------------+----------+
607 Log options are described in the section called “COMMAND OPTIONS”.
608 · SHOW instance_name LOG {ERROR | SLOW | GENERAL}
610 size[,offset_from_end]
611 This command retrieves a portion of the specified log file. Because
613 most users are interested in the latest log messages, the size
614 parameter defines the number of bytes to retrieve from the end of
615 the log. To retrieve data from the middle of the log file, specify
616 the optional offset_from_end parameter. The following example
617 retrieves 21 bytes of data, starting 23 bytes before the end of the
618 log file and ending 2 bytes before the end:
619 mysql> SHOW mysqld LOG GENERAL 21, 2;
621 +---------------------+
622 | Log |
623 +---------------------+
624 | using password: YES |
625 +---------------------+
626 · SET instance_name.option_name[=option_value]
628 This command edits the specified instance's configuration section to
630 change or add instance options. The option is added to the section
631 is it is not already present. Otherwise, the new setting replaces
632 the existing one.
633 mysql> SET mysqld2.port=3322;
635 Query OK, 0 rows affected (0.00 sec)
636 Changes made to the configuration file do not take effect until the
637 MySQL server is restarted. In addition, these changes are not stored in
638 the instance manager's local cache of instance settings until a FLUSH
639 INSTANCES command is executed.
640 · UNSET instance_name.option_name
642 This command removes an option from an instance's configuration
644 section.
645 mysql> UNSET mysqld2.port;
647 Query OK, 0 rows affected (0.00 sec)
648 Changes made to the configuration file do not take effect until the
649 MySQL server is restarted. In addition, these changes are not stored in
650 the instance manager's local cache of instance settings until a FLUSH
651 INSTANCES command is executed.
652 · FLUSH INSTANCES
654 This command forces Instance Manager reread the configuration file
656 and to refresh internal structures. This command should be performed
657 after editing the configuration file. The command does not restart
658 instances.
659 mysql> FLUSH INSTANCES;
661 Query OK, 0 rows affected (0.04 sec)
662 FLUSH INSTANCES is deprecated and will be removed in MySQL 5.2.
663
665 Copyright 1997-2007 MySQL AB
666 This documentation is NOT distributed under a GPL license. Use of this
668 documentation is subject to the following terms: You may create a
669 printed copy of this documentation solely for your own personal use.
670 Conversion to other formats is allowed as long as the actual content is
671 not altered or edited in any way. You shall not publish or distribute
672 this documentation in any form or on any media, except if you
673 distribute the documentation in a manner similar to how MySQL
674 disseminates it (that is, electronically for download on a Web site
675 with the software) or on a CD-ROM or similar medium, provided however
676 that the documentation is disseminated together with the software on
677 the same medium. Any other use, such as any dissemination of printed
678 copies or use of this documentation, in whole or in part, in another
679 publication, requires the prior written consent from an authorized
680 representative of MySQL AB. MySQL AB reserves any and all rights to
681 this documentation not expressly granted above.
682 Please email <docs@mysql.com> for more information.
684
686 For more information, please refer to the MySQL Reference Manual, which
687 may already be installed locally and which is also available online at
688 http://dev.mysql.com/doc/.
689
691 MySQL AB (http://www.mysql.com/). This software comes with no
692 warranty.
693MySQL 5.0 07/04/2007 MYSQLMANAGER(8)