How to configure Mysql root password on Oracle Linux 8

• Login to your server as root.
• By default, there is no password for mysql root.
• mysql -u root -p
• Alter user 'root'@'localhost' identified WITH mysql_native_password by 'StrongPassword';
• flush privileges;
• exit;

Now, try to connect to Mysql as root user. It works!

Mysql Slave_SQL_Running no

 Mysql Slave_SQL_Running no

Environment is Centos8 with MySQL 8.0 and its a MySQL master slave replication. When I checked the "SHOW SLAVE STATUS\G" I am seeing status no for "Slave_SQL_Running".

Fix the issue :

Login to Slave Server.

STOP SLAVE;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 

START SLAVE;

This will fix my issue.

The GPG keys listed for the "MySQL 8.0 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.

 OS : CentOS 7

Error while installing Mysql

The GPG keys listed for the "MySQL 8.0 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.

Fix:

Import new key from rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

Yum update

yum install mysql-server

Access denied for user 'user'@'hostname' (using password: YES) in mysql8

 Error : Access denied for user 'user'@'hostname' (using password: YES) in mysql8

Mysql version is Mysql8

OS is Ubuntu 22

I am sure that i can connect from my both apps servers to the DB from the command shell.

The issue is the mysql user password. In my password, i had some special characters. When we input this password in command prompt, in bash shell it will work. But its not working from nodejs code (.env file)

Solution :

I change the password without a special character and the issue got fixed.

MySQL Error : which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 I got mysql error when executing a command

which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Solution to fix :

- Login to your server as root

- Open your mysql configuration file (for me its /etc/mysql/mysql.conf.d/mysqld.cnf)

- Add the following code after [mysqld] block

[mysqld]

sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

- Restart mysql service ( systemctl restart mysql )

Issue fixed

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.

 OS is Amazon Linux and trying to install Mysql 5.7, I am getting the below error

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.

Fix : 

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

Now run, yum install mysql-community-server

It will work for me.

authentication plugin caching_sha2_password is not supported

 Getting the below error message when you connect to mysql8

authentication plugin caching_sha2_password is not supported

In MySQL 8.0, caching_sha2_password is the default authentication plugin rather than mysql_native_password.

Login to Mysql as root

Check whether the mysql user plugin is mysql_native_password or caching_sha2_password

select Host,User,plugin from mysql.user;

If its caching_sha2_password, change to mysql_native_password

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '{Password}';

RESTART MYSQL SERVICE.

Try now!

The server requested authentication method unknown to the client

 Mysql Version : 8.0.23

PHP version : 5.6.40

CentOS Linux release 7.9.2009 (Core)

Take a backup of /etc/my.cnf

[mysqld]

default_authentication_plugin= mysql_native_password

Restart Mysql service

systemctl restart mysqld

Login to Mysql root prompt

alter user 'user'@'localhost' identified with mysql_native_password by 'your_password';

After this, if you get an error "If it is FULL_GROUP_GROUP_BY exist, then we need to disable it:"

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Failed to connect to MySQL: Server sent charset unknown to the client. Please, report to the developers

 Mysql Version : 8.0.23

PHP version : 5.6.40

CentOS Linux release 7.9.2009 (Core)

Take a backup of /etc/my.cnf

[mysqld]

character-set-server=utf8

Restart MysQl service

systemctl restart mysqld

How to install Mysql5.7 on Oracle linux 7

 By default Mariadb comes with Oracle linux 7. I need to have mysql on my box.

yum remove mariadb-server -y

wget https://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm

rpm -ivh mysql57-community-release-el7-8.noarch.rpm

yum install mysql-server -y

systemctl start mysqld

systemctl enable mysqld

sudo grep 'temporary password' /var/log/mysqld.log

mysql_secure_installation

I forgot the admin user/password of moodle, but i have mysql access. How can i restore the admin password?

Login to the mysql prompt.
use table_name
update mdl_user set password='0192023a7bbd73250516f069df18b500' where  username='admin';
quit

I know moodle is using MD5 encryption.
The above mentioned is MD5 encryption of admin123

Login to the admin panel with password admin123 It works!!!

mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode'':

Issue happens when I take a mysqldump after I upgrade Mysql 5.5 to Mysql 5.7

Error:

[root@ip-10-26-91-168 mnt]# mysqldump -u userone -p db1 > db1.sql
Enter password:
mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode'': Native table 'performance_schema'.'session_variables' has the wrong structure (1682)
[root@ip-10-26-91-168 mnt]#

Solution:

Login to the server as root

# mysql_upgrade -u root -p --force
# systemctl restart mysqld

This helped to fix my issue :)

How to create Oracle MySQL Cloud Service

Oracle MySQL Cloud Service is a single MySQL server having full access to the features and its operations.

Steps in creating an instance of Oracle MySQL Cloud Service

1. Login to your cloud account.
2. From Action Menu, select Open Service Console

3. Click Create Service
4. Input the Instance Name, Region and Availability Domain

5. Next page, input the compute shape, ssh key, cloud storage container, username, password, storage size, administrator username, password, database schema name and port.

6. Once you confirm, you could see the mysql instance running in your dashboard.
7. With your ssh keys and inputed connection string, you can either ssh or connect database from your application.

Appendix

MySQL Cloud Service

How to install Mysql8 on OEL7.5

How to install Mysql8 on OEL7.5

Login to the server:

wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
rpm -ivh <filename>
yum install mysql-community-server -y
systemctl enable mysqld.service
systemctl start mysqld
grep 'temporary password' /var/log/mysqld.log (for getting the temporary password)
/usr/bin/mysql_secure_installation

mysql daemon failed to start in amazon linux

mysql daemon failed to start in amazon linux.

I could see that all the configurations and everything is fine and it was working fine.

Solution:

Issue was with there is not free swap memory on the server. You need to add manually a swap file to fix the issue.

Login to the server

# dd if=/dev/zero of=/swapfile1 bs=1024 count=524288
# chown root:root /swapfile1
# chmod 0600 /swapfile1
# mkswap /swapfile1
# swapon /swapfile1
# vi /etc/fstab
Add the following at the last
/swapfile1 none swap sw 0 0
Save the file
$ free -m

Now try to start mysqld service.

ERROR 1558 (HY000): Column count of mysql.user is wrong. Expected 42, found 39. Created with MySQL 50173, now running 50556. Please use mysql_upgrade to fix this error.

I am getting the following error in mysql operation.

ERROR 1558 (HY000): Column count of mysql.user is wrong. Expected 42, found 39. Created with MySQL 50173, now running 50556. Please use mysql_upgrade to fix this error.

Solution:

Login to the server
[root@blackmagic ~]# mysql_upgrade -u root -p
Enter password:
(Enter your password)

This will fix the issue and its solved.

Mattermost Error: MySQL FULLTEXT indexes issue in logs

Mattermost Error: MySQL FULLTEXT indexes issue in logs

While working on Mattermost in my local machine I got the following error:

MySQL FULLTEXT indexes

Solution for fixing the issue is:

1. Login the server
2. Login to the mysql prompt:
show table status;
3. There you can see all the tables are having Engine as MYISAM.
4. You need to update those tables as INNODB
ALTER TABLE <table name> ENGINE = INNODB;

This helped to solve the issue.

To change the siteurl value in wordpress using mysql

While during a wordpress migration, here i need to change the domain name. Currently, after WP migration all the links are pointing to the old domain name. We can solve this my updating a field in mysql DB.

Changing WordPress Home URL and Site URL via MySQL database.

Login to Mysql
use Database_name;
SELECT * FROM wp_options WHERE option_name = 'home';
       This will show the current site url
UPDATE wp_options SET option_value="http://newdomain.com" WHERE option_name = "home";
       Verify whether the change got reflected
SELECT * FROM wp_options WHERE option_name = 'siteurl';

How to reset wordpress admin password?

I have a wordpress and lost its admin password. We can change the admin password via. phpmyadmin.

logging into phpMyAdmin and click databases.
A list of databases will appear. Click your WordPress database.
All the tables in your database will appear. If not, click Structure
Look for wp_users
Click on the icon for browse.
Locate your Username under user_login
Click edit
Your user_id will be shown, click on Edit
Next to the user_pass is a long list of numbers and letters.
Select and delete these and type in your new password.
Type in the password you want to use. Just type it in normally, but remember, it is case-sensitive.
Once you have done that, click the dropdown menu indicated, and select MD5 from the menu.
Check that your password is actually correct, and that MD5 is in the box.
Click the 'Go' button to the bottom right.
Test the new password on the login screen. If it doesn't work, check that you've followed these instructions exactly.

http://codex.wordpress.org/Resetting_Your_Password

How to disable STRICT_MODE in mysql ?

Login to mysql server
# Check global settings for strict mode
mysql> select @@GLOBAL.sql_mode;
+--------------------------------------------------------------+
| @@GLOBAL.sql_mode                                            |
+--------------------------------------------------------------+
| STRICT_ALL_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------------------------+

# Remove settings
mysql> SET GLOBAL sql_mode="";
   
# Verify
mysql> select @@GLOBAL.sql_mode;
+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
|                   |
+-------------------+