MySQL¶
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 5.7 (Percona) is currently available on Simple Hosting and can be used with all languages.
You can create as many databases and users you want in your instance. 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 Simple 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 instance.
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.
Managing your database with phpMyAdmin¶
You can access your Simple Hosting database from the Database section in your instance’s management page.
Click the “Go to phpMyAdmin” button in the “Database management” widget and enter your instance user id and instance 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 Simple 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 Simple Hosting, go to your phpMyAdmin interface and click on the “Import” tab.
Bemerkung
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 instance 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.
Bemerkung
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.
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.
Bemerkung
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 instance’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 dataase 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'