MySQL on Simple 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 5.7 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.

The MySQL database service can be managed from the console or from a Web interface. This article describes how to access, create and manage MySQL databases on Simple Hosting.

Connecting to your MySQL 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 MySQL database with phpMyAdmin

You can access your Simple Hosting database by clicking on the “login” link that you see corresponding to your dabase in the 'Access' section of your instances' management page:

When you click on the link to login, you will then enter your Simple Hosting instance ID number and the admin password of your instance (the one that you specified when you created it). 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 normally.

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.

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 thism i fyou 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, visit this page.

Note that only the '/srv/data/tmp' directory 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” link that you see in your Instance's management area. Once you have done this, you can log into MySQL with the user “root” and a password that is empty. The act of resetting your “root” user password does not affect your database in any other way.

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.

See also

Last modified: 07/06/2018 at 02:14 by Richard M. (Gandi)