World's simplest online MySQL password hash creator for web developers and programmers. Just paste your password in the form below, press the Generate Password button, and you'll get a MySQL hash. Press a button – get a MySQL password hash. No ads, nonsense, or garbage.
Announcement: We just launched SCIURLS – a neat science news aggregator. Check it out!
Using a MySQL Password Hash Generator in Cross-browser Testing
A MySQL hash generator can be useful if you're doing cross-browser testing. For example, if you have implemented the same password hashing algorithm that MySQL uses, then with this program, you can write test cases for verifying that your algorithm is correct and that passwords hash to the right values. You can write two sets of different tests – the first set tests if the given plain text password generates the correct password hash, and the second tests if the given password hash matches the given plain text password. Additionally, if you have forgotten your MySQL password, you can use this utility to generate a new one, or quickly test if the password that you have is the one used in your MySQL server.
Pro tip: You can use ?input=text query argument to pass text to tools.
This page describes a technical detail of our MySQL database system that most customers don’t need to worry about. It’s here to assist advanced customers who need to know technical details about how our MySQL servers are set up.
- About hashed passwords
- Hashed password incompatibilities
- Which hashing method does Tiger Technologies use?
About hashed passwords
When you create a new MySQL database or change a MySQL database password, the MySQL software doesn’t actually store the password you type. For security it instead stores a “hashed” version of that password.
Current versions of MySQL store the hashed password in a different format than older versions. For example, if you choose the password “mypass”, MySQL stores that as “*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4” [the MySQL documentation calls this a “long hash”] — but older versions stored the same password as “6f8c114b58f2ce9e” [a “short hash”].
Hashed password incompatibilities
The different password hashing formats can cause incompatibilities described on the MySQL password hashing page. In particular:
- Old MySQL client software designed for MySQL versions before 4.1.1 cannot connect to databases that use long hashes.
- New MySQL client software using the “mysqlnd” system cannot connect to databases that use short hashes.
That means it’s impossible for a database to support connections from both types of clients. Each database has to use one of these hashing methods and accept the fact that some client software will not be able to connect to that database.
Which hashing method does Tiger Technologies use?
We use “long hashes” when you create a new database or change the password of an existing database. This makes sure that all current client software, including copies of PHP that use “mysqlnd” [including PHP running on our servers], can connect without problems.
However, we used “short hashes” before November 28, 2011. We attempted to convert all old short hashes to long hashes by extracting a copy of the plaintext password from a database configuration file on your site [such as a WordPress “wp-config.php” file] if possible, but a very small number of older databases may still use short hashes.
This usually doesn’t cause any problems. However, if:
- you created a database on our servers before 2012; and
- you haven’t changed the password since then; and
- your site doesn’t store the plaintext version of the password so we can update it
... then you may see an error saying “mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication” [in this context, “insecure authentication” means a “short password hash”].
If this happens, you can convert your database to use a “long hash”. Simply “change” your MySQL password and use the same password that the database already has. The same password will be re-saved as a “long hash” instead of a “short hash”.
Copyright © 2000-2022 Tiger Technologies LLC
There is no magic: as long as only hashes are stored and not the original passwords, the only way to recover the lost password is to brute force it from the known hash.
Note on Security and mysql-unsha1 Attack
Interestingly, if a hacker has access to password hash and can sniff mysql traffic, he doesn’t need to recover a plain text password from it. It doesn’t matter how strong the password and how strong the hashing algorithm inside the auth plugin, due to MySQL protocol design, sniffed hash is enough to connect to a database with a patched version of MySQL client. It means, if a hacker has access to a database backup and traffic, he automatically receives all needed information [SHAs] for connecting to a running database. See for the attack details.
Since MySQL 8.0, caching_sha2_password
auth plugin is used by default, and this plugin brings a stronger sha256
function instead of sha1
used in mysql_native_password
plugin. For authentication with caching_sha2_password
plugin, it is also enough to have only a hash and be
able to sniff traffic, see for the implementation details.
Still, if you want to have a password that works with an unmodified client, however, you need to do some hacking, see instructions below.
Dump Hash
Let’s return to the password recovery. First of all, we need to dump hashes.
MySQL 5.7 uses the mysql_native_password
auth plugin by
default and we can dump sha1
hashes with the following command.
%mysql-Ns-uroot-e"SELECT SUBSTR[authentication_string,2] AS hash FROM mysql.user WHERE plugin = 'mysql_native_password' AND authentication_string NOT LIKE '%THISISNOTAVALIDPASSWORD%' AND authentication_string !='';">sha1_hashes |
MySQL 8.0 uses the caching_sha2_password
auth plugin by default and we can dump sha256
hashes as follows.
%mysql-Ns-uroot-e"SELECT CONCAT['\$mysql',LEFT[authentication_string,6],'*',INSERT[HEX[SUBSTR[authentication_string,8]],41,0,'*']] AS hash FROM mysql.user WHERE plugin = 'caching_sha2_password' AND authentication_string NOT LIKE '%INVALIDSALTANDPASSWORD%' AND authentication_string !='';"> sha256_hashes |
If you need to get the root password hash and don’t have a user who has read access to mysql.user
table, you should start mysqld with the --skip-grant-tables
option, see the official doc for details.
Run Linode GPU Instance
For password recovery, it is needed to run calculations on some powerful GPUs, and there are not so many cloud providers with GPU instances on the market. Linode is one of the remarkable cloud providers if you need a simple, reliable provider with a really helpful support department. Linode has a powerful CLI tool that simplifies “bash” automation a lot. Also, for more serious automation, the official Terraform provider exists.
128GB GPU Linode instance password recovery speed is 30000 MH/s [million hashes per second], which is very good. It needs only 2 hours to brute-force an 8-characters MySQL 5.7 passwords [upper case, lower case, numbers]. Instance price is only 6 USD/Hour.
For
example, the other biggest cloud provider [4 x NVIDIA Tesla V100 instance] with the same recovery speed cost two times more expensive – 12.24 USD/Hour.
Prepare Dictionary
The password brute-forcing is done based on dictionaries. We will use a small rockyou
dictionary as an example, to show how it goes.
%wget'//gitlab.com/kalilinux/packages/wordlists/-/raw/kali/master/rockyou.txt.gz' %gunzip rockyou.txt.gz |
You can find really good dictionaries on the weakpass dot com website.
But it is possible that even the largest dictionary will not be enough for the recovery. In such a case you should check if the validate_password plugin is enabled and prepare a dictionary based on it. Check it as follows:
%mysql-uroot-e"SHOW VARIABLES LIKE 'validate_password%';" +--------------------------------------+-------------------------------+ |Variable_name | Value | +--------------------------------------+-------------------------------+ |validate_password_check_user_name |ON | |validate_password_dictionary_file |/var/lib/mysql/prohibited.txt| |validate_password_length |8 | |validate_password_mixed_case_count |1 | |validate_password_number_count |1 | |validate_password_policy |STRONG | |validate_password_special_char_count|1 | +--------------------------------------+-------------------------------+ |
If the output of this command is empty, it means that the plugin is disabled. You can find some more details about the plugin in one of our previous blog posts about it, Improving MySQL Password Security with Validation Plugin.
The validate_password_policy
field is the most important one here. It can have the following values:
Policy | Tests Performed |
0 or LOW | Length |
1 or MEDIUM | Length; numeric, lowercase/uppercase, and special characters |
2 or STRONG | Length; numeric, lowercase/uppercase, and special characters; dictionary file |
If validate_password_policy=STRONG
and validate_password_dictionary_file
is set, we need to exclude passwords from validate_password_dictionary_file
:
cathuge-dictonary.txt\ |pw-inspector-m8-M32-l-u-n-p\ | sort-u\ |grep-F-v-x-fprohibited.txt\ >reduced-dictonary.txt |
In the example above:
-m 8
is the minimal length of the password, value from validate_password_length
variable;
-M 32
is the maximal length of the password, for replication passwords the maximal length is 32 characters, see MySQL release nodes;
-n
password should contain numbers, see validate_password_number_count
variable;
-l -u
password should contain
lowercase/uppercase characters, see validate_password_mixed_case_count
variable;
-p
password should contain special characters, see validate_password_special_char_count
variable;
prohibited.txt
is a file from validate_password_dictionary_file
variable;
huge-dictonary.txt
is the initial dictionary;
reduced-dictonary.txt
is the new dictionary without words from prohibited.txt
.
If the dictionary attack failed, you have to create your own dictionary for the brute force. In this case, we recommend using one of the following tools: crunch, maskprocessor or via Hashcat options.
Compile Hashcat
In the case of MySQL 8.0, the latest version of hashcat from the master branch should be compiled due to the fact that code from //github.com/hashcat/hashcat/issues/2305 wasn’t released in any version right now.
%sudoapt-yinstallmakegcc %gitclonehttps://github.com/hashcat/hashcat.git % cdhashcat %make %sudo makeinstall |
Enable OpenCL for NVIDIA
Update to the latest software, disable the nouveau driver and reboot:
%sudoaptupdate&&sudoaptfull-upgrade-y %echo-e"blacklist nouveau\noptions nouveau modeset=0\nalias nouveau off"| sudotee/etc/modprobe.d/blacklist-nouveau.conf %sudoupdate-initramfs-u %reboot |
Install the proprietary driver and reboot
%sudoaptinstall-ynvidia-cuda-toolkitocl-icd-libopencl1 %sudoaptinstall-y nvidia-driver-440nvidia-utils-440 %sudoaptremovemesa-opencl-icd %reboot |
Check the driver
%sudonvidia-smi %hashcat-I |
Run Password Recovery
For mysql_native_password
[MySQL 5.7] use the 300 code:
%hashcat-m300-a0-D2-O-w3./sha1_hashes./rockyou.txt |
For caching_sha2_password
[MySQL 8.0] use the 7401 code:
%hashcat-m7401-a0-D2-O-w3./sha256_hashes./rockyou.txt |
If your password was recovered correctly, you can run the same command with the --show
option to display the password.
%hashcat-m300-a0-D2./sha1_hashes./rockyou.txt--show 0913bf2e2ce20ce21bfb1961af124d4920458e5f:new_password |
Here new_password
is the correct answer.
Conclusion
8-chars password with lower and upper case letters and digits for MySQL 5.7 can be recovered only in 2 hours on the Linode GPU instance. The same password for MySQL 8.0 can be recovered in 2.8 years. But in general, hackers don’t need to recover plain text passwords at all [see “mysql-unsha1 attack” section above]. To reduce risks, it is needed to protect the content of mysql.user
table, there are a few things
that can be done:
- don’t store hashes in MySQL itself, for example, use LDAP plugin for Percona Server
- or use encryption at rest with HashiCorp Vault plugin
- or at least use encryption at rest for backups.
Our solution brief “Get Up and Running with Percona Server for MySQL” outlines setting up a MySQL® database on-premises using Percona Server for MySQL. It includes failover and basic business continuity components.
Download PDF