1 - Kasetsart University



Create a World Database in MySQL

1. Install MySQL on your computer.

has instructions for this.

For windows, just run the installer. On Linux, use package manager or Synaptic.

2. Download these source files containing data for the world database. This is my version of the World sample database supplied by MySQL; I corrected some inconsistencies in the data.

world-schema-mysql.sql

City-data.sql

Country-data.sql

3. Use the mysql command line utility to connect to the database server as root. You can also use the MySQL Workbench (GUI tool) for this.

cmd> mysql -u root -p

Password: ********

mysql>

4. Create a new world database and open it:

mysql> CREATE DATABASE world;

mysql> USE world;

5. Create tables using the schema file. (You can open it with a text editor to view the SQL commands.)

mysql> SOURCE world-schema-mysql.sql;

6. Add cities, countries, and country language data to the database using SQL statements in the other two files:

mysql> SOURCE Country-data.sql;

Add the City data using the file city-data.sql:

mysql> SOURCE City-data.sql;

7. Verify that the correct number of countries, cities, and languages were added:

mysql> SELECT count(*) FROM Country;

239

mysql> SELECT count(*) FROM City;

4079

mysql> SELECT count(*) FROM Countrylanguage;

986 (answer may differ slightly.)

8. Create a user with read-write permission on the database, so you don't need to use the MySQL "root" user. There are two cases:

8.1 Allow access only from the local computer (preferred if your application that uses the database will run on the same computer as MySQL). The user name is "nerd" with password "secret".

mysql> GRANT ALL on world.*

TO 'nerd'@'localhost' IDENTIFIED BY 'secret';

To allow the user to query, insert, update, and delete data but not modify the database schema or indices, use:

mysql> GRANT SELECT,INSERT,UPDATE,DELETE on world.*

TO 'nerd'@'localhost' IDENTIFIED BY 'secret';

8.2 Allow access via the network. You must also configure the MySQL server to allow network connections.

mysql> GRANT ALL on world.*

TO 'nerd'@'%' IDENTIFIED BY 'secret';

To create a user with read-only access to the world database, use:

mysql> GRANT SELECT on world.* TO 'clueless'@'localhost'

IDENTIFIED BY 'anothersecret';

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download