# Is it possible to run pt-table-checksum with the authentication plugin 'caching\_sha2\_password'? **URL:** \ **Category:** MySQL & MariaDB\ **Created:** [May 23, 2024, 2:59pm UTC](https://forums.percona.com/t/is-it-possible-to-run-pt-table-checksum-with-the-authentication-plugin-caching-sha2-password/30523 "2024-05-23T14:59:45Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![basha04](https://avatars.discourse-cdn.com/v4/letter/b/8e8cbc/32.png) [@basha04](https://forums.percona.com/u/basha04)\ **Post date:** [May 23, 2024, 2:59pm UTC](https://forums.percona.com/t/is-it-possible-to-run-pt-table-checksum-with-the-authentication-plugin-caching-sha2-password/30523/1 "2024-05-23T14:59:46Z")
Hello , I have previously asked this question on [mysql - Is it possible to use pt-table-checksum with the authentication plugin 'caching\_sha2\_password'? - Database Administrators Stack Exchange](https://dba.stackexchange.com/questions/339365/is-it-possible-to-use-pt-table-checksum-with-the-authentication-plugin-caching?noredirect=1#comment660135_339365) . Is it possible to run pt-table-checksum with the authentication plugin ‘caching\_sha2\_password’ ? Is there a step by step guide on how to use pt-table-checksum with the authentication plugin ‘caching\_sha2\_password’ ? I read a lot of online guides/tutorials such as the following , but nothing helps. > [@How to use pt-table-checksum](https://forums.percona.com/t/how-to-use-pt-table-checksum/9777): > > Hi All, I am new to percona and want to use pt-table-checksum to check differences between my master mysql database and backup mysql database. I want the pt-table-checksum to show errors if there are difference between these two and not to make any other changes. Also, i want to be as minimalistic as possible while my objective is to solely report the difference between the master and the backup. Also, the backup is in a docker container so doesn’t want to hardcode or make things hairy. can s… Running on MySQL 8.0.36 ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [May 23, 2024, 4:09pm UTC](https://forums.percona.com/t/is-it-possible-to-run-pt-table-checksum-with-the-authentication-plugin-caching-sha2-password/30523/2 "2024-05-23T16:09:18Z")
pt-table-checksum is a perl script that uses DBD::Mysql. Make sure your perl install has the latest version of this library. Check the percona repository for the latest supported version. Yes, you can use sha2\_password with all percona toolkits, if your perl is updated. ---
**Author:** ![basha04](https://avatars.discourse-cdn.com/v4/letter/b/8e8cbc/32.png) [@basha04](https://forums.percona.com/u/basha04)\ **Post date:** [May 24, 2024, 3:16pm UTC](https://forums.percona.com/t/is-it-possible-to-run-pt-table-checksum-with-the-authentication-plugin-caching-sha2-password/30523/3 "2024-05-24T15:16:32Z")
Hello , Perl DBD::Mysql ```auto gesti@gestitest:~$ perl -MDBD::mysql -e 'print $DBD::mysql::VERSION' 5.005 ``` The command I am using gesti@gestitest:~$ pt-table-checksum --replicate=percona.checksums --ignore-databases mysql h=192.168.100.136,P=3306,F=/etc/mysql/mysql.conf.d/percona.cnf --recursion-method dsn=“D=percona,t=dsns,F=/etc/mysql/mysql.conf.d/percona.cnf” --no-check-binlog-format --no-check-replication-filters /etc/mysql/mysql.conf.d/percona.cnf contains ```auto [client] ssl_mode = required ssl_cert = /var/lib/mysql/client-cert.pem ssl_key = /var/lib/mysql/client-key.pem ssl_ca = /var/lib/mysql/ca.pem user = percona password = "pERc0nSAj1!#1e" ``` I can login with the above user ```auto gesti@gestitest:~$ sudo mysql -upercona -p'pERc0nSAj1!#1e' --ssl-mode=VERIFY_CA mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1017 Server version: 8.0.36-0ubuntu0.22.04.1 (Ubuntu) Copyright (c) 2000, 2024, Oracle and/or its affiliates. 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. You are enforcing ssl connection via unix socket. Please consider switching ssl off as it does not make connection via unix socket any more secure. ``` The user has all privileges ```auto mysql> show grants for percona@`%`; | Grants for percona@% | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `percona`@`%` | | GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `percona`@`%` | ``` Note when I remove the quotes from the password on /etc/mysql/mysql.conf.d/percona.cnf the error is ```auto gesti@gestitest:~$ pt-table-checksum --replicate=percona.checksums --ignore-databases mysql h=192.168.100.136,P=3306,F=/etc/mysql/mysql.conf.d/percona.cnf --recursion-method dsn="D=percona,t=dsns,F=/etc/mysql/mysql.conf.d/percona.cnf" --no-check-binlog-format --no-check-replication-filters 05-24T17:14:55 DBI connect(';mysql_read_default_file=/etc/mysql/mysql.conf.d/percona.cnf;host=192.168.100.136;port=3306;mysql_read_default_group=client','',...) failed: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. at /usr/bin/pt-table-checksum line 1636. ``` With single and double quotes on the password ```auto gesti@gestitest:~$ pt-table-checksum --replicate=percona.checksums --ignore-databases mysql h=192.168.100.136,P=3306,F=/etc/mysql/mysql.conf.d/percona.cnf --recursion-method dsn="D=percona,t=dsns,F=/etc/mysql/mysql.conf.d/percona.cnf" --no-check-binlog-format --no-check-replication-filters 05-24T17:15:49 DBI connect(';mysql_read_default_file=/etc/mysql/mysql.conf.d/percona.cnf;host=192.168.100.136;port=3306;mysql_read_default_group=client','',...) failed: Access denied for user 'percona'@'192.168.100.136' (using password: YES) at /usr/bin/pt-table-checksum line 1636. ``` This is a test server which has no replica, still I guess it would be connected. Am I missing something ? ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [May 24, 2024, 7:02pm UTC](https://forums.percona.com/t/is-it-possible-to-run-pt-table-checksum-with-the-authentication-plugin-caching-sha2-password/30523/4 "2024-05-24T19:02:40Z")
> [@basha04](#): > > `Access denied for user 'percona'@'192.168.100.136' (using password: YES) ` I would try a password without special characters, just as a test. ---
**Author:** ![basha04](https://avatars.discourse-cdn.com/v4/letter/b/8e8cbc/32.png) [@basha04](https://forums.percona.com/u/basha04)\ **Post date:** [May 25, 2024, 8:02am UTC](https://forums.percona.com/t/is-it-possible-to-run-pt-table-checksum-with-the-authentication-plugin-caching-sha2-password/30523/5 "2024-05-25T08:02:38Z")
After further debugging the same error persist. I created the following user and gave all privileges. ```auto mysql> use mysql; Database changed mysql> CREATE USER perconaTest@`%` IDENTIFIED WITH caching_sha2_password BY 'perc0Na0123aERt' REQUIRE SSL; Query OK, 0 rows affected (0.02 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO perconaTest@`%`; Query OK, 0 rows affected (0.02 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) ``` I noted that I have the following error when trying to connect ```auto gesti@gestitest:~$ mysql --defaults-extra-file=/etc/mysql/mysql.conf.d/perconaTest.cnf --ssl-mode=VERIFY_CA ERROR 2026 (HY000): SSL connection error: SSL_CTX_set_default_verify_paths failed ``` I regenerated the certificates and made changes on client and server side ```auto [client] user = perconaTest password = "perc0Na0123aERt" ssl_mode = required ssl_cert = /etc/mysql/newcerts/client-cert.pem ssl_key = /etc/mysql/newcerts/client-key.pem ssl_ca = /etc/mysql/newcerts/ca.pem [mysqld] #ssl ssl_ca = /etc/mysql/newcerts/ca.pem ssl_cert = /etc/mysql/newcerts/server-cert.pem ssl_key = /etc/mysql/newcerts/server-key.pem ``` Now the login is successfully ```auto root@gestitest:/etc/mysql/newcerts# mysql --defaults-extra-file=/etc/mysql/mysql.conf.d/perconaTest.cnf --ssl-mode=VERIFY_CA Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 Server version: 8.0.36-0ubuntu0.22.04.1 (Ubuntu) Copyright (c) 2000, 2024, Oracle and/or its affiliates. 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. You are enforcing ssl connection via unix socket. Please consider switching ssl off as it does not make connection via unix socket any more secure. mysql> mysql> exit Bye ``` After trying pt-table-checksums the following error is thrown ```auto gesti@gestitest:~$ pt-table-checksum --replicate=percona.checksums --ignore-databases mysql h=192.168.100.136,P=3306,F=/etc/mysql/mysql.conf.d/perconaTest.cnf --recursion-method dsn="D=percona,t=dsns,F=/etc/mysql/mysql.conf.d/perconaTest.cnf" --no-check-binlog-format --no-check-replication-filters 05-25T09:56:40 DBI connect(';mysql_read_default_file=/etc/mysql/mysql.conf.d/perconaTest.cnf;host=192.168.100.136;port=3306;mysql_read_default_group=client','',...) failed: Access denied for user 'perconaTest'@'192.168.100.136' (using password: YES) at /usr/bin/pt-table-checksum line 1636. ``` Now I am clueless as I can login fine if I use the credentials on the perconaTest.cnf . Is there anything else I have to lookup ?