Skip to content

MySQL

MySQL - is an open-source relational database management system. Management is available from hosting account panel, phpMyAdmin and after logging into SSH with the command: devil mysql.

Warning

The MySQL server address depends on the server number. For server s0.serv00.com will be mysql0.serv00.com, for server s1.serv00.com - mysql1.serv00.com, for server s2.serv00.com - mysql2.serv00.com etc. The examples use mysqlX.serv00.com

DevilWEB

After logging into the hosting account panel, management of MySQL databases is in the MySQL tab. After clicking, a list of databases is shown.

MySQL databases - list of databases

Database management

  • To change access permissions to the database, click the Manage button and then Manage privileges.

MySQL databases - management

  • You can modify the permissions for all user's hosts by selecting the blue button or select a specific host.

MySQL databases - permissions

  • To add database permissions to a user, click + Add privileges.

Adding a database

To add a new MySQL database, click + Add database. After loading the page you have to choose:

  • database name
  • name and password of a new user (or select an existing user)
  • optionally the character comparison method (after expanding the Advanced settings).

MySQL databases - add database

List of users

Click Users to display the list of users. With added users, it is possible to edit the hosts from which connections can be made and to change the password.

MySQL databases - user list

Adding a user

To add a new user, click + Add user. After loading the page, you need to enter a Username and Password.

MySQL databases - add user

Connecting to a database from outside Serv00.com

Information

Available only for premium hosting services.

Information

The host field can be filled with the IP address (e.g. 10.10.10.1), host (e.g. example.es) or the % sign, which means any IP address.

To connect to a database outside of Serv00.com:

  • Click Manage hosts in the list of users with the selected user
  • Click + Add access from new host.
  • Fill in the Host field and click + Add.
  • Click Manage in the list of databases next to the selected database.
  • Click + Add privileges.
  • Select the previously added host and click + Add.
  • The previously selected user now has access to the selected database from the given IP address.

Devil

Configuration of MySQL is also possible from SHELL using the devil mysql module.

Adding and removing a database

Adding the MySQL database is done with the command: devil mysql db add DATABASE_NAME [LOGIN] [--collate=...] where the arguments are:

  • BASE_NAME - database name
  • LOGIN - (optional) user name, which will be automatically created, if it exists - all permissions to the indicated database will be granted to it
  • --collate=... - (optional) allows you to select the character comparison method

Deleting the MySQL database is done with the command: devil mysql db del DATABASE_NAME.

Adding and removing users

Adding a MySQL user is done with the following command: devil mysql user add LOGIN where LOGIN is its name. After its creation, you must manually set its permissions for the selected database. To remove the MySQL user, use the following command: devil mysql user del LOGIN

User password change

Changing the password is done with the command: devil mysql passwd LOGIN

User permissions

The first stage of granting permissions to users is adding access from the indicated address. To do this, use the command: devil mysql access add LOGIN@HOST where LOGIN is the name of the previously created user, and HOST is the domain, IP address or MySQL mask. Then, the previously added LOGIN@HOST combination needs to be permissions granded with the command: devil mysql privileges LOGIN[@HOST] DATABASE_NAME PERMISSIONS. PERMISSIONS are the MySQL permissions that are about to change. The +ALL and -ALL macros that set or remove all permissions are allowed, or individual permissions can be specified, for example: -INSERT +SELECT.

List of databases and users

To get a list of all databases and users use the command devil mysql list [-v]. Using the -v switch will additionally show permissions.

Examples

Information

To connect in text mode (remotely) to the database, use the following command: mysql -h mysqlX.serv00.com -u DATABASE_USER -p where X is the server number.

All of the following operations must be performed after logging into SSH.

  • Database import (UTF-8): 

    mysql --host mysqlX.serv00.com --user DATABASE_USER -p DATABASE_NAME < MY_COPY.sql

  • Database export (UTF-8): 

    mysqldump --host mysqlX.serv00.com --user DATABASE_USER -p DATABASE_NAME --no-tablespaces > MY_COPY.sql

  • Database export with stored procedures (UTF-8): 

    mysqldump --routines --host mysqlX.serv00.com --user DATABASE_USER -p DATABASE_NAME --no-tablespaces > MY_COPY.sql

  • Database export (Latin1/CP1252/ISO 8859-1): 

    mysqldump --default-character-set=latin1 --host mysqlX.serv00.com --user DATABASE_USER -p DATABASE_NAME --no-tablespaces > MY_COPY.sql

  • Database export (Latin2/ISO 8859-2): 

    mysqldump --default-character-set=latin2 --host mysqlX.serv00.com --user DATABASE_USER -p DATABASE_NAME --no-tablespaces > MY_COPY.sql

Connecting to a database from outside Serv00.com

Information

Available only for premium hosting services.

By default, the host of all MySQL database users is set to %.devil, which makes it impossible to log into the database from outside. In the example below, the ability to log in to the user m1111 from any address with all rights to the m1111_db database will be added.

  • Allow to login from any address: devil mysql access add m1111@%
  • Adding all permissions to the database m1111_db to the user m1111 allowing login from any address: devil mysql privileges m1111@% m1111_db +ALL