1MYSQLMANAGER(8) MySQL Database System MYSQLMANAGER(8)
2
6 mysqlmanager - the MySQL Instance Manager
7
9 mysqlmanager [options]
10
12 Important
13 MySQL Instance Manager has been deprecated and is removed in MySQL
14 5.5.
15 mysqlmanager is the MySQL Instance Manager (IM). This program monitors
17 and manages MySQL Database Server instances. MySQL Instance Manager is
18 available for Unix-like operating systems, as well as Windows. It runs
19 as a daemon that listens on a TCP/IP port. On Unix, it also listens on
20 a Unix socket file.
21 MySQL Instance Manager can be used in place of the mysqld_safe script
23 to start and stop one or more instances of MySQL Server. Because
24 Instance Manager can manage multiple server instances, it can also be
25 used in place of the mysqld_multi script. Instance Manager offers these
26 capabilities:
27 · Instance Manager can start and stop instances, and report on the
29 status of instances.
30 · Server instances can be treated as guarded or unguarded:
32 · When Instance Manager starts, it starts each guarded instance.
34 If the instance crashes, Instance Manager detects this and
35 restarts it. When Instance Manager stops, it stops the
36 instance.
37 · A nonguarded instance is not started when Instance Manager
39 starts or monitored by it. If the instance crashes after being
40 started, Instance Manager does not restart it. When Instance
41 Manager exits, it does not stop the instance if it is running.
42 Instances are guarded by default. An instance can be designated as
44 nonguarded by including the nonguarded option in the configuration
45 file.
46 · Instance Manager provides an interactive interface for configuring
48 instances, so that the need to edit the configuration file manually
49 is reduced or eliminated.
50 · Instance Manager provides remote instance management. That is, it
52 runs on the host where you want to control MySQL Server instances,
53 but you can connect to it from a remote host to perform
54 instance-management operations.
55 The following sections describe MySQL Instance Manager operation in
57 more detail.
58
60 Important
61 MySQL Instance Manager has been deprecated and is removed in MySQL
62 5.5.
63 The MySQL Instance Manager supports a number of command options. For a
65 brief listing, invoke mysqlmanager with the --help option. Options may
66 be given on the command line or in the Instance Manager configuration
67 file. On Windows, the standard configuration file is my.ini in the
68 directory where Instance Manager is installed. On Unix, the standard
69 file is /etc/my.cnf. To specify a different configuration file, start
70 Instance Manager with the --defaults-file option.
71 mysqlmanager supports the following options. The options for managing
73 entries in the password file are described further in the section
74 called “INSTANCE MANAGER USER AND PASSWORD MANAGEMENT”.
75 · --help, -?
77 Display a help message and exit.
79 · --add-user
81 Add a new user (specified with the --username option) to the
83 password file. This option was added in MySQL 5.1.12.
84 · --angel-pid-file=file_name
86 The file in which the angel process records its process ID when
88 mysqlmanager runs in daemon mode (that is, when the
89 --run-as-service option is given). The default file name is
90 mysqlmanager.angel.pid.
91 If the --angel-pid-file option is not given, the default angel PID
93 file has the same name as the PID file except that any PID file
94 extension is replaced with an extension of .angel.pid. (For
95 example, mysqlmanager.pid becomes mysqlmanager.angel.pid.)
96 This option was added in MySQL 5.1.11.
98 · --bind-address=IP
100 The IP address to bind to.
102 · --check-password-file
104 Check the validity and consistency of the password file. This
106 option was added in MySQL 5.1.12.
107 · --clean-password-file
109 Drop all users from the password file. This option was added in
111 MySQL 5.1.12.
112 · --debug=debug_options, -# debug_options
114 Write a debugging log. A typical debug_options string is
116 'd:t:o,file_name'. This option was added in MySQL 5.1.10.
117 · --default-mysqld-path=path
119 The path name of the MySQL Server binary. This path name is used
121 for all server instance sections in the configuration file for
122 which no mysqld-path option is present. The default value of this
123 option is the compiled-in path name, which depends on how the MySQL
124 distribution was configured. Example:
125 --default-mysqld-path=/usr/sbin/mysqld
126 · --defaults-file=file_name
128 Read Instance Manager and MySQL Server settings from the given
130 file. All configuration changes made by the Instance Manager will
131 be written to this file. This must be the first option on the
132 command line if it is used, and the file must exist.
133 If this option is not given, Instance Manager uses its standard
135 configuration file. On Windows, the standard file is my.ini in the
136 directory where Instance Manager is installed. On Unix, the
137 standard file is /etc/my.cnf.
138 · --drop-user
140 Drop a user (specified with the --username option) from the
142 password file. This option was added in MySQL 5.1.12.
143 · --edit-user
145 Change an entry for an existing user (specified with the --username
147 option) in the password file. This option was added in MySQL
148 5.1.12.
149 · --install
151 On Windows, install Instance Manager as a Windows service. The
153 service name is MySQL Manager.
154 · --list-users
156 List the users in the password file. This option was added in MySQL
158 5.1.12.
159 · --log=file_name
161 The path to the Instance Manager log file. This option has no
163 effect unless the --run-as-service option is also given. If the
164 file name specified for the option is a relative name, the log file
165 is created under the directory from which Instance Manager is
166 started. To ensure that the file is created in a specific
167 directory, specify it as a full path name.
168 If --run-as-service is given without --log, the log file is
170 mysqlmanager.log in the data directory.
171 If --run-as-service is not given, log messages go to the standard
173 output. To capture log output, you can redirect Instance Manager
174 output to a file:
175 mysqlmanager > im.log
177 · --monitoring-interval=seconds
179 The interval in seconds for monitoring server instances. The
181 default value is 20 seconds. Instance Manager tries to connect to
182 each monitored (guarded) instance using the nonexisting
183 MySQL_Instance_Manager user account to check whether it is
184 available/not hanging. If the result of the connection attempt
185 indicates that the instance is unavailable, Instance Manager
186 performs several attempts to restart the instance.
187 Normally, the MySQL_Instance_Manager account does not exist, so the
189 connection attempts by Instance Manager cause the monitored
190 instance to produce messages in its general query log similar to
191 the following:
192 Access denied for user 'MySQL_Instance_M'@'localhost' »
194 (using password: YES)
195 The nonguarded option in the appropriate server instance section
197 disables monitoring for a particular instance. If the instance dies
198 after being started, Instance Manager will not restart it. Instance
199 Manager tries to connect to a nonguarded instance only when you
200 request the instance's status (for example, with the SHOW INSTANCES
201 status.
202 See the section called “MYSQL SERVER INSTANCE STATUS MONITORING”,
204 for more information.
205 · --mysqld-safe-compatible
207 Run in a mysqld_safe-compatible manner. For details, see the
209 section called “STARTING THE MYSQL SERVER WITH MYSQL INSTANCE
210 MANAGER”. This option was added in MySQL 5.1.12.
211 · --password=password, -p password
213 Specify the password for an entry to be added to or modified in the
215 password file. Unlike the --password/-P option for most MySQL
216 programs, the password value is required, not optional. See also
217 the section called “INSTANCE MANAGER USER AND PASSWORD MANAGEMENT”.
218 This option was added in MySQL 5.1.12.
219 · --password-file=file_name
221 The name of the file where the Instance Manager looks for users and
223 passwords. On Windows, the default is mysqlmanager.passwd in the
224 directory where Instance Manager is installed. On Unix, the default
225 file is /etc/mysqlmanager.passwd. See also the section called
226 “INSTANCE MANAGER USER AND PASSWORD MANAGEMENT”.
227 · --pid-file=file_name
229 The process ID file to use. On Windows, the default file is
231 mysqlmanager.pid in the directory where Instance Manager is
232 installed. On Unix, the default is mysqlmanager.pid in the data
233 directory.
234 · --port=port_num
236 The port number to use when listening for TCP/IP connections from
238 clients. The default port number (assigned by IANA) is 2273.
239 · --print-defaults
241 Print the current defaults and exit. This must be the first option
243 on the command line if it is used.
244 · --print-password-line
246 Prepare an entry for the password file, print it to the standard
248 output, and exit. You can redirect the output from Instance Manager
249 to a file to save the entry in the file.
250 Prior to MySQL 5.1.12, this option was named --passwd.
252 · --remove
254 On Windows, removes Instance Manager as a Windows service. This
256 assumes that Instance Manager has been run with --install
257 previously.
258 · --run-as-service
260 On Unix, daemonize and start an angel process. The angel process
262 monitors Instance Manager and restarts it if it crashes. (The angel
263 process itself is simple and unlikely to crash.)
264 · --socket=path
266 On Unix, the socket file to use for incoming connections. The
268 default file is named /tmp/mysqlmanager.sock. This option has no
269 meaning on Windows.
270 · --standalone
272 This option is used on Windows to run Instance Manager in
274 standalone mode. You should specify it when you start Instance
275 Manager from the command line.
276 · --user=user_name
278 On Unix, the user name of the system account to use for starting
280 and running mysqlmanager. This option generates a warning and has
281 no effect unless you start mysqlmanager as root (so that it can
282 change its effective user ID), or as the named user. It is
283 recommended that you configure mysqlmanager to run using the same
284 account used to run the mysqld server. (“User” in this context
285 refers to a system login account, not a MySQL user listed in the
286 grant tables.)
287 · --username=user_name, -u user_name
289 Specify the user name for an entry to be added to or modified in
291 the password file. This option was added in MySQL 5.1.12.
292 · --version, -V
294 Display version information and exit.
296 · --wait-timeout=N
298 The number of seconds to wait for activity on an incoming
300 connection before closing it. The default is 28800 seconds (8
301 hours).
302 This option was added in MySQL 5.1.7. Before that, the timeout is
304 30 seconds and cannot be changed.
305
307 Important
308 MySQL Instance Manager has been deprecated and is removed in MySQL
309 5.5.
310 Instance Manager uses its standard configuration file unless it is
312 started with a --defaults-file option that specifies a different file.
313 On Windows, the standard file is my.ini in the directory where Instance
314 Manager is installed. On Unix, the standard file is /etc/my.cnf.
315 Instance Manager reads options for itself from the [manager] section of
317 the configuration file, and options for server instances from [mysqld]
318 or [mysqldN] sections. The [manager] section contains any of the
319 options listed in the section called “MYSQL INSTANCE MANAGER COMMAND
320 OPTIONS”, except for those specified as having to be given as the first
321 option on the command line. Here is a sample [manager] section:
322 # MySQL Instance Manager options section
324 [manager]
325 default-mysqld-path = /usr/local/mysql/libexec/mysqld
326 socket=/tmp/manager.sock
327 pid-file=/tmp/manager.pid
328 password-file = /home/cps/.mysqlmanager.passwd
329 monitoring-interval = 2
330 port = 1999
331 bind-address = 192.168.1.5
332 Each [mysqld] or [mysqldN] instance section specifies options given by
334 Instance Manager to a server instance at startup. These are mainly
335 common MySQL Server options (see Section 5.1.3, “Server Command
336 Options”). In addition, a [mysqldN] section can contain the options in
337 the following list, which are specific to Instance Manager. These
338 options are interpreted by Instance Manager itself; it does not pass
339 them to the server when it attempts to start that server.
340 Warning
342 The Instance Manager-specific options must not be used in a
343 [mysqld] section. If a server is started without using Instance
344 Manager, it will not recognize these options and will fail to start
345 properly.
346 · mysqld-path = path
348 The path name of the mysqld server binary to use for the server
350 instance.
351 · nonguarded
353 This option disables Instance Manager monitoring functionality for
355 the server instance. By default, an instance is guarded: At
356 Instance Manager start time, it starts the instance. It also
357 monitors the instance status and attempts to restart it if it
358 fails. At Instance Manager exit time, it stops the instance. None
359 of these things happen for nonguarded instances.
360 · shutdown-delay = seconds
362 The number of seconds Instance Manager should wait for the server
364 instance to shut down. The default value is 35 seconds. After the
365 delay expires, Instance Manager assumes that the instance is
366 hanging and attempts to terminate it. If you use InnoDB with large
367 tables, you should increase this value.
368 Here are some sample instance sections:
370 [mysqld1]
372 mysqld-path=/usr/local/mysql/libexec/mysqld
373 socket=/tmp/mysql.sock
374 port=3307
375 server_id=1
376 skip-stack-trace
377 core-file
378 log-bin
379 log-error
380 log=mylog
381 log-slow-queries
382 [mysqld2]
383 nonguarded
384 port=3308
385 server_id=2
386 mysqld-path= /home/cps/mysql/trees/mysql-5.1/sql/mysqld
387 socket = /tmp/mysql.sock5
388 pid-file = /tmp/hostname.pid5
389 datadir= /home/cps/mysql_data/data_dir1
390 language=/home/cps/mysql/trees/mysql-5.1/sql/share/english
391 log-bin
392 log=/tmp/fordel.log
393
395 Important
396 MySQL Instance Manager has been deprecated and is removed in MySQL
397 5.5.
398 This section discusses how Instance Manager starts server instances
400 when it starts. However, before you start Instance Manager, you should
401 set up a password file for it. Otherwise, you will not be able to
402 connect to Instance Manager to control it after it starts. For details
403 about creating Instance Manager accounts, see the section called
404 “INSTANCE MANAGER USER AND PASSWORD MANAGEMENT”.
405 On Unix, the mysqld MySQL database server normally is started with the
407 mysql.server script, which usually resides in the /etc/init.d/ folder.
408 That script invokes the mysqld_safe script by default. However, you can
409 use Instance Manager instead if you modify the /etc/my.cnf
410 configuration file by adding use-manager to the [mysql.server] section:
411 [mysql.server]
413 use-manager
414 Before MySQL 5.1.12, Instance Manager always tries to start at least
416 one server instance: When it starts, it reads its configuration file if
417 it exists to find server instance sections and prepare a list of
418 instances. Instance sections have names of the form [mysqld] or
419 [mysqldN], where N is an unsigned integer (for example, [mysqld1],
420 [mysqld2], and so forth).
421 After preparing the list of instances, Instance Manager starts the
423 guarded instances in the list. If there are no instances, Instance
424 Manager creates an instance named mysqld and attempts to start it with
425 default (compiled-in) configuration values. This means that the
426 Instance Manager cannot find the mysqld program if it is not installed
427 in the default location. (Section 2.1.5, “Installation Layouts”,
428 describes default locations for components of MySQL distributions.) If
429 you have installed the MySQL server in a nonstandard location, you
430 should create the Instance Manager configuration file.
431 The startup behavior just described is similar to that of mysqld_safe,
433 which always attempts to start a server. However, it lacks the
434 flexibility required for some operations because it is not possible to
435 run Instance Manager in such a way that it refrains from starting any
436 server instances. For example, you cannot invoke Instance Manager for
437 the purpose of configuring an instance without also starting it (a task
438 that a MySQL installer application might want to perform).
439 Consequently, MySQL 5.1.12 introduces the following changes:
440 · A new option, --mysqld-safe-compatible, may be used to cause
442 Instance Manager to run with startup behavior similar to that used
443 before MySQL 5.1.12: If Instance Manager finds a [mysqld] instance
444 section in the configuration file, it will start it. If Instance
445 Manager finds no [mysqld] section, it creates one using default
446 configuration values, writes a [mysqld] section to the
447 configuration file if it is accessible, and starts the mysqld
448 instance. Instance Manager also starts any other guarded instances
449 listed in the configuration file.
450 · Without --mysqld-safe-compatible, Instance Manager reads its
452 configuration file if it exists and starts instances for any
453 guarded instance sections that it finds. If there are none, it
454 starts no instances.
455 Instance Manager also stops all guarded server instances when it shuts
457 down.
458 The permissible options for [mysqldN] server instance sections are
460 described in the section called “MYSQL INSTANCE MANAGER CONFIGURATION
461 FILES”. In these sections, you can use a special
462 mysqld-path=path-to-mysqld-binary option that is recognized only by
463 Instance Manager. Use this option to let Instance Manager know where
464 the mysqld binary resides. If there are multiple instances, it may also
465 be necessary to set other options such as datadir and port, to ensure
466 that each instance has a different data directory and TCP/IP port
467 number. Section 5.3, “Running Multiple MySQL Instances on One
468 Machine”, discusses the configuration values that must differ for each
469 instance when you run multiple instance on the same machine.
470 Warning
472 The [mysqld] instance section, if it exists, must not contain any
473 Instance Manager-specific options.
474 The typical Unix startup/shutdown cycle for a MySQL server with the
476 MySQL Instance Manager enabled is as follows:
477 1. The /etc/init.d/mysql script starts MySQL Instance Manager.
479 2. Instance Manager starts the guarded server instances and monitors
481 them.
482 3. If a server instance fails, Instance Manager restarts it.
484 4. If Instance Manager is shut down (for example, with the
486 /etc/init.d/mysql stop command), it shuts down all server
487 instances.
488
490 Important
491 MySQL Instance Manager has been deprecated and is removed in MySQL
492 5.5.
493 The Instance Manager stores its user information in a password file. On
495 Windows, the default is mysqlmanager.passwd in the directory where
496 Instance Manager is installed. On Unix, the default file is
497 /etc/mysqlmanager.passwd. To specify a different location for the
498 password file, use the --password-file option.
499 If the password file does not exist or contains no password entries,
501 you cannot connect to the Instance Manager.
502 Note
504 Any Instance Manager process that is running to monitor server
505 instances does not notice changes to the password file. You must
506 stop it and restart it after making password entry changes.
507 Entries in the password file have the following format, where the two
509 fields are the account user name and encrypted password, separated by a
510 colon:
511 petr:*35110DC9B4D8140F5DE667E28C72DD2597B5C848
513 Instance Manager password encryption is the same as that used by MySQL
515 Server. It is a one-way operation; no means are provided for decrypting
516 encrypted passwords.
517 Instance Manager accounts differ somewhat from MySQL Server accounts:
519 · MySQL Server accounts are associated with a host name, user name,
521 and password (see Section 6.3.1, “User Names and Passwords”).
522 · Instance Manager accounts are associated with a user name and
524 password only.
525 This means that a client can connect to Instance Manager with a given
527 user name from any host. To limit connections so that clients can
528 connect only from the local host, start Instance Manager with the
529 --bind-address=127.0.0.1 option so that it listens only to the local
530 network interface. Remote clients will not be able to connect. Local
531 clients can connect like this:
532 shell> mysql -h 127.0.0.1 -P 2273
534 Before MySQL 5.1.12, the only option for creating password file entries
536 is --passwd, which causes Instance Manager to prompt for user name and
537 password values and display the resulting entry. You can save the
538 output in the /etc/mysqlmanager.passwd password file to store it. Here
539 is an example:
540 shell> mysqlmanager --passwd >> /etc/mysqlmanager.passwd
542 Creating record for new user.
543 Enter user name: mike
544 Enter password: mikepass
545 Re-type password: mikepass
546 At the prompts, enter the user name and password for the new Instance
548 Manager user. You must enter the password twice. It does not echo to
549 the screen, so double entry guards against entering a different
550 password than you intend (if the two passwords do not match, no entry
551 is generated).
552 The preceding command causes the following line to be added to
554 /etc/mysqlmanager.passwd:
555 mike:*BBF1F551DD9DD96A01E66EC7DDC073911BAD17BA
557 Use of the --password option fails if mysqlmanager is invoked directly
559 from an IBM 5250 terminal. To work around this, use a command like the
560 following from the command line to generate the password entry:
561 shell> mysql -B --skip-column-name \
563 -e 'SELECT CONCAT("user_name",":",PASSWORD("pass_val"));'
564 The output from the command can be used an entry in the
566 /etc/mysqlmanager.passwd file.
567 Beginning with MySQL 5.1.12, the --passwd option is renamed to
569 --print-password-line and there are several other options for managing
570 user accounts from the command line. For example, the --username and
571 --password options are available on the command line for specifying the
572 user name and password for an account entry. You can use them to
573 generate an entry with no prompting like this (type the command on a
574 single line):
575 shell> mysqlmanager --print-password-line
577 --username=mike --password=mikepass >> /etc/mysqlmanager.passwd
578 If you omit the --username or --password option, Instance Manager
580 prompts for the required value.
581 --print-password-line causes Instance Manager to send the resulting
583 account entry to its output, which you can append to the password file.
584 The following list describes other account-management options that
585 cause Instance Manager to operate directly on the password file. (These
586 options make Instance Manager scriptable for account-management
587 purposes.) For operations on the password file to succeed, the file
588 must exist and it must be accessible by Instance Manager. (The
589 exception is --clean-password-file, which creates the file if it does
590 not exist. Alternatively, if there is no password file, manually create
591 it as an empty file and ensure that its ownership and access modes
592 permit it to be read and written by Instance Manager.) The default
593 password file is used unless you specify a --password-file option.
594 To ensure consistent treatment of the password file, it should be owned
596 by the system account that you use for running Instance Manager to
597 manage server instances, and you should invoke it from that account
598 when you use it to manage accounts in the password file.
599 · Create a new user:
601 mysqlmanager --add-user --username=user_name [--password=password]
603 This command adds a new entry with the given user name and password
605 to the password file. The --username (or -u) option is required.
606 mysqlmanager prompts for the password if it is not given on the
607 command line with the --password (or -p) option. The command fails
608 if the user already exists.
609 · Drop an existing user:
611 mysqlmanager --drop-user --username=user_name
613 This command removes the entry with the given user name from the
615 password file. The user name is required. The command fails if the
616 user does not exist.
617 · Change the password for an existing user:
619 mysqlmanager --edit-user --username=user_name [--password=password]
621 This command changes the given user's password in the password
623 file. The user name is required. mysqlmanager prompts for the
624 password it is not given on the command line. The command fails if
625 the user does not exist.
626 · List existing users:
628 mysqlmanager --list-users
630 This command lists the user names of the accounts in the password
632 file.
633 · Check the password file:
635 mysqlmanager --check-password-file
637 This command performs a consistency and validity check of the
639 password file. The command fails if there is something wrong with
640 the file.
641 · Empty the password file:
643 mysqlmanager --clean-password-file
645 This command empties the password file, which has the effect of
647 dropping all users listed in it. The option creates the password
648 file if it does not exist, so it can be used to initialize a new
649 password file to be used for other account-management operations.
650 Take care not to use this option to reinitialize a file containing
651 accounts that you do not want to drop.
652
654 Important
655 MySQL Instance Manager has been deprecated and is removed in MySQL
656 5.5.
657 To monitor the status of each guarded server instance, the MySQL
659 Instance Manager attempts to connect to the instance at regular
660 intervals using the MySQL_Instance_Manager@localhost user account with
661 a password of check_connection.
662 You are not required to create this account for MySQL Server; in fact,
664 it is expected that it will not exist. Instance Manager can tell that a
665 server is operational if the server accepts the connection attempt but
666 refuses access for the account by returning a login error. However,
667 these failed connection attempts are logged by the server to its
668 general query log (see Section 5.2.3, “The General Query Log”).
669 Instance Manager also attempts a connection to nonguarded server
671 instances when you use the SHOW INSTANCES or SHOW INSTANCE STATUS
672 command. This is the only status monitoring done for nonguarded
673 instances.
674 Instance Manager knows if a server instance fails at startup because it
676 receives a status from the attempt. For an instance that starts but
677 later crashes, Instance Manager receives a signal because it is the
678 parent process of the instance.
679 Beginning with MySQL 5.1.12, Instance Manager tracks instance states so
681 that it can determine which commands are permitted for each instance.
682 For example, commands that modify an instance's configuration are
683 permitted only while the instance is offline.
684 Each instance is in one of the states described in the following table.
686 Guarded instances can be in any of the states. Nonguarded instances can
687 only be offline or online. Instance state information is displayed in
688 the status column of the SHOW INSTANCES and SHOW INSTANCE STATUS
689 commands.
690 ┌──────────┬─────────────────────────────┐
692 │State │ Meaning │
693 ├──────────┼─────────────────────────────┤
694 │offline │ The instance has not been │
695 │ │ started and is not │
696 │ │ running. │
697 ├──────────┼─────────────────────────────┤
698 │starting │ The instance is starting │
699 │ │ (initializing). Nonguarded │
700 │ │ instances cannot be │
701 │ │ in this │
702 │ │ state. A nonguarded │
703 │ │ instance goes directly │
704 │ │ from │
705 │ │ offline to │
706 │ │ online. │
707 ├──────────┼─────────────────────────────┤
708 │stopping │ The instance is stopping. │
709 │ │ Nonguarded instances │
710 │ │ cannot be in this state. │
711 │ │ A │
712 │ │ nonguarded instance goes │
713 │ │ directly from online to │
714 │ │ offline, │
715 │ │ or stays offline if │
716 │ │ startup fails. │
717 ├──────────┼─────────────────────────────┤
718 │online │ The instance has started │
719 │ │ and is running. │
720 ├──────────┼─────────────────────────────┤
721 │failed │ The instance was online │
722 │ │ but it crashed and is │
723 │ │ being restarted by │
724 │ │ Instance │
725 │ │ Manager, or else the │
726 │ │ instance failed to start │
727 │ │ at all and │
728 │ │ Instance Manager is again │
729 │ │ attempting to start │
730 │ │ it. │
731 │ │ Nonguarded instances │
732 │ │ cannot be in this state. │
733 ├──────────┼─────────────────────────────┤
734 │crashed │ Instance Manager failed to │
735 │ │ start the instance after │
736 │ │ several attempts. │
737 │ │ (Instance │
738 │ │ Manager will try again │
739 │ │ later.) Nonguarded │
740 │ │ instances │
741 │ │ cannot be in this state. │
742 ├──────────┼─────────────────────────────┤
743 │abandoned │ Instance Manager was not │
744 │ │ able to start the │
745 │ │ instance, has given up, │
746 │ │ and │
747 │ │ will make │
748 │ │ no further attempts until │
749 │ │ instructed │
750 │ │ otherwise. │
751 │ │ To tell Instance Manager │
752 │ │ to try again, you │
753 │ │ must first │
754 │ │ use STOP INSTANCE to put │
755 │ │ the │
756 │ │ instance in offline state, │
757 │ │ and then use │
758 │ │ START │
759 │ │ INSTANCE to start the │
760 │ │ instance. │
761 │ │ If it is │
762 │ │ necessary to make │
763 │ │ configuration changes for │
764 │ │ the │
765 │ │ instance, │
766 │ │ you must do so after │
767 │ │ putting the instance │
768 │ │ offline │
769 │ │ and before starting it. │
770 │ │ (Instance Manager │
771 │ │ accepts │
772 │ │ configuration-changing │
773 │ │ commands only for offline │
774 │ │ instances.) │
775 │ │ Nonguarded instances │
776 │ │ cannot be in this │
777 │ │ state. │
778 └──────────┴─────────────────────────────┘
779
781 Important
782 MySQL Instance Manager has been deprecated and is removed in MySQL
783 5.5.
784 After you set up a password file for the MySQL Instance Manager and
786 Instance Manager is running, you can connect to it. The MySQL
787 client/server protocol is used to communicate with the Instance
788 Manager. For example, you can connect to it using the standard mysql
789 client program:
790 shell> mysql --port=2273 --host=im.example.org --user=mysql --password
792 Instance Manager supports the version of the MySQL client/server
794 protocol used by the client tools and libraries distributed with MySQL
795 4.1 or later, so other programs that use the MySQL C API also can
796 connect to it.
797
799 Important
800 MySQL Instance Manager has been deprecated and is removed in MySQL
801 5.5.
802 After you connect to MySQL Instance Manager, you can issue commands.
804 The following general principles apply to Instance Manager command
805 execution:
806 · Commands that take an instance name fail if the name is not a valid
808 instance name.
809 · Commands that take an instance name (other than CREATE INSTANCE)
811 fail if the instance does not exist.
812 · As of MySQL 5.1.12, commands for an instance require that the
814 instance be in an appropriate state. You cannot configure or start
815 an instance that is not offline. You cannot start an instance that
816 is online.
817 · Instance Manager maintains information about instance configuration
819 in an internal (in-memory) cache. Initially, this information comes
820 from the configuration file if it exists, but some commands change
821 the configuration of an instance. Commands that modify the
822 configuration file fail if the file does not exist or is not
823 accessible to Instance Manager.
824 As of MySQL 5.1.12, configuration-changing commands modify both the
826 in-memory cache and the server instance section recorded in the
827 configuration file to maintain consistency between them. For this
828 to occur, the instance must be offline and the configuration file
829 must be accessible and not malformed. If the configuration file
830 cannot be updated, the command fails and the cache remains
831 unchanged.
832 · On Windows, the standard file is my.ini in the directory where
834 Instance Manager is installed. On Unix, the standard configuration
835 file is /etc/my.cnf. To specify a different configuration file,
836 start Instance Manager with the --defaults-file option.
837 · If a [mysqld] instance section exists in the configuration file, it
839 must not contain any Instance Manager-specific options (see the
840 section called “MYSQL INSTANCE MANAGER CONFIGURATION FILES”).
841 Therefore, you must not add any of these options if you change the
842 configuration for an instance named mysqld.
843 The following list describes the commands that Instance Manager
845 accepts, with examples.
846 · CREATE INSTANCE instance_name [option_name[=option_value], ...]
848 This command configures a new instance by creating an
850 [instance_name] section in the configuration file. The command
851 fails if instance_name is not a valid instance name or the instance
852 already exists.
853 The created section instance is empty if no options are given.
855 Otherwise, the options are added to the section. Options should be
856 given in the same format used when you write options in option
857 files. (See Section 4.2.3.3, “Using Option Files” for a description
858 of the permissible syntax.) If you specify multiple options,
859 separate them by commas.
860 For example, to create an instance section named [mysqld98], you
862 might write something like this were you to modify the
863 configuration file directly:
864 [mysqld98]
866 basedir=/var/mysql98
867 To achieve the same effect using CREATE INSTANCE, issue this
869 command to Instance Manager:
870 mysql> CREATE INSTANCE mysqld98 basedir="/var/mysql98";
872 Query OK, 0 rows affected (0,00 sec)
873 CREATE INSTANCE creates the instance but does not start it.
875 If the instance name is the (deprecated) name mysqld, the option
877 list cannot include any options that are specific to Instance
878 Manager, such as nonguarded (see the section called “MYSQL INSTANCE
879 MANAGER CONFIGURATION FILES”).
880 This command was added in MySQL 5.1.12.
882 · DROP INSTANCE instance_name
884 This command removes the configuration for instance_name from the
886 configuration file.
887 mysql> DROP INSTANCE mysqld98;
889 Query OK, 0 rows affected (0,00 sec)
890 The command fails if instance_name is not a valid instance name,
892 the instance does not exist, or is not offline.
893 This command was added in MySQL 5.1.12.
895 · START INSTANCE instance_name
897 This command attempts to start an offline instance. The command is
899 asynchronous; it does not wait for the instance to start.
900 mysql> START INSTANCE mysqld4;
902 Query OK, 0 rows affected (0,00 sec)
903 · STOP INSTANCE instance_name
905 This command attempts to stop an instance. The command is
907 synchronous; it waits for the instance to stop.
908 mysql> STOP INSTANCE mysqld4;
910 Query OK, 0 rows affected (0,00 sec)
911 · SHOW INSTANCES
913 Shows the names and status of all loaded instances.
915 mysql> SHOW INSTANCES;
917 +---------------+---------+
918 | instance_name | status |
919 +---------------+---------+
920 | mysqld3 | offline |
921 | mysqld4 | online |
922 | mysqld2 | offline |
923 +---------------+---------+
924 · SHOW INSTANCE STATUS instance_name
926 Shows status and version information for an instance.
928 mysql> SHOW INSTANCE STATUS mysqld3;
930 +---------------+--------+---------+
931 | instance_name | status | version |
932 +---------------+--------+---------+
933 | mysqld3 | online | unknown |
934 +---------------+--------+---------+
935 · SHOW INSTANCE OPTIONS instance_name
937 Shows the options used by an instance.
939 mysql> SHOW INSTANCE OPTIONS mysqld3;
941 +---------------+---------------------------------------------------+
942 | option_name | value |
943 +---------------+---------------------------------------------------+
944 | instance_name | mysqld3 |
945 | mysqld-path | /home/cps/mysql/trees/mysql-4.1/sql/mysqld |
946 | port | 3309 |
947 | socket | /tmp/mysql.sock3 |
948 | pid-file | hostname.pid3 |
949 | datadir | /home/cps/mysql_data/data_dir1/ |
950 | language | /home/cps/mysql/trees/mysql-4.1/sql/share/english |
951 +---------------+---------------------------------------------------+
952 · SHOW instance_name LOG FILES
954 The command lists all log files used by the instance. The result
956 set contains the path to the log file and the log file size. If no
957 log file path is specified in the instance section of the
958 configuration file (for example, log=/var/mysql.log), the Instance
959 Manager tries to guess its placement. If Instance Manager is unable
960 to guess the log file placement you should specify the log file
961 location explicitly by using a log option in the appropriate
962 instance section of the configuration file.
963 mysql> SHOW mysqld LOG FILES;
965 +-------------+------------------------------------+----------+
966 | Logfile | Path | Filesize |
967 +-------------+------------------------------------+----------+
968 | ERROR LOG | /home/cps/var/mysql/owlet.err | 9186 |
969 | GENERAL LOG | /home/cps/var/mysql/owlet.log | 471503 |
970 | SLOW LOG | /home/cps/var/mysql/owlet-slow.log | 4463 |
971 +-------------+------------------------------------+----------+
972 SHOW ... LOG FILES displays information only about log files. If a
974 server instance uses log tables (see Section 5.2.1, “Selecting
975 General Query and Slow Query Log Output Destinations”), no
976 information about those tables is shown.
977 Log options are described in Section 5.1.3, “Server Command
979 Options”.
980 · SHOW instance_name LOG {ERROR | SLOW | GENERAL}
982 size[,offset_from_end]
983 This command retrieves a portion of the specified log file. Because
985 most users are interested in the latest log messages, the size
986 parameter defines the number of bytes to retrieve from the end of
987 the log. To retrieve data from the middle of the log file, specify
988 the optional offset_from_end parameter. The following example
989 retrieves 21 bytes of data, starting 23 bytes before the end of the
990 log file and ending 2 bytes before the end:
991 mysql> SHOW mysqld LOG GENERAL 21, 2;
993 +---------------------+
994 | Log |
995 +---------------------+
996 | using password: YES |
997 +---------------------+
998 · SET instance_name.option_name[=option_value]
1000 This command edits the specified instance's configuration section
1002 to change or add instance options. The option is added to the
1003 section is it is not already present. Otherwise, the new setting
1004 replaces the existing one.
1005 mysql> SET mysqld2.port=3322;
1007 Query OK, 0 rows affected (0.00 sec)
1008 As of MySQL 5.1.12, you can specify multiple options (separated by
1010 commas), and SET can be used only for offline instances. Each
1011 option must indicate the instance name:
1012 mysql> SET mysqld2.port=3322, mysqld3.nonguarded;
1014 Query OK, 0 rows affected (0.00 sec)
1015 Before MySQL 5.1.12, only a single option can be specified. Also,
1017 changes made to the configuration file do not take effect until the
1018 MySQL server is restarted. In addition, these changes are not
1019 stored in the instance manager's local cache of instance settings
1020 until a FLUSH INSTANCES command is executed.
1021 · UNSET instance_name.option_name
1023 This command removes an option from an instance's configuration
1025 section.
1026 mysql> UNSET mysqld2.port;
1028 Query OK, 0 rows affected (0.00 sec)
1029 As of MySQL 5.1.12, you can specify multiple options (separated by
1031 commas), and UNSET can be used only for offline instances. Each
1032 option must indicate the instance name:
1033 mysql> UNSET mysqld2.port, mysqld4.nonguarded;
1035 Query OK, 0 rows affected (0.00 sec)
1036 Before MySQL 5.1.12, only a single option can be specified. Also,
1038 changes made to the configuration file do not take effect until the
1039 MySQL server is restarted. In addition, these changes are not
1040 stored in the instance manager's local cache of instance settings
1041 until a FLUSH INSTANCES command is executed.
1042 · FLUSH INSTANCES
1044 As of MySQL 5.1.12, FLUSH INSTANCES cannot be used unless all
1046 instances are offline. The command causes Instance Manager to
1047 reread the configuration file, update its in-memory configuration
1048 cache, and start any guarded instances.
1049 Before MySQL 5.1.12, this command forces Instance Manager reread
1051 the configuration file and to refresh internal structures. This
1052 command should be performed after editing the configuration file.
1053 The command does not restart instances.
1054 mysql> FLUSH INSTANCES;
1056 Query OK, 0 rows affected (0.04 sec)
1057
1059 Copyright © 1997, 2013, Oracle and/or its affiliates. All rights
1060 reserved.
1061 This documentation is free software; you can redistribute it and/or
1063 modify it only under the terms of the GNU General Public License as
1064 published by the Free Software Foundation; version 2 of the License.
1065 This documentation is distributed in the hope that it will be useful,
1067 but WITHOUT ANY WARRANTY; without even the implied warranty of
1068 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
1069 General Public License for more details.
1070 You should have received a copy of the GNU General Public License along
1072 with the program; if not, write to the Free Software Foundation, Inc.,
1073 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
1074 http://www.gnu.org/licenses/.
1075
1078 For more information, please refer to the MySQL Reference Manual, which
1079 may already be installed locally and which is also available online at
1080 http://dev.mysql.com/doc/.
1081
1083 Oracle Corporation (http://dev.mysql.com/).
1084MySQL 5.1 11/04/2013 MYSQLMANAGER(8)