Setting up a database server is an essential task for anyone looking to build web applications or manage data efficiently. In this tutorial, we’ll be discussing how to set up a database server on CentOS 7 using either MySQL or PostgreSQL.
Step 1: Installing MySQL or PostgreSQL
To install MySQL or PostgreSQL, you’ll need to access the CentOS terminal using SSH or by opening the terminal on your local system if you are using a desktop version of CentOS.
To install MySQL, enter the following command:
sudo yum install mysql-server
To install PostgreSQL, enter the following command:
sudo yum install postgresql-server
Once the installation is complete, you’ll need to start the MySQL or PostgreSQL service by running the following command:
For MySQL:
sudo systemctl start mysqld
For PostgreSQL:
sudo systemctl start postgresql
Step 2: Configuring MySQL or PostgreSQL
Once the service has started, you’ll need to configure it by setting up a root password and other necessary settings.
To configure MySQL, enter the following command:
sudo mysql_secure_installation
This command will prompt you to set up a root password and remove any test databases and anonymous users.
To configure PostgreSQL, enter the following command:
sudo postgresql-setup initdb
This command will initialize the PostgreSQL database cluster and create the necessary directories and files.
Step 3: Accessing MySQL or PostgreSQL
To access MySQL or PostgreSQL, you’ll need to use a client application such as MySQL Workbench or pgAdmin.
For MySQL, you’ll need to use the following command to allow remote access to the server:
sudo firewall-cmd --permanent --zone=public --add-service=mysql
After running this command, you’ll need to reload the firewall rules by running the following command:
sudo firewall-cmd --reload
For PostgreSQL, you’ll need to edit the postgresql.conf file by running the following command:
sudo nano /var/lib/pgsql/data/postgresql.conf
In this file, you’ll need to change the listen_addresses parameter to allow remote access:
listen_addresses = '*'
After making this change, you’ll need to edit the pg_hba.conf file by running the following command:
sudo nano /var/lib/pgsql/data/pg_hba.conf
In this file, you’ll need to add the following line to allow remote access:
host all all 0.0.0.0/0 md5
After making these changes, you’ll need to restart the PostgreSQL service by running the following command:
sudo systemctl restart postgresql
Step 4: Creating a Database
To create a new database, you’ll need to use the MySQL or PostgreSQL client application.
For MySQL, you can use the following command:
CREATE DATABASE dbname;
For PostgreSQL, you can use the following command:
CREATE DATABASE dbname;
After creating the database, you can create tables and insert data using SQL queries.
Conclusion
Set up a database server on CentOS 7 using MySQL or PostgreSQL can seem intimidating, but it’s a straightforward process if you follow the steps outlined in this tutorial. With your new database server up and running, you can start building applications or managing your data more efficiently.
