How to Manage MySQL Database

MySQL database allows you to organize data without hassle. In addition, it is necessary to run several web applications such as content management system (WordPress, Joomla, Drupal), bulletin boards and online shopping carts.

Its function is so crucial that managing databases is vital from time to time. At Crazy Domains, you can single-handedly manage your database within the Hosting Manager. In this article, we have compiled helpful articles and instructions so you could have full control over your databases.

1. How to Access MySQL Databases

Before you can manage your database, you have to access it within the Hosting Manager. Once done, you can do almost everything – from creating, modifying, renaming and deleting databases.

Here are the steps to help you access the databases:

1. Click Sign Up / Log In at the top of this page.
2. Select Hosting Manager from the drop-down menu.
3. Enter your username and password, then click Log In.
4. On the Home page, click Databases or the [ v ] Arrow Icon on its right, then click MySQL Databases.

2. How to Create a Database and a User

It is evident that you have to create a database before you can organize online data. Likewise, an assigned user/s is necessary to access and modify the database. Without the other, neither can function.

Here are some detailed instructions to help you create a database and a new user:

• How to Configure a MySQL Database

3. How to Restore Lost MySQL Database User/Password

Your MySQL databases can only be accessed by users you have granted with the permission to do so. Users are assigned a username and password to access MySQL databases.

If a user forgets his/her MySQL database username or password, this information can be checked in the Hosting Manager.

How to Check Your MySQL Database Username

If you simply want to check the name of a MySQL database privileged user, follow the steps below.

1. Access your Hosting Manager.
2. On the Home page, click the word Databases or the [ v ] arrow symbol. Then, click the MySQL Databases option.
3. Scroll to Current Users section of the page, and you will see the users and the usernames created.

How to Change Your Mysql Database Password

If you want to change the password of a current privileged user, follow the steps below: Access your Hosting Manager.

1. Access your Hosting Manager.
2. On the Home page, click the word Databases or the [ v ] arrow symbol. Then, click the MySQL Databases option.
3. Scroll to Current Users section of the page, select the username you want to change the password of, and then click the Change Password button to the right.
4. Under Set MySQL User Password, fill out the Password fields:
   

   Password

   This will serve as your new key that will enable you to access your MySQL database. The system will tell you how weak or strong your new password is when you type it in the appropriate fields. If you find it difficult to make a strong enough new password for your email, you can let the system create one for you. Click the Password Generator button. In the pop-up box, copy the generated password and paste that in the Password fields. You can also choose to check I have copied this password in a safe place. Then, click Use Password. This will automatically copy and paste the generated password in the Password fields. Important: Make sure you have saved the generated password in a safe and easy to recall place.

   Password (Again)

   This is only for confirmation and should be the same as the new Password you typed above.

5. Click the Change Password button.

A confirmation message will appear that you’ve successfully set the user’s password. Click OK to go back to the MySQL Databases main page.

4. What Is the Size Limit of MySQL Database

Your database size is limited only by the size of your Web Hosting offered by your Web Hosting plan. There is no specified limit for the size of databases.

You can view your Web Hosting plan specifications in your Account Manager and upgrade anytime for more storage space if required.

5. How to Rename Database and a User

To suit your preferences, you can rename your database and user. See simple instructions below to be guided.

Rename Database

1. On MySQL Databases page, scroll down to Current Databases.
2. Look for the database you wish to rename, and click the Rename button on its right side.
3. Enter new database name on the appropriate field.
4. Click Proceed.

Rename User

1. On MySQL Databases page, scroll down to Current Users.
2. Look for the user you wish to rename, and click the Rename button on its right side.
3. Enter new user name on the appropriate field.
4. Click Proceed.

6. How to Backup MySQL Databases

Once logged in the Hosting Manager, you can create MySQL database backup files using either the Backup or the Backup Wizard features.

With Backup

To use the Backup feature to back up your MySQL databases, follow the steps below.

1. Access your Hosting Manager.
2. On the Home page, click the word Files or the [ v ] arrow symbol on the right side. Then, click the Backup option.
3. Under Partial Backups, go to Download a MySQL Database Backup.
4. Click the Select Database field, or the arrow to the right, to access the drop-down menu.
5. Choose the database you want to download a backup copy of.
6. Click the Download button on the right.
   Your browser will then automatically start downloading the MySQL database backup file.

With Backup Wizard

To use the Backup Wizard feature to back up your MySQL database/s, follow the steps below

1. Access your Hosting Manager.
2. On the Home page, click the word Files or the [ v ] arrow symbol on the right side. Then, click the Backup Wizard option.
3. Click the Backup button.
4. In the next page, under the Select Partial Backup, click MySQL Databases.
5. Next, under the Databases column, click the database you want to create backups for.
   Your browser will then automatically start downloading the MySQL database backup file you clicked.

   Note: MySQL Database backup file will have the .sql.gz file extension.

7. How to Restore a MySQL Database Backup

Once you have your database backup files, you will now be able to restore a MySQL database with the Backup and Backup Wizard features in your control panel.

Using Backup

To restore a MySQL database backup using the Backup feature in the Hosting Manager, follow the steps below.

1. Access your Hosting Manager.
2. On the Home page, click the word Files or the [ v ] arrow symbol on the right side. Then, click the Backup option.
3. Under Partial Backups, proceed to the Restore a MySQL Database Backup section. Then, click Choose File.
4. In the pop-up box, locate and select from within your computer the apt database backup file you want to upload. You can also simply drag the said database backup file from your local hard drive to the Choose File box.
5. Click Upload.

This will take a few minutes, depending on the size of your backup file.

In the next screen, you will get a notification that reads Restoring Database and a grey box below it with a list of database files that have been restored.

Using Backup Wizard

To restore a MySQL database backup using the Backup Wizard feature in Hosting Manager, follow the steps below.

1. Access your Hosting Manager.
2. On the Home page, click the word Files or the [ v ] arrow symbol on the right side. Then, click the Backup Wizard option.
3. On the next page, click Restore. Then, click MySQL Databases.
4. Next, click Choose File, then locate and select from within your computer the apt database backup file you want to upload. You can also simply drag the said database backup file from your local hard drive to the Choose File box.
5. Click Upload.

This will take a few minutes, depending on the size of your backup file.

In the next screen, you will get a notification that reads Restoring Database and a grey box below it with a list of database files that have been restored.

Note: A MySQL Database backup file will have the .sql.gz file extension.

8. How to Delete Database and a User

After creating a backup copy of your database, you can proceed to delete it. To delete a MySQL database, follow the steps below.

1. Access your Hosting Manager.
2. On the Home page, click the word Databases or the [ v ] arrow symbol. Then, click the MySQL Databases option.
3. Under Current Databases, find the database you want to remove, and then click the Delete button to the right.
4. Next page, you will be asked if you are sure you wish to permanently remove the database. Click Delete Database to confirm.

A confirmation message will appear that you’ve successfully deleted the database. Click OK to go back to the MySQL Databases main page.

9. How to Manage MySQL User Privileges

Only users granted permission to read, create, edit, delete, or perform any actions regarding data within your MySQL databases can do so. You can set database user privileges within the Hosting Manager.

To set and/or manage database user privileges, follow the steps below:

1. Access your Hosting Manager.
2. On the Home page, click the word Databases or the [ v ] arrow symbol. Then, click the MySQL Databases.
3. On the MySQL Databases main page, under Current Databases, find the database you want to grant or deny access to, and then find the user that you want to modify the privileges of. Click the selected privileged user name.
4. Next page, make the appropriate permission changes by checking, or unchecking, the boxes that correspond to the privileges that you want to grant, or disallow, the user. You can also choose to check All Privileges to give the user permission to do everything on the list.
5. Click Make Changes.
   

   If the modifications are saved, you will get the following message:
   √ Updated the privileges for user “user” on the database “database_name”.

6. Click Go Back to go back to the MySQL Databases main page.

   Note: When modifying privileges, make sure you select the right database and privileged user name. Users may have access to more than one database.

10. How to Set up a Remote MySQL Connection

If you need to access your MySQL databases from a new device or computer, you should add your machine as an access host to your server. This connection is called a Remote MySQL Connection. You can do this by adding your computer’s IP address to the Access Hosts list within your Hosting Manager.

Note: If you don’t know your IP address, you can find out by visiting whatismyipaddress.com.

To add your IP address to the Access Host list and set up your remote MySQL connection, follow the steps below.

1. Access your Hosting Manager.
2. On the Home page, click the word Databases or the [ v ] arrow symbol. Then, click the Remote MySQL.
3. Next page, type in your computer’s IP address in the Host (% wildcard is allowed) field.
4. Click Add Host.

A confirmation message will appear that you’ve successfully set up a remote MySQL Connection. Click OK to go back to the Remote MySQL main page.

At the Remote MySQL page, should now be able to see your new computer’s IP address in the Access Hosts column. You should also be able to connect to your database from your new computer or device.

Note: There is no limit to how many access hosts you may add within your Hosting Manager, so you may add as many IP addresses as you need.

If you want to delete an IP address from the Access Hosts list, follow the steps below.

1. Access your Hosting Manager.
2. On the Home page, click the word Databases or the [ v ] arrow symbol. Then, click the Remote MySQL.
3. Next page, find the IP address you want to remove and click the corresponding Delete button to the right.
4. You will then get the following confirmation question:
   Are you sure you wish to permanently remove “(the IP address)” as an access host?
5. Click the Remove Access Host button to confirm.

A confirmation message will appear that you’ve successfully deleted an IP Address from the Access Host list Click OK to go back to the Remote MySQL main page.

11. How to Check MySQL Databases for Errors

If you are unable to access or connect to one of your MySQL databases, you can check it for errors within the Hosting Manager.

To check your MySQL database for errors, follow the steps below.

1. Access your Hosting Manager.
2. On the Home page, click the word Databases or the [ v ] arrow symbol. Then, click the MySQL Databases.
3. Under Modify Databases, go to Check Database. Select the database you want to check, then click the Check Database button to the right.
4. The system will then run a script to check the database. If the system detects an error, it will say so in the results page and will even specify where the corrupted files or sections can be found.
   
   If the database has no problems, it will display grey box with a list of sections with OK statuses and the phrase Check Complete at the bottom.

If the Checking Database test returned some errors, you can try to resolve these by running Repair Database. To know how to do this, follow the steps below.

12. How to Fix a MySQL Database

You've checked the database and found there are problems with your MySQL Database, what do you do now? You can try to fix the issues by running the Repair Database function. To do this, follow the steps below.

1. Access your Hosting Manager.
2. On the Home page, click the word Databases or the [ v ] arrow symbol. Then, click the MySQL Databases option.
3. Under Modify Databases, go to Repair Database. Select the database you want to fix, then click the Repair Database button to the right.
4. The system will then try to repair whatever is wrong in the database.

Please wait for the repair results since this may take a few minutes.

A confirmation message will appear that you’ve successfully deleted an IP Address from the Access Host list Click OK to go back to the Remote MySQL main page.

If the database issues persist, you may want to check further what is causing the problem. You can visit this linked guide to learn how to modify your MySQL database configuration settings.

If you need any further assistance, or if you have any questions, please let us know. We’d love to help!