This is a collection of notes regarding the actions I took to prepare the MySQL Database (on my Linux Server, carson-city.marlboro.edu) for the Internet Programming Languages II, March 10th & 24th DBI/JDBC assignmnents, due 7-April.
I orignally had trouble with the syntax used for accessing the IPL2 'cgi_data' database on bob.marlboro.edu. Once I removed the space between the "-p" option flag and the password value, I connected to the class database from my student account on bob:
bash-2.04$ mysql cgi_data -u cgi -ppasswd mysql> show tables; +--------------------+ | Tables_in_cgi_data | +--------------------+ | critters | | munchies | | patient | | stocks | | stuff | +--------------------+ 5 rows in set (0.00 sec) mysql>
I wanted my own MySQL database set up on my Linux Server system to use for the Perl & Java DBI/JDBC programming assignments. I created a database called vw (vw is short for Virtual Wanderer, the name of my proposed Capstone Project).
Below are the commands I used to create or delete (drop) a database. Following the create and drop, I used some SQL script files that I'd created for the 10-March DB1 class to populate the vw database. The SQL script files contained CREATE TABLE... and INSERT INTO ... SQL commands (see below):
#### Create & Delete "VW" database [root@carson-city]# cd /usr/local/mysql/bin [root@carson-city bin]# ./mysqladmin create vw [root@carson-city bin]# ./mysqladmin drop vw Dropping the database is potentially a very bad thing to do. Any data stored in the database will be destroyed. Do you really want to drop the 'vw' database [y/N] y Database "vw" dropped [root@carson-city bin]# [root@carson-city bin]# ./mysqladmin create vw [root@carson-city bin]# ./mysql vw mysql> show tables; Empty set (0.00 sec) mysql> exit [root@carson-city bin]# ./mysql -t vw < /home/kid/sandbox/capstone/in_create.sql > /home/kid/sandbox/capstone/out_create.txt
Next, I wanted to be able to access the tables in my Linux MySQL Database from specific user accounts on the local Linux system or from a remote host. The following shows how I set up the MySQL privileges for bluhrs@localhost and bluhrs@bob.marlboro.edu (running from non-root accounts on carson-city or bob):
[root@carson-city bin]# ./mysql mysql
mysql> GRANT ALL PRIVILEGES on *.* TO bluhrs@localhost
-> IDENTIFIED BY 'passwd' WITH GRANT OPTION;
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT ALL PRIVILEGES on *.* TO bluhrs@"%"
-> IDENTIFIED BY 'passwd' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+--------------------------+--------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+
| 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 |
+--------------------------+--------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+
| localhost | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| carson-city.marlboro.edu | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| localhost | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N |
| carson-city.marlboro.edu | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N |
| localhost | bluhrs | 72ac4e3130dc61f4 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| % | bluhrs | 72ac4e3130dc61f4 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
+--------------------------+--------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+
Actually, you'll notice that the way the privileges were granted, connections from bluhrs are accepted from any host as long as the username bluhrs and the corresponding passwd are supplied. This isn't necessarily the most secure arrangement.
Remember, the account that the mysql command is run from is not the same as the MySQL username supplied when accessing the database.
Also, in order to run mysql from a normal user account (kid) on my Linux server, I had to place a symbolic link in that user account's bin area. This has nothing to do with privileges and everything to do with finding the mysql command via the definition of the PATH environment variable.
# pwd /home/kid/bin # ln -s /usr/local/mysql/bin/mysql mysql
Now, I was able to access the sample "vw" database that I'd created from either kid@localhost or bluhrs@bob.marlboro.edu accounts, as seen in these commands:
[kid@carson-city]$ mysql vw -u bluhrs -ppasswd ... bash-2.04$ mysql vw -h carson-city -u bluhrs -ppasswd mysql> show tables; +--------------+ | Tables_in_vw | +--------------+ | Waypoints | +--------------+ 1 row in set (0.00 sec) mysql>
The ability to use SQL Script files to execute SQL commands was show above (of the form: mysql -t vw < /home/kid/sandbox/capstone/in_create.sql > /home/kid/sandbox/capstone/out_create.txt ).
The following show the execution of CREATE, INSERT, UPDATE and DELETE commands. BE VERY CAREFUL with the syntax of the UPDATE and DELETE commands .
bash-2.04$ mysql vw -h carson-city -u bluhrs -ppasswd
mysql> CREATE TABLE Views
-> (view_id INT PRIMARY KEY, assoc_waypoint_id INT,
-> heading_from_waypoint INT, image_file_path CHAR(32),
-> descript_URL CHAR(128), last_edit TIMESTAMP(12) );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO Waypoints VALUES
-> (1, "LCAPIS", 44, 38.222, 73, 15.977, "wid_001.img",
-> "http://vwander/wpts/lccapis.html", 2001032022015);
mysql> UPDATE Waypoints SET image_file_path = 'wid_000.jpg'
-> WHERE waypoint_id='99';
mysql> select waypoint_id, waypoint_name, image_file_path from Waypoints;
+-------------+---------------+-----------------+
| waypoint_id | waypoint_name | image_file_path |
+-------------+---------------+-----------------+
| 6 | LCBIMR | wid_999.jpg |
.....
| 5 | LCBICS | wid_999.jpg |
| 99 | TESTXX | wid_000.jpg |
+-------------+---------------+-----------------+
mysql> DELETE FROM Waypoints WHERE waypoint_id='6';
mysql> select waypoint_id, waypoint_name, image_file_path from Waypoints;
+-------------+---------------+-----------------+
| waypoint_id | waypoint_name | image_file_path |
+-------------+---------------+-----------------+
| 1 | LCAPIS | wid_999.jpg |
......
| 5 | LCBICS | wid_999.jpg |
| 99 | TESTXX | wid_000.jpg |
+-------------+---------------+-----------------+
Instead of loading tables with data using SQL command script files, data contained in comma-separated-value (CSV) text file can be loaded using the LOAD DATA LOCAL INFILE command:
bash-2.04$ emacs wayptdata.txt ! create a text file containing:
99, TESTXX, 49, 49.9, 99, 99.9, wid_00x.jpg, "http://www.bob.edu", 00
bash-2.04$ mysql vw -h carson-city -u bluhrs -ppasswd
mysql> LOAD DATA LOCAL INFILE '/home/bluhrs/html/db1/wayptdata.txt' INTO TABLE Waypoints
-> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\n';
Query OK, 1 row affected (0.01 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 1
mysql>
In the process of debugging a prototype CGI-script on bob.marlboro.edu, I mistakenly thought I that perphaps the CGI-scripts ran as a different "user" (forgetting that Linux "user/process" is not the same as mySQL "user"). My problem that I just wasn't supplying the DB-host name. Anyway, here's how I did some additional edits adding another set of user privileges, then modifying and deleting privileges:
[root@carson-city bin]# ./mysql mysql
# --- this allows access from anyone (' ') connecting from bob
# --- passwd='passwd1' must be supplied ------------------
mysql> GRANT ALL PRIVILEGES on *.* TO ' '@bob.marlboro.edu
-> IDENTIFIED BY 'passwd1' WITH GRANT OPTION;
mysql> SET PASSWORD FOR ' '@bob.marlboro.edu = PASSWORD('passwd2');
mysql> DELETE FROM user WHERE host='bob.marlboro.edu';
# --- that was too likely to screw up looking up other accesses
# --- so from bob.marlboro.edu, only cgi with a passwd allowed
mysql> GRANT ALL PRIVILEGES on *.* TO cgi@bob.marlboro.edu
-> IDENTIFIED BY 'bhl2001' WITH GRANT OPTION;
mysql> select host, user, password from user;
+--------------------------+--------+------------------+
| host | user | password |
+--------------------------+--------+------------------+
| localhost | root | |
| carson-city.marlboro.edu | root | |
| localhost | | |
| carson-city.marlboro.edu | | |
| localhost | bluhrs | 72ac4e3130dc61f4 |
| % | bluhrs | 72ac4e3130dc61f4 |
| bob.marlboro.edu | cgi | 72ac4e3130dc61f4 |
+--------------------------+--------+------------------+
Refer to the appropriate sections in the MySQL Reference Manual for more information.