Mysql user add/password change not working with mysql 8.0

[kolomparrudi]

Describe the problem:

The new mysql user is not created and password change also not working

Steps to Reproduce:

create new database and user in vesta if mysql installed

Debian version:

10 Type here, example: Debian 10

VestaCP Version:

latest

Installed Software (what you got with the installer):

WEB_SYSTEM='apache2' WEB_RGROUPS='www-data' WEB_PORT='80' WEB_SSL_PORT='443' WEB_SSL='mod_ssl' STATS_SYSTEM='webalizer,awstats' FTP_SYSTEM='vsftpd' DNS_SYSTEM='bind9' MAIL_SYSTEM='exim4' ANTIVIRUS_SYSTEM='clamav-daemon' ANTISPAM_SYSTEM='spamassassin' IMAP_SYSTEM='dovecot' CRON_SYSTEM='cron' FIREWALL_SYSTEM='iptables' FIREWALL_EXTENSION='fail2ban' DISK_QUOTA='yes' BACKUP_SYSTEM='local' LANGUAGE='en' VERSION='0.9.8' DB_SYSTEM='mysql' SFTPJAIL_KEY='' FILEMANAGER_KEY='' SOFTACULOUS='no'

Reason: grant ... identified by removed from mysql 8

Here is my workaround:

# Change MySQL database password
change_mysql_password() {
    mysql_connect $HOST
if [ "$(echo $mysql_ver |cut -d. -f1)" -ge 7 ]; then
    query="CREATE USER \`$DBUSER\`@\`%\`"
    mysql_query "$query"
    query="CREATE USER \`$DBUSER\`@\`localhost\`"
    mysql_query "$query"
    query="ALTER USER \`$DBUSER\`@\`localhost\` IDENTIFIED BY '$dbpass'"
    mysql_query "$query"
    query="ALTER USER \`$DBUSER\`@\`%\` IDENTIFIED BY '$dbpass'"
    mysql_query "$query"
    query="GRANT ALL PRIVILEGES ON \`$database\`.* TO \`$DBUSER\`@\`%\`"
    mysql_query "$query"
    query="GRANT ALL PRIVILEGES ON \`$database\`.* TO \`$DBUSER\`@\`localhost\`"
    mysql_query "$query"

    md5=$(mysql_query "SHOW CREATE USER \`$DBUSER\`" 2>/dev/null)
    md5=$(echo "$md5" |grep password |cut -f8 -d \')
else
    query="GRANT ALL ON \`$database\`.* TO \`$DBUSER\`@\`%\`
        IDENTIFIED BY '$dbpass'"
    mysql_query "$query"

    query="GRANT ALL ON \`$database\`.* TO \`$DBUSER\`@localhost
        IDENTIFIED BY '$dbpass'"
    mysql_query "$query"

    md5=$(mysql_query "SHOW GRANTS FOR \`$DBUSER\`" 2>/dev/null)
    md5=$(echo "$md5" |grep PASSW|tr ' ' '\n' |tail -n1 |cut -f 2 -d \')
fi
}

[myvesta]

Can you also provide installation steps :D thank you :)

[kolomparrudi]

install procedure: add mysql repo from here: https://dev.mysql.com/downloads/repo/apt/ install mysql server 8.0