Last_IO_Error: error connecting to master ‘repl@107.189.159.252:3306’ – retry-time: 60 retries: 3 message: SSL connection error: error:0A000086:SSL routines::certificate verify failed

Then I tried to connect the Master server from the slave using the command line, with the client certificate. Again I received the following error with the connection:

mysql -h 192.168.1.100 -u repl_user -p --ssl-ca=/etc/mysql/certs/ca.pem --ssl-cert=/etc/mysql/certs/client-cert.pem --ssl-key=/etc/mysql/certs/client-key.pem 

Output
Enter password:
ERROR 2026 (HY000): SSL connection error: error:14090086:SSL routines:ssl3_get_server_certificate:certificate verify failed

Possible Causes

This error can be occurred due to several reasons. Here are some possible causes:

• The MySQL server’s SSL certificate is not trusted by the client because it is self-signed or not signed by a certificate authority (CA) that is trusted by the client.
• The MySQL server’s SSL certificate has expired.
• The MySQL server’s SSL certificate is not properly configured.
• The client is using an old version of the MySQL client library that does not support the server’s SSL certificate.

Solution

1. Check if both system clocks are synchronized.
2. Next verify the client and server certificate with the CA file and make sure everything is OK. Use the following command to verify the certificates:

   openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem 
   
   server-cert.pem: OK
   client-cert.pem: OK
   

3. Make sure to set a different “Common Name (FQDN)” for the CA certificate and the master/client certificate.
4. Check the state of the SSL/TLS variables by typing. Make sure the correct certificate is used by the server.

   SHOW VARIABLES LIKE '%ssl%'; 
   

   Output
   +-------------------------------------+----------------------------------+
   | have_openssl                        | YES                              |
   | have_ssl                            | YES                              |
   | ssl_ca                              | /etc/mysql/certs/ca-cert.pem     |
   | ssl_capath                          |                                  |
   | ssl_cert                            | /etc/mysql/certs/server-cert.pem |
   | ssl_cipher                          |                                  |
   | ssl_crl                             |                                  |
   | ssl_crlpath                         |                                  |
   | ssl_fips_mode                       | OFF                              |
   | ssl_key                             | /etc/mysql/certs/server-key.pem  |
   | ssl_session_cache_mode              | ON                               |
   | ssl_session_cache_timeout           | 300                              |
   +-------------------------------------+----------------------------------+
   27 rows in set (0.01 sec)
   

5. Finally make sure that you are using the correct database username, hostname, and password to connect.

Conclusion

In conclusion, the `SSL connection error: error:0A000086:SSL routines::certificate verify failed error` can occur when establishing an SSL connection to a MySQL server for several reasons, including an untrusted or expired SSL certificate, a misconfigured SSL certificate, or an outdated MySQL client library. To resolve this error, you can import the server’s SSL certificate into the client’s trust store, renew the SSL certificate, check the server’s SSL configuration, or upgrade the MySQL client library to a newer version that supports the server’s SSL certificate.

The post (Resolved) MySQL connection error: certificate verify failed appeared first on TecAdmin.

In this article, we will look at how to connect to a MySQL database in Python using the `mysql-connector-python` library, which is a MySQL driver for Python. We will also cover some basic operations such as creating tables, inserting data, and querying the database.

Prerequisites

Before we begin, you will need to install the following:

• Python 3: You can download and install Python from the official website (https://www.python.org/). Make sure to install the latest version of Python.
• MySQL: You can download and install MySQL from the official website (https://www.mysql.com/). You will also need to create a database and set up a user with the appropriate permissions.
• mysql-connector-python library: You can install this library using pip, the Python package manager. Open a terminal and run the following command:

  pip install mysql-connector-python 
  

Connecting to the Database

To connect to the MySQL database, we will need to import the `mysql.connector` module and create a connection object. The connection object will allow us to execute SQL queries and perform other operations on the database.

Here is an example of how to create a connection object:

import mysql.connector

# Connect to the database
cnx = mysql.connector.connect(
    user='<username>',
    password='<password>',
    host='<hostname>',
    database='<database>'
)

Replace <username>, <<password>, <hostname>>, and <database> with your MySQL credentials and the name of the database you want to connect to.

Once you have created the connection object, you can use it to execute SQL queries and perform other operations on the database.

Creating Tables

To create a table in the database, you can use the cursor object, which is used to execute SQL queries. First, you will need to create a cursor object and then use the `execute()` method to execute a `CREATE TABLE` statement.

Here is an example of how to create a table:

# Create a cursor object
cursor = cnx.cursor()

# Create a table
cursor.execute(
    '''
    CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) NOT NULL
    )
    '''
)

This will create a table named users with three columns: id, name, and email. The id column is the primary key and will be automatically incremented for each new record. The name and email columns are both required and cannot be NULL.

Inserting Data

To insert data into a table, you can use the `INSERT INTO` statement. You can use the `execute()` method of the cursor object to execute the `INSERT INTO` statement and pass the values you want to insert as arguments.

Here is an example of how to insert a new row into the `users` table:

# Insert a new row
cursor.execute(
    '''
    INSERT INTO users (name, email)
    VALUES (%s, %s)
    ''',
    ('John Smith', 'john@example.com')
)

# Commit the changes to the database
cnx.commit()

This will insert a new row into the `users` table with the name `John Smith` and email `john@example.com`.

You can also insert multiple rows at once using the `executemany()` method of the cursor object. This method takes a list of tuples, where each tuple represents a row to be inserted.

Here is an example of how to insert multiple rows:

# Insert multiple rows
cursor.executemany(
    '''
    INSERT INTO users (name, email)
    VALUES (%s, %s)
    ''',
    [
        ('Jane Doe', 'jane@example.com'),
        ('Bob Smith', 'bob@example.com')
    ]
)

# Commit the changes to the database
cnx.commit()

Querying the Database

To retrieve data from the database, you can use the `SELECT` statement. You can use the `execute()` method of the cursor object to execute a `SELECT` statement and retrieve the rows that match the query.

Here is an example of how to retrieve all rows from the `users` table:

# Execute a SELECT statement
cursor.execute('SELECT * FROM users')

# Fetch all the rows
rows = cursor.fetchall()

# Print the rows
for row in rows:
    print(row)

This will retrieve all rows from the `users` table and print them to the console.

You can also retrieve specific columns by specifying them in the `SELECT` statement. You can also use `WHERE` clauses and other SQL operators to filter the rows that are retrieved.

Here is an example of how to retrieve specific columns and filter the rows:

# Execute a SELECT statement
cursor.execute(
    '''
    SELECT name, email
    FROM users
    WHERE id > 2
    '''
)

# Fetch all the rows
rows = cursor.fetchall()

# Print the rows
for row in rows:
    print(row)

This will retrieve the name and email columns from the `users` table for all rows where the `id` is `greater than 2`.

Closing the Connection

Once you are done working with the database, it is important to close the connection to release the resources. You can do this by calling the `close()` method of the connection object.

Here is an example of how to close the connection:

# Close the connection
cnx.close()

Conclusion

In this article, we have covered how to connect to a MySQL database in Python using the `mysql` database. By following the steps outlined in this guide, you can easily establish a connection to a MySQL database using the Python MySQL Connector library. First, you will need to install the library using pip. Then, you can use the `mysql.connector.connect()` function to establish a connection to the database by passing in the appropriate parameters such as host, username, password, and database name. Once you have established a connection, you can use the cursor object to execute SQL queries and retrieve data from the database. It is important to remember to close the connection and cursor when you are finished working with the database to prevent any errors or issues.

The post How to Connect Python with MySQL Database appeared first on TecAdmin.

This article will walk through the process of installing MySQL 8 on Amazon Linux 2. When you’re finished, you’ll have a fully-functioning MySQL database that you can use with either the AWS Console or your own application. You can also use Amazon RDS to configure other databases besides MySQL.

How to Install MySQL 8 on Amazon Linux

The MySQL official team provides the RPM packages for the installation of Amazon Linux systems. Follow the below steps one by one to complete the MySQL installation.

Configure Yum Repository

Most of the packages required the dependencies that are available in other third-party repositories. Use the following command to configure the EPEL repository that is required for package installation.

sudo amazon-linux-extras install epel -y 

Then configure the MySQL repository by installing the package provided by the MySQL official site.

sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-5.noarch.rpm 

Install MySQL Server

You can successfully configure the repositories, your system is ready for MySQL installation. Execute the below-mentioned command to install MySQL 8 community server on Amazon Linux.

sudo yum install mysql-community-server 

Press ‘y’ for the confirmation prompted during the installation.



Activate and Start MySQL Service

Once the installation is successfully finished. The default MySQL service will be stopped and in an inactive state. First, use the following commands to activate the service to auto-start on system startup, then start it manually for the first time.

systemctl active mysqld 
systemctl start mysqld 

Then, use the following command to view the MySQL service status. It should be active and running.

systemctl status mysqld 



Find initial root password

During the installation of packages, an initial password is configured for the MySQL root account. You can find this password from the MySQL log file.

cat /var/log/mysql.log | grep "A temporary password" 

You will see the output below that includes the default root password.



This password will be required in the next step.

MySQL Post Installation Setup

A post-installation script is provided by the MySQL packages. That is helpful for configuring MySQL once after the installation. This helps us to configure a strong root account password, remote anonymous users, disallow root access remotely and remove the test database.

Execute the following command from the terminal:

sudo mysql_secure_installation 

Enter the root password found in the above step, then set a new password for the MySQL root account. Next, follow the onscreen instructions and Press Y for all other operations to apply improved security.

• Enter password for user root: [Enter current root password]
• New password: [Enter a new root password]
• Re-enter new password: [Re-Enter the new root password]
• Estimated strength of the password: 100
  Change the password for root ? ((Press y|Y for Yes, any other key for No) : n
• Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
• Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
• Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
• Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
• All done!

Connect to MySQL

Your MySQL server is ready to use now. From the terminal, you can run the below command to connect to the MySQL command line interface. It will prompt for the root account password. On successful authentication, you will get the MySQL prompt.

mysql -u root -p 

Enter the MySQL root user password:

Output:
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.30 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Here you can create databases, tables, users, and all the required things by using the structured query language statements.

Next Steps for Maintaining MySQL

As you’re setting up your new database, it’s a good idea to put some thought into how you will maintain the database in the long term. This guide focuses on setting up a new database, but you should also consider ways to make your database more automated and easier to manage. One simple way to do this is to automate the process of backing up your database. This will allow you to keep a copy of your data in case something goes wrong and you need to restore it from a previous point in time. This can be done with the help of some simple scripts that call the MySQL database and write the data to a different location.

We already have created a simple database backup script and one advance MySQL database backup script for the purpose. You can use these scripts to quickly configure the database backups.

Final Words

There are many reasons why you might want to run your database on Amazon’s cloud. Some common ones have cost, ease of setup and maintenance, and the ability to scale up or down as needed. Running your database on Amazon Linux has a few advantages over using a different Linux distribution. Amazon has thoroughly tested its distribution and it is optimized for running on its cloud infrastructure. When you’re setting up a new database, it’s important to choose a solution that meets your needs and can grow with your business.

This guide focuses on installing MySQL on Amazon Linux, which is one of the easiest and most cost-effective ways to get a new database up and running.

The post How To Install MySQL 8 on Amazon Linux 2 appeared first on TecAdmin.

Let’s see the problem and solution to the issue faced recently:

The Problem:

During the migration of a WordPress application, I faced the following error with the restoration of the MySQL database. The collation id may differ based on the MySQL version.

Error message:

Error 1273 (HY000) at line 36 Unknown collation: 'utf8mb4_unicode_520_ci'

Here you go with a solution.

The Solution:

After searching for the error, I found that the MySQL server running on the other server is an older version than the source. So we find out that the destination server doesn’t support the ‘utf8mb4_unicode_520_ci’ collation.

To resolve this issue, I did a little change in the backup file. Edit the database backup file in text editor and replace all occurrences of “utf8mb4_unicode_520_ci” with “utf8mb4_general_ci“. Also, if you found “CHARSET=utf8mb4“, replace this with “CHARSET=utf8“.

Replace the below string:

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

with:

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Save the backup file and restore the database.

The Linux system users can use the sed command to replace text in files directly.

sed -i 's/utf8mb4_unicode_520_ci/utf8_general_ci/g' backup.sql  
sed -i 's/CHARSET=utf8mb4/CHARSET=utf8/g' backup.sql  

That’s it, Now the database is successfully restored without any errors!

Hopefully, this is solution helped you to resolve “Unknown collation: ‘utf8mb4_unicode_520_ci’” issue with MySQL databases.

The post (Resolved) Unknown collation: utf8mb4_unicode_520_ci appeared first on TecAdmin.

This blog will show you how to install and set up phpMyAdmin on Ubuntu 22.04 server.

Step 1 – Install Apache and PHP

We are assuming you already have installed the MySQL server on Ubuntu system. So just install the other required packages to run and access phpMyAdmin.

sudo apt install apache2 wget unzip 
sudo apt install php php-zip php-json php-mbstring php-mysql 

Once the installation is finished, enable and start the Apache web server.

sudo systemctl enable apache2 
sudo systemctl start apache2 

Step 2 – Install phpMyAdmin on Ubuntu 22.04

You can quickly install the phpMyAdmin from the default Ubuntu repositories. But they contain an older version of phpMyAdmin. If you are okay with the old version simply type apt install phpmyadmin, but to install the latest version, you need to download it from the official website.

Your system is ready for the phpMyAdmin installation. Download the latest phpMyAdmin archive from the official download page, or use the below commands to download phpMyAdmin 5.2 on your system. Once the downloading is finished, extract the archive and move it to the proper location.

wget https://files.phpmyadmin.net/phpMyAdmin/5.2.0/phpMyAdmin-5.2.0-all-languages.zip 
unzip phpMyAdmin-5.2.0-all-languages.zip 
sudo mv phpMyAdmin-5.2.0-all-languages /usr/share/phpmyadmin 

Next, create tmp directory and set the proper permissions. This is a necessary step to make it work properly.

sudo mkdir /usr/share/phpmyadmin/tmp 
sudo chown -R www-data:www-data /usr/share/phpmyadmin 
sudo chmod 777 /usr/share/phpmyadmin/tmp 

Step 3 – Configure phpMyAdmin

Now, you need to configure the webserver to serve phpMyAdmin on the network. Create an Apache configuration file for phpMyAdmin and edit it in a text editor:

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

add the below content to the file.

Alias /phpmyadmin /usr/share/phpmyadmin
Alias /phpMyAdmin /usr/share/phpmyadmin

<Directory /usr/share/phpmyadmin/>
   AddDefaultCharset UTF-8
   <IfModule mod_authz_core.c>
      <RequireAny>
      Require all granted
     </RequireAny>
   </IfModule>
</Directory>

<Directory /usr/share/phpmyadmin/setup/>
   <IfModule mod_authz_core.c>
     <RequireAny>
       Require all granted
     </RequireAny>
   </IfModule>
</Directory>

Save your file. Press ESC key to switch to command more. Then type :wq (colon+w+q) and hit Enter button.

After making all the changes, make sure to start the Apache service to reload all settings.

sudo a2enconf phpmyadmin 
sudo systemctl restart apache2 

Step 4 – Adjusting FirewallD

The systems with enabled firewalls need to allow HTTP service from the firewall. Run the below commands to open a port for the webserver in the firewall.

sudo firewall-cmd --permanent --add-service=http 
sudo firewall-cmd --reload 

Step 5 – Create a MySQL Database and User

Connect to the MySQL server running on your system.

mysql 

Execute the following MySQL queries one by one to create a database and user. Also, assign the privileges to the user on the database.

mysql> CREATE DATABASE  tecadmin;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE USER 'tecadmin'@'localhost' IDENTIFIED BY 'Pa$$w0rd';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL ON tecadmin.* TO 'tecadmin'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Step 6 – Access phpMyAdmin

All done. You have finished the setup with the phpMyAdmin on the Ubuntu Linux system. Now access phpMyAdmin with the server IP address or domain name.

http://your-server-ip-domain/phpmyadmin

Replace your-server-ip-domain with the localhost (for the local machines), or system IP address for remote machines. I have updated our DNS and pointed dbhost.tecadmin.net to the server’s IP address.

Log in with the username and password used to access MySQL on the command line.

Conclusion

You have successfully configured phpMyAdmin on the Ubuntu system. Also you can disable root user login for the for the security purposes in phpMyAdmin.

You can use phpMyAdmin to administer the MySQL server without login in through the command line.

The post How To Install phpMyAdmin on Ubuntu 22.04 appeared first on TecAdmin.

Today, I have installed MySQL 5.7 on a CentOS 7 Linux system. I used the MySQL official yum repository for the installation. During the installation, I faced the following error message on the screen and the installation failed.

Downloading packages:
warning: /var/cache/yum/x86_64/7/mysql57-community/packages/mysql-community-client-5.7.38-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql


The GPG keys listed for the "MySQL 5.7 Community Server" repository are already installed but they are not correct for this package.
Check that the correct key URLs are configured for this repository.


 Failing package is: mysql-community-client-5.7.38-1.el7.x86_64
 GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

The Solution

After a few searches, I found that MySQL has updated its GPG key. The latest GPG key can be installed with the following command.

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022 

Hope this blog post helps you to resolve the GPG key issue on your system.

The post GPG Key Error during MySQL 5.7 Installation appeared first on TecAdmin.

• A Simple Bash Script for MySQL Database Backup
• An Advance Bash Script for MySQL Database Backup

This tutorial contains a shell script that creates MySQL databases backup and uploads them to Amazon S3 buckets. You can also use this shell script to back up MariaDB or Amazon Aurora (MySQL compatible) databases.

Backup MySQL Databases to S3

Use the below step-by-step tutorial to back up the MySQL databases and upload them to the Amazon S3 bucket.

1. Install AWS CLI

In order to use this script, the system must have AWS CLI installed.

https://tecadmin.net/installing-aws-cli-in-linux/

2. Create S3 Bucket

Login to AWS Management Console and create a new s3 bucket.

Alternatively, you can also create s3 bucket via AWS CLI. The command will be like:

aws s3api create-bucket --bucket s3-bucket-name --region us-east-1 

Just replace the bucket name and region.

3. Shell Script to Backup MySQL database to S3

Copy the below shell script to a file like db-backup.sh. This script uses mysqldump command to create databases backups. Then use gzip command to archive backup files and finally use aws command to upload backup files to Amazon S3 bucket.

Create a file like /backup/scripts/s3-backup-mysql.sh in edit your favorite text editor. Then add the below content:

#!/usr/bin/env bash

#########################################################################
#########################################################################
###
####       Author: Rahul Kumar
#####      Website: https://tecadmin.net
####
#########################################################################
#########################################################################

# Set the folder name formate with date (2022-05-28)
DATE_FORMAT=$(date +"%Y-%m-%d")

# MySQL server credentials
MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USER="user"
MYSQL_PASSWORD="password"

# Path to local backup directory
LOCAL_BACKUP_DIR="/backup/dbbackup"

# Set s3 bucket name and directory path
S3_BUCKET_NAME="s3-bucket-name"
S3_BUCKET_PATH="backups/db-backup"

# Number of days to store local backup files
BACKUP_RETAIN_DAYS=30 

# Use a single database or space separated database's names
DATABASES="DB1 DB2 DB3"

##### Do not change below this line

mkdir -p ${LOCAL_BACKUP_DIR}/${DATE_FORMAT}

LOCAL_DIR=${LOCAL_BACKUP_DIR}/${DATE_FORMAT}
REMOTE_DIR=s3://${S3_BUCKET_NAME}/${S3_BUCKET_PATH}

for db in $DATABASES; do
   mysqldump \
        -h ${MYSQL_HOST} \
        -P ${MYSQL_PORT} \
        -u ${MYSQL_USER} \
        -p${MYSQL_PASSWORD} \
        --single-transaction ${db} | gzip -9 > ${LOCAL_DIR}/${db}-${DATE_FORMAT}.sql.gz

        aws s3 cp ${LOCAL_DIR}/${db}-${DATE_FORMAT}.sql.gz ${REMOTE_DIR}/${DATE_FORMAT}/
done

DBDELDATE=`date +"${DATE_FORMAT}" --date="${BACKUP_RETAIN_DAYS} days ago"`

if [ ! -z ${LOCAL_BACKUP_DIR} ]; then
	cd ${LOCAL_BACKUP_DIR}
	if [ ! -z ${DBDELDATE} ] && [ -d ${DBDELDATE} ]; then
		rm -rf ${DBDELDATE}

	fi
fi

## Script ends here

Update all the necessary variables as per your system environment.

4. How to run backup script

Set the execute (x) permission on script:

chmod +x s3-backup-mysql.sh 

Then run the backup script.

./s3-backup-mysql.sh 

5. Schedule backup script to run daily

Schedule the shell script using crontab to run on a daily basis.

crontab -e 

Add the below settings to end of the file:

# Run daily @ 2am
0 2 * * * /backup/scripts/s3-backup-mysql.sh > /dev/null 2>&1

Save the file and close it.

Conclusion

This tutorial provides you with a shell script to back up MySQL databases and upload them to the Amazon S3 bucket. That could be helpful for you to automate database backups and save a copy on cloud storage.

The post Backup MySQL Databases to Amazon S3 (Shell Script) appeared first on TecAdmin.

This tutorial will guide you with the installation of the MariaDB server on the Ubuntu 22.04 Linux system.

1. Configure Repository

The MariaDB packages are available in default Ubuntu repositories. Also, MariaDB provides an official repository to install the latest version on Ubuntu systems.

In order to configure MariaDB Apt repository, open a terminal with a sudo privileged account and run the following commands.

sudo apt install software-properties-common dirmngr apt-transport-https 
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc' 
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] https://atl.mirrors.knownhost.com/mariadb/repo/10.8/ubuntu jammy main' 

The above commands will create a repository file under /etc/apt/sources.list.d directory.

2. Installing MariaDB on Ubuntu

Next, execute the following commands to install the MariaDB server and client on your Ubuntu system.

sudo apt update 
sudo apt install mariadb-server 

Press ‘y’ and hit Enter, when prompted for the confirmation.

Once the installation is finished, execute the mysql_secure_installation script to secure MariaDB server and set a password for root account.

sudo sudo mysql_secure_installation 

Follow the on-screen instructions to complete the security wizard.

3. Manage MariaDB Service

MariaDB creates a systemd configuration file to manage its service. Use the following commands to stop, start, or restart the MariaDB service.

sudo systemctl start mariadb 
sudo systemctl stop mariadb 

To view the current status of MariaDB service, type:

sudo systemctl status mariadb 

4. Connect to MariaDB

You can get a MariaDB shell to create users, databases, and other related activities. To connect shell, type:

mysql -u root -p 

Type the MariaDB root user password you set with the mysql_secure_installation command.

Output:
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.8.3-MariaDB-2ubuntu1 Ubuntu 22.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Conclusion

This tutorial helped you to install and configure the MariaDB server on the Ubuntu system. Now you can create databases for your awesome applications. We strongly recommended creating a separate user account for the databases.

The post How to Install MariaDB on Ubuntu 22.04 appeared first on TecAdmin.

Each letter in the acronym refers to a separate software package: That being said, let’s see how we can install and setup LEMP Stack on Ubuntu.

Pre-Requisities

Assuming that you have a running Ubuntu 22.04 Linux system with sudo (or root) privileged access.

Access your system and open a terminal. It will be good to update the package manager cache and upgrade currently installed packages. To do this execute:

sudo apt update && sudo apt upgrade 

Let’s begin the LEMP (Linux, Nginx, MySQL, and PHP) stack installation on Ubuntu 22.04 Jammy Jellyfish Linux system.

Step 1 – Installing NGINX

First, we will install the Latest Nginx web server on our system. Use the following commands to add PPA for installing the latest Nginx version on your Ubuntu 22.04 Linux.

Use the following commands to install Nginx web server.

sudo apt install nginx 

This will install the Nginx web server and start the service.

Now, you need to allow webserver ports in the firewall. To allow ports 80 and 443 in the UFW firewall, execute the following commands.

sudo ufw allow 80/tcp 
sudo ufw allow 43/tcp 

Open a web browser on your system and type the server’s IP in the address bar. You will get the default Nginx server page

Step 2 – Installing PHP

First, you need to decide on the PHP version to install on your system. You can also install multiple PHP versions on a single system. Currently the repository contains PHP 5.6, PHP 7.1, 7.2, 7.3, 7.4 and PHP 8.0, 8.1. The below instruction will install PHP 8.1. Please change the version as per your requirements.

The ondrej/php ppa contains all PHP version’s for Ubuntu systems. So add this repository in your system with command below:

sudo add-apt-repository ppa:ondrej/php 

Now update the apt cache and install PHP 8.1.

sudo apt update 
sudo apt install php8.1 

This will install PHP on your Ubuntu system along with some useful PHP extensions.

Step 3 — Install and Configure PHP-FPM

PHP-FPM (FastCGI Process Manager) is an alternative PHP FastCGI implementation with some additional features. Nginx web server required php-fpm for processing the PHP scripts.

To install PHP-FPM, run the following command based on the installed PHP version:

sudo apt install php8.1-fpm 

Once the installation finished, check the service status:

sudo systemctl status php8.1-fpm 

● php8.1-fpm.service - The PHP 8.1 FastCGI Process Manager
     Loaded: loaded (/lib/systemd/system/php8.1-fpm.service; enabled; vendor preset: enabled)
     Active: active (running) since Thu 2022-04-07 06:26:55 UTC; 11min ago
       Docs: man:php-fpm8.1(8)
    Process: 108650 ExecStartPost=/usr/lib/php/php-fpm-socket-helper install /run/php/php-fpm.sock /etc/php/8.1/fpm/pool.d/www.conf 81 (code=exited, status=0/SUCCESS)
   Main PID: 108647 (php-fpm8.1)
     Status: "Processes active: 0, idle: 2, Requests: 2, slow: 0, Traffic: 0req/sec"
      Tasks: 3 (limit: 1034)
     Memory: 10.7M
        CPU: 88ms
     CGroup: /system.slice/php8.1-fpm.service
             ├─108647 "php-fpm: master process (/etc/php/8.1/fpm/php-fpm.conf)
             ├─108648 "php-fpm: pool www
             └─108649 "php-fpm: pool www

Apr 07 06:26:55 ubuntu2204 systemd[1]: Starting The PHP 8.1 FastCGI Process Manager...
Apr 07 06:26:55 ubuntu2204 systemd[1]: Started The PHP 8.1 FastCGI Process Manager.

In Step 6, we will configure the Nginx virtual host with PHP-FPM to serve PHP applications.

Step 4 – Installing MySQL

The default Ubuntu repositories contain MySQL 8.0. Which can be directly installed using the package manager. To install the available MySQL server version, execute the following command.

sudo apt-get install mysql-server 

Once the installation is finished, you can secure the MySQL server by executing the following command.

sudo mysql_secure_installation 

This will ask for a few questions to secure the MySQL server.

1. Press ‘y’ to enable validate password plugin. This will allow you to set a strict password policy for user accounts.

   VALIDATE PASSWORD COMPONENT can be used to test passwords
   and improve security. It checks the strength of password
   and allows the users to set only those passwords which are
   secure enough. Would you like to setup VALIDATE PASSWORD component?
   
   Press y|Y for Yes, any other key for No: y
   

2. Chose the password complexity level. Read all 3 options and choose one. For production servers we recommend to choose STRONG policy.

   LOW    Length >= 8
   MEDIUM Length >= 8, numeric, mixed case, and special characters
   STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
   
   Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1
   

3. Enter a new password and re-enter it. Make sure it matches the complexity level as described above.

   New password: *************
   Re-enter new password: *************
   

4. Press ‘y’ to continue with provided password.

   Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
   

5. Remove default anonymous users from MySQL server:

   Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
   

6. Disable root login from remote systems

   Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
   

7. Remove test database form MySQL created by default during installation.

   Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
   

8. Reload all privileges to apply above changes immediately.

   Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
   

You have secured the MySQL server in the LAMP stack on Ubuntu 22.04 Linux system.

Remember that the above password set for the root accounts is used for remote users only. To log in from the same system, just type mysql on terminal.

sudo mysql 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.28-0ubuntu4 (Ubuntu)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Type ‘quit’ to exit from the MySQL shell and return to the system terminal.

Step 5 – Installing Additional Packages

You may also need to install modules like MySQL and other extensions for PHP based on the application requirements. Use the following command to find our available PHP extensions.

sudo apt search php8.1-* 

The above command will list all available PHP7 modules for installation, Let’s begin the installation of modules.

sudo apt install php8.1-mysql php8.1-curl php8.1-xml 

You may also need to install other required PHP extensions on your system.

Step 6 — Configure Nginx VirtualHost

Finally, do the configuration of the Nginx virtual host. For this example, we are editing the default configuration file.

sudo nano /etc/nginx/sites-enabled/default 

and make changes as below.

server {
        listen   80;

        root /var/www/example.com;
		
        index index.php;
		
        server_name  example.com www.example.com;

        location / {
                try_files $uri $uri/ =404;
        }

        location ~ \.php$ {
                include snippets/fastcgi-php.conf;
                fastcgi_pass unix:/run/php/php8.1-fpm.sock;
        }

}

In the above configuration file, look for location ~ \.php$ section. Which is required to service PHP scripts via Nginx server.

You have to do the same changes in all VirtualHosts configured.

Step 7 – Verify Setup

You have successfully completed the installation of Nginx, MySQL, and PHP on the Ubuntu 22.04 Linux system. To verify the PHP integration with Nginx, create a PHP script (example: info.php) on the website document root and write the below content.

<?php
   phpinfo();
?>

Now access this file in the web browser. It will so all the details about versions and installation.

http://server-ip-or-domain-name/info.php 

Conclusion

This tutorial helped you to set up the LEMP (Linux, Nginx, MySQL, and PHP) stack on Ubuntu 22.04 LTS system. Now, you can host PHP-based web applications on your server.

The post How To Install Linux, Nginx, MySQL, & PHP (LEMP Stack) on Ubuntu 22.04 appeared first on TecAdmin.

This tutorial will help you to install the MySQL server on Ubuntu 22.04 Jammy Jellyfish Linux systems.

Prerequisities

You must have a running Ubuntu 20.04 Linux system with sudo privileges user access.

Step 1 – Installing MySQL on Ubuntu 22.04

The default Ubuntu repositories contain MySQL 8.0. Which can be installed directly using the package manager without adding third-party PPA.

To install the available MySQL server version, execute the following command.

sudo apt-get install mysql-server 

Press ‘y’ for any confirmation asked by the installer.

Once the installation is finished, you can secure the MySQL server by executing the following command.

sudo mysql_secure_installation 

You will go through a wizard of questions to secure the MySQL server. Follow the onscreen instructions below:

1. Press ‘y’ to enable validate password plugin. This will allow you to set a strict password policy for user accounts.

   VALIDATE PASSWORD COMPONENT can be used to test passwords
   and improve security. It checks the strength of password
   and allows the users to set only those passwords which are
   secure enough. Would you like to setup VALIDATE PASSWORD component?
   
   Press y|Y for Yes, any other key for No: y
   

2. Chose the password complexity level. Read the all 3 options and choose one:

   LOW    Length >= 8
   MEDIUM Length >= 8, numeric, mixed case, and special characters
   STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
   
   Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1
   

3. Enter a new password and re-enter it. Make sure it matches the complexity level as described above.

   New password: *************
   Re-enter new password: *************
   

4. Press ‘y’ to continue with provided password.

   Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
   

5. Remove default anonymous users from MySQL server:

   Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
   

6. Disable root login from remote systems

   Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
   

7. Remove test database form MySQL created by default during installation.

   Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
   

8. Reload all privileges to apply above changes immediately.

   Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
   

You have secured the MySQL server in the LAMP stack on Ubuntu 22.04 Linux system.

Step 2 – Connect to MySQL Server

Remember that the above password set for the root accounts is used for remote users only. To log in from the same system, just type mysql on terminal.

sudo mysql 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.28-0ubuntu4 (Ubuntu)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Step 3 – Creating Database and Users

Here is few example queries to create database and users in MySQL server.

• Create a database named ‘mydb’.

  CREATE DATABASE mydb; 
  

• Next, create a user named ‘myuser’ accessible from ‘localhost’ only.

  CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'secure_password_'; 
  

• Grant permissions on database to user.

  GRANT ALL ON mydb.* to 'myuser'@'localhost'; 
  

• Apply the permission changes at runtime.

  FLUSH PRIVILEGES; 
  

Step 4 – Manage MySQL Service

• To check the database server status.

  sudo systemctl status mysql 
  

• Use below command to start MySQL server.

  sudo systemctl start mysql 
  

• To stop MySQL server:

  sudo systemctl stop mysql 
  

• Restart MySQL database server, type:

  sudo systemctl restart mysql 
  

Step 5 – Uninstall (Remove) MySQL Server

If you no longer need to use the MySQL server, uninstall it from your server.

To remove MySQL server type:

sudo apt purge mysql-server-* 

To completely uninstall MySQL, remove the following folders as well.

rm -rf /etc/mysql 
rm -rf /var/lib/mysql 

Conclusion

This tutorial helped you to install the MySQL server on Ubuntu 20.04 LTS Linux system. Also includes instructions to secure the database server and uninstall it.

The post How To Install MySQL Server on Ubuntu 22.04 appeared first on TecAdmin.