Basics of Mysql

MYSQL  is the world's most popular open source database.

To start, stop, and restart MySQL after boot time using the service commands.

# service mysqld start/stop/restart.

You can test whether the mysqld process is running with

# pgrep mysqld

MySQL listens on all your interfaces for database queries from remote MySQL clients. You can see this using netstat -an. Your server will be seen to be listening on IP address 0.0.0.0 (all) on TCP port 3306.

# netstat -lpn | grep mysql

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1611/mysqld

unix 2 [ ACC ] STREAM LISTENING 5474 1611/mysqld /var/lib/mysql/mysql.sock

The Configuration File

The /etc/my.cnf file is the main MySQL configuration file

Creating a MySQL "root" Account

1. Make sure MySQL is started.

2. Use the mysqladmin command to set the MySQL root password.

# mysqladmin -u root password new-password

Accessing MySQL

# mysql -p

Enter password:

Creating and Deleting MySQL Databases

Create database data:

mysql> create database data;

Query OK, 1 row affected (0.00 sec)

mysql>

Delete Database data:

mysql> drop database data;

Query OK, 0 rows affected (0.00 sec)

mysql>

Creating User

mysql> create user rekha@localhost identified by "rekha";

Query OK, 0 rows affected (0.00 sec)

mysql>

Granting Privileges to Users

We have to create a MySQL username and password with privileges to access the database.

sql> grant all privileges on database.* to username@"servername" identified by 'password';

The next step is to write the privilege changes to the mysql.sql database using the flush privileges command.

sql> flush privileges;

MySQL Database Backup

The syntax for backing up a MySQL database is as follows: mysqldump --add-drop-table -u [username] -p[password] [database] > [backup_file]

# mysqldump database > database.sql

MySQL Database Restoration

The syntax for restoring a MySQL database is: mysql -u [username] -p[password] [database] < [backup_file]

# mysql database < database.sql

Common MySQL Commands

1 Login As The Database User:

# mysql -u mysqluser -p passwd

2.List all your MySQL databases:

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| test |

+--------------------+

3 rows in set (0.22 sec)

mysql>

3. select a Database

mysql> use test

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

mysql>

4 Show Tables in Database

mysql> show tables ;

+----------------+

| Tables_in_test |

+----------------+

| login |

| login1 |

| test |

| user_db |

+----------------+

4 rows in set (0.00 sec)

mysql>

5. MySQL Database's Table Structure

mysql> desc login1;

+-------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| name | char(10) | YES | UNI | NULL | |

| age | int(11) | YES | | NULL | |

| phone | varchar(15) | YES | | NULL | |

| sex | varchar(10) | YES | | NULL | |

+-------+-------------+------+-----+---------+----------------+

5 rows in set (0.03 sec)

mysql>

6. Viewing The Contents Of A Table

mysql> select * from test;

+------+-------+

| id | name |

+------+-------+

| 1 | hello |

+------+-------+

1 row in set (0.07 sec)

mysql> [/code]

You can specify range using : SELECT * FROM `your_table` LIMIT 0, 10

7. Delete a table

mysql> drop table 'table_name';

Query OK, 0 rows affected (0.08 sec)

mysql>

8. Create Table

mysql> CREATE TABLE authors (id INT, name VARCHAR(20), email VARCHAR(20));

9. Insert to Table

mysql> INSERT INTO authors (id,name,email) VALUES(1,"Vivek","xuz@abc.com");

10. Update Table.

mysql> update authors set name="test" where id=1;