MySQL Tips
- Show all users
- Drop all databases
- Delete all users in MySQL with specific name
- Check and duplicate user grants
- 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
- https://rootedlabs.wordpress.com/2009/10/03/drop-all-databases-in-mysql/
- http://stackoverflow.com/questions/11925890/delete-all-users-in-mysql-with-specific-name
- https://stackoverflow.com/questions/33510184/change-mysql-root-password-on-centos7