Manage MySQL/MariaDB Databases (Windows Environment)

From Wiki
Jump to: navigation, search

What is MySQL?

MySQL is an available open source Relational Database Management System (RDBMS) that uses SQL (Structured Query Language).

Why is SQL popular? MySQL is one of the easiest languages for adding, accessing and managing content in a database. It is known for its quick query processing, reliability, and easy management. A lot of popular software used by a majority of the internet uses MySQL / MariaDB databases including WordPress (Most blog sites use WordPress), Joomla, osCommerce, phpBB, etc.

What is MariaDB?

MariaDB is a fork of the MySQL database engine. It is developed on by many developers including some of the original MySQL creators of the MySQL Database Engine. It is also a free open-source project available to the public. This is the engine that is used on all of our newer shared database servers for MySQL/MariaDB.

Manage MySQL/MariaDB Databases via WCP

Let's go ahead and show how to create and manage MySQL/MariaDB databases via the WCP control panel.

  1. First login to your WCP control panel for the account the domain is under:

    Wcp url secure.png

    If you have any issues with logging into WCP then please see our tutorials on recovering your password if necessary

  1. Once logged into WCP if you have multiple domains you may need to select the appropriate domain to manage using the 'Hosted Domains' drop-down in the top-right of the page as shown in the screenshot below:

    Wcp change domain.png

  2. In your control panel click on the 'MySQL' icon under the 'Databases' section.

    Wcp mysql.png

  3. If you don't have any existing databases then skip to step 3, as you'll automatically be on the 'new database' screen.

    If you do already have existing database(s) then you should see them listed here, along with the server they reside on. Click on the 'Add Database' button to create a new database.

    Mysql add db bttn.png

  4. You will now be asked for the below database information to be created:

    Server: This Server field will show the server that your MySQL database will be created on, along with the version of MySQL / MariaDB that the server is using.

    Database Name: Enter the desired database name that you wish to create.

    User: This User drop-down allows you to select an already existing user if you have a MySQL user that is already on the server that your new database is being created on. If you do not have a pre-existing user or you don't wish to use a pre-existing user then you'll need to fill in the username and password fields.

    Username: Enter the username you wish to create that you'll use to login to the MySQL database.

    Password: Enter the password for the new MySQL user that you are creating. You'll use this password to login to the MySQL database user.

    Mysql add db.png

  5. When selecting the 'ColdFusion DataSource' checkbox a field will appear for the 'DataSource Name'.

    A data source is a saved configuration of database connection information including the Database Name, Username, Password, Port, Server, ect. This is used so you do not have to specify the connection information within your sites code and can instead just reference the singular data source name.

    Note: This feature is only viewable if your site resides on a ColdFusion server.

    Note: When selecting this option the database information you create on this page will be stored with the given DataSource Name you provide.

    Mysql add dsn checkbox.png

  6. Double check the values you have entered, then click the 'Save' button to add this MySQL database.

Managing MySQL Users

As you could see in the above section for creating a database you can create a MySQL user during the process of creating a new database. The following steps are for creating MySQL users after the database(s) have already been created and how to manage them.

Navigating to MySQL Users

  1. In your control panel click on the 'MySQL' icon under the 'Databases' section.

    Wcp mysql.png

  2. If you have databases that reside on multiple database servers, you may need to click on the pencil icon next to the related MySQL database server (you should see a list of databases on each database server in order to differentiate).

    As shown in the below screenshot we have a database on our MySQL19 server and we have a database on our MySQL20 server, so we would need to click the pencil icon next to the server we wish to manage the users for.

    Mysql server dbs.png

Add MySQL User

  1. Click on the 'Add User' button to a add a new user.

    Add mysql user.png

  2. You should now see the below requested fields for the new MySQL user:

    Username: Enter the name of the new MySQL user you would like to add.

    Password: Enter the password for the new MySQL user you would like to add.

    Permissions: The 'Permissions' section allows you to choose which databases the MySQL user has access to, as well as to what type of permissions it will have access to for that database.. for example you can enable the user to ONLY have 'Select' permissions so that the user can only view existing data in the database but not actually update, add, or delete any new content within the database.

    Mysql new user info.png

  3. Double check the values you have entered, then click the 'Save' button to add this MySQL database user and it's respective permissions.

Update MySQL User

  1. Click on the pencil icon next to the MySQL user you wish to edit.

    Edit mysql db user.png

  2. Now you will be able to update the MySQL users password and / or permissions.

    Edit mysql user info.png

  3. Double check the values you have entered, then click the 'Save' button to save any changes you've made to the MySQL user.

Delete MySQL User

  1. Click the trash can icon next to the user you wish to delete.

  2. Wcp delete mysql user.png

  3. You should receive a prompt asking if you are sure you wish to delete the selected user. Click 'OK' to delete, or 'Cancel' to cancel the deletion of the MySQL user.

Wcp mysql userdelete confirm.png