How to Use a MySQL Database on Your Web Hosting¶
MySQL is the most popular database system on the Web. It powers popular application frameworks such as WordPress, Drupal or Magento and it’s the go-to database for many web developers.
MySQL versions 5.7 (Percona) and 8.0 (Percona) are currently available on Web Hosting and can be used with all languages.
You can create as many databases and users you want in your web hosting. You are only limited by the disk size, which you can increase at any time up to 1 TB.
The MySQL database service can be managed from the SSH emergency console or from a web interface. This article describes how to access, create and manage MySQL databases on Web Hosting.
Connecting to Your Database¶
The MySQL database service is available on localhost and it’s not possible to access the service from outside of the web hosting.
The preferred connection method is through the Unix socket located at the path detailed below. A default user (root) and database (default_db) exist so you can quickly test your connection and perform management tasks.
Default connection settings:
Host: localhost
Socket: /srv/run/mysqld/mysqld.sock
User: root
Password: <none>
Database: default_db
You are encouraged to create new users with strong credentials and new databases for your websites or application. You’ll find instructions on how to perform these and other management tasks below.
How to Use phpMyAdmin to Manage Your Database¶
You can access your Web Hosting database from the “Administration & Security“ section of your web hosting’s management page.
Click the “Go to Control Panel” button in the “Database” widget and enter your web hosting user id and web hosting password to authenticate with your Control Panel.
When done, you will then see the phpMyAdmin login page.
By default the phpMyAdmin user is root and there is no password. So type root as the user, leave the password empty, and click on “Go”. You will then be logged into PHPmyAdmin and can manage your databases from there.
Create a MySQL Database¶
To create a database, log into your phpMyAdmin interface from your Web Hosting admin page and then once in, click on the “Databases” tab.
Next, choose a name for your database and finish by clicking on “Create”.
Now you will want to create your users for the database.
To do this, use the “Privileges” option once you are in the administration page of the database (click on the name of the database to go there). Click the link “Add a new User” and fill out the form to create a user.
Importing an Existing Database¶
To import your database for use with Gandi’s Web Hosting, go to your phpMyAdmin interface and click on the “Import” tab.
Note
phpMyAdmin supports compressed files. They must be in the format: ‘name.(format).(compression)’
If your database is too big, however, you will need to upload its .sql file by sFTP to your /lamp0/tmp directory. Then you can find the file and import it from phpMyAdmin without encountering errors about it being too large.
Managing Your MySQL Database from the Command Line¶
To access your MySQL database by command line, you must first log into your web hosting via the SSH console.
Note that the console will automatically disconnect after a few minutes of inactivity. If this happens, you can just initiate a new SSH connection without needing to reactivate the console.
Accessing Your Database¶
Once logged into your SSH console, you can connect to your MySQL database with the MySQL client with this line (if no root password has been set):
mysql -u root
Or, if you defined a root password already, with the following:
mysql -u root -p
Exporting a Database¶
This is useful for making backups of your database. To do this, you may use the mysqldump command like this (if you want to export all of your databases):
mysqldump -u root -p --all-databases > /srv/data/tmp/backup_mysql.sql
Or like this if you want to export just one (replacing my_database with the name of your database):
mysqldump -u root -p --database my_database > /srv/data/tmp/backup_mysql.sql
It is possible to perform periodic automatic exports by using an anacron job as well. For more on this, checkout out anacron guide.
Note
Note that only the /srv/data/tmp and /srv/data/home directories can be written to - with the exception of virtualhosts. Consequently, we recommend creating a specific directory for MySQL exports in order to gather them together in the same location.
Modify the MySQL Configuration File¶
It is possible to modify some configuration parameters by editing the custom.cnf file on your web hosting. However, certain parameters cannot be modified in order to guarantee the proper functioning of the web hosting.
To modify the configuration, access your web hosting via SFTP and download the file /lamp0/etc/mysql/custom.cnf.
Edit the file locally by adding the parameters you want to modify. Values that are not changes will remain at the default values.
When you are satisfied with your changes, upload the file to your web hosting, replacing the original. You ca also add other parameter files in /lamp0/etc/myqsql/ so long as the extension of each file is “.cnf”.
For your changes to take effect, you must restart your web hosting.
Resetting the ‘root’ User Password for MySQL¶
It is possible to reset the password of the MySQL root user directly from your Gandi interface, by clicking on the “Reset the password” button.
Once you have done this, you can log into MySQL with the root user and a blank password. The act of resetting your root user password does not affect your database in any other way.
Note
Remember that you will need to modify any configuration pages of your scripts so that they can access your database in the event that you changed your password.
Logs¶
You can access the database logs from your web hosting’s Control Panel, via sFTP or SSH console.
error.log: Logs related to the MySQL server. You can view tables which have crashed and been repaired.
slow-queries.log: Here you can view the queries that take an unusually long time to execute. This can occur in the case where some tables are locked.
Log Examples¶
The following output shows MySQL has restarted:
120614 16:09:59 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.6
120614 16:09:59 InnoDB: Initializing buffer pool, size = 8.0M
120614 16:09:59 InnoDB: Completed initialization of buffer pool
120614 16:09:59 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
120614 16:09:59 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
120614 16:10:00 InnoDB Plugin 1.0.17 started; log sequence number 2657932
120614 16:10:00 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.61-2' socket: '/srv/run/mysqld/mysqld.sock' port: 0 (Debian)
In this output, it shows the tables of your database were checked; if the tables seem to have crashed, MySQL tries to repair them automatically:
120614 16:04:45 [ERROR] /usr/sbin/mysqld: Table './wp/wp_comments' is marked as crashed and should be repaired
120614 16:04:45 [Warning] Checking table: './wp/wp_comments'
120614 16:04:45 [ERROR] /usr/sbin/mysqld: Table './wp/wp_links' is marked as crashed and should be repaired
120614 16:04:45 [Warning] Checking table: './wp/wp_links'
120614 16:04:45 [ERROR] /usr/sbin/mysqld: Table './wp/wp_options' is marked as crashed and should be repaired
120614 16:04:45 [Warning] Checking table: './wp/wp_options'