Question: Previously (MySQL 5.7) we was using this command to add a new email address into an existing table:

```
INSERT INTO `servermail`.`virtual_users`
(`id`, `domain_id`, `password` , `email`)
VALUES
('1', '1', ENCRYPT('password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'user@example.com'),

```

then dovecot was able to authenticate users. ([more information](https://wiki2.dovecot.org/Authentication/PasswordSchemes) about dovecot password scheme)  

Now Encrypt has been deprecated in the recent versions of MySQL. ([link](https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_encrypt))

I want to rewrite that command using SHA2 but I wasn't succeed.

**Edit:**  

This could help someone to use [How To Configure a Mail Server Using Postfix, Dovecot, MySQL, and SpamAssassin](https://www.digitalocean.com/community/tutorials/how-to-configure-a-mail-server-using-postfix-dovecot-mysql-and-spamassassin) to configure a Mail Server with version 8.0 of Mysql.

Answer: 
Finally I changed the default method dovecote uses for its user authentication from `SHA512-CRYPT` to `SHA512`. I think it's not less secure than that but is supported by MySQL 8.  

After that I used this command to add a new user to the table.

```
INSERT INTO `servermail`.`virtual_users`
(`id`, `domain_id`, `password` , `email`)
VALUES
('1', '1', TO_BASE64(UNHEX(SHA2('password', 512))), 'user@example.com'); 

```
SHA512-CRYPT seems to be impossible to implement within MySQL and as far Dovecot goes, I'll admit I can't understand why they suggest using encryption to begin with. Using a strong ***hash*** as the OP suggested works at least as well and is more secure.

Since I prefer my passwords with extra salt, here's how I'm using Dovecot's [SSHA512](https://doc.dovecot.org/configuration_manual/authentication/password_schemes/#sha-based-schemes-also-see-below-for-libc-s-sha-support) which is the "*Salted SHA512 sum of the password stored in base64*":

```
INSERT INTO `virtual_users`
(`id`, `domain_id`, `password` , `email`)
VALUES
(NULL, '1',  (SELECT REPLACE(TO_BASE64(CONCAT(UNHEX(SHA2(CONCAT('YourPasswordHere', v.salt), 512)), v.salt)), '\n', '') AS salted FROM (SELECT SHA2(RAND(), 512) AS salt) v), 'user@example.com');

```

Or to update a password:

```
UPDATE virtual_users
SET `password` = (
    SELECT REPLACE(TO_BASE64(CONCAT(UNHEX(SHA2(CONCAT('YourPasswordHere', v.salt), 512)), v.salt)), '\n', '') AS salted
    FROM (
        SELECT SHA2(RAND(), 512) AS salt
    ) v
)
WHERE email = 'user@example.com';

```

These queries:

1. Generate a random (rather lengthy) salt
2. Append the salt to the password
3. Get the SHA512 Hash of #2
4. Convert the hash to binary
5. Append the salt to #4
6. Convert the whole thing to Base64
7. Remove unwanted newlines added by MySQL's `TO_BASE64()` function

The result is a 256 byte long string so you may need to update your `password` field to VARCHAR(256).

There are a number of tutorials that suggest using `doveadm` to manually generate the encrypted password and while this works well, I find it a bit more cumbersome. For those interested you can call it like so:

```
doveadm pw -s SHA512-CRYPT -p "YourPasswordHere"

```

Even more useful is the ability to validate your generated passwords with the same utility:

```
doveadm auth test user@example.com YourPasswordHere

```