MySQL 8.0 & PHP

See some update at the end: 23 Dec 2019

MySQL and PHP is a love story that started long time ago. However the love story with MySQL 8.0 was a bit slower to start… but don’t worry it rules now !

The support of MySQL 8.0’s new default authentication method in PHP took some time and was added in PHP 7.2.8 but removed in PHP 7.2.11.

Now it’s fully supported in PHP 7.4 !

If you have installed PHP 7.4, you can see that the new plugin auth_plugin_caching_sha2_passwordis now available:

# php -i | grep "Loaded plugins\|PHP Version " | tail -n2
PHP Warning:  Module 'mysql_xdevapi' already loaded in Unknown on line 0
PHP Version => 7.4.0
Loaded plugins => mysqlnd,debug_trace,auth_plugin_mysql_native_password,
                  auth_plugin_mysql_clear_password,
                  auth_plugin_caching_sha2_password,
                  auth_plugin_sha256_password

So no need to create a user with mysql_native_passwordas authentication method in MySQL 8.0 as explained in the following posts:

• https://lefred.be/content/mysql-8-0-17-and-drupal-8-7/
• https://lefred.be/content/migrating-to-mysql-8-0-for-wordpress-episode-1/
• https://lefred.be/content/drupal-and-mysql-8-0-11-are-we-there-yet/
• https://lefred.be/content/joomla-and-mysql-8-0-12/
• https://lefred.be/content/mysql-8-0-and-magento/

In summary, if you want to use a more secure method to connect to your MySQL 8.0 form your PHP application, make sure you upgrade to PHP 7.4

Update:

The drawback of this new mysqli.so is that if you don’t modify the php.ini and by adding a value to mysqli.default_socket, when you try to connect to MySQL on localhost without specifying the socket path, the connection will fail with the following message:

PHP Warning:  mysqli::__construct(): (HY000/2002): 
No such file or directory in <name of your file>.php on line 45
An error occurred when trying to establish a connection to the database: Error #2002

You have then 2 solutions:

1. add in php.ini a default value for mysqli.default_socket
2. or specify the socket path when you initiate the connection to MySQL using mysqli in your code

This is also the default behavior on Ubuntu when using PHP 7.4 from ppa:ondrej/php repository.