How to Create MySQL® Profiles via WHM?

MySQL is an open-source RDBMS remembered for its transparency and popularity. It has many web apps as its application. It is imperative to manage and allocate MySQL resources judiciously on shared hosting servers so that no account can usurp a disproportionate share of the resources. At the same time, the rest of the accounts should also be kept in mind. WHM is quick and easy to set up profiles of MySQL that assist you in setting resource limits and definitions for MySQL servers.

Prerequisites:

1. Root-level access to WHM

2. Basic understanding of MySQL resource management

Step 1: In this phase, open the WHM.

Access your server's WHM by visiting https://your_server_ip:8083; your root log prompts you for your credentials.

Step 2: Proceed to the MySQL Profiles Interface after MySQL Profiles.

On the WHM homepage, move to the "Databases" section and see the "MySQL Profiles" icon.

Step 3: Create a New MySQL Profile

On the "MySQL Profiles" page, click the "Add Profile" button.

Step 4: Configure Profile Settings

WHM will prompt you to configure various settings for the new MySQL profile:

1. Profile Name: Create a specification for the profile ("such as Default; High-Usage" "or Low-Usage").

2. Databases: Thus, mention the highest level of databases a user with that profile can construct.

3. Username Length: Set username length range for this account 0-50 characters accepted.

4. Query Cache: Allow or disaccess the query cache of MySQL for streamlined execution.

5. Simultaneous Connections: Limit the length of the users' connections in this group to the maximum number of parallel connections.

6. Connection Limit: Set the maximum number of users who can submit change requests and servers as the initial parameter.

7. Table Cache: Decide the maximum number of table definitions saved in a wider, expanding table cache for rapid access.

8. Join Query Cache: Enable or turn off the join query cache for improved performance.

9. Table Definition Cache: Set the number of table definitions to store in the table definition cache.

10. Table Open Cache: Define the number of open tables to cache for faster access.

11. Sort Buffer: Set the size of the buffer used for sorting operations.

12. Join Buffer: Define the size of the buffer used for joining operations.

13. Read Buffer: Set the buffer size for reading data from disk.

14. Write Buffer: Define the buffer size used for writing data to disk.

15. Temp Directory: Report output location to the temp files directory.

16. Max Allowed Packet: Establish a maximum transmission size of one packet that the server can send between and receive.

17. Max Heap Table Size: The size of the in-memory heap table used for temporary tables left to one value must be defined.

18. SQL Mode: Configure the SQL mode to enforce specific SQL syntax rules and compatibility modes.

19. Timezone: Set the default timezone for the MySQL server.

In this instance, set those settings per your demands and the expected usage of each user with this profile. This may be in the form of a "High-Usage" profile that includes higher limits for connection, cache, and buffering, and on the other hand, the "Low-Usage" profile may consist of restrictions to individual usage.

Step 5: Save the MySQL Profile

After configuring the desired settings, click the "Save" button to create the new MySQL profile.

Step 6: Assign the Profile to MySQL Users

With the new MySQL profile created, you can now assign it to specific MySQL users or user groups:

1. Navigate to "Home"> "MySQL Manager" in WHM.

2. Click the "Modify User" button next to the desired MySQL user.

3. Under the "MySQL User Profile" section, select the newly created profile from the dropdown menu.

4. Click "Save" to apply the profile to the user.

Alternatively, you can assign the profile when creating a new MySQL user by selecting it from the "MySQL User Profile" dropdown during the user creation process.

Advanced Options:

WHM's MySQL Profiles interface provides additional advanced options:

1. "Edit Profile": Modify the settings of an existing MySQL profile.

2. "Delete Profile": This will remove a MySQL profile from the server (note that users assigned to the deleted profile will be affected).

3. "Disable Profile": Temporarily turn off a MySQL profile without deleting it.

Updating and Monitoring:

As your server's usage and requirements change, you may need to update the MySQL profiles accordingly:

1. The first step is to access the respective "MySQL Profiles" interface, select the "Edit" button next to the given profile, and change the profile settings as needed.

2. Locate "Home," go to "MySQL Manager" in WHM, & check "Current Usage" in each user; & it will let you know what resources have been used.

If you notice any users consistently hitting resource limits or consuming excessive resources, consider adjusting their assigned profile or implementing additional measures like resource throttling or user education.

Best Practices:

To ensure optimal performance and fair resource allocation, follow these best practices:

1. Create multiple MySQL profiles to cater to different usage patterns and requirements.

2. Regularly review and adjust profile settings based on usage data and server performance metrics.

3. Assign appropriate profiles to MySQL users based on their expected resource needs.

4. Monitor resource usage and adjust profiles or user assignments as necessary.

5. Implement resource throttling or user education for consistently high-consuming users.

6. Keep profiles up-to-date with MySQL's and your hosting provider's latest security and performance recommendations.

Conclusion:

Creating and managing MySQL profiles via WHM is a powerful tool for shared hosting providers and server administrators. By defining resource limits and configurations through profiles, you can ensure fair resource allocation, prevent excessive resource consumption by individual users, and maintain optimal server performance. Follow the steps outlined in this guide to create and assign MySQL profiles effectively and leverage the advanced options for fine-tuning and monitoring. Remember to implement best practices, regularly review and update profiles, and seek assistance from your hosting provider or MySQL documentation when needed.