MySQL Cheat Sheet

readme.md

Check if MySQL installed

$ mysql --version

MySQL set up

run

$ sudo mysql_secure_installation

• set new password for root user
• confirm new password
• For the rest of questions, just type y and hit ENTER. This will remove anonymous user, disallow root user login remotely and remove test database.

Change authentication method for root user

change authentication method from auth_socket to mysql_native_password this will help with phpmyadmin

$ sudo mysql
//run to check the current authentication method
$ SELECT user,authentication_string,plugin,host FROM mysql.user;

If root is set to auth_socket under plugin

run this command to change the authentication to mysql_native_password

$ ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '[password]';
$ FLUSH PRIVILEGES;
// check again to make sure it worked
$ SELECT user,authentication_string,plugin,host FROM mysql.user;
$ exit

MySQL Basic Commands

//log into mysql
$ mysql -u root -p
Enter password:

//exit out of mysql
mysql> exit

//Create db
mysql> CREATE DATABASE [db-name];

//list users and host
mysql> SELECT user, host FROM mysql.user;

//create user and grant privialges to them for created db
mysql> GRANT ALL PRIVILEGES ON [dbname].* TO "[username]"@"[hostname]" IDENTIFIED BY "password";
mysql> FLUSH PRIVILEGES;

//DROP or delete user
mysql> DROP USER [user]@[hostname];

//INSERT an .sql file into exsisting db
// 1. create database
// 2. exit mysql

$ mysql -u [username] -p [dbname] < data-dump.sql