Skip to content

MySQL

MySQL is a freely available relational database management system. Management is available from admin panel, phpMyAdmin and after logging in to SSH with the command: devil mysql.

Attention

The MySQL server address depends on the shell server number. For the s0.mydevil.net server, it will be the address mysql0.mydevil.net, for the s1.mydevil.net server, mysql1.mydevil.net, for the s2.mydevil.net server, mysql2.mydevil.net, etc. The examples use the address mysqlX.mydevil .net.

DevilWEB

After logging in to the admin 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 permissions.

MySQL databases - management

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

MySQL databases - permissions

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

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 by the character comparison method (after expanding the advanced settings option).

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 it is possible to log in 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 name and password.

MySQL databases - add user

Connecting to a database from outside Serv00.com

Information

The host field can be filled with the IP address (e.g. 10.10.10.1), host (e.g. example.pl) 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 address.
  • Fill in the host field and click + Add.
  • Click Manage in the list of databases next to the selected database.
  • Click + Add permissions.
  • 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 myssql user del LOGIN

User password change

Changing the password is done with the command: devil mysql password 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 changed with the command devil mysql privileges LOGIN[@HOST] DATABASE_NAME AUTHORIZATION. PERMITS 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 DATAUSER -p where X is the server number.

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

  • Database import (UTF-8): 

    mysql --host mysqlX.serv00.com --user db_user -p db_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

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

  • Allowed login from any address: devil mysql access add m1111@%
  • Adding all permissions to the database m1111_baza to the user m1111 logged in from any address: devil mysql privileges m1111@% m1111_baza +ALL