Database Management: MySQL and PostgreSQL Basics on Linux

In the world of web development and data handling, databases are indispensable. Among the many database systems available, MySQL and PostgreSQL stand out due to their robustness, reliability, and open-source nature. Linux users, in particular, can benefit from the ease of managing these databases right from the Bash shell. This guide is designed to help you get started with MySQL and PostgreSQL on a Linux system, covering installation, basic operations, and tips for efficient management using different package managers such as apt (for Debian-based systems), dnf (for Fedora), and zypper (for openSUSE).

MySQL on Linux

Installation

Debian-based Systems (using apt)

To install MySQL on Debian-based systems, such as Ubuntu, you can use the apt package manager. First, update your package list:

sudo apt update

Then, install MySQL:

sudo apt install mysql-server

After the installation, it’s crucial to secure your MySQL installation:

sudo mysql_secure_installation

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

Fedora (using dnf)

On Fedora, you can install MySQL using the dnf package manager:

sudo dnf install mysql-server

Then, start and enable the MySQL service:

sudo systemctl start mysqld
sudo systemctl enable mysqld

Run the security script as well:

sudo mysql_secure_installation

openSUSE (using zypper)

For openSUSE, use the zypper package manager to install MySQL:

sudo zypper install mysql-community-server

Start the service with:

sudo systemctl start mysql
sudo systemctl enable mysql

Don't forget the security setup:

sudo mysql_secure_installation

Basic Operations

Here’s how to log into the MySQL console:

mysql -u root -p

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

To display all databases:

SHOW DATABASES;

To create a new database:

CREATE DATABASE example_db;

To delete a database:

DROP DATABASE example_db;

PostgreSQL on Linux

Installation

Debian-based Systems (using apt)

Install PostgreSQL on Ubuntu and other Debian-based systems by updating your packages and then installing PostgreSQL:

sudo apt update
sudo apt install postgresql postgresql-contrib

Fedora (using dnf)

On Fedora, use:

sudo dnf install postgresql-server postgresql-contrib

Initialize the database and enable automatic start:

sudo postgresql-setup --initdb
sudo systemctl start postgresql
sudo systemctl enable postgresql

openSUSE (using zypper)

On openSUSE, the installation command is:

sudo zypper install postgresql-server postgresql-contrib

Also, initialize and start the service:

sudo systemctl start postgresql
sudo systemctl enable postgresql

Basic Operations

PostgreSQL uses a default user named postgres. Switch to this user:

sudo -i -u postgres

Enter the PostgreSQL console:

psql

To create a new database:

CREATE DATABASE example_db;

List all databases:

\l

To exit psql, type:

\q

Conclusion

With basic operations and installation out of the way, both MySQL and PostgreSQL offer deep functionality for database management, scalable to enterprise levels. Whether it’s through apt, dnf, or zypper, Linux provides streamlined processes to manage these powerful tools efficiently from the command line.

For further learning, consider delving into more advanced topics such as user management, security practices, and optimization techniques for both MySQL and PostgreSQL. Happy database managing!