Overview
MySQL is one of the most popular database engines used for websites. MySQL is an important part of LAMP (Linux, Apache, MySQL, PHP) stacks as well as other AMP stacks, and is supported by many web software packages.
MySQL is the only relational database software offered by DreamHost for its Shared, VPS, and Dedicated Hosting products. This article helps you install and configure it on a DreamCompute running Debian or Ubuntu.
Due to the combined memory requirements of MySQL 8 and Ubuntu 20, DreamHost recommends that you do not install MySQL 8 on "semisonic" instances.
Installing MySQL
Debian and Ubuntu sometimes offer multiple versions of MySQL in case your needs require an older version, however, it is usually best to go with the default version if there are no specific requirements needed.
To install the default version of MySQL on your system, run the following commands:
[user@instance]:$ sudo apt-get update [user@instance]:$ sudo apt-get install -y mysql-server
Viewing installed packages
You can check which packages were just install by running the following command:
[user@instance]:$ dpkg -l | grep mysql ii libaprutil1-dbd-sqlite3:amd64 1.6.1-4ubuntu2 amd64 Apache Portable Runtime Utility Library - SQLite3 Driver
ii libsqlite3-0:amd64 3.31.1-4 amd64 SQLite 3 shared library
ii mysql-client-8.0 8.0.26-0ubuntu0.20.04.2 amd64 MySQL database client binaries
ii mysql-client-core-8.0 8.0.26-0ubuntu0.20.04.2 amd64 MySQL database core client binaries
ii mysql-common 5.8+1.0.5ubuntu2 all MySQL database common files, e.g. /etc/mysql/my.cnf
ii mysql-server 8.0.26-0ubuntu0.20.04.2 all MySQL database server (metapackage depending on the latest version)
ii mysql-server-8.0 8.0.26-0ubuntu0.20.04.2 amd64 MySQL database server binaries and system database setup
ii mysql-server-core-8.0 8.0.26-0ubuntu0.20.04.2 amd64 MySQL database server binaries
ii php-mysql 2:7.4+75 all MySQL module for PHP [default]
ii php7.4-mysql 7.4.3-4ubuntu2.5 amd64 MySQL module for PHP
Configuring and using MySQL
The configuration files are stored in the /etc/mysql directory.
[user@instance]:$ ls -1 /etc/mysql /conf.d debian.cnf debian-start my.cnf my.cnf.fallback mysql.cnf /mysql.conf.d
The /etc/mysql/mysql.conf.d/mysqld.cnf config file
There are a few settings you may wish to change to customize MySQL. The following changes can be made in the /etc/mysql/mysql.conf.d/mysqld.cnf file.
Change the 'bind' address
Edit this file and you'll see the bind address:
bind-address = 127.0.0.1
This is the IP address MySQL is listening to. It can only listen to one IP address at any time. By default it will listen to 127.0.0.1 (aka localhost), meaning that the MySQL service will only be accessible from the instance it is installed on.
If you want to connect to MySQL from other DreamCompute instances, you can change this to your database instance's IPv4 or IPv6 IP address. Here is what an IPv6 configured mysql bind-address would look like:
bind-address = 2607:f298:6050:8a28:f816:3eff:fe62:c9c3
max_allowed_packet
This value is the largest size allowed for a single packet, which normally is only relevant for restoring backups. If a backup was created on an instance with a high setting for this value, it may have difficulty restoring on another machine with a lower setting. The default is 16M.
Restarting the mysql service
If any changes are made to your mysql configuration, you must restart MySQL for it to read the them. This can be done by running the following:
[user@instance]:$ sudo service mysql restart
Connecting to the mysql shell
If you need to set up new databases or configure new users, you must log into your MySQL Shell. Run any of the following command to log in.
[user@instance]:$ sudo mysql -u root -p
The prompt will change to the following:
mysql>
You can now run mysql commands to configure a database.
Secure MySQL
After MySQL has been installed, it's a good idea to secure its settings. Run the following command to open an interactive settings dialog:
[user@instance]:$ sudo mysql_secure_installation
Set a value for password validation
You can choose to enable the 'Validate Password Component' which tests the strength of your MySQL passwords. If you want to enable this, type the letter 'y'. You can then decide how secure you'd like new passwords to be. 2 is recommended.
Securing the MySQL server deployment. Connecting to MySQL using a blank password. VALIDATE PASSWORD COMPONENT can be used to test passwords and improve security. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD component? Press y|Y for Yes, any other key for No: y There are three levels of password validation policy: LOW Length >= 8 MEDIUM Length >= 8, numeric, mixed case, and special characters STRONG Length >= 8, numeric, mixed case, special characters and dictionary file Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
Create the root password
The dialog will then ask you to set the root password.
Please set the password for root here.
New password:
Re-enter new password:
Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
You are then prompted for several security settings. Enter Yes for all unless you have a specific reason to leave certain settings as they are.
Disable the anonymous user
It's a good idea to disable the anonymous user.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Disable root login
It is also recommended to disable root login.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
Remove 'test' database
You should remove the 'test' database since there's no need for it.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reload
Reload so your new settings take affect.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!