Summary of Common Methods for Connecting to Databases in Linux Systems

1. MySQL/MariaDB Connection

1. Command Line Connection

mysql -u username -p -h host_address -P port

• Example:

  mysql -u root -p -h 127.0.0.1 -P 3306
  

• Parameter Description:
• <span><span>-u</span></span>: Username
• <span><span>-p</span></span>: Interactive password input (no space after password)
• <span><span>-h</span></span>: Database server IP (default is localhost)
• <span><span>-P</span></span>: Port (default is 3306)

2. Operations After Connection

SHOW DATABASES;      -- View all databases
USE database_name;    -- Select database
SELECT * FROM table_name;   -- Query data

2. PostgreSQL Connection

1. Command Line Connection

psql -U username -h host_address -p port -d database_name

• Example:

  psql -U postgres -h 192.168.1.100 -p 5432 -d mydb
  

2. Common Commands

\l                 -- List all databases
\c database_name   -- Switch database
\dt                -- Show all tables in the current database

3. SQLite Connection

sqlite3 database_file_path

• Example:

  sqlite3 /var/lib/app.db
  

• Feature: No server required, operates directly on the file

4. MongoDB Connection

mongo --host host_address --port port -u username -p password --authenticationDatabase auth_db

• Example:

  mongo --host 127.0.0.1 --port 27017 -u admin -p 'password' --authenticationDatabase admin
  

5. Recommended GUI Tools

6. Programming Language Connection Examples

Python (MySQL)

import mysql.connector
db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="your_password",
  database="testdb"
)
cursor = db.cursor()
cursor.execute("SELECT * FROM users")

PHP (PostgreSQL)

<?php
$conn = pg_connect("host=localhost port=5432 dbname=mydb user=postgres password=secret");
$result = pg_query($conn, "SELECT * FROM employees");
?>

7. Troubleshooting Connection Issues

1. Network Connectivity:

   telnet database_IP port  # Check if the port is open
   

2. Permission Issues:

• MySQL:<span><span>GRANT ALL ON *.* TO 'user'@'client_ip'</span></span>
• PostgreSQL: Modify<span><span>pg_hba.conf</span></span> file

sudo ufw allow 3306/tcp  # Open MySQL port

8. Security Recommendations

1. Avoid using the root account for connections
2. Disable remote root login in production environments
3. Use SSL encrypted connections
4. Change passwords regularly