Basic MySQL Administration

Related Posts

• MySQL Query Cheatsheet

Table of Contents

• User Administration
  • List Users
  • Create User
  • Remove User
  • Show Privileges
  • Grant User Privileges
  • Remove User Privileges
  • Change Root Password
• Data Integrity
• Dump / Backup Database
  • Dump a Single Database
  • Dumping RDS Single Database
  • Parallel Dump With MySQL Pump
  • Dump All Databases
  • Dump Structure Of a Database
  • Dump Data Only
  • Dump Large Table
  • Dump Table To CSV
  • Dump Grants
• Import / Restore Database
• Miscellaneous
  • Login Without Password (Scripts)
  • Initialize the MySQL data directory
  • Reinstall MySql
  • Diagnosing Connections/Processes
  • Securing A Fresh MySql Installation
  • System Variables
• References

User Administration

List Users

SELECT User, Host FROM mysql.user;

Show Users With No Password

If you just want to see the users with no passwords, then run the command below. Ideally there should be none of these!

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

Create User

Create Local User

CREATE USER 'USERNAME'@'localhost' IDENTIFIED BY 'PASSWORD_HERE';

For MariaDB and versions of MySQL previous to 8.0, you could skip this step, and simply perform a grant statement with the IDENTIFIED BY clause in it, which would create the user account if it did not exist. However, this will not work in MySQL 8.

Create Remote User

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

CREATE USER 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD_HERE';

This is the default if you were to not specify the host. E.g.
CREATE USER 'USERNAME' IDENTIFIED BY 'PASSWORD_HERE';
would do the same thing.

Remove User

drop user 'usernameHere'@localhost
drop user 'usernameHere'

Remove Anonymous User

drop user ''@localhost
drop user ''

Show Privileges

Show User's Privileges

If you want to see the permissions of a specfic user:

SHOW GRANTS FOR username

If you just wish to view your own privileges then you can run:

SHOW GRANTS

Grant User Privileges

Give User Full Access To Database

Use one of these, whichever is appropriate.

GRANT ALL ON DATABASE_NAME.* to USERNAME@localhost;
GRANT ALL ON DATABASE_NAME.* to USERNAME@%;

Grant All Privileges On Specific Database To Local User

GRANT ALL 
ON `DATABASE_NAME_HERE`.* 
TO 'USERNAME_HERE'@'localhost' 

Grant Read-only Access On Database To Local User

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

Grant All Privileges On All Databases To Local User

GRANT ALL ON *.* TO 'USERNAME_HERE'@'localhost' 

Give Remote root User Full Privileges

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

Allow Users To Create Their Own Databases

Find out here.

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;

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'

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.

Dump / Backup Database

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 \
  --single-transaction \
  $DATABASE_NAME > $DATABASE_NAME.sql

The --single-transaction flag prevents you getting the following error message: Access denied for user 'username'@'%' to database 'databaseName'" when using LOCK TABLES

Dumping RDS Single Database

If dumping an RDS database, you probably want to add --set-gtid-purged=OFF which would look like so:

mysqldump \
  -u $USERNAME \
  -p$PASSWORD \
  -h localhost \
  --no-tablespaces \
  --single-transaction \
  --column-statistics=0 \
  --set-gtid-purged=OFF \
  $DATABASE_NAME > $DATABASE_NAME.sql

Using Gzip Compression

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 \
  --single-transaction \
  $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

Parallel Dump With MySQL Pump

I recently had to backup/dump a database that was well over 200 GB in size. Doing this was going to be slow unless I could do this in a parallelized way that would make full use of my 1 gig connection. Luckily, I was able to do this with mysqlpump.

CORE_COUNT=4

mysqlpump \
  --user=$USERNAME \
  --password \
  --default-parallelism=$CORE_COUNT \
  --databases myDatabase \
  --set-gtid-purged=OFF \
  --host=xxxx.xxxx.rds.amazonaws.com \
  > myDatabase.sql

MySQL pump was deprecated as of MySQL 8.0.34, and will be removed at some future verson of MySQL unfortunately. Thus, if you are reliant on this, you may need to start looking for an alternative solution.

Dump All Databases

You can dump all of the databases by using the --all-databases optional parameter.

mysqldump \
  -u $USERNAME \
  -p \
  -h $HOST \
  --no-tablespaces \
  --column-statistics=0 \
  --all-databases \
  > all-databases.sql 

Dump Structure Of a Database

Dumping just the structure of a database can be achieved by adding the --no-data parameter.

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

Dump Data Only

Dumping just the data (no structure) of a database can be achieved by using the --no-create-info parameter.

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=""

Dump Grants

If you want to export the users/permissions/grants so that you can set up a replacement database, you can do this with the pt-show-grants tool.

Install the tool with:

sudo apt-get install percona-toolkit

Then you can create an SQL with all the commands for creating all the users/grants with:

pt-show-grants --host=$HOST --user=$USER --ask-pass > grants.sql

Import / Restore 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

Login Without Password (Scripts)

If you need the ability to be able to log in without passing a password, such as for a script, you could create a .my.cnf file in your $HOME directory with the login credentials.

E.g.

touch ~/.my.cnf
chmod 0600 ~/.my.cnf

Then put put the following content in it:

[client]
user=myMysqlUser
password=myMysqlUsersPassword

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

Securing A Fresh MySql Installation

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

sudo mysql_secure_installation

System Variables

MariaDB System Variables

• MariaDB 10.6 (Community Edition)
• MariaDB Full List of Server System Variables

MySQL System Variables

• MySQL 9.0
• MySQL 8.4
• MySQL 8.0
• MySQL 5.7

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)
• Stack Overflow - How to auto login in MySQL from a shell script?
• Reddit - Using --single-transaction with mysqldump in RDS
• Stack Overflow - How to Export & Import Existing User (with its Privileges!)