Knowledgebase

Introduction

MyWebSQL is an open-source, web-based database client for managing database servers and written in PHP. It supports managing MySQL, PostgreSQL, and SQLite databases. Some of its features are:

• Multiple syntax highlighted SQL editors.
• WYSIWYG table creator/editor.
• Multilingual interface with themes support.
• Importing and exporting database scripts, tables, or results to multiple formats.
• Multitasking environment.

This article describes how to install MyWebSQL on Ubuntu 20.04 server.

Prerequisites

• Deploy a fully updated Rcs Ubuntu 20.04 Server.
• Create a non-root user with sudo access.

1. Install Required Packages

1. SSH to your server as a non-root user with sudo access.

2. Update the system package list to update all packages to the latest available versions.

    $ sudo apt update

3. Install PHP 7.4 and more modules.

    $ sudo apt install apache2 php7.4 libapache2-mod-php7.4 php7.4-json php7.4-common php7.4-gmp php7.4-curl php7.4-mysql php7.4-pgsql php7.4-sqlite3 php7.4-intl php7.4-mbstring php7.4-xmlrpc php7.4-gd php7.4-cli php7.4-bcmath php7.4-xml php7.4-zip php7.4-imap wget unzip -y

2. Install Database Server

Install a database server for demonstration purposes in this article. Choose either MySQL or PostgreSQL.

MySQL Server

1. Install MySQL database server.

    $ sudo apt install mysql-server -y

2. Secure your MySQL installation.

    $ sudo mysql_secure_installation

   When prompted, answer the questions as shown below:

   • Setup VALIDATE PASSWORD plugin? Press N, then Enter.
   • Remove anonymous users? Press Y, then Enter.
   • Disallow root login remotely? Press Y, then Enter.
   • Remove test database and access to it? Press Y, then Enter.
   • Reload privilege tables now? Press Y, then Enter.

3. Enable the database server to start automatically on a reboot.

    $ sudo systemctl enable mysql

4. Log in to MySQL shell. At the password prompt, just press Enter to continue.

    $ sudo mysql -u root -p

5. Create a database called websql.

    CREATE DATABASE websql;

6. Create a database user called websqluser with a password StrongPassword.

    CREATE USER 'websqluser'@'localhost' IDENTIFIED BY 'StrongPassword';

7. Grant the user full access to the database.

    GRANT ALL ON websql.* TO 'websqluser'@'localhost' WITH GRANT OPTION;

8. Save the changes made to the database.

    FLUSH PRIVILEGES;

9. Exit MySQL shell.

    exit;

PostgreSQL Server

1. Install PostgreSQL database server.

    $ sudo apt install postgresql-12 -y

2. Enable the database server to start automatically on a reboot.

    $ sudo systemctl enable postgresql

3. Start the database server.

    $ sudo systemctl start postgresql

4. Secure PostgreSQL by changing the default PostgreSQL password.

    $ sudo passwd postgres

5. Switch to the postgres user.

    $ su - postgres

6. Create a new database user named websqluser.

    $ createuser websqluser

7. Log in to the PostgreSQL instance.

    $ psql

8. Set a secure password StrongPassword for the user.

    ALTER USER websqluser WITH ENCRYPTED password 'StrongPassword';

9. Create a database named websql and set the owner to websqluser.

    CREATE DATABASE websql OWNER websqluser;

10. Grant all the privileges on the database to the user.

    GRANT ALL PRIVILEGES ON DATABASE websql to websqluser;

11. Exit PostgreSQL instance.

    \q

12. Return to your non-root sudo user account.

    $ exit

3. Install MyWebSQL

1. Download the MyWebSQL package. To find the latest version of this software, visit the download page.

    $ wget https://liquidtelecom.dl.sourceforge.net/project/mywebsql/stable/mywebsql-3.8.zip -O mywebsql-3.8.zip

2. Extract the downloaded archive.

    $ sudo unzip mywebsql-3.8.zip

3. Create the installation directory /var/www/html/mywebsql.

    $ sudo mkdir /var/www/html/mywebsql/

4. Move the extracted files to the installation directory.

    $ sudo mv mywebsql/* /var/www/html/mywebsql

5. Change ownership of the installation directory.

    $ sudo chown -R www-data:www-data /var/www/html/mywebsql/

6. Change access permissions for the directory.

    $ sudo chmod -R 755 /var/www/html/mywebsql/

4. Configure Apache2

1. Create a new Apache virtual host file named mywebsql.conf.

    $ sudo nano /etc/apache2/sites-available/mywebsql.conf

   Add the following content to the file. Then, save and close the file.

    <VirtualHost *:80>
        ServerAdmin admin@example.com
        DocumentRoot /var/www/html/mywebsql
        ServerName example.com
   
        <Directory /var/www/html/mywebsql>
            Options FollowSymlinks
            AllowOverride All
            Require all granted
        </Directory>
   
        ErrorLog ${APACHE_LOG_DIR}/example.com_error.log
        CustomLog ${APACHE_LOG_DIR}/example.com_access.log combined
    </VirtualHost>

2. Disable Apache default configuration file.

    $ sudo a2dissite 000-default.conf

3. Enable MyWebSQL Apache configuration file.

    $ sudo a2ensite mywebsql.conf

4. Enable Apache rewrite mode.

    $ sudo a2enmod rewrite

5. Restart Apache service.

    $ sudo systemctl restart apache2

5. Access MyWebSQL Web Interface

Check the installation status of MyWebSQL. Go to your browser and visit http://Server_IP/install.php. For example:

http://192.0.2.11/install.php

If the environment passes all the requirements, remove the installation file named install.php to avoid security breaches.

$ sudo rm /var/www/html/mywebsql/install.php

Access the MyWebSQL Web Interface, go to your browser, and visit http://Server_IP/. For example:

http://192.0.2.10

Conclusion

You have installed MyWebSQL on your server. For both MySQL and PostgreSQL servers, use websqluser as the User ID and StrongPassword as the Password. Select the corresponding database server, your preferred language, and then log in to the dashboard.

More Information

To learn more about using MyWebSQL, go to the official documentation page.