MySQL



MySQL is a widely used database available in its Community edition for free from the MySQL website. . This page includes some notes about the database and some examples covering things that I would otherwise forget.


Installation

On MacOS installation itself is relatively painless - download the dmg file from the MySQL website, double-click on the pkg file and follow the instructions (you will need to add a password for the root user).

Then in order to start using the product (using the CLI) you will need to update your PATH environment variable. Add the following to your .bash_profile file.

export PATH=$PATH:/usr/local/mysql/bin

You will then be able to access the database -

mysql --user=root --password mysql
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.17 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>    

And create a database for your own use -

mysql> create database testing;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testing            |
+--------------------+
5 rows in set (0.00 sec)

- and create the appropriate tables.

To create a new user and assign to them an appropriate set of permissions -.

CREATE USER 'bar'@'localhost'
  IDENTIFIED BY 'password';
GRANT ALL
  ON bankaccount.*
  TO 'var'@'localhost';    
CREATE USER 'foo'@'localhost'
  IDENTIFIED BY 'password';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
  ON expenses.*
  TO 'foo'@'localhost';    

Here we have created two users - bar has been given all permissions on all tables in the bankaccount database and foo has been given a subset of permissions on the expenses database. It is possible of course to set permissions for individual tables.

To Revoke permissions :-

REVOKE INSERT,UPDATE,DELETE
  ON backaccount.*
  FROM 'bar'@'localhost';
REVOKE CREATE,DROP
  ON expenses.*
  FROM 'foo'@'localhost';