MySQL - Part 1

Packages required:
mysql - contains client programs, configuration files and documentation
mysql-server - contains server daemon, startup script and various administrative files and directories
mysql-devel - contains libraries and header files for development purpose

Optional packages:
php-mysql - contains a shared library to allow PHP applications access MySQL databases
mod_auth_mysql - contains tools to authorize Apache Web server access from data in a MySQL database
perl-DBD-MySQL - contains a perl interface to MySQL databases
mysql-bench - contains scripts for benchmarking MySQL databases
MySQL-python - contains a Python interface to MySQL
qt-MySQL - MySQL drivers for QT SQL classes
mysql-administrator - GUI based tool to manage MySQL databases


Server daemon: mysqld

Startup script: /etc/init.d/mysqld

Configuration file: /etc/my.cnf

Log file: /var/log/mqsqld.log

Few Tips for beginners:

1. To set password for root user in MySQL (this is different from the root password in OS):

# mysqladmin -u root password mypasswd

You need to mention this password whenever you run a mysql command.
To make it easy, you can add the password to the file /root/.my.cnf and change the permission to 600

Here is a sample content of /root/.my.cnf

[client]
mypassword


2. What happens when you start MySQL server for the first time:

It creates tables for 2 databases mysql and test. And the information is stored in the /var/lib/mysql/mysql and /var/lib/mysql/test directories, respectively.

3. How to start mqsql command ?

$ mysql -u root
Enter password: *********

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13

Server version: 5.0.37 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer

mysql>

If you want to connect to mysql on a remote server, use -h hostname or ipaddress to the end of the command.


Some mysql interface commands :

1. Shows the version, connected database, remote or local connection,etc...

mysql> status

2. To create a new database :

mysql> CREATE DATABASE mydb;

You can also create a database using mysqladmin command as below

# mysqladmin -u root -p create

3. To list all the databases on your mysql server :

mysql> SHOW DATABASES;

4. Before working on a database, make it as your current database.

mysql> USE mydb;

5. To create a table:

mysql> CREATE TABLE employee (
-> name      varchar(20)    not null,
-> empid       varchar(6)    not null,
-> phonenumber     varchar(10)    not null,
-> city           varchar(20)    not null,
-> state          varchar(20)    not null,
-> zipcode        varchar(10)    not null
-> );

6. To show all tables in your current database :

mysql> SHOW tables;

7. To display the format of the "employee" table :

mysql> DESCRIBE employee;

8. To add data to the table "employee" :

mysql> INSERT INTO employee
-> VALUES ('Mike','112233','2349871239',
-> 'Philadelphia','PA','11227');

9. To list the contents of the table:

mysql> SELECT * FROM employee;

10. To load data from a text file into a table:

$ mysql -u root -p
Enter password: *******
mysql> USE mydb
Database changed
mysql> LOAD DATA INFILE "/tmp/emp.txt" INTO TABLE employee;
Query OK, 5 rows affected (0.04 sec)
Records: 5  Deleted: 0 Skipped: 0 Warnings: 0