cronnjob for backup

# vi /bin/backup.sh

#!/bin/sh

#backup.sh

ldir=$(mysqldump -u host -p pwd dbname > /test.COM/public_html/backups/dbname _`date +"\%Y\%m\%d\%H\%M\%S"`.sql);

$wq!

then scheduled cronnjob for backup for every 30 minutes 

# vi /etc/crontab

*/30 * * * * root /bin/backup.sh

$wq!

mysql locks

There are two classes of locks:

READ LOCK (SHARED LOCK)

Read locks as the name suggests, are locks taken on resources to provide consistent read by blocking writes to that resource. The read locks are also known as shared locks because many clients can acquire read locks on the same resource simultaneously. The other thing to know is that, while read lock is in place, no write lock can be acquired on that resource.

Following is how MySQL will grant read lock:

• if there is no write lock set on the resource grant the read lock immediately
• if there is a write lock on the resource, put the lock in the read lock queue

WRITE LOCK (EXCLUSIVE LOCK)

Write lock is taken on a resource when it needs to be modified. There can be only one write lock on a
resource at a given time. While a write lock is held on a resource, all other read lock requests must alsowait.

Following is how MySQL will grant write lock:

• if there is no (read/write) lock on the resource, grant the write lock immediately
• if there is a lock on the resource, put the write lock in the write lock queue

PRIORITY OF READ AND WRITE LOCK

Write lock has a higher priority than read lock. When a resource is unlocked, and if there are lock
requests waiting in the queue, then the lock is granted in the following manner:
• grant the lock first to the request waiting in the write lock queue
• if there is no lock request for the resource in the write lock queue, then grant the lock to the first
request in the read lock queue Now let’s have a brief overview of the locking mechanism of two popular table engines.

Locking in MyISAM

MyISAM supports table-level locking and does not support row-level locking. Because of this behavior, MyISAM is typically well suited for applications which have a high percentage of reads as compared to writes. But because MyISAM supports locking at a high level, the memory needed for locking is typically less, say when you compare it to row-level locks.
However, MyISAM does allow concurrent INSERTs in some situations!

CONCURRENT INSERTS

If there are no holes in MyISAM table, then INSERTs happen at the end of the table, and in such a
situation INSERTs and SELECTs are allowed to happen concurrently on the same table, with INSERT
needing no write locks. But there is a caveat, if there are many INSERT requests, then the INSERTs are done serially, which implies that only one INSERT can execute concurrently with SELECTs on the same table. Now what exactly are hole? Holes are produced when rows are deleted and/or updated in the middle of a MyISAM table.

Locking in InnoDB

InnoDB unlike MyISAM supports both table-level locking as well as row-level locking, which allows for a more fine-grained control over the resource to be locked. Row-level locking allows InnoDB to be
extremely efficient in case of heavy write-load.
InnoDB also has a very nice feature MVCC, which allows for non-locking consistent reads. This is
achieved by having different snapshots of data available to different transactions. However, if you have the transaction isolation level set to SERIALIZABLE then plain SELECTs are automatically converted to locking SELECTs. However, AUTO_INCREMENT columns need table-level locks.

TABLE-LEVEL AUTO_INC LOCKS

INSERTs into AUTO_INCREMENT column need table-level lock for the duration of the INSERT
statement. This is so that the INSERTs are safe for statement-based replication. However, as of
MySQL >= 5.1, which also introduced row-based replication, this behavior is configurable using the
variableinnodb_autoinc_lock_mode, but you still need to be using row-based replication for the INSERTs to be safe for replication. Set the value of innodb_autoinc_lock_mode to 2, and then INSERT like statements will not take any table-level locks.

InnoDB Error Codes

The following is a nonexhaustive list of common InnoDB-specific errors that you may encounter, with
information about why each occurs and how to resolve the problem.

• 1005 (ER_CANT_CREATE_TABLE)

Cannot create table. If the error message refers to error 150, table creation failed because a
foreign key constraint was not correctly formed. If the error message refers to error –1, table
creation probably failed because the table includes a column name that matched the name of an
internal InnoDB table.

• 1016 (ER_CANT_OPEN_FILE)

Cannot find the InnoDB table from the InnoDB data files, although the .frm file for the table
exists. SeeSection 14.6.13.4, “Troubleshooting InnoDB Data Dictionary Operations”.

• 1114 (ER_RECORD_FILE_FULL)

InnoDB has run out of free space in the tablespace. Reconfigure the tablespace to add a new
data file.

• 1205 (ER_LOCK_WAIT_TIMEOUT)

Lock wait timeout expired. Transaction was rolled back.

• 1206 (ER_LOCK_TABLE_FULL)

The total number of locks exceeds the lock table size. To avoid this error, increase the value
ofinnodb_buffer_pool_size. Within an individual application, a workaround may be to break
a large operation into smaller pieces. For example, if the error occurs for a large INSERT, perform
several smaller INSERToperations.

• 1213 (ER_LOCK_DEADLOCK)

Transaction deadlock. Rerun the transaction.

• 1216 (ER_NO_REFERENCED_ROW)

You are trying to add a row but there is no parent row, and a foreign key constraint fails. Add the
parent row first.

• 1217 (ER_ROW_IS_REFERENCED)

You are trying to delete a parent row that has children, and a foreign key constraint fails. Delete
the children first.

isolation levels in mysql

The four isolation levels

READ UNCOMMITTED : With the READ-UNCOMMITTED isolation level there isn’t much isolation present between the transactions at all. The transactions can see changes to data made by other transactions that are not committed yet. That means transactions could be reading data that may not even exist eventually because the other transaction that was updating the data rolled-back the changes and didn’t commit. This is known asdirty read. An application rarely needs to rely on dirty needs, in fact this really can’t be called an isolation level. Simply put, there isn’t really any isolation at all and hence such a system can’t really be called a transactional system.

READ COMMITTED : With the READ-COMMITTED isolation level, the phenomenon of dirty read is avoided, because any uncommitted changes is not visible to any other transaction, until the change is committed. Within this isolation level each SELECT uses its own snapshot of the committed data that was committed before the execution of the SELECT. Now because each SELECT has its own snapshot, so the same SELECT when run multiple times during the same transaction could return different result sets. This phenomenon is called non-repeatable read.

REPEATABLE READ : With the REPEATABLE-READ isolation level, the phenomenon of non-repeatable read is avoided. This isolation level returns the same result set through out the transaction execution for the same SELECT run any number of times. A snapshot of the SELECT is taken the first time that SELECT is run during the transaction and the same snapshot is used through out the transaction when the same SELECT is executed. A transaction running in this isolation level does not take into account any changes to data made by other transactions, regardless of whether the changes have been committed or not. This ensures that reads are always consistent(repeatable). This isolation level is the default for InnoDB. Although this isolation level solves the problem of non-repeatable read, but there is another possible problem phantom reads.

SERIALIZABLE : With the SERIALIZABLE isolation level, the phenomenon of phantom reads is avoided. Transactions when run in this isolation level place locks on all records that are accessed, as well as locks the resource so that records cannot be appended to the table being operated on by the transaction. Transactions when run in this fashion, run in a serialized manner. This isolation level is the strongest possible isolation level.

Innodb Performance Optimization

innodb_buffer_pool_size This is very important variable to tune if you’re using Innodb

tables. Innodb tables are much more sensitive to buffer size compared to MyISAM.

MyISAM may work kind of OK with default key_buffer_size even with large data set but

it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both

data and index pages so you do not need to leave space for OS cache so values up to

70-80% of memory often make sense for Innodb only installations. Same rules as for

key_buffer apply – if you have small data set and it is not going to grow dramatically do

not oversize innodb_buffer_pool_size you might find better use for memory available.

innodb_additional_mem_pool_size This one does not really affect performance too

much, at least on OS with decent memory allocators. Still you might want to have it

20MB (sometimes larger) so you can see how much memory Innodb allocates for misc

needs.

innodb_log_file_size Very important for write intensive workloads especially for large

data sets. Larger sizes offer better performance but increase recovery times so be

careful. I normally use values 64M-512M depending on server size.

innodb_log_buffer_size Default for this one is kind of OK for many workloads with

medium write load and shorter transactions. If you have update activity spikes however

or work with blobs a lot you might want to increase it. Do not set it too high however

as it would be waste of memory – it is flushed every 1 sec anyway so you do not need

space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller

installations should use smaller values.

innodb_flush_log_at_trx_commit Crying about Innodb being 100 times slower than

MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each

update transaction commit (or each statement outside of transaction) will need to flush

log to the disk which is rather expensive, especially if you do not have Battery backed

up cache. Many applications, especially those moved from MyISAM tables are OK with

value 2 which means do not flush log to the disk but only flush it to OS cache. The log

is still flushed to the disk each second so you normally would not loose more than 1-

2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose

transactions even in case MySQL Server crashes. Value 2 only cause data loss with full

OS crash.

Myisam Performance Optimization

mysql> show variables;

key_buffer_size – Very important if you use MyISAM tables. Set up to 30-40% of available memory if
you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload
– remember MyISAM uses OS cache to cache the data so you need to leave memory for it as well, and data can be much larger than indexes in many cases.
Key_buffer - The key buffer is where MySQL caches index blocks for MyISAM tables. When a query
uses an index, MySQL checks if the index is in memory or not. Once the buffer is full, MySQL will make room for new data by replacing older data that hasn’t been used recently. Indexes are very important to database server performance that it’s hard to go wrong with making more room in memory for them.

key_buffer_size=128M

bulk_insert_buffer_size=32M

myisam_max_sort_buffer_size=8M

join_buffer_size=4M

max_allowed_packet=1M

query_cache_limit=4M

read_buffer_size=1M

read_rnd_buffer_size=2M

table_cache=128M

tmp_table_size=32M

SQL query to get the second highest salary from the table

Write a SQL query to get the second highest salary from the table above. Also write a query to find the nth highest salary in SQL, where n can be any number.
select MAX(Salary) from Employee;
Figuring out the answer to find the 2nd highest salary

What if we try to exclude the highest salary value from the result set returned by the SQL that we run? If we remove the highest salary from a group of salary values, then we will have a new group of values whose highest salary is actually the 2nd highest in the original Employee table.

So, if we can somehow select the highest value from a result set that excludes the highest value, then we would actually be selecting the 2nd highest salary value. Think about that carefully and see if you can come up with the actual SQL yourself before you read the answer that we provide below. Here is a small hint to help you get started: you will have to use the “NOT IN” SQL operator.

Solution to finding the 2nd highest salary in SQL

Now, here is what the SQL will look like:

SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )

Running the SQL above would return us “450″, which is of course the 2nd highest salary in the Employee table.

Subscribe to our newsletter for more free interview questions.
An explanation of the solution

The SQL above first finds the highest salary value in the Employee table using “(select MAX(Salary) from Employee)”. Then, adding the “WHERE Salary NOT IN” in front basically creates a new set of Salary values that does not include the highest Salary value. For instance, if the highest salary in the Employee table is 200,000 then that value will be excluded from the results using the “NOT IN” operator, and all values except for 200,000 will be retained in the results.

This now means that the highest value in this new result set will actually be the 2nd highest value in the Employee table. So, we then select the max Salary from the new result set, and that gives us 2nd highest Salary in the Employee table. And that is how the query above works.
An alternative solution using the not equals SQL operator

We can actually use the not equals operator – the “<>” – instead of the NOT IN operator as an alternative solution to this problem. This is what the SQL would look like:

select MAX(Salary) from Employee
WHERE Salary <> (select MAX(Salary) from Employee )

How would you write a SQL query to find the Nth highest salary?

What we did above was write a query to find the 2nd highest Salary value in the Employee table. But, another commonly asked interview question is how can we use SQL to find the Nth highest salary, where N can be any number whether it’s the 3rd highest, 4th highest, 5th highest, 10th highest, etc? This is also an interesting question – try to come up with an answer yourself before reading the one below to see what you come up with.
The answer and explanation to finding the nth highest salary in SQL

Here we will present one possible answer to finding the nth highest salary first, and the explanation of that answer after since it’s actually easier to understand that way. Note that the first answer we present is actually not optimal from a performance standpoint since it uses a subquery, but we think that it will be interesting for you to learn about because you might just learn something new about SQL. If you want to see the more optimal solutions first, you can skip down to the sections that says “Find the nth highest salary without a subquery” instead.

The SQL below will give you the correct answer – but you will have to plug in an actual value for N of course. This SQL to find the Nth highest salary should work in SQL Server, MySQL, DB2, Oracle, Teradata, and almost any other RDBMS:

SELECT * /*This is the outer query part */
FROM Employee Emp1
WHERE (N-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

How does the query above work?

The query above can be quite confusing if you have not seen anything like it before – pay special attention to the fact that “Emp1″ appears in both the subquery (also known as an inner query) and the “outer” query. The outer query is just the part of the query that is not the subquery/inner query – both parts of the query are clearly labeled in the comments.
The subquery is a correlated subquery

The subquery in the SQL above is actually a specific type of subquery known as a correlated subquery. The reason it is called a correlated subquery is because the the subquery uses a value from the outer query in it’s WHERE clause. In this case that value is the Emp1 table alias as we pointed out earlier. A normal subquery can be run independently of the outer query, but a correlated subquery can NOT be run independently of the outer query. If you want to read more about the differences between correlated and uncorrelated subqueries you can go here: Correlated vs Uncorrelated Subqueries.

The most important thing to understand in the query above is that the subquery is evaluated each and every time a row is processed by the outer query. In other words, the inner query can not be processed independently of the outer query since the inner query uses the Emp1 value as well.
Finding nth highest salary example and explanation

Let’s step through an actual example to see how the query above will actually execute step by step. Suppose we are looking for the 2nd highest Salary value in our table above, so our N is 2. This means that the query will look like this:

SELECT *
FROM Employee Emp1
WHERE (1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

You can probably see that Emp1 and Emp2 are just aliases for the same Employee table – it’s like we just created 2 separate clones of the Employee table and gave them different names.
Understanding and visualizing how the query above works

Let’s assume that we are using this data:
Employee Employee ID     Salary
3     200
4     800
7     450

For the sake of our explanation, let’s assume that N is 2 – so the query is trying to find the 2nd highest salary in the Employee table. The first thing that the query above does is process the very first row of the Employee table, which has an alias of Emp1.

The salary in the first row of the Employee table is 200. Because the subquery is correlated to the outer query through the alias Emp1, it means that when the first row is processed, the query will essentially look like this – note that all we did is replace Emp1.Salary with the value of 200:

SELECT *
FROM Employee Emp1
WHERE (1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > 200)

So, what exactly is happening when that first row is processed? Well, if you pay special attention to the subquery you will notice that it’s basically searching for the count of salary entries in the Employee table that are greater than 200. Basically, the subquery is trying to find how many salary entries are greater than 200. Then, that count of salary entries is checked to see if it equals 1 in the outer query, and if so then everything from that particular row in Emp1 will be returned.

Note that Emp1 and Emp2 are both aliases for the same table – Employee. Emp2 is only being used in the subquery to compare all the salary values to the current salary value chosen in Emp1. This allows us to find the number of salary entries (the count) that are greater than 200. And if this number is equal to N-1 (which is 1 in our case) then we know that we have a winner – and that we have found our answer.

But, it’s clear that the subquery will return a 2 when Emp1.Salary is 200, because there are clearly 2 salaries greater than 200 in the Employee table. And since 2 is not equal to 1, the salary of 200 will clearly not be returned.

So, what happens next? Well, the SQL processor will move on to the next row which is 800, and the resulting query looks like this:

SELECT *
FROM Employee Emp1
WHERE (1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > 800)

Since there are no salaries greater than 800, the query will move on to the last row and will of course find the answer as 450. This is because 800 is greater than 450, and the count will be 1. More precisely, the entire row with the desired salary would be returned, and this is what it would look like:

Employee ID     Salary
    3                        200
    4                        800
    7                        450

It’s also worth pointing out that the reason DISTINCT is used in the query above is because there may be duplicate salary values in the table. In that scenario, we only want to count repeated salaries just once, which is exactly why we use the DISTINCT operator.

updating multiple column values in single query

update email_table
set EmailId = CASE id

     WHEN 1 THEN 'abc@gmail.com, xyz@gmail.com'
     WHEN 4 THEN '123@gmail.com'
     WHEN 5 THEN 'asdf@hotmail.com'
     WHEN 7 THEN 'zxcv@gmail.com'

END
WHERE id IN(1,4,5,7)

MySQL Storage engines

A storage engine(Data Base Engine) is a software which a Data Base management System uses to create, read, update and delete (CRUD) data from a database.

The default storage engine for MySQL prior to version 5.5 was MyISAM. For MySQL 5.5 and later,
the default storage engine is InnoDB. Choosing the right storage engine is an important strategic
decision, which will impact future development.

mysql> show variables like "default_storage%";

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

| Variable_name | Value |

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

| default_storage_engine | InnoDB |

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

mysql> SET default_storage_engine=”myisam”;

Types of mysql storage engines

1) Transactional storage engines  : Innodb,Nbd
2) Non-Transactional storage engines : MyISAM,MEMORY,ARCHIVE,CSV,MERGE,FEDERATED,ISAM,BLACKHOLE

 Transactional storage engines supports transactions like COMMIT, ROLLBACK,SAVE POINT. Transaction is noting but a collection of executable sql statements. And Transactional storage engines supports foreign key constraint.

MyISAM is a MySQL default storage engine(Before Mysql 5.5 version). It is a non-transactional storage engine. It doesn't support transactions. It process the insert and select statements as fast as possible. It supports fulltest index supporting. And also table level locking. No foreign key support. It supports 64 indexes per row. Count(*) works fast than Innodb. We can compress the data here.>create table emp(id int(10),name varchar(30),sal double(14,2)) engine=”myisam”;

mysql> create table emp2(id int(10),name varchar(30),sal double(14,2),FULLTEXT(name))

here we can see emp.frm,(It contains structure) emp.MYI (it contains indexex) ,emp.MYD(it contains data)..So take disk space more.

Innodb is a MySQL default storage engine (From 5.5 version). It is a transactional storage engine. It supports transactions and row level locking. It provide ACID properties. It process update queries as fast as possible. Count(*) little slow than MyISAM. It does't support fulltext index. Here we can't compress the data because every thing store in a single file(ibdata).

Atomicity : a transaction is a unit of operation - either all the transaction's actions are completed or

Consistency : Any work in progress must not be visible to other transactions until the transaction has

Isolation : A transaction should appear to be running by itself,the effects of other ongoing transactions must be invisible to this transaction,and the effects of this transaction must be invisible to other ongoing transaction.

Durability : When the transaction is committed,it must be persisted so it is not lost in the event of a failure.Only committed transaction are recovered during power-up and crash recovery;uncommitted

>create table emp(id int(10),name varchar(30),sal double(14,2)) engine=”innodb”;

mysql> create table dept(eid int(10),dname varchar(30),loc varchar(30),foreign key(eid) references

here we can see only emp.frm. The data and indexes will be store in ibdata file. innodb_file_per_table supports from mysql 5.5 to stores index and data in .ibd file separately for every table.

mysql> alter table emp2 engine=innodb;

MEMORY (formerly known as HEAP)

For this engine the data will be stored in the memory. When we restart the server server we will loss all the data. When we create a table only .frm file will be store in data directory. It does't support For this engine the data will be store in csv comma separated values.This engine stores data in zip format. It does't support priamary key. It is use to stores historical data. The select statements will be very slow because the data will be stored in compressed format.The MERGE storage engine, also known as the MRG_MyISAM engine, is a collection of identical MyISAM tables that can be used as one. “Identical” means that all tables have identical column and index information.

mysql> CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM;

mysql> CREATE TABLE t2 (a INT NOT NULL

AUTO_INCREMENT PRIMARY KEY,message CHAR(20))

ENGINE=MyISAM;

mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');

mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');

mysql> CREATE TABLE total (a INT NOT NULL AUTO_INCREMENT,message CHAR(20), INDEX(a))ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST/LAST;

FEDERATED

The FEDERATED storage engine is used to access the remote MySQL database without using replication or cluster technology.

> create table emp(id int(10),name varchar(20),sal double(14,2));

>create table emp(id int(10),name varchar(20),sal double(14,2) )connection='mysql:://

root:root@192.168.1.2:3306/exdb/emp';

MySQL User Management

User management is the process of controlling which users are allowed to connect to the MySQL server and what permissions they have on each database.

Start the mysql server

# /etc/init.d/mysql start      or  # service mysql start   or # mysqld_safe –user=”mysql” &

Login into mysql database

[root@maxisdb1 mysql]# mysql -proot -u root

mysql>\q

mysql> show engines;

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| test               |

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

3 rows in set (0.00 sec)

mysql>use mysql;

mysql> show tables;

mysql> select user,host from user;

Mysql default port number is 3306

Mysql default storage engine is “MyIsam”(Up to 5.5)   “Innodb”(From 5.5)

SQL Commands:

Data Definition Language (DDL)

CREATE,ALTER,DROP and TRUNCATE

Data Manipulation Language (DML)

INSERT, UPDATE, and DELETE.

Data query Language (DML)

SELECT

Transaction Control Language (TCL)

COMMIT, ROLLBACK, and SAVEPOINT.

Data Control Language (DCL)

GRANT and REVOKE.

The GRANT statement grants privileges to MySQL user accounts.

Creating a User  with Full Privileges to all databases:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'drk'@'localhost' IDENTIFIED BY 'drk' WITH GRANT OPTION;

Creating a User  with Full Privileges to a single database:

mysql> GRANT ALL PRIVILEGES ON drkdb.* TO 'drk'@'localhost' IDENTIFIED BY 'drk' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'drk'@'%' IDENTIFIED BY 'drk' WITH GRANT OPTION;

Using the WITH GRANT OPTION allows the account to add user accounts to the specified databases.

Creating a User Account with Full Privileges on All DATABASES:

Creating Specific Privileges to a User on one database:

GRANT SELECT, INSERT, DELETE, UPDATE, EXECUTE ON test.* TO 'username'@'192.168.0.3';

To check the privileges for an account, use SHOW GRANTS:

mysql> SHOW GRANTS FOR 'drk'@'localhost';

Removing Users:

USE mysql;

DELETE FROM user WHERE user = 'username' AND host = 'hostname';

As an alternative to GRANT, you can create the same accounts directly by issuing INSERT statements and then telling the server to reload the grant tables:

An example of how to Create a User in MySQL using the INSERT statement

INSERT INTO user (Host,User,Password) VALUES('localhost','my_user',PASSWORD('my_pass'));

If you want to add some privileges with the same command the query will look like this:

An example of how to create a user with INSERT and SELECT privileges by editing the 'mysql.user' table

INSERT INTO user (Host,User,Password,Select_priv,Insert_priv)

VALUES('localhost','my_user',PASSWORD('my_pass'),'Y','Y');

mysql> INSERT INTO user VALUES('localhost','drk',PASSWORD('drk'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO user VALUES('%','drk',PASSWORD('drk'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO user SET Host='localhost',User='admin', Reload_priv='Y', Process_priv='Y';

mysql> INSERT INTO user (Host,User,Password) VALUES('localhost','dummy','');

mysql> FLUSH PRIVILEGES;

The REVOKE statement enables system administrators to revoke privileges from MySQL accounts.

REVOKE ALL PRIVILEGES, GRANT OPTION FROM drk@localhost;

REVOKE INSERT ON *.* FROM 'drk'@'183.82.96.216';

update the password

mysql>update user set passowrd=PASSWORD('drk') where user='drk';

#flush privilges

MySQL Instalaltion

Mysql having three types of instalaltion:

1) rpm                     2) generic binaries               3) source distribution

Mysql server is available in two options

MySQL Community Edition &  MySQL Enterprise Edition

http://dev.mysql.com/downloads/mysql/

1) rpm instalaltion

# rpm -ivh mysql-5.5.25-linux2.6-i686.rpm

i= install  v=verify/verbose information h=hash sysmbol

MySQL Installation Layout for Linux RPM

Directory	Contents of Directory
/usr/bin	Client programs and scripts
/usr/sbin	The mysqld server
/var/lib/mysql	Log files, databases
/usr/share/info	Manual in Info format
/usr/share/man	Unix manual pages
/usr/include/mysql	Include (header) files
/usr/lib/mysql	Libraries
/usr/share/mysql	Miscellaneous support files, including error messages, character set files, sample configuration files, SQL for database installation

[root@maxisdb1 mysql]# scp /usr/share/mysql/my-medium.cnf /etc/my.cnf

[root@maxisdb1 mysql]# /etc/init.d/mysql start/stop/restart

or

[root@maxisdb1 mysql]#  /usr/share/mysql/mysql.server start

/stop/restart

or

[root@maxisdb1 mysql]# /usr/bin/mysqld_safe --user=mysql &

or

[root@maxisdb1 ~]# service mysql start/stop/restart

If any problem occurs see process id's

[root@maxisdb1 ~]# ps -ef | grep "mysql"

then kill the process id's

[root@maxisdb1 ~]# kill -9 3524 3534 4482 4562 4576 4641 

#mysql -proot -uroot

to update the password using mysqladmin

#mysqladmin -u root -p “root”

or

mysql>update user set password=PASSWORD(“root”) where user=”root”;

to see the status

[root@maxisdb1 ~]# mysqladmin -proot -uroot status

Uptime: 256  Threads: 1  Questions: 6  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: %

to see the status variables

[root@maxisdb1 ~]# mysqladmin -proot -uroot status variables

2) Generic binaries installation

################################################################################

mysql-5.5.25-linux2.6-i686.tar.gz generic binary installation on redhat

################################################################################

step 1) Downlaod the above package from http://dev.mysql.com/downloads/mysql/

step 2) # groupadd mysql

          # useradd -r -g mysql mysql

          # scp mysql-5.5.25-linux2.6-i686.tar.gz /usr/local

           # cd /usr/local

           # tar zxvf mysql-5.5.25-linux2.6-i686.tar.gz

          # mv mysql-5.5.25-linux2.6-i686 mysql

          # cd mysql

          # chown -R mysql .

          # chgrp -R mysql .

          #  ./scripts/mysql_install_db (--user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data )

          # chown -R root .

          # chown -R mysql data

         

        Next command is optional

          # cp support-files/my-medium.cnf /etc/my.cnf

          # cp support-files/mysql.server /etc/init.d/mysql

          start the mysql server

          # bin/mysqld_safe --user=mysql &

                   

                      or    

          #/etc/init.d/mysql start

                   

                    or

          #support-files/mysql.server start

          #cd /usr/local/mysql/bin

          #mv * /usr/bin

          #cd ../

          #rm -fr /usr/local/mysql/bin

          #ln -s /usr/bin /usr/local/mysql/bin

before going to binary instalaltion we have to remove all pre installation directories and

#cd /usr/local

#rm -rf mysql*

#cd /usr/share

#rm -rf mysql

#cd /varlib/

#rm -rf mysql

#cd /etc/

#rm my.cnf

#cd init.d

#rm mysql/mysqld

MySQL Installation Layout for Generic Unix/Linux Binary Package

Directory	Contents of Directory
bin	Client programs and the mysqld server
data	Log files, databases
docs	Manual in Info format
man		Unix manual pages
include		Include (header) files
lib		Libraries
scripts		mysql_install_db
share		Miscellaneous support files, including error messages, sample configuration files, SQL for database installation
Supported-files		Configuration files and mysql start up scripts

2) source distribution  installation

mysql-5.5.25-linux2.6-i686.tar.gz source distribution installation on redhat

step 1) Downlaod the above package from http://dev.mysql.com/downloads/mysql/

step 2) # groupadd mysql

# useradd -r -g mysql mysql

# scp mysql-5.5.25-linux2.6-i686.tar.gz /usr/local

# cd /usr/local

# tar zxvf mysql-5.5.25-linux2.6-i686.tar.gz

# ln -s mysql-5.5.25-linux2.6-i686 mysql

# ./configure --prefix=/usr/local/mysql

# make

# make install

# cd /usr/local/mysql

#chown -R mysql .

# chgrp -R mysql .

# bin/mysql_install_db –user=mysql

or

#  ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

#chown -R root .

#chown -R mysql var

Next command is optional

          # cp support-files/my-medium.cnf /etc/my.cnf

          # cp support-files/mysql.server /etc/init.d/mysql

          start the mysql server

          # bin/mysqld_safe --user=mysql &

                   

                      or    

          #/etc/init.d/mysql start

                   

                    or

          #support-files/mysql.server start

          #cd /usr/local/mysql/bin

          #mv * /usr/bin

          #cd ../

          #rm -fr /usr/local/mysql/bin

          #ln -s /usr/bin /usr/local/mysql/bin

before going to souce distribution installation we have to remove all pre installation directories and

#cd /usr/local

#rm -rf mysql*

#cd /usr/share

#rm -rf mysql

#cd /varlib/

#rm -rf mysql

#cd /etc/

#rm my.cnf

#cd init.d

#rm mysql/mysqld

MySQL Layout for Installation from Source

Directory	Contents of Directory
bin	Client programs and scripts
include/mysql	Include (header) files
Docs	Manual in Info format
man	Unix manual pages
lib/mysql	Libraries
libexec	The mysqld server
share/mysql	Miscellaneous support files, including error messages, sample configuration files, SQL for database installation
sql-bench	Benchmarks
var	Log files, databases

******************************************************************************
mysql-5.1.77 source isntallation from mysql-5.1.77.tar.gz
******************************************************************************
Downlaod the above package from http://dev.mysql.com/downloads/mysql/
# yum install gcc gcc-c++
# yum install binutils
# groupadd mysql
# useradd -r -g mysql mysql
# scp mysql-5.1.77.tar.gz /usr/local
# cd /usr/local
# tar zxvf mysql-5.1.77.tar.gz
# ./configure --prefix=/usr/local/mysql (--enable-profiling --with-plugin=partition,federated,innodb_plugin,csv,blackhole)
# chown -R mysql .
# chgrp -R mysql .
# make
# make isntall
# scripts/mysql_install_db --user=mysql
# chown -R root .
# chown -R mysql data
# mysqld_safe --user=mysql &



mysql-5.5.25 source installation from mysql-5.5.25.tar.gz

http://www.hellotechblog.com/2012/02/building-mysql-5520-on-centos-57-from.html

Downlaod the above package from http://dev.mysql.com/downloads/mysql/
# yum install cmake
# yum install ncurses-devel
# yum install gcc-c++
# yum install binutils
# groupadd mysql
# useradd -r -g mysql mysql
# scp mysql-5.5.25.tar.gz /usr/local
# cd /usr/local
# chown -R mysql .
# chgrp -R mysql .
# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql (-DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DMYSQL_DATADIR=/usr/local/mysql/data/  -DINSTALL_LAYOUT=STANDALONE -DENABLED_PROFILING=ON -DMYSQL_MAINTAINER_MODE=OFF
-DWITH_DEBUG=OFF)
# make
# make install
# cd /usr/local/mysql
# scripts/mysql_install_db --user=mysql
# chown -R root .
# chown -R mysql data
# ./bin/mysqld_safe --user=mysql &
# ./bin/mysql_secure_installation