Change 'mysql_native_password' authentication

 Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'


You can identify the affected users by running the following query in your MySQL console

SELECT user, host, plugin FROM mysql.user WHERE plugin='mysql_native_password';

Once you identify the users, update their authentication plugin using this command
ALTER USER 'user'@'location' IDENTIFIED WITH caching_sha2_password BY 'password';
FLUSH PRIVILEGES;

If your application or framework (such as an older version of PHP, Node.js, or a specific driver)
 does not support caching_sha2_password, you can use sha256_password as an alternative

Ubuntu 22.04 mysql password

 

$ mysql -u root -p

mysql > SELECT user,plugin,host FROM mysql.user WHERE user = 'root';

mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'MrsLKYsuck5';

mysql > FLUSH PRIVILEGES;

Moving the MySQL data directory to a new location

In this article I will describe the process of moving the MySQL data directory to a separate location, namely on a level 10 raid with 4 disks, this will increase performance.
 
I’ll take an HP DL360p Gen8 server with a Smart Array P420i raid controller, and I’ll also take 6 Samsung 883 DCT Enterprise 240GB 2.5″ SATA III V-NAND MLC (MZ-7LH240NE).
From two disks we will create a mirrored raid 1 and install the Ubuntu 18.04 operating system on it, from the remaining 4 SSD we will create a level 10 raid that will be used only for the database.
 
Switch to root user:
sudo su
 
Stop the MySQL server:
systemctl stop mysql
systemctl status mysql
 
Let’s make a copy of the database files:
mkdir /var/lib/mysql_ixnfo.com
cp -r /var/lib/mysql/* /var/lib/mysql_ixnfo.com/
 
We delete files:
rm /var/lib/mysql/* -Rf
 
Let’s see the list of available disk systems:
fdisk -l
fdisk -l | grep '/dev/sd'
 
I got it displayed:
 
Disk /dev/sda: 223.6 GiB, 240021504000 bytes, 468792000 sectors
/dev/sda1   2048      4095      2048     1M BIOS boot
/dev/sda2   4096 468789247 468785152 223.5G Linux filesystem
Disk /dev/sdb: 447.1 GiB, 480047095808 bytes, 937591984 sectors
 
Let’s create a section on the second raid:
 
fdisk /dev/sdb
n
p
1
Enter
Enter
w
fdisk -l | grep '/dev/sd'
mkfs.ext4 /dev/sdb1
df -h
 
Mount the newly created partition in the database directory:
mount /dev/sdb1 /var/lib/mysql
 
Let’s see its UUID:
sudo blkid
 
I got it displayed:
/dev/sdb1: UUID="54bf0d45-c190-4185-968d-b440537122bd" TYPE="ext4" PARTUUID="d2090c9f-01"
 
To automatically mount when the operating system starts, open the /etc/fstab file in a text editor (in the nano editor, press Ctrl+X to exit and y/n to save or discard changes):
nano /etc/fstab
 
I added:
UUID=54bf0d45-c190-4185-968d-b440537122bd /var/lib/mysql ext4 defaults 0 0
 
From the previously made copy, copy the database files to the mounted raid 10:
cp -r /var/lib/mysql_ixnfo.com/* /var/lib/mysql/
 
Let’s set the correct owner and rights:
chown mysql:mysql /var/lib/mysql/ -R
chmod 755 /var/lib/mysql
 
Let’s check:
ls -l /var/lib/mysql/
 
We start the MySQL server:
systemctl start mysql
systemctl status mysql
 
Let’s restart the server to make sure that raid 10 is automatically mounted and the database server has started successfully:
reboot
 
After restarting, I noticed the default directory for lost files, I deleted it and restarted MySQL:
rm -d /var/lib/mysql/lost+found
systemctl restart mysql
 
If you wish, you can mount the raid to another directory and specify the path to it in my.cnf: 

[mysqld]
datadir=/mnt/sdb1/mysql
socket=/mnt/sdb1/mysql/mysql.sock
log-bin=/mnt/sdb1/mysql/mysql-bin
[mysqld_safe]
datadir=/mnt/sdb1/mysql
 
You can see which directory is specified like this:
mysql -u root -p
select @@datadir;
 
Let’s copy the files using rsync:
rsync -av /var/lib/mysql /mnt/sdb1/mysql
mv /var/lib/mysql /var/lib/mysql.mybakup
 
Let’s specify a new directory for apparmor in the /etc/apparmor.d/tunables/alias file:
alias /var/lib/mysql/ -> /mnt/sdb1/mysql/,
 
And let’s restart it:
systemctl restart apparmor
 
source: https://ixnfo.com/en/moving-the-mysql-data-directory-to-a-new-location.html
 

Mysql shell script without password exposure

Suppose your username is "db_user". Running from the shell prompt:

mysql_config_editor set --login-path=local --host=localhost --user=db_user --password

It prompts for the password. Once you enter it, the user/pass are saved encrypted in your home/system_username/.mylogin.cnf

write the password between "" if contains spacial character, like "#" or "." ex: "Passw#rd"

Of course, change "system_username" to your username on the server.

Change your bash script from this:

mysqldump -u db_user -pInsecurePassword my_database | gzip > db_backup.tar.gz

to this:

mysqldump --login-path=local my_database | gzip > db_backup.tar.gz


mysql --login-path=local -D DATABASE -e "SELECT * FROM table LIMIT 10"

Mysql root password set, change or recover

Mysql root password set, change or recover:


https://www.howtoforge.com/setting-changing-resetting-mysql-root-passwords

Fix Bug Phpmyadmin [sql.lib.php] + Php7.2 + Ubuntu 18.04

Edit file:

/usr/share/phpmyadmin/libraries/sql.lib.php

Change this (line #613):

|| (count($analyzed_sql_results[‘select_expr’] == 1)

there's a missing close bracket ‘)’ before operation with 1. So i changed it to:

|| ((count($analyzed_sql_results[‘select_expr’]) == 1)

 

Fix Bug Phpmyadmin [sql.lib.php] + Php7.2 + Ubuntu 16.04

Edit file: 

/usr/share/phpmyadmin/libraries/sql.lib.php

Change this (line #601):

|| (count($analyzed_sql_results[‘select_expr’] == 1)

there's a missing close bracket ‘)’ before operation with 1. So i changed it to:

|| (count($analyzed_sql_results[‘select_expr’]) == 1

Adminer on Ubuntu 18.04

Download the latest Adminer into our server

sudo mkdir /usr/share/adminer
sudo mkdir /usr/share/adminer/adminer
sudo mkdir /usr/share/adminer/editor

sudo wget "http://www.adminer.org/latest.php" -O /usr/share/adminer/adminer/latest.php

sudo ln -s /usr/share/adminer/adminer/latest.php /usr/share/adminer/adminer/index.php

echo "Alias /adminer /usr/share/adminer/adminer" > /etc/apache2/conf-available/adminer.conf

sudo a2enconf adminer.conf

reload the Apache config

sudo systemctl reload apache2

Access the adminer interface at
http://<Server_IP_or_Domain>/adminer/

Install adminer-editor:

Download from
https://www.adminer.org/en/editor/#download

Copy file to /usr/share/adminer/editor and rename to index.php

echo "Alias /adminer-editor /usr/share/adminer/editor" >> /etc/apache2/conf-available/adminer.conf

reload the Apache config

sudo systemctl reload apache2

Access the adminer interface at
http://<Server_IP_or_Domain>/adminer-editor

Fix MYSQL authentication issue in Ubuntu 18.04

Secure your MySQL installation and set the root password

sudo mysql_secure_installation

From here, you can just press Y and then ENTER to accept the defaults for all the subsequent questions.

Fix authentication issue caused by Ubuntu using auth_socket plugin by default for the root user.
You can set the root user to use the mysql_native_password instead to fix this issue, and we will have to set the root password again to correct this.

log in as sudo to mysql using the username and password set before.

sudo mysql -u root

in MySQL enter the following

USE mysql;

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Note:

For security make this password different than your server's password, and dont use the root user in any application that requires a database to store and pull data from.

FLUSH PRIVILEGES;

exit;