Skip navigation links
4.6.11 mysql_convert_table_format — Convert Tables to Use a Given Storage Engine »
Section Navigation      [Toggle]

4.6.10.7. MySQL Instance Manager Commands

Important

MySQL Instance Manager has been deprecated and is removed in MySQL 5.4.

After you connect to MySQL Instance Manager, you can issue commands. The following general principles apply to Instance Manager command execution:

  • Commands that take an instance name fail if the name is not a valid instance name.

  • Commands that take an instance name (other than CREATE INSTANCE) fail if the instance does not exist.

  • As of MySQL 5.1.12, commands for an instance require that the instance be in an appropriate state. You cannot configure or start an instance that is not offline. You cannot start an instance that is online.

  • Instance Manager maintains information about instance configuration in an internal (in-memory) cache. Initially, this information comes from the configuration file if it exists, but some commands change the configuration of an instance. Commands that modify the configuration file fail if the file does not exist or is not accessible to Instance Manager.

    As of MySQL 5.1.12, configuration-changing commands modify both the in-memory cache and the server instance section recorded in the configuration file to maintain consistency between them. For this to occur, the instance must be offline and the configuration file must be accessible and not malformed. If the configuration file cannot be updated, the command fails and the cache remains unchanged.

  • On Windows, the standard file is my.ini in the directory where Instance Manager is installed. On Unix, the standard configuration file is /etc/my.cnf. To specify a different configuration file, start Instance Manager with the --defaults-file option.

  • If a [mysqld] instance section exists in the configuration file, it must not contain any Instance Manager-specific options (see Section 4.6.10.2, “MySQL Instance Manager Configuration Files”). Therefore, you must not add any of these options if you change the configuration for an instance named mysqld.

The following list describes the commands that Instance Manager accepts, with examples.

  • CREATE INSTANCE instance_name [option_name[=option_value], ...]

    This command configures a new instance by creating an [instance_name] section in the configuration file. The command fails if instance_name is not a valid instance name or the instance already exists.

    The created section instance is empty if no options are given. Otherwise, the options are added to the section. Options should be given in the same format used when you write options in option files. (See Section 4.2.3.3, “Using Option Files” for a description of the allowable syntax.) If you specify multiple options, separate them by commas.

    For example, to create an instance section named [mysqld98], you might write something like this were you to modify the configuration file directly: 

    [mysqld98]
basedir=/var/mysql98

    To achieve the same effect via CREATE INSTANCE, issue this command to Instance Manager: 

    mysql> CREATE INSTANCE mysqld98 basedir="/var/mysql98";
Query OK, 0 rows affected (0,00 sec)

    CREATE INSTANCE creates the instance but does not start it.

    If the instance name is the (deprecated) name mysqld, the option list cannot include any options that are specific to Instance Manager, such as nonguarded (see Section 4.6.10.2, “MySQL Instance Manager Configuration Files”).

    This command was added in MySQL 5.1.12.

  • DROP INSTANCE instance_name

    This command removes the configuration for instance_name from the configuration file. 

    mysql> DROP INSTANCE mysqld98;
Query OK, 0 rows affected (0,00 sec)

    The command fails if instance_name is not a valid instance name, the instance does not exist, or is not offline.

    This command was added in MySQL 5.1.12.

  • START INSTANCE instance_name

    This command attempts to start an offline instance. The command is asynchronous; it does not wait for the instance to start. 

    mysql> START INSTANCE mysqld4;
Query OK, 0 rows affected (0,00 sec)

  • STOP INSTANCE instance_name

    This command attempts to stop an instance. The command is synchronous; it waits for the instance to stop. 

    mysql> STOP INSTANCE mysqld4;
Query OK, 0 rows affected (0,00 sec)

  • SHOW INSTANCES

    Shows the names and status of all loaded instances. 

    mysql> SHOW INSTANCES;
+---------------+---------+
| instance_name | status  |
+---------------+---------+
| mysqld3       | offline |
| mysqld4       | online  |
| mysqld2       | offline |
+---------------+---------+

  • SHOW INSTANCE STATUS instance_name

    Shows status and version information for an instance. 

    mysql> SHOW INSTANCE STATUS mysqld3;
+---------------+--------+---------+
| instance_name | status | version |
+---------------+--------+---------+
| mysqld3       | online | unknown |
+---------------+--------+---------+

  • SHOW INSTANCE OPTIONS instance_name

    Shows the options used by an instance. 

    mysql> SHOW INSTANCE OPTIONS mysqld3;
+---------------+---------------------------------------------------+
| option_name   | value                                             |
+---------------+---------------------------------------------------+
| instance_name | mysqld3                                           |
| mysqld-path   | /home/cps/mysql/trees/mysql-4.1/sql/mysqld        |
| port          | 3309                                              |
| socket        | /tmp/mysql.sock3                                  |
| pid-file      | hostname.pid3                                     |
| datadir       | /home/cps/mysql_data/data_dir1/                   |
| language      | /home/cps/mysql/trees/mysql-4.1/sql/share/english |
+---------------+---------------------------------------------------+

  • SHOW instance_name LOG FILES

    The command lists all log files used by the instance. The result set contains the path to the log file and the log file size. If no log file path is specified in the instance section of the configuration file (for example, log=/var/mysql.log), the Instance Manager tries to guess its placement. If Instance Manager is unable to guess the log file placement you should specify the log file location explicitly by using a log option in the appropriate instance section of the configuration file. 

    mysql> SHOW mysqld LOG FILES;
+-------------+------------------------------------+----------+
| Logfile     | Path                               | Filesize |
+-------------+------------------------------------+----------+
| ERROR LOG   | /home/cps/var/mysql/owlet.err      | 9186     |
| GENERAL LOG | /home/cps/var/mysql/owlet.log      | 471503   |
| SLOW LOG    | /home/cps/var/mysql/owlet-slow.log | 4463     |
+-------------+------------------------------------+----------+

    SHOW ... LOG FILES displays information only about log files. If a server instance uses log tables (see Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”), no information about those tables is shown.

    Log options are described in Section 5.1.2, “Server Command Options”.

  • SHOW instance_name LOG {ERROR | SLOW | GENERAL} size[,offset_from_end]

    This command retrieves a portion of the specified log file. Because most users are interested in the latest log messages, the size parameter defines the number of bytes to retrieve from the end of the log. To retrieve data from the middle of the log file, specify the optional offset_from_end parameter. The following example retrieves 21 bytes of data, starting 23 bytes before the end of the log file and ending 2 bytes before the end: 

    mysql> SHOW mysqld LOG GENERAL 21, 2;
+---------------------+
| Log                 |
+---------------------+
| using password: YES |
+---------------------+

  • SET instance_name.option_name[=option_value]

    This command edits the specified instance's configuration section to change or add instance options. The option is added to the section is it is not already present. Otherwise, the new setting replaces the existing one. 

    mysql> SET mysqld2.port=3322;
Query OK, 0 rows affected (0.00 sec)

    As of MySQL 5.1.12, you can specify multiple options (separated by commas), and SET can be used only for offline instances. Each option must indicate the instance name: 

    mysql> SET mysqld2.port=3322, mysqld3.nonguarded;
Query OK, 0 rows affected (0.00 sec)

    Before MySQL 5.1.12, only a single option can be specified. Also, changes made to the configuration file do not take effect until the MySQL server is restarted. In addition, these changes are not stored in the instance manager's local cache of instance settings until a FLUSH INSTANCES command is executed.

  • UNSET instance_name.option_name

    This command removes an option from an instance's configuration section. 

    mysql> UNSET mysqld2.port;
Query OK, 0 rows affected (0.00 sec)

    As of MySQL 5.1.12, you can specify multiple options (separated by commas), and UNSET can be used only for offline instances. Each option must indicate the instance name: 

    mysql> UNSET mysqld2.port, mysqld4.nonguarded;
Query OK, 0 rows affected (0.00 sec)

    Before MySQL 5.1.12, only a single option can be specified. Also, changes made to the configuration file do not take effect until the MySQL server is restarted. In addition, these changes are not stored in the instance manager's local cache of instance settings until a FLUSH INSTANCES command is executed.

  • FLUSH INSTANCES

    As of MySQL 5.1.12, FLUSH INSTANCES cannot be used unless all instances are offline. The command causes Instance Manager to reread the configuration file, update its in-memory configuration cache, and start any guarded instances.

    Before MySQL 5.1.12, this command forces Instance Manager reread the configuration file and to refresh internal structures. This command should be performed after editing the configuration file. The command does not restart instances. 

    mysql> FLUSH INSTANCES;
Query OK, 0 rows affected (0.04 sec)