SQL database operations

• Create database

CREATE DATABASE databasename;

• Delete database

DROP DATABASE databasename;

• List all databases

SHOW DATABASES;

• Import database from file

mysql -u root -p database < database_file.sql

SQL user operations

• Create user

CREATE USER databaseuser@localhost IDENTIFIED BY 'password';

• Delete user

DROP USER databaseuser;

• List all users

SELECT user FROM mysql.user;

SQL table operations

• Create a table with defined columns
  • Note that various data types can be defined for each column. These are oulined in the official MySQL reference documentation.

CREATE TABLE table_name (column_name column_type, column_name2 column_type2, PRIMARY KEY(primary_key_column_name));

• Delete a table from current database

DROP TABLE table_name;

• Select a database for table operations

USE database_name;

• List all tables in current database

SHOW TABLES;

• List all entries in a table

SELECT * FROM table_name;

SQL user privilege assignment and user modification operations

• Grant all privileges for databaseuser on database

GRANT ALL PRIVILEGES ON database.* TO databaseuser@localhost;

• Always reload privileges;

FLUSH PRIVILEGES;

Enable remote client access

This allows database access from outside of the localhost environment.

• Enable standard firewall port used for SQL

ufw allow 3306

• Enable port in SQL configuration file and disable binding SQL server to localhost

nano /etc/mysql/my.cnf

  [mysqld]
  port=3306
  skip-networking=0
  skip-bind-address

sudo service mysql restart

• Login to the mysql shell as root and define a new user with remote access privileges from a remote ip (or range)

CREATE USER remoteusername@'192.168.100.%' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON database.* TO remoteusername@'192.168.100.%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

Note the % is a wildcard character. It must be enclosed by quotes.