MySQL cheatsheet

Sometimes I derp out and forget some essential commands on MySQL. This is for me to reference if that ever happens. :D

Commands

If you need to create a new user:

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

Now to create a specific database:

CREATE DATABASE [IF EXISTS]* new_database;

Update a pre-existing user password:

ALTER USER 'preexistinguser'@'localhost' IDENTIFIED BY 'password';

Showing ALL the databases on MySQL:

SHOW DATABASES;

Select specific table on MySQL:

USE database;

Show all the tables within that database:

SHOW TABLES;

Showing ALL the users on MySQL:

SELECT User FROM mysql.user;

To grant ALL the privileges on the table to a specific user:

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

You can also choose which privilege to give to a user when assigning a table, which include the following:

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.

Format if you choose to give specific permissions:

GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;

Revoke any permissions which might have been given to a user for a specific database:

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

Removing a database for whatever reason you need to:

DROP DATABASE [IF EXISTS]* new_database;

If at any time you change a setting related to users or databases, make sure to flush for new changes:

FLUSH PRIVILEGES;

It's fairly easy to add tables and individual data but is not the scope of this entry.

+ [IF EXISTS] will avoid an error relating to said functions already existing.

Transferring database from one host to another

In order to move the database, we're going to first have to use "mysqldump."

Note: You might be asked for your MySQL server password when executing the -p parameter.

Choose the database which you're moving and then do the following:

mysqldump -u root -p --opt [db_name] > [db_name].sql

Your database name (just "db_name" in this instance) will be dumped into "db_name.sql"

Now you'll want to securely transfer the database. We'll be using rsync in this instance. You can use whatever you'd like:

rsync -a [db_name.sql] [username]@[host]:~/path/where/you/would/like/to/put/this

We'll now import the new database:

mysql -u root -p [db_name.sql] < /path/to/newdatabase.sql

That's pretty much it.

Remote Access

In order to allow remote access to a MySQL server, you'll need to allow MySQL to bind to a public address.

You'll need to visit /etc/mysql/mysql.conf.d/mysqld.cnf (MySQL 5.7+) and look for the following in the file:

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
# By default we only accept connections from localhost
# bind-address  = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

Simply comment out bind-address = 127.0.0.1 and restart MySQL.

WARNING! WARNING! WARNING! A reminder to myself really, but always make sure to secure your MySQL installation with the following commands:

mysql_install_db 
mysql_secure_installation

The first one will clean up the structure of all the MySQL databases and their tables. The second one will ask you a series of questions which will secure your MySQL instance.

If you do plan on giving access to some MySQL databases, the commands above still apply, but with a minor change:

GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@‘192.168.1.254’ IDENTIFIED BY '[password]';

Change 192.168.1.254 to reflect the remote IP you're wanting to connect from. To revoke access from this remote connection would follow the same template as above (see REVOKE). It also seems the password needs to be addressed in order for the changes to occur. Don't forget to flush the privileges!

If one wants to be super secure, you could whitelist only certain IP addresses to communicate over the firewall for the specific public MySQL port (3306), change the port all together, etc. However, it is not in the scope of this entry.

Common Issues

KEYEXPIRED error when fetching from repository

Error which one gets when using an expired repository key from the package manager (running Debian Jessie 8):

W: An error occurred during the signature verification. The repository is not updated and the previous index files will be used. GPG error: http://repo.mysql.com jessie InRelease: The following signatures were invalid: KEYEXPIRED 1487236823 KEYEXPIRED 1487236823 KEYEXPIRED 1487236823

W: Failed to fetch http://repo.mysql.com/apt/debian/dists/jessie/InRelease  

W: Some index files failed to download. They have been ignored, or old ones used instead.

Simply fetch the latest key from the MIT PGP server:

apt-key adv --keyserver pgp.mit.edu --recv-keys A4A9406876FCBD3C456770C88C718D3B5072E1F5

Try to update the repository list again:

apt-get update

Yay!

---

Updating MySQL packages to latest version

Especially prevalent on Debian, keeping up with the latest MySQL packages through your distro repository may not always provide you with the latest and greatest. In this instance, we'll have to fetch the latest version of MySQL through its own repository.

Fetch the latest revision of MySQL APT Config package:

Note: mysql-apt-config_0.8.4-1_all.deb is the package version fetched at the time of this writing. To get the latest version, go directly to dev.mysql.com and scroll down to find the latest version. You can either look at the package version, replacing the corresponding "_0.8.4-1_all.deb" with the latest version number, or grab the link directly to wget.

wget https://dev.mysql.com/get/mysql-apt-config_0.8.4-1_all.deb

Then install it using dpkg:

dpkg -i mysql-apt-config_0.8.4-1_all.deb

You will be asked to choose the version of MySQL you wish to install. This is up to you.

Now to update and install the selected version of MySQL:

apt-get update
apt-get install mysql-community-server

Once MySQL is done installing, check that it's actually installed:

mysql --version

...should get you something like this (depending on your installed version):

mysql  Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using  EditLine wrapper

That's all!