Support MySQL 8.0 connectors

[tennix]

Feature Request

Is your feature request related to a problem? Please describe:

When a TiDB cluster has set password, users cannot connect to it with MySQL 8.0 client as usual: mysql -h ${tidb_endpoint} -P 4000 -u root -p. This is because MySQL 8.0 client has changes the default encryption method for password. The following tow issues have details about this.

#6942
#7606

https://docs.pingcap.com/zh/tidb/v4.0/security-compatibility-with-mysql

Describe the feature you'd like:

I would like TiDB to support MySQL 8.0 client.

Though users can workaround this as documented here pingcap/docs-cn#3577, some programs such as Sysbench is dynamically linked to the system MySQL client library and does not have options to configure these parameters (--default-auth=mysql_native_password and --default-character-set=utf8).

So it would be too difficult for users to benchmark TiDB using Sysbench if they have MySQL 8.0 client installed.

Describe alternatives you've considered:

Teachability, Documentation, Adoption, Migration Strategy:

[wwar]

There might be an easy way to implement this. The mysql protocol has a way for the server to tell clients to reauthenticate with a different authentication plugin (so mysql_native_password instead of caching_sha256_password).

[zz-jason]

I can connect to TiDB without --default-character-set=utf8:

➜ mysql -h 127.0.0.1 -P 4000 -u root -D test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.25-TiDB-v4.0.0-beta.2-801-g94fc46424 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ^DBye

The MySQL client version:

➜ mysql --version
mysql  Ver 8.0.21 for osx10.15 on x86_64 (Homebrew)

The tidb-server version:

➜ ./bin/tidb-server -V
Release Version: v4.0.0-beta.2-801-g94fc46424
Edition: Community
Git Commit Hash: 94fc46424f952dd7337bbc6cf7120312637caa28
Git Branch: master
UTC Build Time: 2020-07-20 07:58:07
GoVersion: go1.13.12
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[zz-jason]

@tennix What's your TiDB and MySQL client version?

[ghost]

I believe the character set issue was fixed between TiDB 2.1 and TiDB 3.0-ish. I have updated the title to be specifically about the sha256 auth plugin.

[ghost]

This is a duplicate of #9411

[ghost]

I took a look at the "easy way to implement this", and it does indeed work. So I have created a PR in #19603

I will leave #9411 open as a request to actually implement caching_sha2_password, which has its own security merits.