File:mysql_readme.txt   
First created: Sunday 25th November 2012, 14:27 PT, AD
Updated: Sunday 25th November 2012, 15:53 PT, AD
Last updated: Tuesday 18th August 2015, 6:54 PT, AD


MySql Documentation:
--------------------

http://dev.mysql.com/doc/refman/5.1/en/index.html
http://dev.mysql.com/doc/refman/5.1/en/tutorial.html




From a clean MySQL set up after a new clean server installation:
----------------------------------------------------------------

root@AD4:/var/www# mysql -u root -p
(you will be asked for a password, type in the regular (sudo -s) root password)







To get mysql status (version and more:
--------------------------------------

mysql> status;






To create a new user (anne) on MySql:
-------------------------------------

mysql> CREATE USER 'anne'@'localhost' IDENTIFIED BY 'type_password_here';
(N.B. keep the quote marks in)

mysql> exit
Bye
root@AD4:/var/www# mysql -u anne -p
Enter password: 
(the password will now work for anne)
mysql> exit
Bye




Changing the MySql root password:
---------------------------------

root@AD4:/var/www# mysql -u root -p 
mysql> use mysql;
mysql> update user set password=PASSWORD("not_the_real_password_see_email_25Nov2012") where User='root';
(N.B. keep the quote marks in)
mysql> flush privileges;
mysql> quit






To display the default databases (after a clean installation):
-------------------------------------------------------------

(Logged in as user root)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.00 sec)

mysql> 


N.B. The mysql database describes user access privileges.








Creating a database called adawson:
-----------------------------------

(Logged in as user root)

mysql> create database adawson;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| adawson            |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

mysql> 




Using a database:
----------------

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| adawson            |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)


Creating a database does not select it for use -
you must do that explicitly. 
To make the database adawson the current database, use this command:


mysql> use adawson (semicolon is optional in this case, but no harm done if you use it)
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> 




Creating a table in database adawson:
------------------------------------

mysql> use adawson;
mysql> create table books (title varchar(20), author varchar(20),subject varchar(20), isbn varchar(15));
Query OK, 0 rows affected (0.14 sec)

mysql> 

mysql> show tables;
+-------------------+
| Tables_in_adawson |
+-------------------+
| books             |
+-------------------+
1 row in set (0.00 sec)

mysql> 





Populating the books table in database adawson:
----------------------------------------------

The text file books.data contains the following tab separated data:



Web Programming	Deitel et al	Computer Science	1234567890
Internet Programming	Dawson, A.	Computer Science	1122334455
GIS	Fitzpatrick, K.	Geography	1213141516	



Each line represents one book by title, author, subject and isbn,
matching the fields in the table called books.
Each line ends with a newline character 
- apart from the last line which ends with a tab.


The default directory for mysql databases is: /var/lib/mysql


anne@AD4:~$ sudo -s
[sudo] password for anne: 
root@AD4:~# cd /var/lib/mysql
root@AD4:/var/lib/mysql# ls
AD4.pid  debian-5.1.flag  ib_logfile0  mysql
adawson  ibdata1          ib_logfile1  mysql_upgrade_info
root@AD4:/var/lib/mysql# cd adawson
root@AD4:/var/lib/mysql/adawson# ls
books.frm  books.MYD  books.MYI  db.opt
root@AD4:/var/lib/mysql/adawson# 

To copy the file books.data from the user's desktop (/home/anne/Desktop)
to the current directory:

root@AD4:/var/lib/mysql/adawson# cp /home/anne/Desktop/books.data .
root@AD4:/var/lib/mysql/adawson# ls
books.data  books.frm  books.MYD  books.MYI  db.opt
root@AD4:/var/lib/mysql/adawson# 

Now, we can open the database named adawson and read the data
from the books.data text file into the books table:

mysql> use adawson;
mysql> load data local infile '/var/lib/mysql/adawson/books.data' into table books lines terminated by '\r\n';
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> 


_______________________________________________________________________________________

*** NOTE ***

If you get this error:

"The used command is not allowed with this MySQL version"

when doing this in MySql:

load data local infile '/var/lib/mysql/adawson/books.data' into table books lines terminated by '\r\n';
The fix is below...

http://stackoverflow.com/questions/10762239/mysql-enable-load-data-local-infile

the fix:

need to update /etc/mysql/my.cnf with

local-infile=1 in the [mysql] and [mysqld] sections 

load data local infile '/var/lib/mysql/adawson/books.data' into table books lines terminated by '\r\n';

now table loads OK after the my.cnf file updated as above.

*** END OF NOTE ***
_______________________________________________________________________________________



To view all the records from the books table of the adawson database:
---------------------------------------------------------------------



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> exit

Assuming the server IP is 50.64.35.187,
you can query the above database through the following web page:
http://50.64.35.187/library.html

More information can be obtained here:
http://www.annedawson.net/LAMP.html