Access Denied for User 'root'@'localhost' (using password: YES) - No Privileges?

If you have that same problem in MySql 5.7.+ :

 Access denied for user 'root'@'localhost'

it's because MySql 5.7 by default allow to connect with socket, which means you just connect with sudo mysql. If you run sql :

SELECT user,authentication_string,plugin,host FROM mysql.user;

then you will see it :

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *497C3D7B50479A812B89CD12EC3EDA6C0CB686F0 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

To allow connection with root and password, then update the values in the table with command :

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Current-Root-Password';
FLUSH PRIVILEGES;

Then run the select command again and you'll see it has changed :

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *2F2377C1BC54BE827DC8A4EE051CBD57490FB8C6 | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *497C3D7B50479A812B89CD12EC3EDA6C0CB686F0 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

And that's it. You can run this process after running and completing the sudo mysql_secure_installation command.

Continue reading Access Denied for User 'root'@'localhost' (using password: YES) - No Privileges?

MySQL backup script

#! /bin/bash
### MySQL Server Login Info ###
MUSER="root"
MPASS="roott123"
MHOST="localhost"
MPORT="3306"
DBNAME="radius"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"


### File Info ###
# Save backup in temp directory before upload to server
TEMPDIR="/root/backup_radius" #Exp: ./bkp_temp

# Name of bkp file (before the date)
FILENAME="Radius_Backup_" #Exp: my_prj_daily_bkp_
NOW=$(date +%F)


#Create and clean temp directory
[ ! -d "$TEMPDIR" ] && mkdir -p "$TEMPDIR"
rm -f "$TEMPDIR"/*.*

# Create bkp
TFILENAME="$FILENAME""$NOW".sql
FILE=$TEMPDIR/$TFILENAME
$MYSQLDUMP -u $MUSER -h $MHOST -P $MPORT  -p$MPASS $DBNAME > $FILE
~

Continue reading MySQL backup script