MySQL

MYSQL

This is the Mysql shit page.

Mysql Uptime and Mysql status

Uptime

mysql -e "status;" | grep Uptime

Full Status

mysql -e "status;"

Backing stuffs up

To backup all databases to remote location

mkdir /home/sqlbackup
for db in `mysql -s -B -e "show databases"`;do mysqldump $db > /home/sqlbackup/$db.sql.(version number here);done

Repairing and Defragmenting

To repair a crashed MYISAM table stop chkservd and mysql first.

/usr/local/cpanel/bin/tailwatchd --disable=Cpanel::TailWatch::ChkServd
/etc/init.d/mysql stop

Then You will need to run.

myisamchk -r /var/lib/mysql/(databasename)/(tablename)
/etc/init.d/mysql start
/usr/local/cpanel/bin/tailwatchd --enable=Cpanel::TailWatch::ChkServd

General MySQL Fixes

"MySQL server has gone away" fix is to increase these two settings, usually by doubling them.

wait_timeout
max_allowed_packet

You will find these in the my.cnf.

vim /etc/my.cnf

When you have completed that Restart MySQL.

/etc/init.d/mysql stop
/etc/init.d/mysql start

Correct way to upgrade mysql in cpanel box

vim /var/cpanel/cpanel.config

mysql=version you want (IE...4.1, 5.1)

/usr/local/cpanel/whostmgr/bin/whostmgr2 --updatetweaksettings
mkdir /home/sqlbackup
for db in `mysql -s -B -e "show databases"`;do mysqldump $db > /home/sqlbackup/$db.sql.(old mysql version number here);done
/scripts/mysqlup --force
mysqladmin version
/scripts/easyapache

Myqsl packages are installed

rpm -qa | grep SQL | grep -vi perl

Force perl update

/scripts/perlinstaller --force Bundle::DBD::mysql

what mysql version

mysqladmin version

Mysql database location

cd /var/lib/mysql

imports a dumped file

mysql database < db-dump-file.sql

Mysqldump

mysqldump dbname > dbname.sql

Export all databases to a single sql file

mysqldump -u root -p --all-databases > all.sql

Import all databases back to mysql.

mysql -u root -p < all.sql

Mtop

This is a monitoring tool much like Htop or top, but for mysql.

mysql -e "CREATE USER 'mysqltop'@'localhost';grant super, reload, process on *.* to 'mysqltop'@'localhost';"
cd /usr/local/src
wget http://downloads.sourceforge.net/project/mtop/mtop/v0.6.6/mtop-0.6.6.tar.gz
tar -zxvf mtop-0.6.6.tar.gz
rm -rf mtop-0.6.6.tar.gz
cd mtop-0.6.6
perl Makefile.PL
make
make install
mtop

ENJOY!!!