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

Resetting the Root Password: Generic Instructions

The preceding sections provide password-resetting instructions for Windows and Unix systems. Alternatively, on any platform, you can set the new password using the mysql client (but this approach is less secure):

1. Stop mysqld and restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges. Because this is insecure, you might want to use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting.

/etc/init.d/mysql stop
/etc/init.d/mysql start --skip-grant-tables --skip-networking

2. Connect to the mysqld server with this command:

mysql

3. Issue the following statements in the mysql client. Replace the password with the password that you want to use.

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

The FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change. You should now be able to connect to the MySQL server as root using the new password. Stop the server, then restart it normally (without the --skip-grant-tables and --skip-networking options).

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

Getting a list of InnoDB/MYISAM tables for one database

mysql -e "use databasename;show table status\G" | egrep 'Engine' | sort | uniq -c | sort -rn | head

Getting a list of all InnoDB/MYISAM tables on the server

InnoDB

mysql -e "SELECT concat(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.tables WHERE engine = 'InnoDB';" | tail -n +2 > /root/innodb.txt

MYISAM

mysql -e "SELECT concat(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.tables WHERE engine = 'MYISAM';" | tail -n +2 > /root/myisam.txt

Converting all table in the list to InnoDB or MYISAM

To convert make the list above.
Run the following to convert to MYISAM.

for I in $(cat /root/innodb.txt); do echo "ALTER TABLE $I ENGINE = MYISAM" | mysql; done

or run the following to convert to InnoDB.

for I in $(cat /root/myisam.txt); do echo "ALTER TABLE $I ENGINE = InnoDB" | mysql; done

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.