Introduction
In the other Virtual Workshops we have used commands that are pretty
much applicable to anyone. This part of the MySQL series is aimed at giving
a rudimentary understanding of managing a MySQL database server. As such the
task covered here are not really about manipulating data or database structures,
but the actual databases themselves.
Creating a Database
In order to create a database you need to have the PRIVILEGES- this may be
because you are the root user or you (or you systems administrator) has created
an admin user that has ALL PRIVILEGES over all databases. In these examples
a user called 'admin' has been created precisely for this purpose. Creating
a database is fairly straightforward.
Logging In
A reminder of how to start the MySQL Client Software, and as we are not concerned
with manipulating just one database we don't have to specify a database as
part of our startup command.
$ mysql -u -p
Enter password:
Create database command
Next we are ready to enter the very simple command to create a database which
is:
mysql> CREATE DATABASE ;
Let's imagine that we are going to create a 'vworks' database (those wishing
to create a database for use with the VWs should use this). We would enter
the command:
mysql> CREATE DATABASE vworks;
We can now check for the presence of this database by typing:
mysql> SHOW DATABASES;
+-----------+
| Database |
+-----------+
| mysql |
| vworks |
+-----------+
2 rows in set (0.06 sec)
The other database listed ('mysql') is the internal database which MySQL
uses to manage users, permissions etc.
NOTE: Deleting or DROPing a database is similar to the DROP
TABLE command issued in Part 4. e.g.
DROP DATABASE
Granting Privileges on the new database
Now that we have created a database, we need to decide who gets to use it.
This is done by granting permissions for a user to use the database. This
has a simplified syntax of:
GRANT
ON
TO
[IDENTIFIED BY ]
[WITH GRANT OPTION]
Where the items in square brackets are optional. The most common use is to
give ALL PRIVILEGES on a database to a local user who has to use a password
to access the database (in this case vworks).
mysql> GRANT ALL PRIVILEGES
-> ON vworks.*
-> TO newuser@localhost
-> IDENTIFIED BY 'newpassword';
If you are creating a database for use with the rest of the Virtual Workshops
you should use this statement, substituting your username and password of
choice. There are some other options we will look at. To restrict the user
to manipulating data (rather than table or database structures) the statement
would be altered to:
mysql> GRANT SELECT,INSERT,UPDATE,DELETE
-> ON vworks.*
-> TO newuser@localhost
-> IDENTIFIED BY 'newpassword';
So that the user can only change the data using SELECT,INSERT,UPDATE or DELETE
statements. If you wished to give a non-local user permissions on the database
(for use with remote clients) then you could designate an IP or host address
from which the user can connect:
mysql> GRANT ALL PRIVILEGES
-> ON vworks.*
-> TO newuser@192.168.0.2
-> IDENTIFIED BY 'newpassword';
Now a user on the machine '192.168.0.2' can connect to the database. To allow
a user to connect from anywhere you would use a wildcard '
%'
mysql> GRANT ALL PRIVILEGES
-> ON vworks.*
-> TO newuser@'%'
-> IDENTIFIED BY 'newpassword';
You could even decide that a user doesn't need a password if connecting from
a certain machine.
mysql> GRANT ALL PRIVILEGES
-> ON vworks.*
-> TO newuser@192.168.0.2
But I think it is sometimes good to provide a password anyway. Finally we'll
look at the WITH GRANT OPTION condition. This allows the user to give others
privileges to that database:
mysql> GRANT ALL PRIVILEGES
-> ON vworks.*
-> TO newuser@localhost
-> IDENTIFIED BY 'newpassword'
-> WITH GRANT OPTION;
This would allow the user 'newuser' to log into the database and give their
friend privileges to SELECT,INSERT,UPDATE or DELETE from the database.
mysql> GRANT SELECT,INSERT,UPDATE,DELETE
-> ON vworks.*
-> TO friend@localhost
-> IDENTIFIED BY 'friendpass';
The WITH GRANT OPTION usually signifies ownership although it is worth noting
that no user can GRANT more privileges that they themselves possess.
Revoking privileges
Revoking privileges is almost identical to granting them as you simply substitute
RE VOKE.... FROM for GRANT....TO and omit any passwords or other options.
For example to REVOKE the privileges assigned to a user called '
badvworks':
mysql> REVOKE ALL PRIVILEGES
-> ON vworks.*
-> FROM badvworks@localhost;
Or just to remove UPDATE, INSERT and DELETE privileges to that data cannot
be changed.
mysql> REVOKE INSERT,UPDATE,DELETE
-> ON vworks.*
-> FROM badvworks@localhost;
Backing Up Data
There are several methods we can use to backup data. We are going to look
at a couple of utilities that come with MySQL: mysqlhotcopy and mysqldump.
mysqlhotcopy
mysqlhotcopy is a command line utility written in Perl that backs up (to
a location you specify) the files which make up a database. You could do this
manually, but mysqlhotcopy has the advantage of combining several different
commands that lock the tables etc to prevent data corruption. The syntax (as
ever) first.
$ mysqlhotcopy -u -p /backup/location/
Which SHOULD copy all the tables (*.frm, *.MYI, *.MYD) into the new directory
- the script does require the DBI perl module
though. To restore these backup files simply copy them back into your MySQL
data directory.
mysqldump
This is my preferred method of backing up. This outputs the table structure
and data in series of SQL commands stored in a text file. The simplified syntax
is
$ mysqldump -u -p [] > file.sql
So for example to back up a 'vworks' database which may have been created
by completing the workshops:
$ mysqldump -u admin -p vworks > vworks.sql
After entering the password a 'vworks.sql' file should be created. When you
look at this file you can actually see that the data and structures are stored
as a series of SQL statements. e.g.:
-- MySQL dump 8.22
--
-- Host: localhost Database: vworks
---------------------------------------------------------
-- Server version 3.23.52
--
-- Table structure for table 'artist'
--
CREATE TABLE artist (
artistID int(3) NOT NULL auto _increment,
name varchar(20) default NULL,
PRIMARY KEY (artistID)
) TYPE=MyISAM;
--
-- Dumping data for table 'artist'
--
INSERT INTO artist VALUES (1,'Jamiroquai');
INSERT INTO artist VALUES (2,'Various');
INSERT INTO artist VALUES (3,'westlife');
INSERT INTO artist VALUES (4,'Various');
INSERT INTO artist VALUES (5,'Abba');
And so on for the other tables.
We could also have chosen to output just one table from the database, for
example the artist table:
$ mysqldump -u admin -p vworks artist > artist.sql
We could even dump all the databases out (providing we have the permissions).
$ mysqldump -u admin -p --all-databases > alldb.sql
Restoring a Dump
Restoring a dump depends on what you have actually dumped. For example to
restore a database to a blank database (perhaps having transferred the sql
file to another machine) it is fairly simple.
$ mysql -u admin -p vworks < vworks.sql
...or to add a non-existent table to a database...
$ mysql -u admin -p vworks < artist.sql
However, what happens if we want to restore data to an existing database
(perhaps a nightly backup) ? Well we would have to add other options:
The equivalent of overwriting the existing tables would be telling the dump
to automatically drop any tables that exist before restoring the stored tables.
This is done with the ' --add-drop-table ' option added to our statement.
$ mysqldump -u admin -p --add-drop-table vworks > vworks.sql
Then restore like normal:
$ mysql -u admin -p vworks < vworks.sql
The reverse might also be true. We may wish to create the database if it
doesn't already exist. To do this we use the '--databases' option
to specify the database we wish to back up (you can
specify more than one).
$ mysqldump -u admin -p --databases vworks > vworksDB.sql
This will create additional SQL statements at the start of each database
that CREATEs the dumped database (checking first to see if it does indeed
exist) then USEing that database to import the table data into.
-- Current Database: vworks
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ vworks;
USE vworks;
Again we can resort like normal, but of course this time we can omit the
database name.
$ mysql -u admin -p < vworksDB.sql
Optimising a dump
There are a couple of options that are sometimes worth including when backing
up and restoring large databases.
The first option is '--opt', this is used override the
mysql server's normal method of reading the whole result set into memory
giving
a faster dump. Example:
$ mysqldump -u admin -p --opt vworks > vworks.sql
The second option is '-a' or '-all' (either
will do). Which also optimises the dump by creating mysql specific CREATE
statements that speeds up the restore:
$ mysqldump -u admin -p --all vworks > vworks.sql
Using mysqldump to copy databases.
It is possible to combine a dump and a restore on one line by using a pipe
'|' to pass the output of the dump directly to mysql basically
bypassing the file. This may initially seem a bit redundant, but we can use
this method
to copy a database to another server or even create a duplicate copy.
For example to copy the 'vworks' database to a mysql server
called 'remote.server.com':
$ mysqldump -u admin -p --databases vworks | \
> mysql -u backup -p MyPassword -h remote.server.com
Note: the"\" at the end of the first line means you wish to contine the command on another line before executing it.
You may, in certain circumstances, wish to make a copy of live data so that
you can test new scripts and 'real world' data. To do this you would need
to duplicate a local database. First create the duplicate database:
mysql> CREATE DATABASE vworks2;
Then once appropriate privileges have been assigned we can copy the tables
from the first table into the second.
$ mysqldump -u admin -p vworks | mysql -u backup -p MyPassword vworks2
Notice in both these examples the second half of the line (after the pipe)
passes the password as part of the connection statement. This is because
asking for two separate passwords at the same time breaks most shells. That
is why
I have used a 'backup' user who can be granted permissions and have them
revoked as necessary.
Miscellaneous Leftovers
This final bit includes a few brief tricks that weren't really appropriate
to include elsewhere, but are still worth noting.
Remote Client Connection
If you have set the privileges to allow remote connections
to a database, you can connect from a remote command line client by using
the -h flag:
$ mysql -u -p -h
For example to connect to a fictional vworks.keithjbrown.co.uk server:
$ mysql -u admin -p -h vworks.keithjbrown.co.uk
Non-Interactive Commands
Sometimes you may wish to just do a quick look up on a table without the
hassle of logging into the client, running the query then logging back out
again. You can instead just type one line using the ' -e ' flag. For example:
$ mysql -u admin -p vworks -e 'SELECT cds.artist, cds.title FROM cds'
Enter password:
+------------+------------------------------+
| artist | title |
+------------+------------------------------+
| Jamiroquai | A Funk Odyssey |
| Various | Now 49 |
| westlife | westlife |
| Various | Eurovision Song contest 2001 |
| Abba | Abbas Greatest Hits |
+------------+------------------------------+
