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.
Table of contents
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:
- screen -S mysql -fa -d -m mysqld_safe --defaults-file=~/private/mysql/my.conf
- check running
- pgrep -fu "$(whoami)" 'mysql'
- pkill -fu "$(whoami)" 'mysql'
- 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.
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>
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.
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.