MySQL – Guide

1. Two types of mysql server downloads available

* MySql Community Server – For users or organizations looking to
maintain their own solutions(Free under GPL).
* MySql Enterprise Server – For businesses, public sector institutions
and users looking for the highest reliability in software and services
(Licensed).

distribution to download and use:
* Choosing which version to install
* Choosing between binary and source distributions

Choosing Which Version to Install

MySQL AB currently makes the following two versions of the MySQL database
server available on its web site:

* MySQL Standard  This is the standard version of the MySQL database
server, which includes support for both the regular, non-transactional
tables and the newer, transaction-safe tables. It is suited for
production environments requiring a stable, flexible, and robust
database engine.

* MySQL Max  This version includes the feature set of the standard
version, together with newer, more experimental capabilities. It is
not always best suited for production environments, since it usually
includes a fair share of not-completely-stable enhancements.

***Here, for Production Servers MySQL Standard is recommnded***

MySQL Installation :

Make sure you have superuser (root) privileges and user “mysql” already
exists in your system. If not, create one:
# groupadd mysql
# useradd -g mysql mysql

This will be the default user under which the MySQL server will be running.
download the source

First, download MySQL source . You need the mysql-5.0.37.tar.gz tarball file.
unpack, configure, compile

So you have downloaded the mysql-5.0.37.tar.gz file. You know the drill:
unpack, configure, make, make install:.
# tar -xzf mysql-5.0.37.tar.gz
# cd mysql-5.0.37
# ./configure –prefix=/usr/local/mysql-5.0.37 –with-charset=utf8
–with-collation=utf8_general_ci
# make
# make install

Here used the –with-charset and –with-collation options to set the
default character set and collation – otherwise it would have been the
default Swedish collation.

It is recommended creating a symbolic link called “mysql” pointing to the
MySQL installation directory, in order to make referring to it from
elsewhere easier:
# ln -s /usr/local/mysql-5.0.37/ /usr/local/mysql

This way we can always refer to MySQL installation directory as
/usr/local/mysql . The obvious advantage is that if you install PHP with
the –with-mysql=/usr/local/mysql option (see PHP 5 Installation Guide),
it won’t stop working if the name of the MySQL installation directory
changes in the future (if you upgrade your MySQL for instance).
create my.cnf file

To complete MySQL server installation, you have to create a configuration
file. It offers several security and control options (here you can limit
system resources to be used by MySQL server, set the default collation and
character set etc.). You need not to create a brand new configuration file
– there are 4 pre-made files in the support-files/ directory. Read the
information in those files to determine which one to use. For small
servers (e.g. testing servers, or servers of a limited performance),
my-small.cnf file is the best option. Copy the file of your choice to
/etc/my.cnf:
# cp support-files/my-small.cnf /etc/my.cnf
# chown root /etc/my.cnf
# chgrp root /etc/my.cnf
# chmod 644 /etc/my.cnf

We have made sure both the owner and user group of the my.cnf file are
“root” and the access privileges are properly set. Finally edit the file:
# vi /etc/my.cnf

Search for [mysqld] clause, and add immediately below it:
user = mysql

We have specified that MySQL service is to be run with user “mysql”
privileges.

If you want to use InnoDB databases (what you probably will), uncomment
(and perhaps edit) all innodb options in the my.cnf file. Save all changes
(<ESC> :wq).
additional settings

For proper functioning, MySQL needs a “mysql” database. To create this
database, simply run:
# /usr/local/mysql/bin/mysql_install_db –user=mysql

The script will create /usr/local/mysql/var/ directory containing the
necessary databases. This directory serves as a default storage for all
databases you will create. Make sure it is writable by “mysql” system
user!
start server, check it, connect

Now you are ready to start your MySQL server for the first time.
# /usr/local/mysql/bin/mysqld_safe –user=mysql &

Hit enter again to get your prompt back. The MySQL server should now be
running. To check that server is running and works properly enter
# /usr/local/mysql/bin/mysqladmin version

2. How to set the MySQL root (superuser) password

#mysqladmin -u root password ‘new-password’
#mysqladmin -u root -h linux2 password ‘new-password’

***Note: -h denotes here your local hostname of your server.***

Or use this method to set root password for MqSQL
Stay connected to MySQL and enter:
DELETE FROM mysql.user WHERE User = ”;
FLUSH PRIVILEGES;
SELECT Host, User FROM mysql.user;

Look for the record that has root in the User column and something other
than localhost in the Host column. This is the host_name.
SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘new_password’);
SET PASSWORD FOR ‘root’@’host_name’ = PASSWORD(‘new_password’);

Remember, this is the MySQL superuser for all databases

3. Nullifying MySQL root MyISAM is the default table type i’m MySQL.It
provides a simple tree structure for
quick indexing on small tables. InnoDB table, the one which is best for
huge tables, .It’s fast and effecient, but the trees are pretty large, so
it’s not good for smaller tables.password:

You can recover MySQL database server password with following five easy
steps.
Step # 1: Stop the MySQL server process.
Step # 2: Start the MySQL (mysqld) server/daemon process with the
–skip-grant-tables option so that it will not prompt for password
Step # 3: Connect to mysql server as the root user
Step # 4: Setup new root password
Step # 5: Exit and restart MySQL server

Here are commands you need to type for each step (login as the root user):
Step # 1 : Stop mysql service
# /etc/init.d/mysql stop

Step # 2: Start to MySQL server w/o password:
# mysqld_safe –skip-grant-tables &

Step # 3: Connect to mysql server using mysql client:
# mysql -u root
mysql>

Step # 4: Setup new MySQL root user password
mysql> use mysql;
mysql> update user set password=PASSWORD(”NEW-ROOT-PASSWORD”) where
User=’root’;
mysql> flush privileges;
mysql> quit

Step # 5: Stop MySQL Server:
# /etc/init.d/mysql stop

Step # 6: Start MySQL server and test it
# /etc/init.d/mysql start
# mysql -u root -p

4. Major Differences between “MyISAM & InnoDB” (Table Storage Structures)

* MyISAM is the default table type in MySQL.It provides a simple tree
structure for
quick indexing on small tables. InnoDB table, the one which is best for
huge tables.It’s fast and effecient, but the trees are pretty large, so
it’s not good for smaller tables.

* MyISAM is the default table type in MySQL, the data is stored in three
files, name.frm for table definition, name.myd for the data and name.myi
for the indexing. InnoDB is for transactional operations (for example used
in banking to “queue” queries and update the table with all the changes at
once (commit)) as well as rollback and crash recovery.

* One more difference between MyISAM and InnoDB is that InnoDB supports
transaction. You can commit and rollback with InnoDB but with MyISAM once
you issue a command it’s done.

* MyISAM does not support foreign keys where as InnoDB supports.

* Locking: MyISAM provide the table level locking means if the data in one
table has been modified  by the other table,the entire table will lock for
the next process.But INNODB provide the row level locking only the row of
the table that is being updated is locked.

* Data Types: MyISAM provide a full variety of datatypes,but INNODB doesn’t.

* Memory: MyISAM occupies less memory sapce for tables rather than InnoDB.

5. MySQL Database Backup and Restore:

You can use mysqldump to create a simple backup of your database using the
following syntax.

mysqldump -u [username] -p [databasename] > [backupfile.sql]

o -u – this is your database username
o -p – this is the password for your database
o [databasename] – the name of your database
o [backupfile.sql] – the file to which the backup should be written.
EX:
#mysqldump -u act -p actdb > actdbbak.sql
***Note: It will aprompt you the password for the user act***

For restoring the database,
mysql -u [username] -p [databasename] < [backupfile.sql]

Ex:
#mysql -u act -p actdb < actdbbak.sql
***Note: It will aprompt you the password for the user act***

Backing up only specified tables:

Syntax: mysqldump -u [username] -p [databasename] [table1 table2 ….] >
db_backup_name

If you’d like restrict the backup to only certain tables of your database,
you can also specify the tables you want to backup. Let’s say that you
want to backup only customer_master & customer_details from the Customers
database, you do that by issuing

Here i dropped table1 and table2,

mysqldump –add-drop-table -u act -p actdb table1 table2 > custback.sql

If you need to restore a database that already exists, you’ll need to use
mysqlimport command. The
syntax for mysqlimport is as follows:

mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]

6. Backing Up and Restoring using PHPMyAdmin:
To backup your MySQL database using PHPMyAdmin just follow a couple of
steps:
* Open phpMyAdmin.
* Select your database by clicking the database name in the list on
the left of the screen.
* Click the Export link. This should bring up a new screen that says
View dump of database (or something similar).
* In the Export area, click the Select All link to choose all of the
tables in your database.
* In the SQL options area, click the right options.
* Click on the Save as file option and the corresponding compression
option and then click the ‘Go’ button. A dialog box should appear
prompting you to save the file locally.

Restoring your database is easy as well as backing it up. Make the following:

* Open phpMyAdmin.
* Create an appropriately named database and select it by clicking the
database name in the list on the left of the screen. If you would like
to rewrite the backup over an existing database then click on the
database name, select all the check boxes next to the table names and
select Drop to delete all existing tables in the database.
* Click the SQL link. This should bring up a new screen where you can
either type in SQL commands, or upload your SQL file.
* Use the browse button to find the database file.
* Click Go button. This will upload the backup, execute the SQL
commands and re-create your database.

7.MySQL Commands:

To login (from unix shell) use -h only if needed.

# [mysql dir]/bin/mysql -h hostname -u root -p
Create a database on the sql server.

mysql> create database [databasename];
List all databases on the sql server.

mysql> show databases;
Switch to a database.

mysql> use [db name];
To see all the tables in the db.

mysql> show tables;
To see database’s field formats.

mysql> describe [table name];
To delete a db.

mysql> drop database [database name];
To delete a table.

mysql> drop table [table name];
Show all data in a table.

mysql> SELECT * FROM [table name];
Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];
Show certain selected rows with the value “whatever”.

mysql> SELECT * FROM [table name] WHERE [field name] = “whatever”;

Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES(‘%’,’username’,PASSWORD(‘password’));
mysql> flush privileges;
Change a users password from unix shell.

# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password ‘new-password’

Change a users password from MySQL prompt. Login as root. Set the password. Update privs.

# mysql -u root -p
mysql> SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘passwordhere’);
mysql> flush privileges;
Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.

# /etc/init.d/mysql stop
# mysqld_safe –skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD(“newrootpassword”) where User=’root’;
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start
Set a root password if there is on root password.

# mysqladmin -u root password newpassword
Update a root password.

# mysqladmin -u root -p oldpassword newpassword

Allow the user “bob” to connect to the server from localhost using the password “passwd”. Login as root. Switch to the MySQL db. Give privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by ‘passwd’;
mysql> flush privileges;
Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,’databasename’,’username’,’Y’,’Y’,’Y’,’Y’,’Y’,’N’);
mysql> flush privileges;

or

mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

mysql> DELETE from [table name] where [field name] = ‘whatever’;
Update database permissions/privilages.

mysql> flush privileges;
Delete a column.

mysql> alter table [table name] drop column [column name];

# [mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql
Dump one database for backup.

# [mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql
Dump a table from a database.

# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
Restore database (or database table) from backup.

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
Create Table Example 1.

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
Create Table Example 2.

mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default ‘bato’);

– Jayachandran Palanisamy.

chandranjoy@gmail.com

+91 98944 97649

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: