Pages

Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Friday, February 16, 2024

All the databases are showing size 0 MB in cpanel

When managing a server with cPanel, encountering an issue where all databases are showing as 0 MB in size can be concerning. This typically indicates a configuration issue rather than actual data loss. The solution involves a simple tweak in the cPanel configuration file and running a script to update the database cache. Here's a step-by-step guide to resolve this problem:

Step 1: Access the Server

First, you need to access your server's command line interface (CLI). This is usually done via SSH. Once logged in as the root user, you're ready to proceed to the next step.

Step 2: Edit the cPanel Configuration File

The cPanel configuration file, cpanel.config, contains various settings that control how cPanel behaves. To edit this file, you'll use the vi editor, a widely-used text editor in Unix-like operating systems. The command to open the file is:vi /var/cpanel/cpanel.config

Step 3: Modify the Configuration

While in the vi editor, you're looking to change the setting that includes database sizes in the disk usage calculations. By default, or due to a misconfiguration, this setting might be disabled (set to 0). You need to find the line that reads:
disk_usage_include_sqldb=0
and change it to:
disk_usage_include_sqldb=1

This change tells cPanel to include SQL database sizes in its disk usage calculations.

To edit in vi:
Navigate to the line using the arrow keys.
Press i to enter insert mode.
Make the necessary changes.
Press Esc to exit insert mode.
Type :wq and press Enter to save the changes and quit vi.

Step 4: Run the Update Script

After saving the changes to the cpanel.config file, you'll need to update the cPanel database cache. This is done by running a script included with cPanel:/scripts/update_db_cache


This script will process the change you've made and update the database sizes accordingly.
Conclusion

Following these steps should resolve the issue of databases showing as 0 MB in size within cPanel. It's a straightforward fix that addresses a common configuration oversight. After running the update script, you can verify the changes by checking the disk usage statistics in cPanel again. If the problem persists, it might be wise to consult with cPanel's documentation or reach out to their support for further assistance.

Friday, January 12, 2024

Enabling Remote Connections to PostgreSQL Database

Introduction:

By default, PostgreSQL is configured to be accessed only from the local machine. To access your PostgreSQL database remotely, you'll need to modify a couple of configuration files: pg_hba.conf for client authentication and postgresql.conf for listening addresses. This guide will take you through the steps to enable remote connections.

Understanding the Error

When remote connections are not enabled, attempting to connect to your PostgreSQL server from a remote machine will typically result in the following error:

psql: could not connect to server: Connection refused

Configuring PostgreSQL for Remote Access

Step 1: Modify pg_hba.conf

1. Open the pg_hba.conf file located in your PostgreSQL data directory (/var/lib/pgsql/data/ or similar):

nano /var/lib/pgsql/data/pg_hba.conf

2. Add a new line under the IPV4 local connections section to specify which hosts are allowed to connect:

# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD host all all 127.0.0.1 255.255.255.255 trust
  • To allow connections from any IP:
    host all all 0.0.0.0/0 trust
    Note: Allowing connections from any IP (0.0.0.0/0) can be very insecure if proper measures are not taken to secure the database.

3. Save and exit the file.

Step 2: Change the Listen Address in postgresql.conf

1. Open the postgresql.conf file:

nano /var/lib/pgsql/data/postgresql.conf

2. Find the line that specifies listen_addresses and change it to:

listen_addresses = '*'

This setting allows PostgreSQL to listen for connections from any IP address.

3. Save and exit the file.

Step 3: Restart PostgreSQL

Apply the changes by restarting PostgreSQL:

service postgresql restart

or

systemctl restart postgresql

Testing the Remote Connection

1. On the remote machine, attempt to connect to your PostgreSQL server:

psql -U postgres -h YOUR_SERVER_IP
  • Replace YOUR_SERVER_IP with the actual IP address of your PostgreSQL server.

2. If everything is configured correctly, you should be prompted for a password and then given access to the PostgreSQL interactive terminal.

Conclusion

You've now configured your PostgreSQL server to accept remote connections. This setup is essential for development and production environments where database access is required from remote locations. Remember, allowing remote connections to your database can expose it to security risks. Always ensure your database is secured with strong passwords, firewalls, and consider using SSL connections. If you're opening up your database to the internet, it's crucial to implement additional security measures and regular security audits.

Thursday, December 28, 2023

Database Replication Master-Slave

Database replication is a strategy for maintaining multiple copies of data across different servers to ensure data availability and redundancy. In a master-slave setup, the master server handles all write operations while one or more slave servers handle read operations and act as a backup. This guide will take you through 10 simple steps to set up a master-slave replication in MySQL.


A. Master Server Configuration

Step 1: Configure MySQL Settings

Edit MySQL Configuration: Open /etc/my.cnf in a text editor and enter the following under the [mysqld] section:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id = 1
log-bin=db-bin
relay-log=db-relay-bin

Step 2: Grant Replication Privileges
2. Grant Slave Access:
Access MySQL (mysql -u root -p) and execute:
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'SLAVE_IP' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;

Replace SLAVE_IP with your slave server's IP address and password with a secure password.

Step 3: Lock Tables and Note Master Status
3. Lock Tables:
FLUSH TABLES WITH READ LOCK;

4. Check Master Status:
SHOW MASTER STATUS;

Note the File and Position values.

5. Unlock Tables:
UNLOCK TABLES;

B. Slave Server Configuration

Step 4: Configure MySQL Settings
Edit MySQL Configuration:Open /etc/my.cnf in a text editor and enter the following under the [mysqld] section
server-id=2
master-host = 10.10.3.21
master-user = replication
master-password = password
master-port = 3306 
10.10.3.21 is the MASTER server IP. These information will be stored to /var/lib/mysql/master.info

Step 5: Setup Replication on Slave
2. Access MySQL:
mysql -u root -p

Stop Slave Threads:
STOP SLAVE;

Reset Previous Master Info:
RESET SLAVE;

Change Master:
CHANGE MASTER TO MASTER_HOST='MASTER_IP', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='Master_Binlog_File', MASTER_LOG_POS=Master_Log_Position;

Replace MASTER_IP with your master server's IP address, Master_Binlog_File with the file noted earlier, and Master_Log_Position with the position noted earlier.

Start Slave Threads:
START SLAVE;

Step 6: Verify Slave Status Check Replication Status:
SHOW SLAVE STATUS \G;

Ensure Slave_IO_Running and Slave_SQL_Running are both set to Yes, and Seconds_Behind_Master is a non-negative number.


Conclusion
You've now successfully configured a basic master-slave replication setup. This configuration allows the slave to take over in case the master goes down, and it can also help with load balancing by directing read queries to the slave. Regularly check the replication status and backup your data to ensure everything operates smoothly. Remember, replication is just one part of a comprehensive disaster recovery plan.

Setting Up PostgreSQL on Your Linux System

PostgreSQL, also known as "Postgres," is a highly extensible and standards-compliant object-relational database management system (ORDBMS). It's renowned for its robustness, ACID-compliance, advanced features like multiversion concurrency control (MVCC), and a wide array of indexing methods, functions, and more. This guide will walk you through configuring your YUM repository, installing PostgreSQL, and setting up a basic database schema.

Step 1: Configure Your YUM Repository

Locate and Edit Your Distributions .repo File:

  • Fedora: Edit /etc/yum.repos.d/fedora.repo and /etc/yum.repos.d/fedora-updates.repo, specifically the [fedora] sections.
  • CentOS: Edit /etc/yum.repos.d/CentOS-Base.repo, focusing on the [base] and [updates] sections.
  • Red Hat: Edit /etc/yum/pluginconf.d/rhnplugin.conf and look for the [main] section.

Append the Exclude Line: To each of the sections identified above, append the following line to prevent the default PostgreSQL package from being installed, as it may be outdated:

exclude=postgresql*

Step 2: Download and Install the PGDG RPM File

PGDG File: PostgreSQL Global Development Group (PGDG) provides an optimized and more up-to-date version of PostgreSQL.

  1. Find the Correct RPM:

  2. Download the RPM:

    • For example, to install PostgreSQL 9.3 on CentOS 6 64-bit:
    curl -O http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm
  3. Install the RPM Distribution:

    rpm -ivh pgdg-centos93-9.3-1.noarch.rpm

Step 3: Install PostgreSQL

List Available Packages:

yum list postgres*

Install PostgreSQL Server:

  • For a basic PostgreSQL 9.3 server installation:
    yum install postgresql93-server

Step 4: Accessing PostgreSQL

Switch to the PostgreSQL User:

su - postgres

Start Using PostgreSQL:

psql

You're now in the PostgreSQL command line. Here you can manage databases, execute SQL queries, and more.

Step 5: Setting Up a Basic Database Schema

Create a Schema Called test:

CREATE SCHEMA test;

Create a Role (User) with Password:

CREATE USER xxx PASSWORD 'yyy';

Grant Privileges on New Schema to New Role:

GRANT ALL ON SCHEMA test TO xxx;

Grant Privileges on Tables in the New Schema to the New Role:

GRANT ALL ON ALL TABLES IN SCHEMA test TO xxx;

Step 6: Disconnecting

Exit psql:

\q

Conclusion

Congratulations! You've successfully set up PostgreSQL on your Linux system. You've also created your first schema and user with privileges. PostgreSQL is a powerful tool with many more capabilities and features to explore. As you become more familiar with its workings, you'll be able to leverage its full potential in managing and analyzing your data effectively. Don't forget to regularly check for updates and maintain your PostgreSQL installation to ensure security and performance.