{"id":2786,"date":"2021-02-06T01:00:00","date_gmt":"2021-02-06T01:00:00","guid":{"rendered":"https:\/\/chewett.co.uk\/blog\/?p=2786"},"modified":"2021-01-17T16:44:00","modified_gmt":"2021-01-17T16:44:00","slug":"fixing-raspberry-pi-mysql-access-denied-for-user-root-with-non-root-user","status":"publish","type":"post","link":"https:\/\/chewett.co.uk\/blog\/2786\/fixing-raspberry-pi-mysql-access-denied-for-user-root-with-non-root-user\/","title":{"rendered":"Fixing Raspberry Pi MySQL Access Denied for user root with non root user"},"content":{"rendered":"\n
\"\"<\/figure>\n\n\n\n

This blog post talks about how you can log in as root to your MySQL database with a non-root user on your Raspberry Pi.<\/p>\n\n\n\n\n\n\n\n

The “Access denied for user ‘root’@’localhost'” error<\/h2>\n\n\n\n

After setting up your database you might have changed the root password using mysql_secure_installation<\/code> however it will still give an error when trying to log in from a non-root user. Here I am trying to log in as the Raspberry Pi pi<\/code> user.<\/p>\n\n\n\n

pi@bunker-master3:~ $ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'<\/pre>\n\n\n\n
A common error relating to this will be as above, where MySQL just denies access to the user. This will however work if you try logging when using the root account user.<\/p>\n\n\n\n
It is recommended not to use the root user normally so you can change MySQL so you can log in as root, as a non root user.<\/p>\n\n\n\n
Fixing “Access denied for user ‘root’@’localhost” error<\/h2>\n\n\n\n
Typically this problem occurs just after installing your webserver. A number of Ubuntu based distributions now install the root user with no password but set the authentication method to auth_socket<\/code>.<\/p>\n\n\n\n
This means that the root user can log in using the socket but no other users will be able to.<\/p>\n\n\n\n
To change this we can modify the root user to have a new password and ensure it is authenticated using the mysql_native_password<\/code> method. This can be done with the following MySQL command.<\/p>\n\n\n
\nUPDATE mysql.user SET\n   authentication_string=password('your_password_here'),\n   plugin='mysql_native_password'\n   WHERE user='root';\nFLUSH PRIVILEGES;\n<\/pre><\/div>\n\n\n
You will want to ensure you use a different password in the above SQL.<\/p>\n\n\n\n
Once ran your root MySQL user will be able to be logged in by any non-root user with the given password.<\/p>\n","protected":false},"excerpt":{"rendered":"
This blog post talks about how you can log in as root to your MySQL database with a non-root user on your Raspberry Pi.<\/p>\n","protected":false},"author":1,"featured_media":2793,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"Today I am talking about how you can fix your #RaspberryPi #MySQL Access Denied for user root with non root user","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[5],"tags":[398,286,317,37,393],"class_list":["post-2786","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-informational","tag-mariadb","tag-mysql-database","tag-mysql-server","tag-raspberry-pi","tag-raspberry-pi-os"],"wppr_data":{"cwp_meta_box_check":"No"},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/01\/fixing_raspi_mysql_root_login_posticon_OUTPUT.png?fit=1200%2C628&ssl=1","jetpack_shortlink":"https:\/\/wp.me\/p2toWX-IW","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":2783,"url":"https:\/\/chewett.co.uk\/blog\/2783\/how-to-install-a-mysql-server-on-a-raspberry-pi\/","url_meta":{"origin":2786,"position":0},"title":"How to install a MySQL Server on a Raspberry Pi","author":"Chewett","date":"January 30, 2021","format":false,"excerpt":"This blog post explains the steps you need to perform to install a MySQL server on a Raspberry Pi. Ensuring the Raspberry Pi is up to date Before installing any programs it is recommended to make sure your Raspberry Pi is up to date. This reduces the change of a\u2026","rel":"","context":"In "Informational"","block_context":{"text":"Informational","link":"https:\/\/chewett.co.uk\/blog\/category\/informational\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/01\/install_mysql_raspi_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/01\/install_mysql_raspi_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/01\/install_mysql_raspi_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/01\/install_mysql_raspi_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/01\/install_mysql_raspi_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":673,"url":"https:\/\/chewett.co.uk\/blog\/673\/grant-sudo-access-user-raspberry-pi-raspbian\/","url_meta":{"origin":2786,"position":1},"title":"Grant sudo access for a user with Raspberry Pi Raspbian","author":"Chewett","date":"September 30, 2017","format":false,"excerpt":"This blog post describes how you can enable sudo for any user on the Raspberry Pi OS, Raspbian. Why sudo is important Sudo is a command that lets you run a command as the \"Super User\". The super user's username is typically root on Linux machines. Instead of logging in\u2026","rel":"","context":"In "Raspberry Pi Cluster"","block_context":{"text":"Raspberry Pi Cluster","link":"https:\/\/chewett.co.uk\/blog\/category\/raspberry-pi-cluster\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2017\/09\/add_sudo_to_a_user_raspbian.jpg?fit=800%2C800&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2017\/09\/add_sudo_to_a_user_raspbian.jpg?fit=800%2C800&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2017\/09\/add_sudo_to_a_user_raspbian.jpg?fit=800%2C800&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2017\/09\/add_sudo_to_a_user_raspbian.jpg?fit=800%2C800&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":2734,"url":"https:\/\/chewett.co.uk\/blog\/2734\/rsync-entire-raspberry-pi-to-windows\/","url_meta":{"origin":2786,"position":2},"title":"Rsync Entire Raspberry Pi to Windows","author":"Chewett","date":"November 21, 2020","format":false,"excerpt":"This post includes a short snippet to copy your entire Raspberry Pi filesystem to Windows using Rsync. Copying and Archiving particular directories If you want to rsync specific directories rather than the entire Raspberry Pi I suggest you read my previous blog post. It details how to backup a Raspberry\u2026","rel":"","context":"In "Raspberry Pi Cluster"","block_context":{"text":"Raspberry Pi Cluster","link":"https:\/\/chewett.co.uk\/blog\/category\/raspberry-pi-cluster\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2020\/11\/raspberrypi_rsync_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2020\/11\/raspberrypi_rsync_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2020\/11\/raspberrypi_rsync_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2020\/11\/raspberrypi_rsync_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2020\/11\/raspberrypi_rsync_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":407,"url":"https:\/\/chewett.co.uk\/blog\/407\/ssh-connect-host-raspberrypi-port-22-connection-refused-error-fixed\/","url_meta":{"origin":2786,"position":3},"title":"ssh: connect to host raspberrypi port 22: Connection refused Error Fixed","author":"Chewett","date":"June 24, 2017","format":false,"excerpt":"I was setting up a new install of Raspbian Pixel and found I couldn't login to the Raspberry Pi. It was giving me an error: chewett@bunker-master:~$ ssh pi@raspberrypi ssh: connect to host raspberrypi port 22: Connection refused It turns out that recently the Raspberry Pi Foundation have changed the default\u2026","rel":"","context":"In "Raspberry Pi Cluster"","block_context":{"text":"Raspberry Pi Cluster","link":"https:\/\/chewett.co.uk\/blog\/category\/raspberry-pi-cluster\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2002,"url":"https:\/\/chewett.co.uk\/blog\/2002\/raspberry-pi-cluster-node-12-automatic-start-with-rc-local\/","url_meta":{"origin":2786,"position":4},"title":"Raspberry Pi Cluster Node \u2013 12 Automatic Start with rc.local","author":"Chewett","date":"March 6, 2019","format":false,"excerpt":"This post builds on\u00a0my previous posts in the Raspberry Pi Cluster series by creating a script which automatically starts the slave and master with rc.local. What is the rc.local file? The rc.local file is used on many Linux operating systems to schedule operations to run after services are started on\u2026","rel":"","context":"In "Raspberry Pi Cluster"","block_context":{"text":"Raspberry Pi Cluster","link":"https:\/\/chewett.co.uk\/blog\/category\/raspberry-pi-cluster\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2019\/02\/rpi_cluster_12_automatic_start.jpg?fit=800%2C800&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2019\/02\/rpi_cluster_12_automatic_start.jpg?fit=800%2C800&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2019\/02\/rpi_cluster_12_automatic_start.jpg?fit=800%2C800&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2019\/02\/rpi_cluster_12_automatic_start.jpg?fit=800%2C800&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":2952,"url":"https:\/\/chewett.co.uk\/blog\/2952\/running-a-python-script-on-boot-with-systemd\/","url_meta":{"origin":2786,"position":5},"title":"Running a Python Script on Boot with Systemd","author":"Chewett","date":"August 7, 2021","format":false,"excerpt":"This short blog post details how you can run a python script on boot using systemd. Using Systemd to manage the python script Systemd is a daemon that is used to control various aspects of services and initialization scripts that run on and after boot. It is used by a\u2026","rel":"","context":"In "Informational"","block_context":{"text":"Informational","link":"https:\/\/chewett.co.uk\/blog\/category\/informational\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/07\/systemd_bootup_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/07\/systemd_bootup_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/07\/systemd_bootup_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/07\/systemd_bootup_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/07\/systemd_bootup_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=1050%2C600 3x"},"classes":[]}],"_links":{"self":[{"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/posts\/2786","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/comments?post=2786"}],"version-history":[{"count":5,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/posts\/2786\/revisions"}],"predecessor-version":[{"id":2800,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/posts\/2786\/revisions\/2800"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/media\/2793"}],"wp:attachment":[{"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/media?parent=2786"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/categories?post=2786"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/tags?post=2786"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}