I can't create a database with mysql

Hello,

The error you’re encountering, “ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)”, is a common MySQL authentication issue. It seems like there’s a problem with the root password or the way MySQL is interpreting your login credentials. Here’s a step-by-step guide to resolve this and ensure a clean start with MySQL on Debian:

Please be aware that removing the MySQL directory, specifically /var/lib/mysql, will permanently delete all existing MySQL databases and data stored on your server.

1. Uninstall MySQL Completely:

   • You’ve already done a basic uninstall, but let’s make sure everything is completely removed.

     sudo apt-get remove --purge mysql-server mysql-client mysql-common
     sudo apt-get autoremove
     sudo apt-get autoclean
     sudo rm -rf /etc/mysql /var/lib/mysql
     sudo apt update
     

   • This will remove all MySQL packages and configurations.

2. Reinstall MySQL:

   • Install MySQL again with:

     sudo apt install mysql-server
     

   • During the installation, you should be prompted to set a new root password.

3. Secure MySQL Installation:

   • Run the mysql_secure_installation script:

     sudo mysql_secure_installation
     

   • This script will guide you through several security settings, including setting the root password, removing anonymous users, disallowing root login remotely, and more.

4. Troubleshooting the Root Password:

   • If you still can’t log in, you might need to reset the root password manually. Stop the MySQL service:

     sudo systemctl stop mysql
     

   • Start MySQL with skip-grant-tables option:

     sudo mysqld_safe --skip-grant-tables &
     

   • Log in to MySQL without a password:

     mysql -u root
     

   • Once logged in, reset the root password:

     FLUSH PRIVILEGES;
     ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
     EXIT;
     

   • Replace new_password with your new root password. Then restart MySQL:

     sudo systemctl restart mysql
     

Besides that, check for any error logs in /var/log/mysql/ for more specific error messages and ensure that there are no other conflicting services or incorrect configurations.

Best,

Bobby