Note: This article assumes you are using MySQL 5.7.6 and later.
Default Root Password of Newly Installed MySQL 5.7
After you installed MySQL community server 5.7 on fresh Linux, you will need to find out the temporary password from
/var/log/mysqld.log to login as root.
grep 'temporary password' /var/log/mysqld.log
mysql_secure_installationto change new password
The above method is for normal procedure. If you have other scenario, e.g. automate the MySQL server installation, please refer to the following section.
Make MySQL Root Accessible from Elsewhere
To remote access MySQL server with
root account, you need the following trick (disclaimer: this is considered DANGEROUS!):
$ mysql -u root -p mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
After that, you can connect to remote MySQL server with this command:
$ mysql -h 10.0.0.1 -u root -p mysql>
Resetting MySQL Root Password
So how to force change MySQL root password even if you don’t know the password?
Stop the MySQL server if necessary, then restart it with the
--skip-grant-tables option. This enables anyone to connect without a password and with all privileges, and disables account-management statement such as
ALTER USER and
SET PASSWORD. Because this is insecure, you might want to use
--skip-grant-tables in conjunction with
--skip-networking to prevent
remote clients from connecting.
$ sudo service mysqld stop $ sudo mysqld_safe --skip-grant-tables --skip-networking $ mysql -u root --connect-expired-password
After connecting to MySQL server with root account, tell the server to reload the grant tables so that account-management statements work:
mysql> FLUSH PRIVILEGES;
Then change the
'root'@'localhost' account password:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
You should now be able to connect to MySQL server as
root using the new password. Stop the server and restart it normally (without the
Type of MySQL Table Engine
You can easily check what engine type is used for every tables by doing following:
mysql> SHOW TABLE STATUS WHERE Name = 'table_name';
If you want to change it, e.g. from MyIASM to InnoDB, try this:
mysql> ALTER TABLE table_name ENGINE=InnoDB;