MySQL running on Linux Server
-----------------------------

MySQL_Server.txt
Last updated: AHD, Monday 17th November 2008, 7:51 PT


On server go to webadmin:

https://localhost:10000/

Servers -> MySQL Database Server -> Global Options -> Change Admin Password -> to same as pw for root for sudo -s
otherwise the root password for entering MySQL is blank for root.

Remote (from Windows machine) by SSH shell - login as anne2:

Login to MySQL server as root:

anne2@server:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor. 

mysql> quit
Bye

Login to MySQL database "adawson" as root:

anne2@server:~$ mysql -h localhost -u root -p adawson
Enter password: 

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 112 to server version: 5.0.22-Debian_0ubuntu6.06.10-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show tables;
+-------------------+
| Tables_in_adawson |
+-------------------+
| 165A_PQ1_FA06     | 
| BUSI237_PQ1       | 
| example_test_01   | 
| q_table           | 
+-------------------+
4 rows in set (0.00 sec)

mysql> 
[see p364 of Web Programming in Python - Techniques for integrating Linux, Apache and MySQL by Thiruvathukal et al, 
Prentice Hall, 2002, ISBN: 0-13-041065-9]

mysql> CREATE TABLE Books (title VARCHAR(20), author VARCHAR(20),subject VARCHAR(20), isbn VARCHAR(15));
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+-------------------+
| Tables_in_adawson |
+-------------------+
| 165A_PQ1_FA06     | 
| BUSI237_PQ1       | 
| Books             | 
| example_test_01   | 
| q_table           | 
+-------------------+
5 rows in set (0.00 sec)

mysql> 
mysql> LOAD DATA LOCAL INFILE 'books.data' INTO TABLE Books LINES TERMINATED BY '\r\n';
Query OK, 3 rows affected (0.02 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> 

(note - books.data is in /var/www/ on server)

mysql> select * from Books;
+----------------------+-----------------+------------------+------------+
| title                | author          | subject          | isbn       |
+----------------------+-----------------+------------------+------------+
| Web Programming      | Deitel et al    | Computer Science | 1234567890 | 
| Internet Programming | Dawson, A.      | Computer Science | 1122334455 | 
| GIS                  | Fitzpatrick, K. | Geography        | 1213141516 | 
+----------------------+-----------------+------------------+------------+
3 rows in set (0.00 sec)

mysql> quit
Bye

[In webadmin -> Servers -> MySQL Database Server -> Global Options -> User Permissions
set anne2 wirth server host all permissions and pw set to regular one]

anne2@server:~$ mysql -h localhost -u anne2 -p
Enter password: (enter regular password)
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 203 to server version: 5.0.22-Debian_0ubuntu6.06.10-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use adawson  (selects database adawson on MySQL server)
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed  (changed it while in concurrent root session)
mysql> show tables;
+-------------------+
| Tables_in_adawson |
+-------------------+
| 165A_PQ1_FA06     | 
| BUSI237_PQ1       | 
| Books             | 
| example_test_01   | 
| q_table           | 
+-------------------+
5 rows in set (0.00 sec)

mysql> select * from Books;
+----------------------+-----------------+------------------+------------+
| title                | author          | subject          | isbn       |
+----------------------+-----------------+------------------+------------+
| Web Programming      | Deitel et al    | Computer Science | 1234567890 | 
| Internet Programming | Dawson, A.      | Computer Science | 1122334455 | 
| GIS                  | Fitzpatrick, K. | Geography        | 1213141516 | 
+----------------------+-----------------+------------------+------------+
3 rows in set (0.00 sec)

mysql> 

created new user anne3mysql to use just for mysql php scripts - access to mysql server over web

set home dir as /var/www as per anne2