Setting Up Databases (MySQL, PostgreSQL, MSSQL) Print

Databases are essential for dynamic websites and applications, storing and managing critical data. This guide outlines the setup for popular database servers: MySQL/MariaDB, PostgreSQL (Linux), and Microsoft SQL Server (Windows).

1. Introduction
   • A database server is a software application that provides database services to other computer programs or to human users. It's where your website's content, user information, and other dynamic data are stored.
   • MySQL/MariaDB: Popular open-source relational database management systems. MariaDB is a community-developed fork of MySQL.
   • PostgreSQL: An advanced, open-source object-relational database system known for its reliability and robust features.
   • Microsoft SQL Server: A commercial, enterprise-grade relational database management system developed by Microsoft, primarily for Windows environments.
2. MySQL / MariaDB (Linux)
   • Installation:
     • Ubuntu/Debian (MySQL):sudo apt updatesudo apt install mysql-server
     • CentOS (MariaDB - often default):sudo yum install mariadb-server
   • Starting and Enabling Service:sudo systemctl start mysql # or mariadbsudo systemctl enable mysql # or mariadb
   • Initial Security Setup (Highly Recommended):sudo mysql_secure_installation
     • Follow prompts to set root password, remove anonymous users, disallow root login remotely, remove test database.
   • Creating a Database and User:-- Log in as root or sudo usermysql -u root -p-- Create a new databaseCREATE DATABASE mydatabase;-- Create a new user and grant privileges (replace 'myuser' and 'mypassword')CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost';-- Apply changesFLUSH PRIVILEGES;-- ExitEXIT;
   • Remote Access (Use with Caution!):
     • By default, MySQL/MariaDB only accepts local connections. To allow remote access, edit the configuration file (/etc/mysql/mysql.conf.d/mysqld.cnf for Ubuntu/Debian or /etc/my.cnf for CentOS) and comment out or change bind-address = 127.0.0.1 to 0.0.0.0 (or your server's public IP).
     • You'll also need to create a user that can connect from any host ('myuser'@'%') or a specific remote IP ('myuser'@'your_remote_ip').
     • Firewall: Open port 3306 for TCP traffic.
3. PostgreSQL (Linux)
   • Installation:
     • Ubuntu/Debian:sudo apt updatesudo apt install postgresql postgresql-contrib
     • CentOS:sudo yum install postgresql-server postgresql-contribsudo /usr/bin/postgresql-setup initdb # Initialize database cluster
   • Starting and Enabling Service:sudo systemctl start postgresqlsudo systemctl enable postgresql
   • Accessing PostgreSQL Shell:
     • Switch to the 'postgres' system user:sudo -i -u postgres
     • Enter the PostgreSQL interactive terminal:psql
   • Creating a Database and User:-- From psql promptCREATE DATABASE mydatabase;CREATE USER myuser WITH PASSWORD 'mypassword';GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;\q -- Exit psqlexit -- Exit postgres user
   • Configuring Client Authentication ('pg_hba.conf'):
     • Edit /etc/postgresql/[version]/main/pg_hba.conf (Ubuntu/Debian) or similar path (CentOS).
     • Add a line to allow access for your user and database (e.g., host mydatabase myuser 0.0.0.0/0 md5).
     • Reload PostgreSQL: sudo systemctl reload postgresql.
   • Remote Access (Use with Caution!):
     • Edit postgresql.conf (same directory as pg_hba.conf).
     • Uncomment and set listen_addresses = '*'.
     • Firewall: Open port 5432 for TCP traffic.
4. Microsoft SQL Server (Windows Server)
   • Installation:
     • Download the SQL Server installer (e.g., from Microsoft's website).
     • Run the installer and choose "New SQL Server stand-alone installation".
     • Follow the Setup Wizard:
       • Select features (Database Engine Services is essential).
       • Choose an Instance Name (e.g., SQLEXPRESS for free versions, or MSSQLSERVER for default).
       • Configure Server Configuration (service accounts).
       • Select Mixed Mode Authentication (SQL Server and Windows Authentication) and set a strong password for the 'sa' (System Administrator) user. Add current Windows user if needed.
       • Complete the installation.
   • SQL Server Management Studio (SSMS):
     • Download and install SSMS separately from Microsoft's website. It's the primary GUI tool for managing SQL Server.
     • Launch SSMS and connect to your SQL Server instance (e.g., localhost or localhost\SQLEXPRESS).
   • Creating a Database and Login:
     • In SSMS, right-click "Databases" -> "New Database...". Provide a database name.
     • Under "Security" -> "Logins", right-click -> "New Login...".
     • Choose "SQL Server authentication", provide a login name and password. Uncheck password policy options if desired (not recommended for production).
     • Go to "User Mapping" for the new login, select your database, and grant appropriate permissions (e.g., db_owner for full control within that database).
   • Enabling Remote Connections:
     • Open SQL Server Configuration Manager (via Start Menu or Tools in Server Manager).
     • Navigate to "SQL Server Network Configuration" -> "Protocols for [Your SQL Instance Name]".
     • Ensure TCP/IP is Enabled. Right-click TCP/IP -> Properties -> IP Addresses tab. Scroll down to "IPAll", confirm "TCP Port" is 1433.
     • Ensure SQL Server Browser service is running (if you use named instances and want to connect without specifying port).
     • Firewall: Open Windows Firewall with Advanced Security and create an "Inbound Rule" to allow connections on Port 1433 (TCP) for SQL Server.
5. Conclusion
   • Setting up a robust database is a cornerstone of server management. Always prioritize strong passwords and the principle of least privilege for database users. Regularly back up your databases to prevent data loss.