MySQL Administration
This is just basics of maintaining a database including creation of databases, creation of users, granting of priveleges to users, changing passwords, and other activities from within the mysql CLI front end to MySQL.
Entering and Exiting the mysql Program
[sai@test ~]$ mysql
ERROR 1045: Access denied for user: 'sai@localhost' (Using password: NO)
OOPs! User sai is apparently password protected, so we must run mysql to query for a password, and then type in the password.
[sai@test ~]$ mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 41 to server version: 4.0.18
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> quit
Bye
In the preceding, you ran mysql with the -p option so that it would query for the password, you typed in the password, and then you exited the mysql program by typing quit.
By the way, if the account has no password (this is sometimes an installation default), you would just press Enter when prompted for the password.
Perhaps if you want to log in as root instead of sai. This would probably be the case if you wanted to add a database or any other high responsibility action.
Here's how you log in as root:
[sai@test ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 42 to server version: 4.0.18
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> quit
Bye
The -u root tells mysql to log you in as root user, and as previously discussed, the -p tells mysql to query for a password.
In mysql, when you see a prompt like this:
mysql>
you are in the mysql program. For the rest of this section, most examples will begin and end in mysql.
As a preventive measure
There may be more than one root account, and you must password protect all of them.
mysql> use mysql
Database changed
mysql> select host, user, password from user;
+-----------------------+-------+------------------+
| host | user | password |
+-----------------------+-------+------------------+
| localhost | root | 2aa0e9e91dbd7cf2 |
| localhost.localdomain | root | 2aa0e9e91dbd7cf2 |
| localhost | | |
| localhost.localdomain | | |
| localhost | sai | 2aa0e9e91dbd7cf2 |
+-----------------------+-------+------------------+
5 rows in set (0.00 sec)
mysql>
As you can see, there's a root account at localhost and another to localhost.localdomain. Both must be password protected. In reality, all accounts should be password protected.
Exploring Your MySQL Installation
From within mysql you can find quite a bit of information concerning your installation.
As what user are you logged into mysql? What databases exist? What tables exist in a database? What is the structure of a table? What users exist?
Because different operations require different priveleges, to save time we'll perform all these actions logged into mysql as root.
Let's start with finding out your username within mysql:
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql>
Now let's list all the users authorized to log into mysql:
mysql> use mysql
Database changed
mysql> select host, user, password from user;
+-----------------------+-------+------------------+
| host | user | password |
+-----------------------+-------+------------------+
| localhost | root | 2aa0e9e91dbd7cf2 |
| localhost.localdomain | root | 2aa0e9e91dbd7cf2 |
| localhost | | |
| localhost.localdomain | | |
| localhost | sai | 2aa0e9e91dbd7cf2 |
+-----------------------+-------+------------------+
5 rows in set (0.00 sec)
mysql>
Notice there are two root logons -- one at localhost and one at localhost.localdomain.
They can have different passwords, or one of them can even have no password. Therefore, You'd Better check to make sure they're both password protected.
DO NOT forget the password(s). If you lose all administrative passwords, you lose control of your mysql installation.
User Maintenance
Before discussing user maintenance, it's necessary to understand what properties exist for each user. To do that, we'll use mysql -e option to execute SQL commands and push the output to stdout.
Watch this:
[sai@test ~]$ mysql -u root -p -e "use mysql;describe user;" | cut -f 1
Enter password:
Field
Host
User
password
Select_priv
Insert_priv
Update_priv
Delete_priv
Create_priv
Drop_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv
Grant_priv
References_priv
Index_priv
Alter_priv
Show_db_priv
Super_priv
Create_tmp_table_priv
Lock_tables_priv
Execute_priv
Repl_slave_priv
Repl_client_priv
ssl_type
ssl_cipher
x509_issuer
x509_subject
max_questions
max_updates
max_connections
The word "Field" is a heading, not a piece of information about a user. After that, the first three fields are the user's host, username and password.
The password is encoded. If you hadn't included the cut command, you'd have seen that the same username can exist in multiple hosts, even if both hosts refer to the same physical machine.
That's why it's vital to MAKE SURE TO password protect ALL users. The next several fields after password are priveleges that can be granted to the user, or not.
There's also a way to grant ALL privileges to a user. But from a security point of view this is very dangerous, as ordinary looking users can be turned into backdoors.
I'd suggest always granting and revoking specific privileges. Here is a list of the privileges that MySQL users can have, and what those privileges allow them to do:
USER FIELD PRIVILEGE FUNCTION
Select_priv Select Ability to use the select command. In other words, ability to read.
Insert_priv Insert Ability to insert new data -- insert a new row.
Update_priv Update Ability to change existing data -- change contents of a row.
Delete_priv Delete Ability to delete rows of existing data.
Create_priv Create Ability to create a new table.
Drop_priv Drop Ability to drop a table.
Reload_priv Reload
Shutdown_priv Shutdown
Process_priv Process
File_priv File
Grant_priv Grant Ability to grant and revoke privileges to others.
References_priv References
Index_priv Index Ability to create new indexes or drop indexes.
Alter_priv Alter Ability to change the structure of a table.
Show_db_priv
Super_priv
Create_tmp_table_priv Ability to create temporary tables.
Lock_tables_priv Ability to lock tables.
Execute_priv
Repl_slave_priv
Repl_client_priv
The root user, or any user given sufficient privileges, can create new users with the grant command:
mysql> grant select on test2.* to myuid@localhost identified by 'mypassword';
Query OK, 0 rows affected (0.00 sec)
mysql> select host, user, password from mysql.user;
+-----------------------+-------+------------------+
| host | user | password |
+-----------------------+-------+------------------+
| localhost | root | 7939f45f28d0aa41 |
| localhost.localdomain | root | 2aa0e9e91dbd7cf2 |
| localhost | | |
| localhost.localdomain | | |
| localhost | sai | 2aa0e9e91dbd7cf2 |
| localhost | myuid | 162eebfb6477e5d3 |
+-----------------------+-------+------------------+
6 rows in set (0.00 sec)
mysql>
In the preceding, we grant one privilege, select, on every table in the test2 database (test2.*), to user myuid at host localhost (myuid@localhost), giving that user the password "mypassword". We then query table user in the mysql database (mysql.user) in order to see whether user myuid has been created. Indeed he has.
Granting select privilege is insufficient for any user using any app that modies data. Let's give myuid more privileges in the test2 database:
mysql> grant Insert, Update, Delete, Create on test2.* to myuid@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql>
Now user myuid can not only select, but can insert rows, update rows, delete rows, and create tables and databases. Conspicuously missing is the ability to drop tables and databases -- that can be more dangerous, in the hands of a careless but not malicious user, than some of the other abilities.
Privileges granted by the grant command are not kept in the mysql.user table, but instead in the mysql.db table. You can see results by issuing this command from the operating system:
[sai@test ~]$ mysql -u root -p -e 'select * from mysql.db where host="localhost" and user="myuid";' > temp.txt
Enter password:
The results look like this:
Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv
localhost test2 myuid Y Y Y Y Y N N N N N N N
You can revoke privileges like this:
mysql> revoke Delete, Create on test2.* from myuid@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql>
If you redo the select on mysql.db, you'll see that those two privileges are now marked N.
To actually delete a user, you use a SQL statement to delete him from the mysql.users table after revoking all his privileges.
When deleting the user from mysql.user, if you forget the where clause, or any of its tests, especially the test on column user, you will delete too many users -- possibly all users, in which case you'll have no way to operate the database. BE VERY CAREFUL!
Now that you understand the potential landmines, here's how you delete a user:
mysql> revoke all on test2.* from myuid@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from mysql.user where user='myuid' and host='localhost';
Query OK, 1 row affected (0.00 sec)
mysql>
Exploring Your Databases
You can refer to a table in two ways: By name after connecting to its database with a use statement, and with the databasename.tablename syntax.
The former is much more common in applications, but the latter is often used in database administration, especially when you must access the system database (mysql) in order to perform work on a different database.
The first item for exploration is to find all databases:
mysql> show databases;
+-------------------+
| Database |
+-------------------+
| depot_development |
| depot_production |
| depot_test |
| mysql |
| test |
| test2 |
+-------------------+
6 rows in set (0.00 sec)
mysql>
In the preceding, you went into the mysql program, determined what databases existed. Now let's explore the test database:
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| dogs |
| people |
+----------------+
2 rows in set (0.00 sec)
mysql>
So the database test has two tables, dogs and people. Let's examine the columns in each of those tables:
mysql> show columns from dogs;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| owner | varchar(20) | | PRI | | |
| name | varchar(20) | | PRI | | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> show columns from people;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| lname | varchar(20) | | PRI | | |
| fname | varchar(20) | | | | |
| mname | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
Another way to get the same information is with the describe command:
mysql> describe dogs;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| owner | varchar(20) | | PRI | | |
| name | varchar(20) | | PRI | | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> describe people;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| lname | varchar(20) | | PRI | | |
| fname | varchar(20) | | | | |
| mname | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> \q
