I'm having a tough time getting up and running with MySQL on Ubuntu, and all similar questions have solutions which seem to assume that I've already solved the problem.
I installed MySQL with: sudo apt-get install mysql-server, then tried to configure with sudo mysql_secure_installation. However, output to was as follows:
$ sudo mysql_secure_installation
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
The user provided does not have enough permissions to continue.
mysql_secure_installation is exiting.
It seems this is because MySQL on Ubuntu is configured to use the auth_socket plugin. For example, see the discussion here. The solutions proposed are to either:
- Set the root user to use the mysql_native_password plugin
- Create a new db_user with you system_user (recommended)
I am unable to do either. When I attempt them, I cannot enter the first command (nor any subsequent to it). Output is:
$ sudo mysql -u root # I had to use "sudo" since it was new installation
mysql> USE mysql;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'mysql'
When I attempt to trace down a solution to this, it seems I need to add privileges. When I attempt to do so with the following command, again I receive an errror:
$ sudo mysql -u root
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
I get similar error messages about privileges whenever I attempt to execute any command.
What am I missing? I am able to confirm that I am root:
mysql> select user(); select current_user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
Privileges for root are as follows:
mysql> SHOW GRANTS FOR 'root'@'localhost';
+------------------------------------------+
| Grants for root@localhost |
+------------------------------------------+
| GRANT USAGE ON *.* TO `root`@`localhost` |
+------------------------------------------+
EDIT: I uninstalled MySQL and installed MariaDB instead and everything is working now. For the record: I was using Ubuntu 21.04 and installing mysql-server from the Ubuntu repositories. MariaDB, also installed from the repos, is working fine.
