How to Reset Root Password in MySQL 8.0

MySQL

If you forget the MySQL root password on MySQL 8.0 server, you can follow the instruction here to reset the root user password. First, stop the MySQL server if it is already running

Now start MySQL with –skip-grant-tables option

Take a new terminal, connect to MySQL

When it asks for the root […]

Read More…

MySQL 8 server requested authentication method unknown to the client

MySQL

On a server running MySQL 8 and PHP 7.3, I get the following error [email protected]:~$ php 1.php PHP Warning: mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in /var/www/html/1.php on line 6 PHP Warning: mysqli_connect(): (HY000/2054): The server requested authentication method unknown to the client in /var/www/html/1.php on line 6 Failed to […]

Read More…

Repair MySQL database with mysqlcheck

MySQL

To check and repair all databases in a database run

Forcefully optimize all tables, automatically fixing table errors that may come up.

To check all databases

To analyze all tables in all databases:

To repair all tables in all databases:

To optimize all tables in all databases:

See MySQL […]

Read More…

MySQL Unknown collation: ‘utf8mb4_0900_ai_ci’

When restoring a MySQL backup taken on MySQL 8 server, i get following error

To fix this error, open the file in a text editor, find utf8mb4_0900_ai_ci, replace with utf8mb4_unicode_ci. I used following sed command to do the replace.

See MySQL […]

Read More…

How to Install MySQL 5.7 on Amazon Linux

MySQL

Amazon Linux 2 come with MariaDB by default. To install MySQL 5.7, install repository with

Install MySQL 5.7 with

Set MySQL to start on boot

Start MySQL server

By default MySQL 5.7 installation generate a temporary password. To find the password, run

To set password and secure the installation, run […]

Read More…

MySQL Your password does not satisfy the current policy requirements

MySQL

When i try to create MySQL user on a server, i get following error

This is due to MySQL validate password settings

To fix the error for current session, run

Now password change will work.

If you need it made permanent, then edit MySQL configuration file and add the line under […]

Read More…

Change MySQL user password

To change the password for a MySQL user, run

Or via SQL

See MySQL […]

Read More…

Reset MySQL root password on Bitnami server

MySQL

To reset MySQL root password on Bitnami server, first check MySQL server version you are running.

Create a file

Add following text For MySQL 5.7 or MySQL 8

For MySQL 5.6

In above code, replace NEW_PASSWORD with your new MySQL root password. Stop MySQL

Reset MySQL root password by running […]

Read More…