MySQL Tips

1. Show all users
2. Drop all databases
3. Delete all users in MySQL with specific name
4. Check and duplicate user grants
5. Reset root password (mysql 5.7)

Tip #1: Show all users

MariaDB [(none)]> select User,Host from mysql.user order by User, Host;

Output

+-------------+---------------------+
| User        | Host                |
+-------------+---------------------+
|             | hdp2-3-4-c7.sandbox |
|             | localhost           |
| ambari-user | %                   |
| ambari-user | hdp2-3-4-7.sandbox  |
| ambari-user | localhost           |
| root        | 127.0.0.1           |
| root        | ::1                 |
| root        | hdp2-3-4-c7.sandbox |
| root        | localhost           |
+-------------+---------------------+

Tip #2: Drop all databases

mysql -uroot -p  -e "show databases" | grep -v Database | grep -v mysql| grep -v information_schema| grep -v test | grep -v OLD |gawk '{print "drop database " $1 ";select sleep(0.1);"}' | mysql -uroot -ppassword

Tip #3: Delete all users in MySQL with specific name

SET @users = NULL;
SELECT GROUP_CONCAT('\'',user, '\'@\'', host, '\'') INTO @users FROM mysql.user WHERE user = 'rms';

SET @users = CONCAT('DROP USER ', @users);
PREPARE stmt1 FROM @users;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

Tip #4: Check and duplicate user grants

$ mysql -uroot  -sNe"`mysql -uroot -se"SELECT CONCAT('SHOW GRANTS FOR \'',user,'\'@\'',host,'\';') FROM mysql.user;"`"

#with password enabled
mysql -uroot -p -sNe"`mysql -uroot -p -se"SELECT CONCAT('SHOW GRANTS FOR \'',user,'\'@\'',host,'\';') FROM mysql.user;"`"

Tip #5: Reset root password (mysql 5.7)

Initial root password on install can be found by running

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

To reset the root password start mySQL with –skip-grant-tables options and then update the root user password

$ systemctl stop mysqld
$ systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
$ systemctl start mysqld
$ mysql -u root

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
mysql> quit

$ systemctl stop mysqld
$ systemctl unset-environment MYSQLD_OPTS
$ systemctl start mysqld

# Login using your new password:
$ mysql -u root -p

References

1. https://rootedlabs.wordpress.com/2009/10/03/drop-all-databases-in-mysql/
2. http://stackoverflow.com/questions/11925890/delete-all-users-in-mysql-with-specific-name
3. https://stackoverflow.com/questions/33510184/change-mysql-root-password-on-centos7