Programster's Blog

Tutorials focusing on Linux, programming, and open-source

Basic MySQL Commands

MySQL

MySQL Query Cheatsheet

Security

Securing A Fresh MySql Installation

Answer all the questions in this security script that comes with MySQL installations.

sudo mysql_secure_installation

Show Accounts With No Password

Ideally there should be none of these!

SELECT User,Password,Host FROM mysql.user WHERE Password='';

Remove anonymous user

drop user ''@localhost
drop user ''

Change Root Password

Changing root password (change abc with your current password and 123456 with your desired new one).

mysqladmin -u root -p'abc' password '123456'

Setting Up Password For First Time

On Ubuntu 18.04, after installing mysql-server-5.7 you need to set the password by logging into the mysql CLI with

sudo mysql

... then set the password with:

GRANT ALL 
ON *.*
TO root@localhost
IDENTIFIED BY 'PASSWORD_HERE';

GRANT GRANT OPTION
ON *.*
TO root@localhost 
IDENTIFIED BY 'PASSWORD_HERE';

FLUSH PRIVILEGES;

If you are using CentOS and just perform a yum install mysql-server, you may notice that it never asks you for a password. By default there is no password and you can just log in as root. In fact if you specify password, then it will fail. To set the root user's password for the first time, run the following command.

mysqladmin -u root password [your password here]

User Administration

List Users

SELECT Host, User FROM mysql.user;

Add Fully Priviledged Local User

GRANT ALL 
ON `DATABASE_NAME_HERE`.* 
TO 'USERNAME_HERE'@'localhost' 
IDENTIFIED BY 'PASSWORD_HERE';
FLUSH PRIVILEGES;

Adding a read-only local user to a database

GRANT SELECT \
ON `DATABASE_NAME_HERE`.* \
TO 'USERNAME_HERE'@'localhost' \
IDENTIFIED BY 'PASSWORD_HERE';
FLUSH PRIVILEGES;

Add a remote user to a MySQL

Adding a user to a MySQL and allow them to connect remotely from any IP

GRANT ALL ON *.* to [username]@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

This will also work

GRANT ALL ON *.* to [username] IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

If you only want to allow localhost then use [username]@'localhost'. If you only want to allow a certain IP then use [username]@[IP Address]'

The above examples uses GRANT ALL ON *.* which will give the user all privileges across all databases. You may want to change this.

Give Remote root User Full Privileges

GRANT ALL ON *.* TO root WITH grant option;

Allow Users To Create Their Own Databases

Find out here.

Get User's Privileges / Grants

This will show you the permissions of the currently logged in user.

SHOW GRANTS

If you want to see the permissions of another user:

SHOW GRANTS FOR username

Revoke / Remove User Privileges

The following command will revoke permissions from the myDatabase database for the user called user1;

REVOKE ALL PRIVILEGES ON myDatabase.* FROM user1;

Data Integrity

To check your database for any issues, run the following command:

mysqlcheck -u [user] -p --databases [database name]

You can also use --all-databases to check all the databases, and --auto-repair to automatically try to fix problems, not just find them. E.g.

mysqlcheck -u root -p --all-databases --auto-repair

For more options, refer to the mysqlcheck documentation.

Dumping Databases

Dump a Single Database

This is useful for copying/moving/backups

mysqldump \
-u $USERNAME \
-p$PASSWORD \
-h localhost \
$DATABASE_NAME > $DATABASE_NAME.sql

Dump All Databases

mysqldump \
-u $USERNAME \
-p$PASSWORD \
-h localhost \
$DATABASE_NAME > $DATABASE_NAME.sql

Dump Structure Of a Database

Dumping just the structure of a database is exactly the same but with the -d switch:

mysqldump \
-u $USERNAME \
-p$PASSWORD \
-h localhost \
-d $DATABASE_NAME > $DATABASE_NAME.sql

Dump Data Of a Database

Dumping just the data (no structure) of a database:

mysqldump \
-u $USERNAME \
-p$PASSWORD \
-h localhost \
--skip-triggers \
--compact \
--no-create-info \
$DATABASE_NAME > $DATABASE_NAME.sql

Dump Table To CSV

mysqldump \
-u [user] \
--password="[password]" \
-t \
-T[dump folder path] [database name] [table name] \
--fields-terminated-by=','

If you're using Ubuntu, AppArmor restricts where you can save to. Using /var/lib/mysql works. Debian users do not have to worry about this.

On Debian 8 with MySQL 5.5, I had to add a line to the /etc/mysql/my.cnf file: secure_file_priv=""

Import Database

Importing a database from a dumped database (previous command) can be done like so:

mysql \
-u $USERNAME \
-p$PASSWORD \
-h localhost \
$DATABASE_NAME < $DATABASE_NAME.sql

Misc

Initialize the MySQL data directory

If you just changed the datadir in your my.cnf file, then you probably want to initialize the new directory.

sudo mysql_install_db

Reinstall MySql

You may want to do this because cant login/set root password

sudo service mysql stop
sudo apt-get remove --purge mysql-server mysql-client mysql-common
sudo apt-get autoremove
sudo apt-get autoclean
sudo deluser mysql

Diagnosing Connections/Processes

If you ever get a "too many connections" error message, make sure to log into your database and run the following command to see what connections are open:

SHOW FULL PROCESSLIST

If you are ok with showing a truncated query, then SHOW PROCESSLIST will suffice