caching_sha2_password support

[rbro]

Just asking as I'm not sure how much is involved to do this:

I see it's noted that php-mysql-replication only supports mysql_native_password. Now that mysql_native_password is deprecated starting with MySQL 8.0.34, would it be possible to support caching_sha2_password?

Thanks for your help.

[krowinski]

Right now only way to bypass this is to use ALTER USER 'username' IDENTIFIED WITH mysql_native_password BY 'password';. Right now I can't find any code that explain how to connect using caching_sha2_password if you find any I will try to implement.

[rbro]

Thanks, could this maybe help:

https://github.com/PyMySQL/PyMySQL/blob/main/pymysql/connections.py

It looks like there are 2 functions based on the server response:

• _auth.scramble_caching_sha2
• _auth.caching_sha2_password_auth

Also, I was looking how python-mysql-replication does it, but it looks like it is using PyMySQL directly and the _write_bytes function, so it doesn't need a separate socket to connect as a replica. I don't believe that PHP allows you to write to the socket directly with mysqli from what I was able to research.

[krowinski]

Right now I can execute query using sockets but parsing reponses to get data is a mess so I use mysql client for that. In future if I have more time I will program that.

In tag 8.0 I added support for caching_sha2_password_auth

[rbro]

Thanks for the updates. I'm getting an error now when using mysql_native_password. It seems to be trying caching_sha2_password even though my user is set for mysql_native_password:

[2024-01-30T16:00:26.320632+00:00] replicator.INFO: Connected to 127.0.0.1:3306 [] []
[2024-01-30T16:00:26.376606+00:00] replicator.INFO: Server version name: MySQL, revision: 8 [] []
[2024-01-30T16:00:26.376669+00:00] replicator.INFO: Trying to authenticate user: test using caching_sha2_password plugin [] []
[2024-01-30T16:00:26.377068+00:00] replicator.INFO: User authenticated [] []

Fatal error: Uncaught MySQLReplication\BinLog\BinLogException: Got packets out of order in /tmp/php-mysql-replication/src/MySQLReplication/BinLog/BinLogSocketConnect.php:109

[krowinski]

hm I forgot that you can have auth with caching_sha2_password and password in old native mode I added auto switch mode in #105 check if this helps

[rbro]

@krowinski - thanks for the quick updates. All is working great.

My other reason for bringing up that PHP doesn't allow you to write to the socket directly is that I need to connect to MySQL over TLS. Is there a way to do that with php-mysql-replication? Right now, I'm looking at using proxysql in-between.

Do you think PHP internals would be open to adding functions to mysqli to have direct access to the socket to read and write, so then php-mysql-replication wouldn't need to do a custom socket? I believe that is how python-mysql-replication works now with _write_bytes() and _read_packet(). Or is that something you have already tried?