MySQL Cheatsheet

Under Ubuntu Server 16.04:

To login to MySQL from terminal as root:

mysql -u root -p

Once inside the MySQL prompt:

To view the list of databases:

SHOW SCHEMAS;

To use a particular database:

USE databasename;

To view the list of tables inside the database:

SHOW TABLES;

To create a new user:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

To enable remote access to the MySQL database (for example, using MySQL Workbench):

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

(optional) To grant only specific rights instead of all (using SELECT as example):

GRANT SELECT ON *.* TO 'username'@'localhost';

 Back to terminal, comment out the "bind_address" line in the MySQL config file. It's in /etc/mysql/mysql.conf.d/mysqld.cnf on my Ubuntu server 16.04.

Finally, restart the MySQL process:

sudo service mysql restart

- - - - -

Recently migrated from SQLite to MySQL. You can't beat SQLite's simplicity, but it's not so good for simultaneous access (single writer, multiple readers). Try to rsync the db to another machine while it's open and it quickly gets out of sync. On top of that there are the Linux permission issues (read-only users need write access to the db and its containing folder because SQLite needs to write temporary journal files).