MySQL Server Guide ================== The InterWorx Control Panel provides and easy-to-use interface for managing the System-level MySQL Service. From the MySQL Overview page in NodeWorx, server administrators may manage the start-up options for the MySQL service, reset the MySQL root password, terminate existing MySQL queries, and control MySQL settings. .. contents:: To Manage the MySQL Service --------------------------- Checking MySQL Status ^^^^^^^^^^^^^^^^^^^^^ #. Log into NodeWorx from the browser (https://ip.ad.dr.ess:2443/nodeworx) #. In NodeWorx, navigate to **System Services > MySQL Server > Overview** .. image:: /images/nw-mysql-overview.png :alt: mysql overview page #. Under MySQL Server Control, the MySQL Status bar indicates the current status of the service .. image:: /images/mysql-server-control.png :alt: mysql status - It is also possible to control the status of the service from this interface: - **Play** starts the service - **Arrow** restarts/refreshes the service - **Stop** stops the service Setting MySQL Start-On-Boot Options ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ #. Log into NodeWorx from the browser (https://ip.ad.dr.ess:2443/nodeworx) #. In NodeWorx, navigate to **System Services > MySQL Server > Overview** .. image:: /images/nw-mysql-overview.png :alt: mysql overview page #. Under MySQL Control, select either **Yes** or **No** from the Start on Boot-up dropdown .. image:: /images/nw-mysql-onboot.png :alt: mysql onboot options #. Click **Update** Setting MySQL Auto-Restart Options ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ #. Log into NodeWorx from the browser (https://ip.ad.dr.ess:2443/nodeworx) #. In NodeWorx, navigate to **System Services > MySQL Server > Overview** .. image:: /images/nw-mysql-overview.png :alt: mysql overview page #. Under MySQL Control, select either **Yes** or **No** from the Auto-restart MySQL dropdown .. image:: /images/nw-mysql-autorestart.png :alt: mysql autorestart options #. Click **Update** To Change the MySQL Root Password --------------------------------- #. Log into NodeWorx from the browser (https://ip.ad.dr.ess:2443/nodeworx) #. In NodeWorx, navigate to **System Services > MySQL Server > Overview** .. image:: /images/nw-mysql-overview.png :alt: mysql overview page #. Under MySQL Root Password update the **Password** and **Confirm Password** options with the new password - CLicking the **magic wand** will generate a password .. image:: /images/mysql-root-password.png :alt: mysql root password #. Click **Update** To Terminate Running MySQL Queries ---------------------------------- #. Log into NodeWorx from the browser (https://ip.ad.dr.ess:2443/nodeworx) #. In NodeWorx, navigate to **System Services > MySQL Server > Overview** .. image:: /images/nw-mysql-overview.png :alt: mysql overview page #. Under Current MySQL Processes, select the **checkbox** next to the processes/queries to be terminated - Selecting the **checkbox** next to the With Selected dropdown will automatically select all .. note:: If there are no active or running processes, the Current MySQL Processes section will state "No Active MySQL Processes/Queries". .. image:: /images/mysql-process-management.png :alt: mysql processes #. From the With Selected dropdown, select **Terminate** #. Click **Go** To Edit MySQl Service Options ----------------------------- The most common configuration options are exposed in the InterWorx Control Panel. As with many of the system services, a system administrator still retains the ability to configure the service by editing the configuration file. #. Log into NodeWorx from the browser (https://ip.ad.dr.ess:2443/nodeworx) #. In NodeWorx, navigate to **System Services > MySQL Server > Overview** .. image:: /images/nw-mysql-overview.png :alt: mysql overview page #. Under MySQL Server Options, update the desired fields: .. image:: /images/mysql-server-options.png :alt: mysql server options - **Connections**: The maximum number of simultaneous connections allowed for MySQL .. note:: Increasing max_connections increases the number of file descriptors that mysqld requires. It is strongly suggested that the maximum number of connections is kept below 1500. - **Connection Errors**: The maximum number of allowed connection errors for a given host - **Connect Timeout**: The maximum number of seconds before a connection to the MySQL server will time out - **Wait Timeout**: The number of seconds the server waits for activity on a connection before closing the connection - **Key Buffer Size**: The key buffer is a memory buffer shared by all threads. Increasing the key buffer size will result in better index handling for all reads, and multiple writes .. note:: If set too large, for instance more than 50% of the total memory, the system may start to page and become extremely slow. It is important to remember that, because MySQL does not cache data reads, leaving some room for the OS filesystem cache is required. - **Sort Buffer Size**: The size of the buffer used when sorting table data. Increasing this value will lead to faster ORDER BY or GROUP BY operations - **Read Buffer Size**: Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If many sequential scans are performed regularly, it may be prudent to increase this value - **Maximum Allowed Packet Size**: The maximum size of one packet. By default, the value is small so as to catch large, possibly wrong, packets. Using big BLOB columns will require increasing this value, so that it is large as the biggest BLOB in use - **Thread Cache Size**: The number of threads that are kept in the cache for reuse. This variable can be increased to improve performance if there are a lot of new connections.Increasing this value increases the number of file descriptors that mysqld requires - **Open Table Cache**: This variable controls the number of open tables that are cached. Table cache is related to max connections. For example, for 200 concurrent running connections, the table cache should be set to at leat 200 \* N, where N is the maximum number of tables in a join. If this value is increased, the number of file descriptors needed by mysqld will also increase - **Query Cache Limit**: Sets the maximum amount of memory to be allocated for storage of old query results - **Query Cache Size**: Sets size of the query cache. If set to 0, query caching is disabled #. Click **Update**