Posted on
• Web Development

Setting up MySQL/MariaDB on Linux

Author
  • User
    Linux Bash
    Posts by this author
    Posts by this author

Comprehensive Guide: Setting up MySQL/MariaDB on Linux for Web Developers

As a web developer, managing databases is a crucial part of your workflow. Whether you’re developing a new application or managing an existing one, having a reliable database system is essential. MySQL and MariaDB are two of the most popular relational database management systems, and both are widely used in web development. This guide will help you set up MySQL or MariaDB on your Linux environment, covering everything from installation to securing your database.

Choosing Between MySQL and MariaDB

Before diving into the setup process, it’s important to understand the differences between MySQL and MariaDB. MySQL is one of the oldest and most popular database systems but was acquired by Oracle in 2010. In response to concerns about Oracle’s ownership, the original developers of MySQL forked it and created MariaDB. MariaDB is fully compatible with MySQL, meaning that it supports the same commands, interfaces, and APIs, and is designed to be a drop-in replacement.

Most developers choose MariaDB over MySQL due to its open-source nature and additional features. However, both are excellent choices depending on your specific needs and compatibility requirements with other tools.

Step 1: Installing MySQL/MariaDB

Depending on your Linux distribution, the installation process may vary. Below are the installation steps for some of the most popular Linux distributions.

For Ubuntu/Debian-based systems:

To install MySQL, use:

sudo apt update
sudo apt install mysql-server

To install MariaDB, use:

sudo apt update
sudo apt install mariadb-server

For CentOS/Red Hat-based systems:

To install MySQL, follow these steps:

sudo yum install wget
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm
sudo yum update
sudo yum install mysql-server

To install MariaDB, use:

sudo yum install mariadb-server

For openSUSE systems:

To install MySQL, use:

sudo zypper refresh
sudo zypper install mysql-community-server

To install MariaDB, use:

sudo zypper refresh
sudo zypper install mariadb

Step 2: Securing Your Database

After installation, the next important step is securing your database. Both MySQL and MariaDB come with a script that helps you improve your database security.

For both MySQL and MariaDB, you can run:

sudo mysql_secure_installation

This script will guide you through setting up a root password, removing anonymous users, disallowing remote root access, and removing test databases.

Step 3: Configuring Databases

To start using your MySQL or MariaDB server, you’ll need to start the service and then log in to the database console.

Start the database server:

For systemd systems (common in latest distributions):

sudo systemctl start mariadb.service  # For MariaDB
sudo systemctl start mysql.service    # For MySQL

Enable the database server to start on boot:

sudo systemctl enable mariadb.service  # For MariaDB
sudo systemctl enable mysql.service    # For MySQL

Log in to your database:

mysql -u root -p

You will be prompted to enter the root password you set during the secure installation step.

Step 4: Creating Databases and Users

Once you are logged in, you can start creating new databases and assigning user permissions. Here’s how you can create a database and a user specifically for that database:

CREATE DATABASE mydatabase;
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;

Step 5: Testing Your Database

To ensure that everything is set up correctly, try creating a table within your new database:

USE mydatabase;
CREATE TABLE test_table (
    id INT AUTO_INCREMENT,
    sample_column VARCHAR(255),
    PRIMARY KEY (id)
);
INSERT INTO test_table (sample_column) VALUES ('Hello, Database!');
SELECT * FROM test_table;

Conclusion

Setting up MySQL or MariaDB on Linux is a straightforward process that can significantly enhance your web development capabilities. By following this guide, you can install, secure, and configure your database environment effectively, allowing you to focus more on developing robust applications for your users.

Additional Tips

  • Regularly back up your databases.

  • Keep your database server updated to protect against vulnerabilities.

  • Familiarize yourself with the advanced features that MariaDB and MySQL offer to optimize your applications' performance.

Further Reading

For further reading and more in-depth information related to setting up MySQL/MariaDB on Linux, consider the following resources:

  • MariaDB vs MySQL - Understanding the Differences: Explore detailed differences and decision-making criteria between MariaDB and MySQL. Read more here

  • MySQL Official Installation Guide: Get official instructions for installing MySQL on various Linux distributions directly from Oracle's MySQL documentation. Visit the guide

  • Secure MariaDB Installation: This tutorial explains how to secure your MariaDB installation on a Linux system to ensure safer operations. Read the tutorial

  • MySQL Performance Tuning: Dive into performance optimization for MySQL with this comprehensive guide. Start tuning

  • Learning SQL with MySQL and MariaDB: This resource provides lessons for beginners in SQL using MySQL and MariaDB, essential for managing databases effectively. Learn SQL here

These links will help you deepen your understanding of database management, security practices, and performance improvements.

Related posts