Administration Commands

Introduction

This page summarises most common administration commands you will need for your MySQL server administration duties.

The commands in this page have been explained in detail in other pages of the MySQL administration series:

Backup and Restore

For more information, check MySQL Backup and Restore with mysqldump.

Backup and Restore a database

Use mysqldump with --databases option:

Restore the database using mysql. As the --databases option adds DROP/CREATE DATABASE statements to the dump file, the command below will drop the database name if already exists in the MySQL server.

Copy a database

Use mysqldump without any option:

As the dump file does not have any DROP/CREATE DATABASE statements, we need to ensure the database exists before importing it:

We can then import specifying the target database:

Exporting a subset from a table

You can use mysqldump with the --where option:

Time zone

For more information, check MySQL Date Types and Time Zone support.

Show the session Time Zone

The time_zone system variable provides the server time zone. This is the default time zone used to create new connections.

Use the below statement to retrieve the server time zone:

Sessions can however change the default time zone. To retrieve the current session time zone:

Set the Session Time Zone

The time_zone system variable provides the default time zone that the server uses when creating new sessions. To change the default session time zone, you can use the SET time_zone  command:

Account Management

For more information, check MySQL User Management and Access Control.

Create a User account

To create a new user account you use the CREATE USER command:

Change Account Password

You can change the password with the SET PASSWORD command:

You can also change the password directly into the mysql tables:

Show all users in the database

User accounts and passwords are stored in the user table of the mysql database. You can query this table to retrieve the list of all user accounts.

Privileges

For more information, check MySQL User Management and Access Control.

 

Show User Privileges

To show the privileges granted to a user, you use the SHOW GRANTS command:

To show the privileges granted to the current user, you can use either:

Grant Privileges

The following GRANT statement assigns all privileges to a use (except the GRANT OPTION privilege):

To create a super-user account, you would also need to assign the GRANT OPTION privilege:

The following GRANT statement provides basic SQL usage to all tables in a database:

Revoke Privileges

To revoke all privileges you can use this statement:

You can find below few REVOKE statement examples:

Table Metadata

Show all databases

The following command will show all databases in the server:

Show status for all Database Tables

The command below retrieves status information about each table, such as engine, number of rows, data size and index size.

Show all tables

The following command will show all tables in the current database:

You can also show tables from another database:

You can also filter the tables retrieved with the LIKE part:

Show Table details

The following command shows details for all columns in a table

An alternative option is the following:

You can use SHOW FULL COLUMNS to additionally include column collation and privileges:

Show CREATE TABLE

To show the CREATE TABLE statement of a table:

Bibliography

The following two tabs change content below.

Eduard Manas

Eduard is a senior IT consultant with over 15 years in the financial sector. He is an experienced developer in Java, C#, Python, Wordpress, Tibco EMS/RV, Oracle, Sybase and MySQL.Outside of work, he likes spending time with family, friends, and watching football.

Latest posts by Eduard Manas (see all)

Leave a Reply