MySQL Administration
In this section you will find a lot of useful MySQL administration
tutorials including MySQL server startup and shutdown, MySQL server
security, MySQL database maintainance, backup and replication.
Getting Started with MySQL Access Control System
Summary: In this tutorial you are getting to know MySQL Access Control System and various privileges tables in MySQL.
MySQL implemented a sophisticated access control and privilege system
to allow you to create comprehensive access rules for handling client
operations and prevent unauthorized clients from accessing the database
system.
The MySQL access control has two stages when a client connects to the server:
- Connection verification: A client, which wants to connect to the
MySQL database server, needs to have a valid username and password. In
addition, the host, which the client connects from, has to be match with
the host in the MySQL grant table.
- Request verification: Once a connection established
successfully, MySQL checks the privileges to verify whether the client
has sufficient privileges. It is possible to check privilege at
database, table, and even field level.
There is a database called mysql is created automatically by MySQL
installer. The mysql database contains five main grant tables:
- user: contains user account and global
privileges columns. MySQL uses this table to either accept or reject
connection from a host. A privilege granted in user table is effective to all databases on the server.
- db: contains database level privileges. MySQL uses this db table to determine which database user can access from which host. A privilege granted at database level in db table applies to the database and all objects belong to that database such as table, view, stored procedures.
- table_priv and columns_priv: contains table and column level privileges. A privilege granted in table_priv table applies to the table and all its columns while a privilege granted in columns_priv table applies only to a specific column.
- procs_priv: contains function and stored procedure privileges
All of privileges of MySQL are controlled by those grant tables.
And from those grant tables you can create flexible access rules to meet
your requirements.
MySQL Create User
Summary: In this tutorial, you will learn how to create user in MySQL by using CREATE USER and INSERT statements.
In MySQL you specify not only who can connect to a database server
but also from where. Therefore account in MySQL consists of username and
hostname, where user connect from, which separated by the character
‘@’. If user ‘root’ connects from host ‘mysqltutorial.org’ to the
database server the account name would be ‘root’@’mysqltutorial.org’.
This allows you to setup multiple accounts with the same name but
connect from different hosts and have different privileges. In addition,
you have control to setup account to connect from either specific or
broad a set of hosts. The username and host are stored in the grant
table called user.
Create new user by using MySQL CREATE USER statement
MySQL provides you CREATE USER statement to allow you to create a new
user in a database server. The syntax of the CREATE USER statement is
as follows:
1 | CREATE USER user IDENTIFIED BY password | | | | |
|
User account in the format
‘username’@’hostname’ is followed by the CREATE USER clause. The
password is specified after the IDENTIFIED BY clause. The password must
be in plaintext. MySQL will encrypt the password for you when it saves
the user into the user table. For example, to create a new user dbadmin that connects from localhost with the password ‘CrEate-User’ we use the CREATE USER statement as follows:
1 | CREATE USER ‘dbadmin’@‘localhost’ IDENTIFIED BY ‘CrEate-User’; |
To allow user to connect from any host you use the wildcard ‘%’ that means any host.
1 | CREATE USER ‘superadmin’@’%’ IDENTIFIED ‘Secured’; |
The wildcard ‘% ‘works like in the
SQL LIKE statement. So in order to allow user to connect from any host
in the mysqltutorial.org you use wildcard ‘%’ as follows:
1 | CREATE USER ‘mysqladmin’@’%.mysqltutorial.org’ IDENTIFIED ‘SecurePass704’; |
Note that you can also use the
wildcard ‘_’ in the CREATE USER statement as well. If you omit the
hostname part of the account, MySQL still accepts it and allow user to
connect from any host. The quote is very important especially when the
account contains special characters such as ‘-‘ or ‘%’. If you
accidentally quote the account name like ‘username@hosname’, MySQL will
create a user with username username@hosname and allow you to
connect from any host that is not expected. MySQL CREATE USER statement
only creates a new user and does not grant any privileges to that user.
If you want to grant privileges to the user you use the MySQL GRANT
statement.
Create new user by using INSERT statement
A less common way to create user is using INSERT statement to insert a new record into the grant table user. By doing this way, you need to use PASSWORD function to encrypt the password by yourself before insert it into the table.
1 | INSERT INTO user (host,user,password) |
2 | VALUES('localhost','dbadmin',PASSWORD(‘CrEate-User’)); |
In this tutorial, you’ve learned various way to create user in MySQL by using CREATE USER and INSERT statement.
MySQL Changing Password for Accounts
Summary: In this tutorial you will learn how to change or reset password for MySQL account in various ways by using the UPDATE, SET PASSWORD and GRANT statements.
MySQL provides several ways to allow you to change or reset password
of a MySQL account. Before changing the password, you have to answer the
following questions:
- Which user do you want to change the password?
- From host, which user connects to, you want to change the password?
- Do you notify the related applications which are using the MySQL
account that you are planning to change password? Otherwise those
applications will lose the connection to the database and stop
functioning.
The first way to change the password is by using the UPDATE statement
to update the User and Host values for account user table in the
database MySQL. And then you need to run the command FLUSH PRIVILEGES to
reload privileges from the grant table in the mysql database.
Suppose you want to change the password for user
mysqltutorial from host
mysqltutorial.org to
Secret1970, you need to perform the following queries:
4 | SET password = PASSWORD('Secret1970') |
5 | WHERE user = 'mysqltutorial' AND |
6 | host = 'mysqltutorial.org'; |
It is important to note that we've
used the PASSWORD() function to encrypt the plain text password to store
in the password column of the grant table. Here is an example of using
password function to return an encrypted format of plain text password.
1 | SELECT PASSWORD('Secret1970') AS encrypt_password |
*16A54B5EE15C823362FAE7F64409A8F8C8501DD6
The second way to change the password is by using SET PASSWORD statement. You use the MySQL account format as user@host
to update the password. If you need to change password for other
account, you need to have UPDATE privilege. You don't need to call FLUSH
PRVILILEGES to reload privileges from grant table.
Here is the query for changing password using SET PASSWORD statement.
1 | SET PASSWORD FOR 'mysqltutorail'@'mysqltutorial.org' = PASSWORD('Secret1970') |
The third way to change the
password is by using GRANT USAGE statement with an IDENTIFIED BY clause,
in which you specify the plain text password instead of PASSWORD
function. Here is the query to change the password by using GRANT USAGE
with the IDENTIFIED BY clause:
1 | GRANT USAGE ON *.* TO ‘mysqltutorial@mysqltutorial.org IDENTIFIED BY ‘Secret1970 |
In case you want to reset the root
account's password because you forget it, you need to force the server
to stop and restart without using grant table validation.
How to Use MySQL GRANT to Grant Privileges to Account
Summary: In this tutorial, you will learn how to use MySQL GRANT statement to grant privileges to accounts. In order to follow this tutorial, we recommend that you read the ‘Getting started with MySQL access control system" and "How to create a user in MySQL" tutorials first.
MySQL GRANT Statement Explained
In order to give access privileges to an account, you use the MySQL GRANT statement with the syntax as follows:
1 | GRANT privileges (column_list) |
2 | ON [object_type] privilege_level |
3 | TO account [IDENTIFIED BY 'password'] |
If the account exists, MySQL
modifies its privileges. If account does not exist, GRANT statement
creates a new account with the specified privileges. Besides granting
privileges to the account, the GRANT statement also sets other account's
characteristics such as limit on access to the database server
resources and use secure connection. If you want to grant a privilege to
an account, you must have that privilege and GRANT OPTION privilege. We
will examine the MySQL GRANT statement in detail as follows:
- privileges indicates the privileges to assign to the
account. For example the CREATE privilege allows user to create database
and tables. You can assign multiple privileges at a time. Each
privilege is separated by a comma. You can use the privileges in the
table 1.1 below.
- column_list specifies the columns to which a privilege
applies. Each column is separated by commas and listed within
parentheses. The column_list is optional.
- privilege_level specifies the level at which the
privileges apply. You can use global privileges, database-specific
privileges, table-specific privileges, column-specific privileges…
- account specifies which account is being granted the privileges.
- password specifies the password to assign to the account.
If the account exists, the GRANT statement replaces the old password.
Like the CREATE USER statement, you use plaintext password followed by
the IDENTIFIED BY. The IDENTIFIED BY clause is optional.
- After the REQUIRE clause, you specifies whether the user has to connect to the database server over secure connection using SSL.
- If you want the account to have the privilege to grant its own
privileges to other accounts, you need to use WITH with GRANT OPTION. In
addition, WITH clause is used to allocate the database server's
resource that enable you to set how many connections or statements an
account can use per hour.
The MySQL GRANT statement is normally used with the CREATE USER
statement. You create a new user first before granting privileges to
that user.
Examples with MySQL GRANT
Let's practice with some examples to have a better understanding of
MySQL GRANT statement. If you want to create a superuser account that
can do anything including being able to grant privileges to other user
you can use the following queries:
1 | CREATE USER 'super'@'localhost' IDENTIFIED BY 'SecurePass1'; |
3 | GRANT ALL ON *.* TO 'super'@'localhost' WITH GRANT OPTION; |
The ON *.* clause means all
databases and all objects in them. The only limit here is that super can
only connect to the database server from localhost, which makes the
server more secured. To create a user that has all access in our sample
database classicmodels and can connect from any host you use the
following queries:
1 | CREATE USER 'super2'@'%' IDENTIFIED BY 'SecurePass2'; |
3 | GRANT ALL classicmodels.* TO 'super2'@'%' WITH GRANT OPTION; |
You can grant multiple privileges
at a time; a comma separates each privilege. For example, you can create
a user to use SELECT, INSERT and UPDATE statements on our sample
database classicmodels, you can use the following queries:
1 | CREATE USER 'rfc'@'%' IDENTIFIED BY 'SecurePass3'; |
3 | GRANT SELECT, UPDATE, DELETE ON classicmodels.* TO 'rfc'@'%'; |
Available privileges to use with MySQL GRANT
Here is the table that lists all the available privileges in MySQL
| Privilege |
Description |
| ALL [PRIVILEGES] |
Grant all privileges at specified access level except GRANT OPTION |
| ALTER |
Allow to use of ALTER TABLE statement |
| ALTER ROUTINE |
Allow user to alter or drop stored routine |
| CREATE |
Allow user to create database and table |
| CREATE ROUTINE |
Allow user to create stored routine |
| CREATE TABLESPACE |
Allow user to create, alter or drop tablespaces and log file groups |
| CREATE TEMPORARY TABLES |
Allow user to create temporary table by using CREATE TEMPORARY TABLE |
| CREATE USER |
Allow user to use the CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES statements. |
| CREATE VIEW |
Allow user to create or modify view |
| DELETE |
Allow user to use DELETE |
| DROP |
Allow user to drop database, table and view |
| EVENT |
Allow user to schedule events in Event Scheduler |
| EXECUTE |
Allow user to execute stored routines |
| FILE |
Allow user to read any file in the database directory. |
| GRANT OPTION |
Allow user to have privileges to grant or revoke privileges from other accounts |
| INDEX |
Allow user to create or remove indexes. |
| INSERT |
Allow user to use INSERT statement |
| LOCK TABLES |
Allow user to use LOCK TABLES on tables for which you have the SELECT privilege |
| PROCESS |
Allow user to see all processes with SHOW PROCESSLIST statement. |
| PROXY |
Enable user proxying |
| REFERENCES |
Not implemented |
| RELOAD |
Allow user to use FLUSH operations |
| REPLICATION CLIENT |
Allow user to query to see where master or slave servers are |
| REPLICATION SLAVE |
Allow user to use replicate slaves to read binary log events from the master. |
| SELECT |
Allow user to use SELECT statement |
| SHOW DATABASES |
Allow user to show all databases |
| SHOW VIEW |
Allow user to use SHOW CREATE VIEW statement |
| SHUTDOWN |
Allow user to use mysqladmin shutdown command |
| SUPER |
Allow user to use other administrative
operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET
GLOBAL, and mysqladmin command |
| TRIGGER |
Allow user to use TRIGGER operations. |
| UPDATE |
Allow user to use UPDATE statement |
| USAGE |
Equivalent to “no privileges” |
Table 1.1
In this tutorial, you've learned how to use
MySQL GRANT statement to grant various privileges to users with different options.
Revoking Privileges from Users by Using MySQL REVOKE
Summary: In this tutorial, you will learn how to use MySQL REVOKE statement to revoke privileges from MySQL accounts.
MySQL REVOKE Syntax
In order to revoke privileges from an account you use the MySQL
REVOKE statement. The syntax of MySQL revoke statement is as follows:
1 | REVOKE privilege_type [(column_list)] [, priv_type [(column_list)]]... |
2 | ON [object_type] privilege_level |
Let’s examine the MySQL Revoke statement in detail.
- You
list a list of privileges you want to revoke from an account after the
REVOKE keyword. Each privilege is separated by a comma.
- ON clause specifies the privilege level at that privileges are to be revoked.
- After
FROM keyword, you specify the account that you want to revoke the
privileges. You can list multiple users in FROM clause. Each user is
separated by a comma.
In order to revoke privileges from an
account, you must have GRANT OPTION privilege and privileges you are
revoking.To revoke all privileges you use the following MySQL REVOKE
Syntax:
1 | REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user]… |
Note that to use the above syntax; you must have the global CREATE USER privilege or the UPDATE privilege for the mysql database.To revoke proxy user you use the REVOKE PROXY syntax as below:
1 | REVOKE PROXY ON user FROM user [, user]... |
A quick remind that a proxy user is a
valid user in MySQL who can impersonate as another user therefore has
all privileges of that user.Before revoking privileges of a user, it is
better to check if the user has that privilege by using SHOW GRANT S
statement as follows:
MySQL REVOKE examples
Suppose account rfc has privileges SELECT, UPDATE and DELETE on our sample database classicmodels.
If you want to revoke UPDATE and DELETE privilege from rfc you can do
as follows:First we check the privileges of rfc by using SHOW GRANTS
statement:
1 | SHOW GRANTS FOR 'rfc'@'localhost'; |
GRANT SELECT, UPDATE, DELETE ON 'classicmodels'.* TO 'rfc'@'localhost'
If you do not followthe previous
tutorial on granting privileges to user, you can first grant the
privileges SELECT, UPDATE and DELETE for rfc connecting from localhost
to the database classicmodels as follows:
1 | GRANT SELECT, UPDATE, DELETE ON classicmodels.* TO 'rfc'@'localhost'; |
Second, we can revoke the UPDATE and DELETE privileges from user rfc as follows:
1 | REVOKE UPDATE, DELETE ON classicmodels.* FROM 'rfc'@'localhost'; |
Finally we can check the privileges of user rfc again by using SHOW GRANTS command.
1 | SHOW GRANTS FOR 'rfc'@'localhost'; |
GRANT SELECT ON 'classicmodels'.* TO 'rfc'@'localhost'
If you want to revoke all privileges of user rfc you can do it as follows:
1 | REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'rfc'@'localhost'; |
If you check the privileges of rfc again by using SHOW GRANTS statement, you will see the user rfc has no privilege.
1 | SHOW GRANTS FOR 'rfc'@'localhost'; |
GRANT USAGE ON *.* TO 'rfc'@'localhost'
Note that USAGE privilege means no privileges in MySQL.
When MySQL REVOKE takes effect
The effect of MySQL REVOKE statement depends on the privilege level as follows:
- Changes are made to the global privileges only effect next time
client connect to the database server. All current connected clients do
not affect by the changes.
- Database privilege’s change applies after the next USE statement.
- Table and column privilege’s changes apply to all queries issued after the changes are made.
In this tutorial, you've learned how to use MySQL REVOKE statement to revoke privileges from users.
Maintaining Database Tables
Summary: MySQL
provides several commands to allow you to maintain database table more
efficiently. Those commands enable you to analyze, optimize, check, and
repair the database tables. In this tutorial, you will learn those
MySQL command to maintain database tables.
Analyze table statement
Basically analyze table statement allows you to update cardinality of
an index column. By updating cardinality, you can select data faster by
utilizing all index features of database tables.
You will work with the employees and offices tables in our sample database. Let’s follow the examples below to understand more how analyze table statement works.
We can get the indexes information from employees table by executing the show index statement as follows:
1 | SHOW INDEX FROM employees |
MySQL gives you quite a lot of
information about index in the employee table including cardinality; in
this case the cardinality is 23.
Now we create a new index in the officeCode column to allow us to retrieve office and employee information faster by using join clause.
2 | ADD INDEX employee_offices (officeCode) |
At this time the cardinality of the
index is not updated, we can see this by performing the show index
statement on employees table again.
1 | SHOW INDEX FROM employees |
The cardinality now is NULL.
So
the cardinality of index is not updated automatically when index
created and a new record is inserted to the table. In this case, we can
use analyze table statement to trigger an update on cardinality of the index column by executing the following query:
If you perform the show index statement on the table employees again you can see that the cardinality of indexed column employeeNumber and officeCode are updated.
Optimize table statement
While
working with the database, you do a lot of changes such as insert,
update and delete data in the database tables therefore it causes the
physical of database table fragmented. As a result, the performance of
database server is degraded. MySQL provides you optimize table
statement to allow you to optimize the database table to avoid this
problem by defragmenting the table at physical level. The optimize table
statement is as follows:
1 | OPTIMIZE TABLE table_name |
With this optimize table statement, you should run it often with the tables which updated frequently.
Suppose you want to optimize the employees table to make it defragmented, you can perform the following query:
1 | OPTIMIZE TABLE employees |
Here is the output
Table Op Msg_type Msg_text
----------------------- -------- -------- --------
classicmodels.employees optimize status OK
Check table statement
Something
wrong can happen to the database server such as server turn off
unexpectedly, error while writing data to the hard disk and so on… All
of these situations could make the database operate incorrectly and in
the worst case it can be crashed. MySQL supports you to check database
tables by using check table statement. Here is the syntax of check table statement:
The check table statement checks both table and correspondent indexes. For example, you can you check table statement to check the table employees as follows:
And here is the output
Table Op Msg_type Msg_text
----------------------- ------ -------- --------
classicmodels.employees check status OK
Check table statement only detects
problems in a database table but it does not repair them. In order to do
so you can you repair table statement.
Repair table statement
Repair
table statement allows you to repair some errors occurred in database
tables. MySQL does not guarantee that this statement can repair all
errors which your database may have. The repair table statement can be
written as follows:
Suppose you have some errors in the employees table and need to fix, you can use repair table statement by performing this query:
MySQL will return what it has done
with the table and the table is repaired or not. Here is the output you
always want to see in such cases:
Table Op Msg_type Msg_text
----------------------- ------ -------- --------
classicmodels.employees repair status OK
How to Backup Databases Using mysqldump Tool
Summary: In thistutorial you will learn how to use mysqldump tool back up MySQL databases.
MySQL GUI tools such as phpMyAdmin, SQLyog and etc often provide
features for backup MySQL databases with ease. However if your database
is big, the backup process could be very slow because the backup file
need to be transferred across the network to your client PC. As the
result, the backup process increases locking time therefore MySQL
unavailability.
MySQL provides a very useful tool for backup or dump MySQL databases
locally on server very fast. The backup file is stored in the file
system in the server so you just need to download it when needed. The
tool to backup MySQL databases is mysqldump. It is located in the
root/bin folder of MySQL installation folder. The mysqldump is a program
provided by MySQL that can be used to dump databases for backup or
transfer database to another database server. The dump file contains a
set of SQL statements to create and populate tables. In addition, the
mysqldump can be used to generate CSV, delimited or XML files. In this
tutorial, we will focus only on how to backup MySQL database by using
mysqldump tool.
How to Backup a MySQL Database
To backup a MySQL database, the database first has to exist in the
database server and you have access to that server as well. You can use
SSH or Telnet to login to the remote server if you do not have remote
desktop to it. The command to backup a MySQL database as follows:
1 | mysqldump -u [username] –p[password] [database_name] > [dump_file.sql] |
The parameter of the command above as follows:
- [username]: valid MySQL username.
- [password]: valid password for the user. Note that there is no space between –p and the password.
- [database_name]: database name you want to backup
- [dump_file.sql]: dump file you want to generate.
By executing the above command all database structure and data will
be exported into a single [dump_file.sql] dump file. For example, in
order to back our sample database classicmodels, we use the following
command:
1 | mysqldump -u mysqltutorial –psecret classicmodels > c:\temp\backup001.sql |
How to Backup MySQL Database Structure Only
If you only want to backup database structure only you just need to
add an option –no-data to tell mysqldump that only database structure
need to export as follows:
1 | mysqldump -u [username] –p[password] –no-data [database_name] > [dump_file.sql] |
For example to backup our sample database with structure only, you use the following command:
1 | mysqldump -u mysqltutorial –psecret -no-data classicmodels > c:\temp\backup002.sql |
How to Backup MySQL Database Data Only
There is a case that you want to refresh data in staging and
development system so the data in those systems are the same as
production system. In this case you just need to export data only from
production system and import it to staging and development system. In
order to backup data only, you use option –no-create-info of mysqldump
as follows:
1 | mysqldump -u [username] –p[password] –no-create-info [database_name] > [dump_file.sql] |
For example to backup our sample database with data only, you use the following command:
1 | mysqldump –u mysqltutorial –psecret –no-create-info classicmodels > c:\temp\backup002.sql |
How to Backup Multiple MySQL Databases into a Single File
If you want to backup multiple database just separate database name
by command in the [database_name]. If you want to back up all databases
in the database server use the option –all-database.
1 | mysqldump -u [username] –p[password] [dbname1,dbname2,…] > [dump_file.sql] |
3 | mysqldump -u [username] –p[password] –all-database > [dump_file.sql] |
