..

MySQL Cheatsheet

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.

  1. grep 'temporary password' /var/log/mysqld.log
  2. Run mysql_secure_installation to 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 --skip-grant-tables and --skip-networking options).

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;

References