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

Firstly Check the versions currently installed (after the upgrade easy apache wont report the php version so we do this first.)

 php -v
 mysqladmin version

Back up the databases before anything else.

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

Change mysql-version to the version you want.

vim /var/cpanel/cpanel.config

change mysql-version to what 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

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.