MySQL

MYSQL

This is the Mysql shit page.

Mysql Uptime and Mysql status

Uptime

mysql -e "status;" | grep Uptime

Full Status

mysql -e "status;"

Find avtual max connections (what it is currently set to)

mysql -e "show variables;" | grep max_connections

Show all mysql variables

 mysql -e "show variables;"

Mysql tools

Mysql report

http://hackmysql.com/mysqlreport

install as folows.

cd /usr/local/src/
wget http://hackmysql.com/scripts/mysqlreport-3.5.tgz
tar -zxvf mysqlreport-3.5.tgz
cd mysqlreport-3.5
./mysqlreport

Mysql tuner

THE optimization script.

wget -O /scripts/mysqltuner.pl http://mysqltuner.com/mysqltuner.pl
chmod +x /scripts/mysqltuner.pl
/scripts/mysqltuner.pl

Tuning Primer

Yet another optimization script.

wget -O /scripts/tuning-primer.sh http://day32.com/MySQL/tuning-primer.sh
chmod +x /scripts/tuning-primer.sh
/scripts/tuning-primer.sh 

Changing ENGINEs (innodb and myisam)

Change out the relavent information below.

mysql -D dbname -e "ALTER TABLE \`dbtable\` ENGINE = InnoDB or MYISAM;"

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

change mysql=)version you want (IE...4.1, 5.1)) Or I have a copy paste clicky here for it.

To do 4.1 to 5.0
    sed -i -e 's#mysql-version=4.1#mysql-version=5.0#g' /var/cpanel/cpanel.config
To do 5.0 to 5.1
    sed -i -e 's#mysql-version=5.0#mysql-version=5.1#g' /var/cpanel/cpanel.config
To do 4.1 to 5.1 (worst idea ever)
    sed -i -e 's#mysql-version=4.1#mysql-version=5.1#g' /var/cpanel/cpanel.config

Warning: READ THE DAMN THING, make sure there arent errors on the screen

/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.`date +%Y%m%d%I`;done

Warning: READ THE DAMN THING, make sure there arent errors on the screen

/scripts/mysqlup --force
mysqladmin version

Notice: Make sure the EA options all match current versions.

/scripts/easyapache

Info: Grats if you got here with no errors

Other Mysql stuffs

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

/scripts/mysqlup --force fails

The first thing you should try in this instance is checking for a bad configuration file.

updatedb&
locate mysql_config

From what I have seen this can be located in a few directories.

/usr/bin/mysql_config
/usr/local/bin/mysql_config

From what I learned in a recent cpanel ticket, /usr/bin/mysql_config is the current correct location for this file. Back up any of these files found and then remove them and try the build again.

cp /usr/bin/mysql_config /usr/bin/mysql_config.bak
cp /usr/local/bin/mysql_config /usr/local/bin/mysql_config.bak
rm /usr/local/bin/mysql_config /usr/bin/mysql_config
/scripts/mysqlup --force

If that doesnt work remove all but the /usr/bin/ copy of this config and restore that one.

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!!!

INNODB foo

Restoring a innodb database on a shared server:

1) Create a datadir for new mysql in /home cause its the largest drive:

mkdir /home/*datadir*
chown mysql. /home/*datadir*

2) Copy all files keeping permission from /backup/*backup date*/var/lib/mysql to /home/*datadir*

cp -Rp /backup/*backup date*/var/lib/mysql/* /home/*datadir*/

3) Run the following to start a new instance of mysql:

mysqld_safe --socket=/home/*datadir*/mysql.sock --basedir=/ --datadir=/home/*datadir*/ --pid-file=/home/*datadir*/test.pid --skip-external-locking --skip-networking

4) Dump innodb database(s):

mysqldump --socket=/home/*datadir*/mysql.sock database_name > database_file.sql

5) Once complete stop mysql processes ((ps aux | grep mysql) then kill root and mysql process's that were created) and remove the /home/*datadir* directory.