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

/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.