Knowledgebase

     Creating Databases

MySQL Databases are used by script applications such as Forums, Shopping Carts and CMS Systems like Wordpress or Drupal.  Most popular scripts can be installed using the Script Installer which will automatically generate and configure any Databases required.
 

MySQL Wizard

If you are installing your own scripts that require MySQL then you should use the MySQL Wizard Icon in your cPanel.

Create a new MySQL database

1. Enter a database name in the New Database box click create
2. Enter a database   User and Password click create User
3. Select Permissions (recommend all) then Next Step
4. Wrote down your Database Settings.

MySQL Databases

If  you want to view the current MySQL Databases and Database Users and perform DB Index Checks and Repairs then you should use the MySQL Database Icon in your cPanel.

Check a MySQL database for errors or corruption

1. Select a database from the Check DB menu.
2. Click Check DB.

Repair a MySQL database with errors or corruption

1. Select a database from the Repair DB menu.
2. Click Repair DB.

Delete a MySQL database

1. Click Delete Database. You will be directed to a new page.
2. Click Yes to permanently remove the database, or click No to cancel the removal.

Add a new MySQL user

You will need to create MySQL user accounts separately from mail and web administrator accounts.

1. Enter the username, password, and password confirmation in the appropriate boxes.
   Note: The Strength box will show you the strength of your password choice. If you cannot think of a strong password, click Password Generator for an appropriate option.
2. Click Create User.

Add a MySQL user to a MySQL database

For a user to be able to access a database, you will need to assign the user to the database.

1. Select the appropriate user from the User menu.
2. Select the appropriate database from the Database menu.
3. Click Add.

Delete a MySQL user

1. Click X. You will be directed to a new page.
2. Click Yes to permanently remove the user, or click No to cancel the removal.

Delete a MySQL user from a specific database

1. In the Current Databases table, locate the user whose privileges you wish to delete.
2. Click the X. You will be directed to a new page.
3. Click Yes to permanently remove the user, or click No to cancel the removal

PhpMyAdmin To Edit Your Databases

If you need to manually  edit your Database you can use the PhpMyadmin tool located in your cPanel.

*** Note  PhpMyadmin uses your cPanel login to define permissions so you must be logged directly into your cpanel not your master account or it will not be able to access the database.  You can find your direct cPanel credentials via your Account portal under services / MyServices / View Details / Change Password tab.

Using PHP My Admin To Manage Your Database

• browse and drop databases, tables, views, fields and indexes
• create, copy, drop, rename and alter databases, tables, fields and indexes
• maintenance server, databases and tables, with proposals on server configuration
• execute, edit and bookmark any SQL-statement, even batch-queries
• load text files into tables
• create and read dumps of tables
• export data to various formats: CSV, XML, PDF, ISO/IEC 26300 - OpenDocument Text and Spreadsheet, Word, Excel andLATEX formats
• import data and MySQL structures from Microsoft Excel and OpenDocument spreadsheets, as well as XML, CSV, and SQLfiles
• administer multiple servers
• manage MySQL users and privileges
• check referential integrity in MyISAM tables
• using Query-by-example (QBE), create complex queries automatically connecting required tables
• create PDF graphics of your Database layout
• search globally in a database or a subset of it
• transform stored data into any format using a set of predefined functions, like displaying BLOB-data as image or download-link
• track changes on databases, tables and views
• support InnoDB tables and foreign keys
• support mysql, the improved MySQL extension
• synchronize two databases residing on the same as well as remote servers

For additional help using PhpMyadmin see their website.

Using Your MySQL Databases

When installing a script on your server it will ask you to supply the Database Credentials:

1. Database Name:  this will be the name as specified by cpanel in format user_dbname

2. Database User:  this will be the user name specified when the DB was created

3. Host:  this will always be     localhost   for any script that runs on the server

4. Password:   what ever password you specified when creating the database

I Lost my Database password?

Database passwords are encrypted on the server.  You can generally find them out by analyzing the configuration files for your scripts that call the database.  If you have difficulty open a Support Ticket and specify the script name and database name, and our staff will attempt to retrieve it for you.

Upgrading To MariaDB

MariaDB is now the default database for cPanel and all new servers will use MariaDB.  MariaDB is an enhanced drop-in replacement for MySQL.  It is built on the same foundation as MySQL, providing a robust, reliable, and scalable database server. It offers enhanced features and performance over MySQL while maintaining full compatibility.

MariaDB also provides a number of features that MySQL does not, such as:

• Support for pluggable storage engines
• A more robust and extensible SQL parser
• Improved security
• Enhanced performance
• Support for virtual columns

Query Execution: MariaDB has faster query execution due to parallel processing for analytics workload, whereas, MySQL is slower at processing query-intensive tasks. 

Storage Engines: MariaDB supports larger datasets and used diverse engines such as MyRock, Aria, and ColumnStore according to the use case. MySQL is limited to InnoDB or MyISAM engines. 

Replication:  MariaDB is an advanced multi-source replication and Galera Cluster for synchronous multi-master setup, whereas MySQL lacks native multi-master support for replication. 

Scalability:  MariaDB excels in distributed environments with sharding and Galera Cluster wheras MySQL only scales in traditional setups. 

Bulk Operations: MariaDB has faster bulk inserts and updates as compared to MySQL. 

Concurrency Management:  MariaDB has efficient thread pooling and caching for high concurrency environments and can handle far higher 200,000+ connections whereas MySQL uses older connection based thread handling. 

Compatibility: MariaDB is fully backward compatible with MySQL.

Storage Engines: MariaDB offers additional engines like TokuDB and MyRocks for specialized tasks. MySQL focuses on InnoDB primarily. 

JSON Support: MariaDB has enhanced JSON features for complex, semi-structured data handling, MySQL only has the basic JSON functionality. 

Tool Integration:  MariaDB works with the phpmyadmin in your cpanel.