MySQL

This article will take you through the installation and usage of MySQL, a database management system which underpins many other pieces of software. For the most part no expertise is required to use the guide. Some areas will require being able to connect to your slot via SSH and you will be instructed to connect via SSH where this is the case.

Installation

It is very simple to install or reinstall MySQL as the Feral web manager supports its installation. Please follow the separate guide to installing software from the web manager, selecting MySQL from the list.

Getting your MySQL details

Once the installation has been completed your Software page (accessible via link to the left-hand side of the Feral website) will display the socket, username and password.

Starting, stopping and restarting

As part of the initial installation process MySQL will also be started up for you. Every five minutes the system will scan the processes running and if MySQL is not running it will attempt to start it up. You can still control the process manually though, as below:

start
screen -S mysql -fa -d -m mysqld_safe --defaults-file=~/private/mysql/my.conf
check running
pgrep -fu "$(whoami)" 'mysql'
stop
pkill -fu "$(whoami)" 'mysql'
restart
pkill -fu "$(whoami)" 'mysql' && sleep 3 && screen -S mysql -fa -d -m mysqld_safe --defaults-file=~/private/mysql/my.conf
kill (force stop)
pkill -9 -fu "$(whoami)" 'mysql'

If you get any errors running these commands please check the troubleshooting section.

The check running command will return a process number (or three) if MySQL is running. If it doesn't return anything, MySQL is not running.

Using MySQL

This section provides information on using MySQL via the command line. You may prefer using PHPMyAdmin if you prefer a web interface.

Connecting to the MySQL monitor

Connect to your slot via SSH then use the following command (changing the variables) to connect:

mysql --socket=path-to-socket -ppassword

Both the path-to-socket and password will be displayed on your Software page. Note that there is no space between -p and your password in the command above.

If successful, you'll see something similar to this:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.55-0+deb8u1 (Debian)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Basic operations

Below is a list of basic operations you can perform from the MySQL monitor:

List the databases
show databases;
Create a database
CREATE DATABASE dbname;
Select database as current
USE dbname
Delete database
DROP DATABASE dbname;

Replace dbname in the commands above with the database name. Database names are case sensitive.

Please note that the ; character in the commands above is mandatory.

Backing up databases

Backups are not created in the MySQL monitor - instead, you need to use another piece of software mysqldump. To do this, connect to your slot via SSH and use the following command (note that you'll need to use your actual details, rather than the variables here:

mysqldump --socket=path-to-socket -ppassword dbname > ~/dbname.sql

Both the path-to-socket and password will be displayed on your Software page. Note that there is no space between -p and your password in the command above. Replace dbname with the name of the database you wish to back up.

To restore it, you can use the following (after changing the variables as above):

mysql --socket=path-to-socket -ppassword dbname < ~/dbname.sql

If you need to restore a backup because the database no longer exists in your MySQL, please remake the database first using the commands in the Basic operations section above, then import the .sql file.

Uninstalling

pkill -9 -fu "$(whoami)" 'mysql'
rm -rf ~/private/mysql

These commands act only on your slot, so the software page will not change to reflect the fact that MySQL has been removed. The socket, user and password will remain despite the fact that MySQL is gone.

External links