Database Concepts

Feb

Filed Under (Articles, MySQL) by WebScHoLaR on 19-02-2008

Tagged Under : database, mysql

A database is a collection of data organized in a particular way. Databases are managed by DBMS (Database Management Systems) like MySQL, PostGreSQL, Oracle, Microsoft SQL Server, IBM DB2 etc. SQL is the language used to query all databases. It’s simple to learn and appears to do very little but is the heart of a successful database application.

BASIC SQL COMMANDS:

There are four basic commands which are the workhorses for SQL and figure in almost all queries to a database.

• INSERT – Insert Data
• DELETE – Delete Data
• SELECT – Pull Data
• UPDATE – Change existing Data

In web hosting Industry, MySQL is the DBMS that is mostly used so this manual will be focusing more on MySQL that how it works and how the Administrators can administer the MySQL.

MYSQL CONFIGURATION FILE my.cnf:

MySQL can read startup options from option files (also sometimes called configuration files). Option files provide a convenient way to specify commonly used options so that they need not be entered on the command line each time you run MySQL. This file, entitled my.cnf, stores default startup options for both the server and for clients. On Unix/Linux, MySQL read startup options from the following files:

DATADIR represents the path to the directory in which the server-specific my.cnf file resides. Empty lines in option files are ignored. Non-empty lines can take any of the following forms:

• #comment, ;comment
Comment lines start with “#” or “;”. As of MySQL 4.0.14, a “#” comment can start in the middle of a line as well.

• [group]
group is the name of the program or group for which you want to set options. After a group line, any option-setting lines apply to the named group until the end of the option file or another group line is given.

• opt_name
This is equivalent to –opt_name on the command line.

• opt_name=value
This is equivalent to –opt_name=value on the command line. In an option file, you can have spaces around the “=” character, something that is not true on the command line. As of MySQL 4.0.16, you can enclose the value within double quotes or single quotes. This is useful if the value contains a “#” comment character or whitespace.

• set-variable = var_name=value
Set the program variable var_name to the given value.

Here is a typical global option file:

[client]
port=3306
[mysqld]
set-variable = max_connections=500
set-variable = max_allowed_packet=8M
safe-show-database

MYSQLADMIN:

The mysqladmin program is used to administrate various aspects of the MySQL database server. Using it, the administrator can perform tasks such as: create and delete databases, shutdown the database server, update the privilege tables, and view running MySQL processes. The general syntax is:

mysql> mysqladmin [options] command(s)

Where [options] can be one or a series of options used in conjunction with the mysqladmin program, and [database] is the name of the database to use. Since it is assumed to be the reader’s first time using the MySQL monitor, take a moment to review all offered options by executing the following command:

mysql> mysqladmin –help

This produces a long list of options that can be used in conjunction with the mysqladmin program. As a demonstration of how these options are used, let’s use mysqladmin to create a new database named widgets, which will be used throughout the remainder of this article to demonstrate various other useful MySQL functions.

DATABASE BACKUP:

The utility mysqldump provides a rather convenient way to dump existing data and table structures. Note that while mysqldump is not the most efficient method for creating backups (mysqlhotcopy is, described next), it does offer a convenient method for copying data and table structures which could then be used to repopulate another SQL server, that server not even necessarily being MySQL.

The function mysqldump can be used to backup all databases, several databases, one database, or just certain tables within a given database. In this section, the syntax involved with each scenario is provided, followed with a few examples.

Using mysqldump to backup just one database:

mysql> mysqldump [options] db_name

Using mysqldump to backup several tables within a database:

mysql> mysqldump [options] db_name table1 table2 . . . tableN

Using mysqldump to backup several databases:

mysql> mysqldump [options] –databases [options] db_name1 db_name2 . . . db_nameN

Using mysqldump to backup all databases:

mysql> mysqldump [options] –all-databases [options]

The options can be viewed by executing the following command:

mysql> mysqldump –help

DATABASE RESTORE:

mysql utility is used to restore the database dumps. For example:

mysql> mysql db_name < database_backup.sql

From time to time mySQL databases can get corrupt, whether it due to lack of disk space, power failure, or just an error.

REPAIRING MYSQL TABLES/DATABASES:

MYISAMCHK:

NOTE: Please note that we assume your mySQL data directory is /var/lib/mysql.

The simplest way to use myisamchk is:

cd /var/lib/mysql/DBNAME
myisamchk *.MYI

Couples of other useful ways are:

myisamchk tbl_name

This finds 99.99% of all errors. What it cannot find is corruption that involves only the data file (which is very unusual). If you want to check a table, you should normally run myisamchk without options or with the -s (silent) option.

myisamchk -m tbl_name

This finds 99.999% of all errors. It first checks all index entries for errors and then reads through all rows. It calculates a checksum for all key values in the rows and verifies that the checksum matches the checksum for the keys in the index tree.

myisamchk -e tbl_name

This does a complete and thorough check of all data (-e means “extended check”). It does a check-read of every key for each row to verify that they indeed point to the correct row. This may take a long time for a large table that has many indexes. Normally, myisamchk stops after the first error it finds. If you want to obtain more information, you can add the -v (verbose) option. This causes myisamchk to keep going, up through a maximum of 20
errors.

myisamchk -e -i tbl_name

This is like the previous command, but the -i option tells myisamchk to print additional statistical information. In most cases, a simple myisamchk command with no arguments other than the table name is sufficient to check a table.

MYSQLCHECK:

The mysqlcheck client checks, repairs, optimize, and analyze tables. mysqlcheck is similar in function to myisamchk, but works differently. The main operational difference is that mysqlcheck must be used when the mysqld server is running, whereas myisamchk should be used when it is not. The benefit of using mysqlcheck is that you do not have to stop the server to check or repair your tables.

There are three general ways to invoke mysqlcheck:

mysql> mysqlcheck [options] db_name [tables]

mysql> mysqlcheck [options] –databases db_name1 [db_name2 db_name3...]

mysql> mysqlcheck [options] –all-databases

If you do not name any tables following db_name or if you use the –databases or –all-databases option, entire databases are checked.
mysqlcheck has a special feature compared to other client programs. The default behavior of checking tables (–check) can be changed by renaming the binary. If you want to have a tool that repairs tables by default, you should just make a copy of mysqlcheck named mysqlrepair, or make a symbolic link to mysqlcheck named mysqlrepair. If you invoke mysqlrepair, it repairs tables.

The following names can be used to change mysqlcheck default behavior:

mysqlrepair

The default option is –repair

mysqlanalyze

The default option is –analyze

mysqloptimize

The default option is –optimize

I am proceeding further with lots of examples with SQL using MySQL that make understanding the process of constructing SQL queries easy and also using MySQL and to illustrate the mechanism of storing and retrieving information from databases.

From the shell prompt (Linux):

mysqladmin create db_contacts;

This will create an empty database called “db_contacts“. You can also create the database after login to mysql as:

mysql> create database db_contacts;

Now run the command line tool “mysql” and from the mysql prompt do the following:

mysql> use db_contacts;

(You’ll get the response “Database changed”). This means that now you will be able to make changes to the database ‘db_contacts’.

The following commands entered into the MySQL prompt will create the table in the database.

mysql> CREATE TABLE names (contact_id SMALLINT NOT NULL
AUTO_INCREMENT PRIMARY KEY, FirstName CHAR(20), LastName CHAR(20),
BirthDate DATE);

mysql> CREATE TABLE address(contact_id SMALLINT NOT NULL PRIMARY KEY,
StreetAddress CHAR(50), City CHAR(20), State CHAR(20), Zip CHAR(15), Country
CHAR(20));

To see the tables inside the database:

To see the columns within a particular table:

Inserting data, one row at a time:

mysql> INSERT INTO names (FirstName, LastName, BirthDate) VALUES
(Web,’ScHoLaR’,’1984-01-01′);
Query OK, 1 row affected (0.00 sec)

Let’s see what the data looks like inside the table. We use the SELECT command for this.

Try another handy command called ‘DESCRIBE’.

Now lets populate the other tables. Observer the syntax used.

Let’s look at how MySQL stores the Data.

To do this, execute the following command from the Linux shell prompt.

mysqldump db_contacts > db_contacts.sql

The above command will generate the backup of database ‘db_contacts’ into a human readable dump file ‘db_contacts.sql’.

Note: The reverse operation for this command i.e. restoring the database from backup is:

mysql db_contacts < db_contacts.sql

The above command will restore the database from the dump file ‘db_contacts.sql’.

The dump file generated is a text file that contains all the data and SQL instruction needed to recreate the same database. As you can see, the SQL here is slightly different than what was typed in. Don’t worry about this. It’s all good ! It would also be obvious that this is a good way to backup your stuff.

# MySQL dump 8.2
#
# Host: localhost Database: db_contacts
#——————————————————–
# Server version 4.1

#
# Table structure for table ‘address’
#

CREATE TABLE address (
contact_id smallint(6) DEFAULT ’0′ NOT NULL,
StreetAddress char(50),
City char(20),
State char(20),
Zip char(10),
Country char(20),
PRIMARY KEY (contact_id)
);

#
# Dumping data for table ‘address’
#

INSERT INTO address(contact_id, StreetAddress, City, State, Zip, Country)
VALUES (’1′, F-12, Booyah’, ‘Planet Earth’, ‘Solar System’, ’123456′, ‘Universe’);

#
# Table structure for table ‘names’
#

CREATE TABLE names (
contact_id smallint(6) DEFAULT ’0′ NOT NULL auto_increment,
FirstName char(20),
LastName char(20),
BirthDate date,
PRIMARY KEY (contact_id));

#
# Dumping data for table ‘names’
#

INSERT INTO names (FirstName, LastName, BirthDate) VALUES
(Web,’ScHoLaR’,’1984-01-01′);

PHPMYADMIN:

phpMyAdmin is an open source Graphical tool written in PHP intended to handle the administration of MySQL over the Internet. Currently it can create and drop databases, create/drop/alter tables, and delete/edit/add fields, execute any SQL statement, and manage keys on fields.

You can check its Demo at:

http://demo.phpmyadmin.net/

Note: You have full control over MySQL server here. Log in as root without password.

No Comments

You must be logged in to post a comment.

WebScHoLaR Dot NET
A blog discussing common issues of Linux, cPanel/WHM, Plesk, DirectAdmin, InterWorx