Fresh Installation of MySql: Access denied for user ‘root’@’localhost’ (using password: YES) (MacOS)

192.168.2.199 is the IP address of your PC you are running MySQL Workbench on. MySQL allows to specify addresses certain users are allowed to connect from. In your case root is not allowed to connect from 192.168.2.199.

By default MySQL creates user root as 'root'@'localhost'. To remove the restriction you need to rename the user to 'root'@'%'

Follow the steps below.

1. Start the MySQL server instance or daemon with the --skip-grant-tables option (security setting).

   $ mysqld --skip-grant-tables
   

2. Execute these statements.

   $ mysql -u root mysql
   $mysql> UPDATE user SET Password=PASSWORD('my_password') where USER='root';
   $mysql> FLUSH PRIVILEGES;
   

If you face the unknown field Password error above use:

update user set authentication_string=password('my_password') where user='root';

3. Finally, restart the instance/daemon without the --skip-grant-tables option.

   $ /etc/init.d/mysql restart
   

You should now be able to connect with your new password.

$ mysql -u root -p

Enter password: my_password

Fix for MySQL “Unable to lock ibdata1” error

sudo mv /usr/local/mysql/data/ibdata1 /usr/local/mysql/data/ibdata1.bak
sudo mv /usr/local/mysql/data/ib_logfile0 /usr/local/mysql/data/ib_logfile0.bak
sudo mv /usr/local/mysql/data/ib_logfile1 /usr/local/mysql/data/ib_logfile1.bak
sudo cp -a /usr/local/mysql/data/ibdata1.bak /usr/local/mysql/data/ibdata1
sudo cp -a /usr/local/mysql/data/ib_logfile0.bak /usr/local/mysql/data/ib_logfile0
sudo cp -a /usr/local/mysql/data/ib_logfile1.bak /usr/local/mysql/data/ib_logfile1
sudo /etc/init.d/mysql restart