MySQL for Mac OS X

Get MySQL from dev.mysql.com

Make alias for Terminal

alias mysql=/usr/local/mysql/bin/mysql
alias mysqladmin=/usr/local/mysql/bin/mysqladmin

if you want to make the alias permanent, you need to create / edit a file call [.bash_profile]

nano ~/.bash_profile

and add the command to the file save and exit.
reference

Reset MySQL server root account password

$ cd /usr/local/mysql/bin
$ ./mysqladmin -u root password 'NEW_PASSWORD'

Use new password to login

$ mysql -u root -p
Enter password:

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW_PASSWORD") where User = "root";
mysql> flush privileges;
mysql> \q

Create new user and grant all privileges to new user

mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';
mysql> FLUSH PRIVILEGES;

Grant different user permissions

GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;
  • ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)
  • CREATE- allows them to create new tables or databases
  • DROP- allows them to them to delete tables or databases
  • DELETE- allows them to delete rows from tables
  • INSERT- allows them to insert rows into tables
  • SELECT- allows them to use the Select command to read through databases
  • UPDATE- allow them to update table rows
  • GRANT OPTION- allows them to grant or remove other users’ privileges

Revoke user permission from database

REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘localhost’;

Delete user with DROP

DROP USER ‘username’@‘localhost’;

Log in to MySQL via command line

$ mysql -u username -h localhost -p
Enter password: