Windows – How to install MySQL on Windows 7

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

In MySQL 5.5+ you can use the auth_socket authentication plugin for this:

CREATE USER 'root'@'localhost' IDENTIFIED WITH auth_socket;

MariaDB 5.2.0+ has a similar unix_socket plugin – as far as I know, it is even active by default:

INSTALL PLUGIN unix_socket SONAME 'auth_socket';
CREATE USER root IDENTIFIED VIA unix_socket;

Similarly, PostgreSQL has the "local" auth method in its pg_hba.conf and enables it by default.

On Unixes, the general mechanism is often called "peercred", as in SO_PEERCRED. (The Windows equivalent is often called "Windows Native Authentication".)