Effortlessly install MySQL on your VPS and manage your databases using phpMyAdmin. This guide also covers creating MySQL users and databases, securing your installation, and integrating with Django projects for seamless database management. Perfect for developers and system administrators.

Managing databases on your VPS can be simplified by installing MySQL and accessing it through phpMyAdmin. This guide provides a comprehensive walkthrough on how to set up MySQL, secure it, create users and databases, and integrate it with your Django project.

Installing MySQL and Configuring Django with phpMyAdmin

Step 1: Update Package List

Begin by updating your package list to ensure you have the latest information:

sudo apt update

Step 2: Install MySQL Server

Install the MySQL server on your VPS:

sudo apt install mysql-server

Step 3: Secure MySQL Installation

After installation, secure your MySQL installation by running:

sudo mysql_secure_installation

Follow the prompts to:

• Set a password for the root user
• Remove anonymous user accounts
• Disable remote root login
• Remove the test database

Follow the prompts to set up a root password, remove anonymous users, disallow remote root login, remove test databases, and reload the privilege tables.

Step 4: Log in to MySQL

Log in to the MySQL server as the root user:

sudo mysql -u root -p

Enter the root password when prompted.

Step 5: Create a Database for Django

Create a new database for your Django project:

CREATE DATABASE mydatabase CHARACTER SET UTF8;

Replace mydatabase with your preferred database name.

Step 6: Create a MySQL User and Grant Permissions

Create a new MySQL user and grant necessary privileges:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'your_password';

GRANT ALL PRIVILEGES ON mydatabase.* TO 'newuser'@'localhost';

GRANT ALL PRIVILEGES: This part specifies that all privileges should be granted. Privileges include permissions such as SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, etc.

GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';

Replace newuser and your_password with your desired username and password, and mydatabase with your database name.

Step 7: Apply Privilege Changes and Exit MySQL

Ensure the changes take effect by flushing privileges, then exit MySQL:

FLUSH PRIVILEGES;
EXIT;

FLUSH PRIVILEGES;: This command reloads the grant tables in MySQL, applying the new privileges immediately.

Step 8: Configuring Django to Use MySQL

Install the MySQL client for Django:

pip install mysqlclient

Update your Django project's settings.py to use MySQL:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'mydatabase',
        'USER': 'newuser',
        'PASSWORD': 'your_password',
        'HOST': 'localhost',
        'PORT': '3306',
    }
}

Step 9: Apply Migrations

Apply Django migrations to set up the database schema:

python manage.py makemigrations

python manage.py migrate

Step 10: Test the Connection

Run the Django development server to ensure everything is set up correctly:

python manage.py runserver

Step 11: Install phpMyAdmin

To manage your MySQL databases via a web interface, install phpMyAdmin:

sudo apt install phpmyadmin

echo "<?php phpinfo(); ?>" | sudo tee /var/www/html/info.php

During installation:

• Select apache2 when prompted for the web server.
• Choose "Yes" to configure the database for phpMyAdmin using dbconfig-common.
• Enter a password for the phpMyAdmin user when prompted.

Step 12: Configure Apache and PHP Modules

Install necessary PHP modules and restart Apache:

sudo apt install php php-mysql php-xml php-mbstring php-zip php-gd php-json php-curl -y

sudo systemctl restart apache2

Step 13: Access phpMyAdmin

You can now access phpMyAdmin via your web browser:

http://your-server-ip/phpmyadmin

Log in using the MySQL user credentials (newuser and your_password) that you created earlier.

Final Steps

Check Permissions: Ensure your Django project files have the correct permissions and ownership for Apache to serve them.

Restart Services: Restart Apache and MySQL to apply any changes:

sudo systemctl restart apache2

sudo systemctl restart mysql

Verify Database Access: Log in to MySQL as the new user to verify access:

mysql -u newuser -p

SHOW TABLES;

SHOW DATABASES;

USE mydatabase;

SHOW TABLES;

SELECT User, Host FROM mysql.user;

Enter the password for newuser and check if the user can access the mydatabase database.

Additional Tips:

• Use a strong password for your MySQL root user and new user.
• Consider using a firewall to protect your MySQL server.
• Back up your MySQL database regularly.
• Keep your MySQL server and phpMyAdmin up to date with the latest security patches.
• I hope this guide is helpful!

By following these steps, you have successfully installed MySQL, created a user and database, integrated MySQL with Django, and set up phpMyAdmin for easy database management. You can now manage your databases through a web interface or within your Django project.

1. Create phpMyAdmin Configuration File

sudo nano /etc/apache2/conf-available/phpmyadmin.conf

Add the following content to the file:

Alias /phpmyadmin /usr/share/phpmyadmin

<Directory /usr/share/phpmyadmin>
    Options FollowSymLinks
    DirectoryIndex index.php

    <IfModule mod_php7.c>
        AddType application/x-httpd-php .php
    </IfModule>

    <IfModule mod_authz_host.c>
        Require all granted
    </IfModule>
</Directory>

sudo a2enconf phpmyadmin

3. Restart Apache

sudo systemctl restart apache2

4. Verify URL Access

Try accessing phpMyAdmin again via:

http://51.79.147.1/phpmyadmin

5. Check Apache Logs for Errors

sudo tail -f /var/log/apache2/error.log

6. Ensure Correct Permissions

sudo chown -R www-data:www-data /usr/share/phpmyadmin
sudo chmod -R 755 /usr/share/phpmyadmin

7. Symlink (Optional)

If you continue to face issues, you can create a symlink to make phpMyAdmin accessible:

sudo ln -s /usr/share/phpmyadmin /var/www/html/phpmyadmin

After following these steps, you should be able to access phpMyAdmin at the URL. If any specific errors occur, refer to the Apache error logs for troubleshooting.

With this setup, you have successfully installed MySQL on your VPS, created a user and database, integrated it with Django, and set up phpMyAdmin for easy database management.

Thank you for visiting and taking the time to read through this guide. I hope it has provided you with valuable insights and helped you successfully set up MySQL and phpMyAdmin on your VPS. If you have any questions or need further assistance, feel free to reach out. Happy coding!