Introduction

In this comprehensive guide, we’ll delve into the intricacies of installing ProxySQL, walking you through the process step by step. From laying down the groundwork and installation procedures to advanced configurations and testing, this guide aims to equip you with the knowledge and confidence to harness the power of ProxySQL effectively. So, buckle up as we embark on a journey to unlock the true potential of your database infrastructure with ProxySQL.

Pre-Requirements for installing ProxySQL 

Before diving into the installation process of ProxySQL, it’s essential to ensure that your system meets certain prerequisites to guarantee a smooth and successful setup. Here are the key prerequisites for installing ProxySQL:

  1. Supported Operating Systems: ProxySQL is compatible with a variety of operating systems, including Linux distributions like Ubuntu, Debian, CentOS, and Red Hat Enterprise Linux (RHEL). Ensure that your system runs one of these supported operating systems. If necessary, consider updating or upgrading your operating system to a compatible version.
  2. System Requirements: Check the system requirements for ProxySQL, including minimum RAM, CPU, and disk space specifications. While ProxySQL itself is lightweight, it’s crucial to allocate sufficient resources based on your anticipated workload and database size.
  3. MySQL/MariaDB Installation: ProxySQL typically acts as a middleware between database clients and MySQL/MariaDB servers. Therefore, you need to have MySQL or MariaDB installed and running on your system before installing ProxySQL. Make sure that your MySQL/MariaDB servers are reachable from the system where you plan to install ProxySQL. If MySQL or MariaDB is not yet installed, follow the respective installation guides provided by MySQL or MariaDB documentation.
  4. Network Configuration: Ensure that network connectivity is properly configured between ProxySQL and your MySQL/MariaDB servers. Verify that firewalls, if enabled, allow traffic on the necessary ports for communication between ProxySQL and your database servers.
  5. Access Privileges: You’ll need administrative privileges on the system where you plan to install ProxySQL. Additionally, ensure that you have appropriate privileges to access and modify configuration files and manage services.
  6. Package Managers and Dependencies: Depending on your operating system, you may need to have package managers like apt (Advanced Package Tool) for Debian/Ubuntu-based systems or yum (Yellowdog Updater Modified) for CentOS/RHEL-based systems installed and updated. ProxySQL may also have dependencies that need to be satisfied before installation. If applicable, update package managers such as apt (for Debian/Ubuntu) or yum (for CentOS/RHEL) to ensure access to the latest packages and dependencies.
  7. DNS Configuration (Optional): If you plan to use hostnames instead of IP addresses for defining backend servers in ProxySQL’s configuration, ensure that DNS resolution is configured correctly on your system.

By verifying and addressing these prerequisites beforehand, you’ll be well-prepared to proceed with the installation of ProxySQL and seamlessly integrate it into your database environment, unlocking its full potential for efficient database management and performance optimization.

Decide whether to install ProxySQL from source or using a package manager

When it comes to installing ProxySQL, you have two primary options: installing it from source or using a package manager. Each approach has its advantages and considerations, so it’s essential to weigh them before proceeding with the installation.

Installing from Source:

  • Advantages:
    • Latest Version: Installing from source allows you to access the latest version of ProxySQL, ensuring that you benefit from the most recent features and improvements.
    • Customization: Installing from source gives you greater flexibility to customize ProxySQL according to your specific requirements. You can modify configuration options and compile settings to tailor ProxySQL to your environment.
    • Control: Installing from source provides you with full control over the installation process, allowing for finer-grained adjustments and optimizations.
  • Considerations:
    • Complexity: Installing from source typically involves more steps and complexity compared to using a package manager. You’ll need to download the source code, configure build settings, and compile ProxySQL before installation.
    • Dependencies: Installing from source may require you to manually install dependencies and satisfy build requirements, which can be time-consuming and error-prone.
    • Maintenance: With source installations, you’ll be responsible for managing updates and maintenance tasks, including applying patches and resolving compatibility issues.

Using a Package Manager:

  • Advantages:
    • Ease of Installation: Using a package manager simplifies the installation process, as it automatically handles dependencies and package installation steps for you.
    • Convenience: Package managers provide a streamlined approach to installing and managing software, reducing the time and effort required for setup.
    • Dependency Management: Package managers ensure that all necessary dependencies are resolved and installed correctly, minimizing the risk of compatibility issues.
  • Considerations:
    • Version Availability: Depending on the package manager and distribution, the available version of ProxySQL may not always be the latest. You might have to wait for the package repository to update with the newest release.
    • Limited Customization: Installing via a package manager may offer fewer customization options compared to installing from source. While basic configuration settings can still be adjusted, you may have less flexibility in fine-tuning ProxySQL to your specific needs.
    • Delayed Updates: Updates provided through package managers may take some time to become available in the repository, potentially delaying access to new features or bug fixes.

Decision

Consider your specific requirements, preferences, and expertise level when choosing between installing ProxySQL from source or using a package manager. If you prioritize having the latest version and require extensive customization, installing from source may be the preferred option. On the other hand, if ease of installation and maintenance is paramount, using a package manager might be more suitable. Ultimately, both approaches can lead to a successful installation of ProxySQL, so choose the method that best aligns with your needs and resources.

Installation from Source

Step 1: Download the Source Code

    1. Recommended: if you have Git installed on your system, you can clone the repository using the following command:
      git clone https://github.com/sysown/proxysql.git
    2. Alternatively, navigate to the official ProxySQL GitHub repository at https://github.com/sysown/proxysql and click on the green “Code” button, then select “Download ZIP” to download the latest source code as a ZIP archive.

Step 2: Extract the Source Code:

  1. If you cloned the repository using Git, navigate to the directory where you cloned the repository.
  2. If you downloaded the source code as a ZIP archive, extract it to a directory of your choice using your preferred file extraction tool.

Step 3: Install Build Dependencies

  1. Before compiling ProxySQL, ensure that you have the necessary build dependencies installed on your system. These dependencies may vary depending on your operating system. Common dependencies include cmake, libssl-dev, libboost-dev, and libmysqlclient-dev.
  2. You can install these dependencies using your package manager. For example, on Debian/Ubuntu-based systems, you can use the following command:
    sudo apt-get install cmake libssl-dev libboost-dev libboost-program-options-dev libboost-filesystem-dev libboost-thread-dev libboost-regex-dev libboost-system-dev libmysqlclient-dev

Step 4: Compile ProxySQL

    1. Navigate to the ProxySQL source code directory.
    2. Compile ProxySQL using the make command:
      make
  1. After compilation completes without errors, you can install ProxySQL using the make install command. This will install ProxySQL binaries and related files to the appropriate directories on your system:
    sudo make install

Step 5: Verify Installation

  1. Once installation is complete, you can verify that ProxySQL has been installed by checking its version:
proxysql --version
  1. Additionally, you may want to verify the installation directory and configuration files to ensure that ProxySQL was installed correctly.

Step 6: Start ProxySQL

  1. You can start ProxySQL using the following command:
proxysql -f -c /etc/proxysql.cnf
  1. Replace /etc/proxysql.cnf with the path to your ProxySQL configuration file if it’s located elsewhere.
  2. Note that with parameter -f , proxysql starts in foreground and not as service. To start ProxySQL as a service you need to remove -f from the command line, for example using the following command:
proxysql -c /etc/proxysql.cnf

Congratulations! You have successfully downloaded the source code and compiled ProxySQL from source. ProxySQL is now ready to be configured and deployed in your environment to enhance database management and performance.

Installation via Package Manager

Step 1: Install ProxySQL

Installing ProxySQL using a package manager provides a convenient and straightforward method, as it automatically handles dependencies and simplifies the installation process. Below are step-by-step instructions for installing ProxySQL using the package manager specific to your operating system:

  1. Debian/Ubuntu:
  • Install ProxySQL official repository. Details available here
  • Update the package index:
sudo apt update
  • Install ProxySQL:
sudo apt install proxysql
  1. CentOS/RHEL:
  • Enable the EPEL (Extra Packages for Enterprise Linux) repository:
sudo yum install epel-release
  • Install ProxySQL official repository. Details available here
  • Install ProxySQL:
sudo yum install proxysql
  1. Other Systems (if applicable):Consult your system’s documentation or package manager’s documentation for instructions on installing ProxySQL. Some systems may have pre-built packages available through community repositories or third-party package managers.

Step 2: Start ProxySQL

Once ProxySQL is installed, you can start the service using your system’s service management commands. For example:

  • On systemd-based systems (Debian/Ubuntu, CentOS/RHEL 7+):
sudo systemctl start proxysql
  • On SysVinit-based systems (CentOS/RHEL 6):
sudo service proxysql start

Step 3: Verify Installation

  • Check the status of the ProxySQL service to ensure it’s running without errors:
sudo systemctl status proxysql
  • You can also verify the ProxySQL version to confirm a successful installation:
proxysql --version

Step 4: Configuration (if needed)

  • By default, ProxySQL comes with a basic configuration that may require adjustments based on your environment and requirements. Refer to ProxySQL documentation for guidance on configuring ProxySQL according to your needs.

Step 5: Access ProxySQL Admin Interface

ProxySQL provides an admin interface accessible via MySQL protocol, allowing you to configure and manage ProxySQL using SQL commands. To access the ProxySQL Admin Interface, follow these steps:

  1. Connect to ProxySQL using your preferred MySQL client. You can do this from the command line or using a MySQL client application such as MySQL Workbench or HeidiSQL.
  2. Use the following connection details to connect to ProxySQL:
  • Host: 127.0.0.1 (or the IP address of the machine where ProxySQL is installed)
  • Port: 6032
  • Username: admin
  • Password: admin (or the password you set during installation)
  1. Once connected, you can execute SQL commands to configure ProxySQL according to your requirements. For example, you can manage server definitions, query rules, and scheduler settings using SQL commands.

Here’s an example of connecting to ProxySQL using the mysql command-line client:

mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin> '

Please ensure to use 127.0.0.1 and not localhost . In mysql client , localhost uses an unix socket and not a TCP connection.

Upon successful connection, you’ll see the MySQL prompt (ProxySQLAdmin> in this example), indicating that you’re connected to the ProxySQL admin interface.

From here, you can explore ProxySQL’s capabilities and configure it to suit your specific use case using SQL commands.

Check out First steps after installing for useful post installation information