Basic MySQL Commands

Related Posts

• MySQL Query Cheatsheet

Table of Contents  

1. Security
2. User Administration
3. Data Integrity
4. Dumping Databases
5. Miscellaneous
6. References

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  

MySQL 8 Note

A lot of the commands below revolve around creating users, which didn't work for me after upgrading to MySQL 8. Instead I had to split the single GRANT command into two parts. One to create the user first with their password, then another to assign their permissions to a database.

CREATE USER 'USERNAME'@'localhost' IDENTIFIED BY 'PASSWORD_HERE';
GRANT ALL ON DATABASE_NAME.* to USERNAME;

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  

No Tablespaces

I have added the option --no-tablespaces to all of the commands below. This resolves a breaking change MySQL added in a minor update. Refer to this stack overflow post for more info.

Dump a Single Database

This is useful for copying/moving/backups

mysqldump \
  -u $USERNAME \
  -p$PASSWORD \
  -h localhost \
  --no-tablespaces \
  --column-statistics=0 \
  $DATABASE_NAME > $DATABASE_NAME.sql

If space is tight, you can dump to a compressed file like so:

mysqldump \
  -u $USERNAME \
  -p$PASSWORD \
  -h $HOST \
  --no-tablespaces \
  --column-statistics=0 \
  $DATABASE_NAME | gzip -c > $OUTPUT_FILENAME

If you are connecting to a remote host and the bandwidth is limited, then use the --compress flag like so:

mysqldump \
  -u $USERNAME \
  -p$PASSWORD \
  -h $HOST \
  --compress \
  --no-tablespaces \
  --column-statistics=0 \
  $DATABASE_NAME > $DATABASE_NAME.sql

If you want to be able to read the file, rather than being for an export/import, then it is much more human-readable if you use the --skip-extended-insert option:

mysqldump \
  -u $USERNAME \
  -p$PASSWORD \
  -h localhost \
  --no-tablespaces \
  --column-statistics=0 \
  --skip-extended-insert \
  $DATABASE_NAME > $DATABASE_NAME.sql

Dump All Databases

mysqldump \
  -u $USERNAME \
  -p$PASSWORD \
  -h localhost \
  --no-tablespaces \
  --column-statistics=0 \
  $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 \
  --no-tablespaces \
  --column-statistics=0 \
  -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 \
  --no-tablespaces \
  --column-statistics=0 \
  --skip-triggers \
  --compact \
  --no-create-info \
  $DATABASE_NAME > $DATABASE_NAME.sql

Dump Large Table

mysqldump \
  --host $HOST \
  --user $USER \
  --password="$PASSWORD" \
  --no-tablespaces \
  --column-statistics=0 \
  --single-transaction \
  --quick \
  $DATABASE_NAME \
  $TABLE_NAME > $TABLE_NAME.sql

The --quick option is what is important here when dealing with large tables. Also, by using --single-transaction we can be safe (if using InnoDB rather than MYISAM) by using a single transaction, and mysqldump will not feel the need to lock the table.

Dump Table To CSV

mysqldump \
  -u [user] \
  --password="[password]" \
  --no-tablespaces \
  --column-statistics=0 \
  -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

References  

• Nixcraft - Import MySQL Dumpfile, SQL Datafile Into My Database
• Nixcraft - MySQL Change root Password
• Digital Ocean - How to Create a New User and Grant Permissions in MySQL
• Mysqldump Data Only
• How To Geek - Dump just the table structure to a file in MySQL
• Ask Ubuntu - MySQL keeps failing to install
• Nixcraft - MySQL Change root Password
• ServerFault - mysqldump to a tar.gz
• Serverfault - mysqldump throws: Unknown table 'COLUMN_STATISTICS' in information_schema (1109)