Enabling the MsSQL Extension in cPanel/WHM: A Manual Installation Guide

While cPanel/WHM offers a wide range of PHP extensions out of the box, the MsSQL extension for connecting to Microsoft SQL Server databases requires a bit of manual effort. In this guide, we'll walk you through the step-by-step process of installing and configuring the MsSQL extension on your cPanel server.

Prerequisites:

• Root Access: You'll need root privileges on your server to perform these steps.
• Source Code: Instead of using RPM packages (which can lead to dependency issues), we'll compile the necessary components from source code.

Installing Required Modules

1. unixODBC:

   • Download: Get the source code from the official unixODBC website.
   • Extract: tar -xvf unixODBC-X.X.X.tar.gz (replace X.X.X with the version you downloaded).
   • Configure and Install:

     cd unixODBC-X.X.X
     ./configure --prefix=/usr/local --enable-gui=no
     make
     make install
     

     FreeTDS:
2. • Download: Download FreeTDS version 0.82 (or a compatible older version) from ftp://ftp.freetds.org/pub/freetds/old/0.82/freetds-0.82.tar.gz.
   • Extract: tar -xvf freetds-0.82.tar.gz
   • Configure and Install:

     cd freetds-0.82
     ./configure --with-tdsver=8.0 --with-unixODBC=/usr/local
     make
     make install
     

     Configure FreeTDS:
3. • Edit freetds.conf: Find the freetds.conf file (usually in /usr/local/etc or /etc) and add the following, replacing placeholders:

     [MSHOSTNAME]
     host = your_sql_server_hostname_or_IP
     port = 1433 
     tds version = 8.0
     

Compiling mssql.so

1. Navigate to PHP Extension Directory:

   cd /home/cpeasyapache/src/php-X.X.X/ext/mssql

   (Replace X.X.X with your PHP version.)

2. Prepare and Build:

   phpize
   ./configure
   make
   make install

Activating the Extension

1. Locate php.ini: Find your PHP configuration file (php.ini). Its location can vary depending on your setup.
2. Add Extension: Open php.ini in a text editor and add the following line:

   extension="mssql.so"
   

3. Restart Apache:

   service httpd restart

Verifying Installation

To confirm that the extension is loaded, you have two options:

• Check Modules: Run php -m | grep mssql. If the installation was successful, you'll see "mssql" in the output.
• Create a phpinfo Page: Create a PHP file with the following content:

  <?php phpinfo(); ?>
  Open this file in your browser and search for "mssql." You should see detailed information about the MsSQL extension.

Troubleshooting Tip:

If you encounter an error during the FreeTDS configuration related to unixODBC, try using an older version of FreeTDS (like 0.82), as newer versions might have compatibility issues.

By carefully following these steps, you can manually install and enable the MsSQL extension in your cPanel/WHM environment, allowing your PHP applications to seamlessly interact with Microsoft SQL Server databases.

Analyse slow-query-log using mysqldump

Analyzing the slow-query-log using tools like mysqldumpslow, pt-query-digest, and mysqlsla can provide valuable insights into the performance of your MySQL database. Here's a step-by-step guide on how to use these tools:

1. Enable slow-query-log:

• Uncomment the following lines in /etc/mysql/my.cnf:

slow-query-log = 1
slow-query-log-file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes

• Adjust long_query_time based on your server's performance and the expected execution time of queries.

2. Collect slow query logs:

• Let the server run with the slow query log enabled for some time to collect data.

3. Analyze slow query logs:

Using mysqldumpslow:

• Command to show top 5 queries returning maximum rows:
  

  mysqldumpslow -a -s r -t 5 /var/log/mysql/mysql-slow.log

• Command to sort output by count (frequency of occurrence) of queries:
  

  mysqldumpslow -a -s c -t 5 /var/log/mysql/mysql-slow.log

Using pt-query-digest:

• Basic command to analyze slow query logs:
  

  pt-query-digest /var/log/mysql/mysql-slow.log

• Filter queries for a specific database:
  

  pt-query-digest /var/log/mysql/mysql-slow.log --filter '$event->{db} eq "db_wordpress"'

Using mysqlsla:

• Basic command to analyze slow query logs

./mysqlsla /var/log/mysql/mysql-slow.log

• Filter queries for a specific database:

  
  ./mysqlsla /var/log/mysql/mysql-slow.log -mf "db=db_name"

4. Interpretation and Action:

• Review the output of the analysis tools to identify slow queries, their frequency, and potential performance bottlenecks.
• Investigate queries that are returning a large number of rows or have a high frequency of occurrence.
• Optimize slow queries by adding indexes, rewriting queries, or optimizing database schema.
• Monitor the performance after optimizations and fine-tune as necessary.

By following these steps and using the provided tools, you can effectively analyze and optimize the performance of your MySQL database, especially in scenarios like debugging WordPress plugins or identifying performance issues in large-scale applications.

Simplifying PostgreSQL Authentication: A Guide to Editing pg_hba.conf

Managing authentication in PostgreSQL can sometimes be cumbersome, especially when dealing with frequent user switches. However, there's a straightforward solution to streamline this process by editing the pg_hba.conf file. Let's explore how to modify this file to enable passwordless authentication for specific users, such as the PostgreSQL superuser, and then revert the changes after setting a new password.

Locating the pg_hba.conf File

Before making any changes, it's crucial to locate the pg_hba.conf file, which contains authentication rules for PostgreSQL connections. The file path may vary depending on the distribution:

• CentOS: /var/lib/pgsql/data/pg_hba.conf
• Ubuntu: /etc/postgresql/9.1/main/pg_hba.conf

Editing pg_hba.conf for Trust Authentication

Once the file is located, open it using a text editor and locate the relevant authentication rule. For example, to allow passwordless authentication for the PostgreSQL superuser (postgres), find the line similar to:

# Database administrative login by Unix 
domain socket local all postgres peer
 





Change the authentication method from "peer" to "trust" as follows:

# Database administrative login by Unix

domain socket local all postgres trust

Changing PostgreSQL User Password

After modifying pg_hba.conf, save the changes and exit the text editor. Now, it's time to change the password for the PostgreSQL superuser. Open a terminal and execute the following command:

psql -U postgres ALTER USER postgres WITH PASSWORD 'secure-password';

Replace 'secure-password' with your desired password. This command updates the password for the postgres user within the PostgreSQL database.

Reverting Changes and Reloading PostgreSQL Service

Once the password is updated successfully, revert the changes made to pg_hba.conf to enhance security. Restore the authentication method back to its original state by modifying the line to:

# Database administrative login by Unix 

domain socket local all postgres peer

Save the changes and exit the text editor. Finally, reload the PostgreSQL service to apply the modifications:

sudo service postgresql reload

Conclusion

By editing the pg_hba.conf file, PostgreSQL authentication can be simplified, allowing passwordless authentication for specific users when needed. However, it's essential to revert the changes promptly after updating passwords to maintain security standards. With this guide, managing PostgreSQL authentication becomes more accessible and efficient for system administrators.