How to Install MySQL/MariaDB for Your Website

So You Need a Database for Your Website?

Every dynamic website needs a place to store its data - whether it's blog posts, user accounts, products, or anything else. That's where MySQL or MariaDB come in. They're the backbone of countless websites, from small personal blogs to massive applications like Facebook and Wikipedia.

I remember when I first started with web development, databases seemed intimidating. All those commands, permissions, and configuration options... where do you even start?

Turns out, setting up a database server for your website isn't as complicated as it looks. In this guide, I'll walk you through installing either MySQL or MariaDB (they're essentially the same thing for most purposes) on your Ubuntu server.

Let's get your data sorted!

MySQL vs MariaDB: What's the Difference?

Before we dive in, you might be wondering which one to choose. Here's the quick breakdown:

MySQL: - The original, created by Sun Microsystems, now owned by Oracle - Slightly more features in the enterprise versions - More common in corporate environments

MariaDB: - A fork of MySQL created by the original MySQL developers after Oracle's acquisition - Fully open source with no enterprise restrictions - Generally slightly better performance - Drop-in replacement for MySQL (same commands, same syntax)

**My recommendation?** Go with MariaDB. It's what I use on most of my projects. It's completely free, slightly faster, and you won't run into any licensing headaches down the road.

Step 1: Update Your System

Always start with an updated system. This prevents compatibility issues and ensures you have the latest security patches:

sudo apt update
sudo apt upgrade -y

Step 2: Install Your Database Server

Choose either MySQL or MariaDB - not both!

Option A: Install MySQL

sudo apt install mysql-server -y

Option B: Install MariaDB (Recommended)

sudo apt install mariadb-server -y

That's it for the installation! The package manager handles everything. Now let's get it configured properly.

Step 3: Secure Your Database Installation

This is probably the most important step. Both MySQL and MariaDB come with a handy security script that locks things down:

sudo mysql_secure_installation

This will ask you several questions. Here's what I recommend for each:

**For MySQL:** ``` Would you like to setup VALIDATE PASSWORD PLUGIN? [Y/n] Y There are three levels of password validation policy... Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2 New password: [enter a strong password] Re-enter new password: [confirm password] Remove anonymous users? [Y/n] Y Disallow root login remotely? [Y/n] Y Remove test database and access to it? [Y/n] Y Reload privilege tables now? [Y/n] Y ```

**For MariaDB:** ``` Enter current password for root (enter for none): [press Enter] Switch to unix_socket authentication [Y/n] Y Change the root password? [Y/n] Y New password: [enter a strong password] Re-enter new password: [confirm password] Remove anonymous users? [Y/n] Y Disallow root login remotely? [Y/n] Y Remove test database and access to it? [Y/n] Y Reload privilege tables now? [Y/n] Y ```

Pro tip: Use a password manager to generate and store that root password. You'll rarely need it, but when you do, you want it secure.

Step 4: Test Your Database Connection

Let's make sure everything's working. Log into your database:

sudo mysql -u root -p

Enter the password you just set. You should see something like:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 10.6.11-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> 

Type `exit` to leave the database prompt. If you can log in and out successfully, you're golden!

Step 5: Create a Database for Your Website

You shouldn't use the root account for your actual websites. Let's create a dedicated database and user:

sudo mysql -u root -p

Now run these SQL commands:

-- Create a new database
CREATE DATABASE mywebsite;

-- Create a new user
CREATE USER 'websiteuser'@'localhost' IDENTIFIED BY 'YourSecurePassword123!';

-- Give the user permissions on the database
GRANT ALL PRIVILEGES ON mywebsite.* TO 'websiteuser'@'localhost';

-- Apply the changes
FLUSH PRIVILEGES;

-- Exit
EXIT;

Important: Replace `YourSecurePassword123!` with an actually secure password. And maybe change `mywebsite` to something more descriptive for your project.

Step 6: Basic Database Configuration

The default settings work fine for most websites, but there are a few tweaks I always make:

Configure Character Set

Edit the main configuration file:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Or for MySQL:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add these lines under the `[mysqld]` section:

[mysqld]
# Set character set
character-set-server  = utf8mb4
collation-server      = utf8mb4_unicode_ci

# Basic performance tweaks
innodb_buffer_pool_size = 256M
innodb_log_file_size   = 64M
max_connections        = 100

Save the file (Ctrl+X, then Y, then Enter) and restart the database:

sudo systemctl restart mysql

Step 7: Enable Remote Access (Optional)

By default, your database only accepts connections from localhost. If you need to connect from another server (like a separate application server), you'll need to enable remote access.

Edit the configuration file again:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Find this line:

bind-address = 127.0.0.1

Change it to:

bind-address = 0.0.0.0

Then create a remote user:

CREATE USER 'websiteuser'@'%' IDENTIFIED BY 'YourSecurePassword123!';
GRANT ALL PRIVILEGES ON mywebsite.* TO 'websiteuser'@'%';
FLUSH PRIVILEGES;

Security warning: Only do this if you really need remote access, and make sure your firewall is properly configured!

Common Database Tasks

Here are some commands I use all the time:

View All Databases

SHOW DATABASES;

View Database Users

SELECT User, Host FROM mysql.user;

Backup a Database

mysqldump -u websiteuser -p mywebsite > backup.sql

Restore a Database

mysql -u websiteuser -p mywebsite < backup.sql

Check Database Status

sudo systemctl status mysql

Troubleshooting Common Issues

"Access Denied for User 'root'@'localhost'"

This usually happens if you messed up the password during the secure installation. Reset it:

sudo systemctl stop mysql
sudo mysqld_safe --skip-grant-tables &
mysql -u root
UPDATE mysql.user SET authentication_string = PASSWORD('newpassword') WHERE User = 'root';
FLUSH PRIVILEGES;
EXIT;
sudo systemctl start mysql

"Can't Connect to Local MySQL Server"

Check if the service is running:

sudo systemctl status mysql
sudo systemctl start mysql

Performance Issues

For basic optimization, I recommend installing this:

sudo apt install mysqltuner -y
mysqltuner

It'll give you specific recommendations based on your server's usage.

Best Practices for Production

• Use strong passwords - Seriously, use a password manager
• Regular backups - Set up automated daily backups
• Monitor performance - Keep an eye on slow queries
• Update regularly - Keep your database server patched
• Limit database size - Don't let logs grow infinitely

What's Next?

Congratulations! You now have a fully functional database server ready for your website. From here, you can:

• Install WordPress or another CMS
• Build a custom application
• Set up automated backups
• Configure monitoring and alerting

Remember, your database is the heart of your website. Take good care of it, backup regularly, and don't be afraid to experiment in a development environment before making changes to production.

Got questions or run into any issues? Drop them in the comments below - I've probably made every database mistake in the book at some point!

Happy data managing! 🚀