Database Experts

Tuning MYSQL Parameters These are the list of variables need to tune to better performance of MYSQL Database. This is in alphabetical orders. 1. have_query_cache      Values YES Description. If Query Cache enabled 2. innodb_additional_mem_pool_size Values:50M Description: As per MySQL documentation, this pool is used to store data dictionary information and other internal data structures. If InnoDB runs out of memory on this pool, it starts allocating from OS. Most of the additional memory pool usage goes to tables in the data dictionary and connections. Normally, with increasing number of tables you may need to allocate more memory to this pool. But as modern OS'es have good and fast memory allocating functions, this variable does not hit performance. 3. innodb_buffer_pool_size  Values 50-80% of Installed RAM Description: The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables....

MYSQL Shut and start service This script is mainly developed for restarting mysql services in solaris machine. 1.Make one sh file and execute it. /usr/local/mysql/.profile; sh /usr/local/mysql/shutnstart.sh 2.Here the contents of shutnstart.sh script. /usr/local/mysql/bin/mysqladmin -u root -ppass shutdown echo "STOPPING...." sleep 4 echo "STARTING...." /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/my.cnf --user=mysql & echo "MYSQL STARTED.." We use the utility of mysqladmin

MYSQL Databases backup MYSQL provide the utility for hot backup that can be done by mysqldump 1.For Backup of single database in shell script /usr/local/mysql/bin/mysqldump  -uroot -ppass --log-error=/mysql2/mysqlbackup/db_name_`date +"%d-%m-%y"`.log DBNAME --routines >/mysql2/mysqlbackup/DBNAME_`date +"%d-%m-%y"`.sql 2.For All MYSQL Databases backup in one single file. /usr/local/mysql/bin/mysqldump -u root -p --log-error=/mysql2/mysqlbackup/ALL_DB_ERROR_`date+"%d-%m-%y"`.log  --all-databases --routines >/mysql2/mysqlbackup/ALL_MYSQL_DB_`date +"%d-%m-%y"`.sql Use  mysqlimport to restore backup.