Used chown for /var/lib/mysql to change owner from root, now getting Error 1049 (42000) in mysql

mysql permissions root ownership chown
Wei picture Wei · Apr 24, 2017 · Viewed 16.8k times · Source

With Ubuntu, I previously created a mysql database using the following code in the terminal:

$ my sql -u root -p

Then within mysql:

CREATE DATABASE securities_master;

I was trying to use file explorer to view the contents related to this database. But because I did not have permissions to open the folder /var/lib/mysql I wanted to change the permissions on this folder. I did some searching on stackoverflow, and without fully understanding what I was doing, I used something like the following (my username being wei):

sudo chown -R root:wei /var/lib/mysql

OR (unfortunately I had since closed terminal window so not sure exactly what I typed)

sudo chown -R wei:wei /var/lib/mysql

OR

sudo chown -R wei /var/lib/mysql

This allowed me to view inside the directory and see my files, so I thought I was making progress. However, once I had done this I realized that when I tried to USE the database in mysql:

USE securities_database;

I was getting an error message like:

ERROR 1049 (42000): Unknown database 'securities_master'

I believe this is related to my use of chown earlier. Upon looking into this with further stackoverflow searches, I'm now under the impression I shouldn't be willy nilly taking away root ownership of some files from root as "bad things can happen". I've tried to restore root ownership with:

sudo chown -R root /var/lib/mysql

AND/OR:

sudo chown -R root:root /var/lib/mysql

But unfortunately I seem to still get the same error message.

I apologize if my question seems so basic, or such a rookie error, total newbie to Ubuntu, Linux and mysql here. Thanks.

Answer

Barmar picture Barmar · Apr 24, 2017

The normal ownership of everything in /var/lib/mysql is mysql:mysql. So you should be able to fix it with:

sudo chown -R mysql:mysql /var/lib/mysql

Related questions

MySQL error 1449: The user specified as a definer does not exist

When I run the following query I get an error: SELECT `a`.`sl_id` AS `sl_id`, `a`.`quote_id` AS `quote_id`, `a`.`sl_date` AS `sl_date`, `a`.`sl_type` AS `sl_type`, `a`.`sl_status` AS `sl_…

Read More
Re-assign host access permission to MySQL user

I have several thousand MySQL users all set to allow access from a specific host. The problem is that now I'm going to have two machines (more in the future) which will need to use the same account to access …

Read More
MySQL Stored Procedure Permissions

I am trying to give a user permission to run a stored procedure at the stored procedure level on a MySQL Database rather than allowing a user to execute any stored procedure in the database. I was trying to execute …

Read More