Move MySQL database location

MySQL DB might using some location as data repository where hard-disk space is limited. This will warrant moving the data from that location to some other location which can provide more room to accomodate the increasing data. Following steps can move this data to the new location:


du -h : will show the memory parition of the current folder

--------------------


-->> Set the password for mysqlmanager (required if password is not set already).
mysqlmanager --passwd >> /etc/mysqlmanager.passwd

--> log into mysql manager
mysql -uroot -psh*pp@r --socket=/tmp/mysqlmanager.sock

-->> kill all instances of mysql
ps afx | grep mysql //get process ids
kill -9 4465 4473 4474 // kill those processes

-->> create the directory where you want to transfer the files
mkdir /mnt/mysql/marketingportal

-->> Give mysql the ownership of the directory and its contents (presently they are owned ny root)
chown -R mysql.mysql /mnt/mysql/ //mysql.mysql : group:user

--> Open the folder where the current data is
cd mysql-marketingportal/

--> Copy this data to the new folder
cp -R * /mnt/mysql/marketingportal/

-->> Give mysql the ownership of the new directory and its contents (presently they are owned ny root)
chown -R mysql.mysql /mnt/mysql/ //mysql.mysql : group:user

-->> Change the name of the old directory (optional - this is done to make sure that it isn't beign used any longer)
mv mysql-marketingportal/ mysql-marketingportal-old/

-->> edit the my.cnf file
change the value of datadir

-->> Start the mysqlmanager
nohup mysqlmanager --user=mysql > mysqlmanager.out &

High Performance Database Tips

Some Database mantras discovered on the basis of practical experience.

• Use the smallest datatype possible. eg. where a integer can take care of all possible states, don't use bigint. Every bit of extra space alloted will make the DB to grow in size without any actual utility, and there by taking toll on the performance
• Choose what to index and how much to index. Indexing is creating a meta data of data. If data is being fetched on the basis of a particular attribute, make that attribute an index. It would will definitely boost-up the reads and updates. Also, keep in mind that indexing increases the insert time and these meta-data information do occupy disk space. Partial Indexing is a good way to create indexes not the on the complete field, but the deciding part of it. eg. Index can be created for the first few character's URLs if data to be fetched is on the basis of host name.
  
• Choose storage engine wisely. eg. If the DB is not relational, go for MyISAM. Read the pros-cons of each engine before choosing one.
  
• Controlled Data Redundancy. Higher the normalisation of a DB, lesser is the redundancy. But, one think to keep in mind is that retrieving data from such a DB might require joining of tables, which is a very expensive operation. If the size of DB is huge or if the data to be fetched is distributed across a significant no. of tables, then performance will take the hit. One has to make a educated choice between space and performance. My experience tells me that a bit of redudancy isn't that bad !
• Fetch only relevant data. If you require some fields of a table(s), retrieve only those fields. Don't do a 'select *'.
• Better programming logic: When programitically accessing DB use reuse connections. eg. If on the same DB schema, we need to fire 2 quries to retrive logical/related set of data, try using the same connection. Creating connection is costly. Also, Make sure to close all connections when use is over.
• Don't run all servers on the same node. If application receives a huge no. of hits, and also tremendous DB operations are taking place in the DB (like huge no. of inserts/read/writes), its better to host the application on a node diffrerent from the DB. There is only so much a single node can handle.
  

Write SQL query output on file

Query into outfile 'filename';

Query - query to be fired
filename - name of the file on which the data is to be written. Full path can also be given. Make sure that the folder is already in existence.


eg. select * from HOTEL_DETAILS into outfile 'C:/datafolder/sqldata.txt';


Further Reading:
http://gilfster.blogspot.com/2005/11/writing-sql-results-to-file.html