shift mysql database's data directory to external hard disk

Hi,
Do your database is of several GBs? And you can not store all of data in root? Don't compromise on the space of database because I got solution for you. This solution is for linux users. I am here using centOs. So use your own discretion to change default paths and mysql config path.

One solution is that rather then storing database on root's default mysql path which is /var/lib/mysql you can store this mysql files into external hard disk which have several of free space and which is permanently mounted.

• so first step is to create a directory into your hard-disk. Let's say your hard-disk's path is /disk2 and you want to store your database in /disk2/database. then create that directory using command

mkdir /disk2/database

• now you have to stop mysql service to change data directory. It requires root permission. If your are sudoer user then you can use following command. The reason of doing step is to stop mysql interaction with file system so that move can files. 

sudo service mysqld stop 

• now move all your present content stored at /var/lib/mysql to new path. 

sudo cp -R -p /var/lib/mysql /disk2/database

BTW do you know what this mysql directory contains? It's encoded data which you can not use without root permission. It's another long discussion. But I will feel great to throw some light as topic is about that. It contains directory named database which contains following files:
If database is MyISam then it contains:

table_name.MYD encoded data
table_name.MYI index file
table_name.frm structure of table

if datbase is InnoDB then it contains only table_name.frm

• It's time to change mysql configuration file. open  /etc/my.cnf through editor.

sudo vi /etc/my.cnf

• change datadir variable to described following:

datadir=/disk2/database/mysql 

• it's the last step which is to start mysql service.

sudo service mysqld start

Starting mysqld:                                           [  OK  ]

• if it's shows OK status then  it's awesome!

Hope you liked the blog and it was useful.

mysql foreign-key engine

I found today that if you want to have foreign key constraint in mysql db and if you are not choosing 'innodb' engine then it will be ignored by default engine which is 'MyISAM'. 

one of explanation of this behaviour is as follow taken from url

http://stackoverflow.com/questions/296730/disappearing-foreign-keys-in-phpmyadmin

When you define a table with the MyISAM storage engine, it accepts FOREIGN KEY constraints, but it silently throws them away. It parses them, but does not store the constraint in the table metadata, and subsequently cannot enforce the constraint. When you ask to look at the database's idea of the table definition, it knows nothing about that constraint, as you have found out.

The same thing happens with CHECK constraints (regardless of the storage engine); it parses the syntax and accepts it, but then ignores it.

IMHO, this is a terrible thing for the MySQL product to do. It accepts standard SQL with no error, leaving you with the impression that it's going to support the constraint in the standard way. But it doesn't! Not even SHOW WARNINGS reveals that MySQL has disregarded the constraint.

If you use the InnoDB storage engine, it does heed the foreign key constraint.

To provide engine type to create table sql syntax provide ENGINE = InnoDB; argument.

Hope it's new thing . Ignore it if you knew earlier.