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.

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.

In this guide, we’ll show you how to easily change your MySQL user password in just a few steps. We’ll cover the basics of MySQL user passwords, how to reset your MySQL user password from the command line, how to change your MySQL user password using PhpMyAdmin, and tips for creating a strong MySQL user password. Let’s get started!

Changing MySQL User Password

You have the version of the running MySQL server. Use one of the following commands to change the MySQL user password as per of database server version running on your system.

In the below SQL statements, make sure to change dbuser with your database user and localhost with the user’s host.

• First of all, check for the existing user and corresponding host.
  

  mysql> SELECT User,Host FROM mysql.user WHERE User='dbuser';;

  Output
  +--------+-----------+
  | User   | Host      |
  +--------+-----------+
  | dbuser | localhost |
  +--------+-----------+
  1 row in set (0.00 sec)
  

• Use the ALTER USER to change MySQL user password for the MySQL database server version 5.7.6 or newer versions:

  mysql> ALTER USER 'dbuser'@'localhost' IDENTIFIED BY 'new-password';

  

• For the MySQL database server version 5.7.5 or older version, Use the “SET PASSWORD” statement to change a MySQL user password using SQL queries.

  mysql> SET PASSWORD FOR 'dbuser'@'localhost' = PASSWORD('new-password');

  

Understanding MySQL User Password Basics

The first step in changing your MySQL user password is to understand the basics of MySQL user passwords. MySQL user passwords are used to authenticate the user that is attempting to access the database. They are typically stored in a separate table in the database and encrypted using a hashing algorithm.

When you create a new MySQL user, you must provide a password that meets certain requirements, such as having at least 8 characters and including both letters and numbers. You should also be sure to create a unique password for each user, as this will help to prevent unauthorized access to the database.

Once you have created a new MySQL user, you can easily change the user’s password by following the steps outlined in this guide.

Tips for Creating a Strong MySQL User Password

Now that you know how to quickly and easily change your MySQL user password, it is important to ensure that you are creating a strong password. A strong password should include at least 8 characters and include both letters and numbers.

It is also important to avoid using common words or phrases as passwords. If possible, try to use a randomly generated password from a password manager. This will ensure that your password is secure and difficult to guess.

Finally, be sure to create a unique password for each user. This will help to prevent unauthorized access to the database.

Conclusion

In this guide, we have shown you how to quickly and easily change your MySQL user password. We have covered the basics of MySQL user passwords, how to reset your MySQL user password from the command line, how to change your MySQL user password using PhpMyAdmin, and tips for creating a strong MySQL user password.

We hope that you have found this guide helpful and that you are now able to confidently manage your MySQL user passwords. With these tips, you’ll be able to keep your MySQL user passwords secure and prevent unauthorized access to your database.

The post Quickly Changing Your MySQL User Password! appeared first on TecAdmin.

In this article you will learn to find accounts in a MySQL server and remote unnecessary user accounts.

View Current Users

Connect to the existing MySQL server using administrative privileged account.

mysql -u root -p 

Next, list all the available user account in MySQL server. Default database named “mysql” contains system related details including user account under “user” table.

SELECT User,Host FROM mysql.user; 

List out the account with hostname to delete or all unused accounts no longer required.

Drop/Delete MySQL User

Use DROP USER statement is used to delete one or more accounts from MySQL. It also removes privilege rows for the account from all grant tables. You must have specified username along with hostname as per showing in above screenshot.

For example, to remove user “dummy” with host “localhost”, execute below query:

DROP USER 'dummy'@'localhost';

The above command drop only those user from MySQL maches both username and hostname.

Let’s use another example to delete user “myuser” with hostname “%”, execute below query.

DROP USER 'myuser'@'%';

That’s it. Repeat the same command to remove more accounts no longer required.

Conclusion

This tutorial describes you to how to remove user accounts from MySQL database server.

The post How to Delete A MySQL User Account appeared first on TecAdmin.

This tutorial will help you to install MySQL 8 on Ubuntu 20.04 LTS Focal Linux systems.

Prerequisites

You must have a running Ubuntu 20.04 LTS system with sudo privileged account access. Next, login to your system and open a terminal.

ssh ubuntu@remote.host 

Run below commands to upgrade the current packages to the latest version.

sudo apt update && sudo apt upgrade 

Step 1 – Enable MySQL PPA

MySQL team provides official MySQL PPA for Ubuntu operating systems. You can download and install the package on your Ubuntu 18.04 system, which will add PPA file to your system. Run below command to enable PPA.

wget http://repo.mysql.com/mysql-apt-config_0.8.10-1_all.deb 
sudo dpkg -i mysql-apt-config_0.8.10-1_all.deb 

The installer will prompt you to set default repository for the MySQL version. Make sure the MySQL-8 is set in currently selected. You can change this by navigating to line and press enter.

Once the properly version is selected move curser to last row “Ok” using keyboard up/down keys. Press enter to continue process.

In case you select wrong version. Execute dpkg-reconfigure mysql-apt-config command after package installation to get this window again.

Step 2 – Installing MySQL 8 on Ubuntu

Your system is ready for the MySQL installation. Follow the below commands to install MySQL 8 on a Ubuntu 20.04 Linux system.

1. Add GPG Key – Run the following commands to install MySQL on Ubuntu 18.04 system.

   sudo apt-key adv --keyserver keys.gnupg.net --recv-keys 8C718D3B5072E1F5 
   

2. Update cache – Execute below command to update apt cache on your system.

   sudo apt update  
   

3. Install MySQL – finally install the MySQL server packages.

   sudo apt install mysql-server 
   

The installation process will prompt for the root password to set as default. Input a secure password and same to confirm password window. This will be MySQL root user password required to log in to MySQL server.

Step 3 – Secure MySQL Installation

Execute the below command on your system to make security changes on your Database server. This will prompt some questions. The do the high security provide all answers to yes.

sudo mysql_secure_installation 

Here you can enable/disable validate password plugin, Set required strength for passwords, remove anonymous users, disallow root login remotely, Remove test database and access to it and reload the reload privileges after applied changes.

See the below output and action taken by me:

Securing the MySQL server deployment.

Enter password for user root:

VALIDATE PASSWORD PLUGIN 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 plugin?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation 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: 2
Using existing password for root.

Estimated strength of the password: 50
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y

New password: ****************

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

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

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


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

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

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


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

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

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

All done!

Step 4 – Connect to MySQL

The MySQL server has been installed on your system. Now connect to the MySQL database using the command line. Use root account password set in above step.

 mysql -u root -p 

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

Copyright (c) 2000, 2021, 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>

Conclusion

After completing this tutorial, You have installed MySQL 8 on Ubuntu 20.04 Linux system. Even you can access the MySQL from terminal, you can also install phpMyAdmin to work with it.

The production users recommended to schedule database backup on a regular interval. This script will help you to schedule MySQL database backup and copy them to remote locations.

The post How to Install MySQL 8.0 on Ubuntu 20.04 appeared first on TecAdmin.

• Install MySQL on Ubuntu 20.04
• Install MySQL on Debian 10

Find Version with mysqld Command

Use mysqld command with -V option to check MySQL version running on local host system. The below examples can only used for the localhost only.

mysqld -V 

Output:
/usr/sbin/mysqld  Ver 8.0.25-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

The above result shows that you are running MySQL version 8.0.25 on your local system.

Using SELECT VERSION Statement

You can also obtain the version from within the MYSQL shell by typing the SELECT VERSION() statement:

mysql> SELECT VERSION();

Output:
+-------------------------+
| VERSION()               |
+-------------------------+
| 8.0.25-0ubuntu0.20.04.1 |
+-------------------------+
1 row in set (0.00 sec)

Using SHOW VARIABLES Statement

You can also find the MySQL version information, which is stored in a variable named version. Type the below statement to view the MySQL server version.

mysql> SHOW VARIABLES LIKE "%version%";

Check MySQL Version During Connecton

You can also check the MySQL version with a log-in to MySQL command-line interface. When we connect to the MySQL shell, it displays a welcome message including the MySQL version running on the system.

This method can be used to check MySQL versions running on localhost as well as on the remote hosts.

mysql -u root -p 

Conclusion

This tutorial helps you to check the MySQL version running on localhost or a remote host.

The post How to Check MySQL Version appeared first on TecAdmin.

This tutorial will help you to install MySQL server on Ubuntu 18.04 Bionic Beaver systems.

Prerequisites

Login to your Ubuntu 18.04 system using shell access. For remote systems connect with SSH. Windows users can use Putty or other alternatives applications for SSH connection.

ssh ubuntu@remote.host

Run below commands to upgrade the current packages to the latest version.

sudo apt update && sudo apt upgrade

Step 1 – Configure MySQL PPA

MySQL team provides official MySQL PPA for Ubuntu operating systems. You can download and install the package on your Ubuntu 18.04 system, which will add PPA file to your system. Run below command to enable PPA.

wget http://repo.mysql.com/mysql-apt-config_0.8.10-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.10-1_all.deb

During the installation of MySQL apt config package. Select the first line as showing in below screenshot and press enter. Here you can select MySQL version (8.0 or 5.7) to install on your system. After that Select, the last line with text Ok and press enter to complete this.

Step 2 – Install MySQL 8 on Ubuntu 18.04

Your system is ready for the MySQL installation. Run the following commands to install MySQL on Ubuntu 18.04 system.

sudo apt-key adv --keyserver keys.gnupg.net --recv-keys 8C718D3B5072E1F5

sudo apt update 
sudo apt install mysql-server

The installation process will prompt for the root password to set as default. Input a secure password and same to confirm password window. This will be MySQL root user password required to log in to MySQL server.

Step 3 – Secure MySQL Installation

Execute the below command on your system to make security changes on your Database server. This will prompt some questions. The do the high security provide all answers to yes.

sudo mysql_secure_installation

Here you can enable/disable validate password plugin, Set required strength for passwords, remove anonymous users, disallow root login remotely, Remove test database and access to it and reload the reload privileges after applied changes.

See the below output and action taken by me:

Securing the MySQL server deployment.

Enter password for user root:

VALIDATE PASSWORD PLUGIN 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 plugin?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation 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: 2
Using existing password for root.

Estimated strength of the password: 50
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y

New password:

Re-enter new password:

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

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


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

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

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


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

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

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

All done!

Step 4 – Connect to MySQL

The MySQL server has been installed on your system. Now connect to the MySQL database using the command line. Use root account password set in above step.

 mysql -u root -p

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

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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>

Conclusion

This tutorial helped you to install MySQL 8 on Ubuntu 18.04 LTS Linux system.

The post How To Install MySQL 8.0 on Ubuntu 18.04 appeared first on TecAdmin.