MySQL
Warning
THIS PAGE HAS BEEN TRANSLATED AUTOMATICALLY - MAY CONTAIN INCORRECT OR OUTDATED INFORMATION.
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
.
Warning
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.
Database management
- To change access permissions to the database, click the
Manage
button and thenmanage permissions
.
- You can modify the permissions for all user hosts by selecting the blue button or select a specific host.
- 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).
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.
Adding a user
To add a new user, click + Add user
. After loading the page, you need to enter a name
and password
.
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
Warning
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 userm1111
allowing login from any address:devil mysql privileges m1111@% m1111_db +ALL