Database Server - MySQL


I originally started working on this guide with the assumption it would be used just for others with a Raspberry Pi. Since I use Raspbian, these commands should work the same with Debian or Ubuntu.

(This guide assumes that you are running all commands elevated, either as the root user or with the sudo command, and that your system is fully up to date.)

While the MySQL/MariaDB database server can be used with just the command line, you may want to use the web-based phpMyAdmin to make things easier. To use phpMyAdmin you should have a web server with PHP already set up.

First, install MySQL or MariaDB:

# sudo apt install mysql-server

Some installations of MySQL may prompt you for its root password (note that this is for the root account for MySQL, not the root account of your system).

If the MySQL installation did not ask for a password, it probably uses a blank password for root.

You should be able to set the root password with this program:

# sudo mysql_secure_installation

At this point, if you're installing MySQL on an underpowered device such as a Raspberry Pi, you will want to set MySQL to default to MyISAM instead of InnoDB.

Locate your MySQL configuration file (which was "/etc/mysql/mariadb.conf.d/50-server.cnf" on my Raspberry Pi) and add this to it:

# use MyISAM instead of InnoDB
default-storage-engine = myisam

Some installations of MySQL will only allow the local root account to log into the MySQL root account. To change that, you can run this:

# mysql -u root
> update mysql.user set plugin='' where User='root';
> flush privileges;
> exit;

I use phpMyAdmin to manage my MySQL databases. During the installation of phpMyAdmin it will ask you if you wish to have it auto-configure Apache or lighthttp. If you followed my web server guide, and have neither installed (if you're using nginx), leave both unchecked.

The installer will also ask you for the MySQL root password you created when you installed MySQL. Enter it here.
When the installer asks you to create a password to use for phpMyAdmin's own database, you can leave that field blank and just press Enter to have it automatically generate a random one (it will only be used by phpMyAdmin, so it's not too important to remember it).

# apt install phpmyadmin

If you followed my web guide, you should already have a web root set up. You can put a symbolic link to phpMyAdmin in the web root with this command (which is all on one line):

# ln -s /usr/share/phpmyadmin /srv/www/default/htdocs/phpmyadmin

You should then be able to bring up phpMyAdmin in your web browser by going to "http://<ip-address>/phpmyadmin". Log in with the MySQL root account.

(Page ID: 2821)