MySQL show processlist

MySQL

To see currently running MySQL processes, login to MySQL server, run in MySQL command prompt, run the command

To see the full SQL command, use

You can also use the following command from the bash command prompt

To see the number of SQL processes, use

See MySQL […]

Read More…

MySQL backup all databases

MySQL

To backup all databases on a MySQL server, run

This will create sql backup file for each database in your MySQL server on the current directory. The first command will create a file with all database names. Second command loop through the database names and create backup using mysqldump command. See MySQL Backup […]

Read More…

bash: mysql_safe: command not found

MySQL

On CentOS 7 server running MySQL 5.7, when trying to reset MySQL root password, I get an error

This is because MySQL 5.7 installation using yum removed the mysqld_safe binary file. To reset, you need to start MySQL using systemctl with skip-grant-tables options. So you don’t need mysqld_safe. To see how to reset MySQL […]

Read More…

CentOS 7 MySQL 5.7 root password reset

MySQL

To reset MySQL 5.7 root password on CentOS 7 server, do the following Stop MySQL Server

Set the MySQL MYSQLD_OPTS environment to start MySQL with –skip-grant-tables

Start MySQL with –skip-grant-tables

Login as user root

Update MySQL root password

Or

Exit MySQL command prompt

Stop MySQL server

Unset […]

Read More…

ERROR 1045 (28000): Plugin caching_sha2_password could not be loaded

When I try to connect to a MySQL 8 server, I get the following error

This is because of MySQL 8 use more secure authentication. This is not supported by your MySQL installation. What you can do is connect using MySQL 8 client or change the authentication method to use old authentication. To create […]

Read More…

Magento catalog_product_relation MySQL Error

On transferring a Magento website Database running on MySQL 5.7.34 to MariaDB 10.2.39, I get the following error

SQL Strict mode was already off on this MySQL server.

To fix the problem, edited the db.sql file, added

at the top of the file. After this change, the restore worked without any error. […]

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…