tcpdump -i eth0 ‘port 3306’
MySQL
Migrate MySQL from Slaved Drive
Migrate MySQL from Slaved Drive
1. Mount the slave drive. We’ll assume you mounted it at /media/slave
Find the drive:
# fdisk -l Disk /dev/sda: 1000.2 GB, 1000204886016 bytes 255 heads, 63 sectors/track, 121601 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes Disk identifier: 0x000374d4 Device Boot Start End Blocks Id System /dev/sda1 * 1 64 512000 83 Linux Partition 1 does not end on cylinder boundary. /dev/sda2 64 587 4194304 82 Linux swap / Solaris Partition 2 does not end on cylinder boundary. /dev/sda3 587 121602 972054528 83 Linux Disk /dev/sdb: 1000.2 GB, 1000204886016 bytes 255 heads, 63 sectors/track, 121601 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes Disk identifier: 0x00000000 Device Boot Start End Blocks Id System /dev/sdb1 * 1 62 497983+ 83 Linux /dev/sdb2 64 584 4184932+ 82 Linux swap / Solaris /dev/sdb3 585 121598 972044955 82 Linux swap / Solaris
sdb is the drive – the slaved drive after the reinstall
Check to see if it is ext3 or ext4
# blkid /dev/sdb3 /dev/sdb3: UUID="52721885-a9af-45e9-89f5-5f26ffca55dd" TYPE="ext3"
Mount according to ext3 or ext4
mount -t ext3 /dev/sdb3 /media/slave
Add to fstab
# nano /etc/fstab /dev/sdb3 /media/slave ext3 default 0 1
2. Edit the MySQL config file to point to the slave’s mysql databases
nano /etc/my.cnf
# datadir = /var/lib/mysql datadir = /media/slave/var/lib/mysql
service mysqld restart
3. Export the required database
mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
for plesk:
mysqldump -u admin -p`cat /etc/psa/.psa.shadow` [database_name] > dumpfilename.sql
4. Reset the Mysql path and import the file
nano /etc/my.cnf
datadir = /var/lib/mysql # datadir = /media/slave/var/lib/mysql
service mysqld restart
mysql -u root -p[root_password] [database_name] < dumpfilename.sql
Plesk:
mysql -u admin -p`cat /etc/psa/.psa.shadow` [database_name] < dumpfilename.sql
Hotkey for Plesk specifically mounted to /olddrive/:
Before migrating, make sure the database you are migrating has already been created in Plesk with the correct username and password.
1. Log onto your server as root using ssh.
2. Edit the mysql config file to use the slave drive
nano /etc/my.cnf
3. Comment out the current path, add your slaved drive’s path and save the file
# datadir = /var/lib/mysql datadir = /olddrive/var/lib/mysql
(save the file using
4. Restart mysql to load the new settings
service mysqld restart
5. Create a dump file of the desired database
mysqldump -u admin -p`cat /etc/psa/.psa.shadow` [database_name] > /tmp/database_name.sql
(Repeat this step for all databases that need to be imported)
6. Repeat step 2-4 and reset the original setting
datadir = /var/lib/mysql # datadir = /olddrive/var/lib/mysql
7. Import the database
mysql -u admin -p`cat /etc/psa/.psa.shadow` [database_name] < /tmp/database_name.sql
(Repeat this step for all .sql files created in step 5)
Optimize Mysql
Did you know that mysql comes with configuration files for better optimization? These files are located at:
/usr/share/doc/mysql-server-5.1.71/my-huge.cnf /usr/share/doc/mysql-server-5.1.71/my-innodb-heavy-4G.cnf /usr/share/doc/mysql-server-5.1.71/my-large.cnf /usr/share/doc/mysql-server-5.1.71/my-medium.cnf /usr/share/doc/mysql-server-5.1.71/my-small.cnf /usr/share/man/man5/openssl.cnf.5ssl.gz
Or
/usr/share/mysql/my-huge.cnf /usr/share/mysql/my-innodb-heavy-4G.cnf /usr/share/mysql/my-large.cnf /usr/share/mysql/my-medium.cnf /usr/share/mysql/my-small.cnf
Stop mysql
# service mysqld stop Stopping mysqld: [ OK ]
backup the original my.cnf file.
# mv /etc/my.cnf /etc/my.cnf.original
Move the new config file to /etc based on your needs:
# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
Restart MySQL:
# service mysqld start
Other Resources:
http://geekdecoder.com/using-mysqltuner/
http://www.hardwaresecrets.com/article/How-to-Optimize-a-MySQL-Server/1747
http://www.thegeekstuff.com/2011/03/sar-examples/
http://www.cyberciti.biz/tips/top-linux-monitoring-tools.html
http://www.codero.com/knowledge-base/questions/319/How+to+install+mytop+for+database+performance+monitoring%3A
http://httpd.apache.org/docs/current/misc/perf-tuning.html
http://dev.mysql.com/doc/refman/5.0/en/optimization.html
Mytop
Install the repository
CentOS 5.x
rpm -Uhv http://apt.sw.be/redhat/el5/en/i386/rpmforge/RPMS/rpmforge-release-0.3.6-1.el5.rf.i386.rpm
CentOS 6.x 64 bit
rpm -Uhv http://apt.sw.be/redhat/el6/en/i386/rpmforge/RPMS/rpmforge-release-0.5.3-1.el6.rf.x86_64.rpm
Install mytop
yum -y install mytop
Configuration
Odds are, you will get an error initially running this program unless you configure it first.
whereis mytop
Example: /usr/bin/mytop or /usr/local/bin/mytop
nano /usr/bin/mytop or nano /usr/local/bin/mytop
Locate the following lines:
my %config = ( delay => 5, host => ‘localhost’, db => ‘test’, user => ‘root’, …
change the following line and save the file:
db => ‘mysql’, Run mytop
Plesk
mytop -u admin -p`cat /etc/psa/.psa.shadow`
WHM / cPanel
mytop
Now – go here to learn how to use it!
http://linux.die.net/man/1/mytop
Debugging sleeping connections with MySQL
Have you ever seen connection in the SHOW PROCESSLIST output which is in “Sleep” state for a long time and you have no idea why this would happen ?
I see if frequently with web applications and it is often indication of trouble. Not only it means you may run out of MySQL connections quicker than you expected but it also frequently indicates serious problems in the application. If you do not use persistent connections and you have connection in Sleep stage for 600 seconds what could it be ? It may mean some of your pages take that long to generate (or might be the code simply gets into the tight loop and page never gets generated) it also could mean some of external Web Services are slow or not available and you’re not dealing with timeouts properly. Or may be you have several connections to MySQL server and right now running query which takes that long ? In any case it is something frequently worth looking at.
First task is to find to which process the connection belongs. Using different user names for different application is a good practice however it will not tell you which of apache children is handling request in question. If you just want to fix it, ie by restarting apache it is enough but if you want to figure our why it is happening you need more info.
You my notice in the “Host” filed of SHOW PROCESSLIST output not only host but also port is specified, showing you something like “192.168.1.70:58555″ This port can be used to identify the process which owns connection in question:
[root@w1 ~]# netstat -ntp | grep :45384 tcp 0 0 192.168.1.70:45384 192.168.1.82:3306 ESTABLISHED 28540/php-cgi
As you can see in this case we can find php-cgi is holding connection in question (this is lighttpd based system with fastcgi)
Now you know the process and you can use your favorite tools to check what that process is doing.
[root@w1 ~]# netstat -ntp | grep 28540 tcp 0 0 192.168.1.70:58555 192.168.1.90:11211 ESTABLISHED 28540/php-cgi tcp 0 0 192.168.1.70:52711 192.168.1.88:8080 ESTABLISHED 28540/php-cgi tcp 0 0 192.168.1.70:45384 192.168.1.82:3306 ESTABLISHED 28540/php-cgi tcp 0 0 192.168.1.70:45399 192.168.1.82:3306 ESTABLISHED 28540/php-cgi tcp 0 0 192.168.1.70:45407 192.168.1.82:3306 ESTABLISHED 28540/php-cgi tcp 0 0 192.168.1.70:45408 192.168.1.82:3306 ESTABLISHED 28540/php-cgi tcp 0 0 192.168.1.70:35556 192.168.1.92:11211 ESTABLISHED 28540/php-cgi
Using same netstat command and filtering on the PID we can find which connections does this process have. Here you can see it has couple of memcached connections. Few MySQL connections (to the same host, which if usually bad idea) and connection to some external web server.
You can use strace -p to see what host is doing, it often gives a clue. In this case I for example found the process is stuck in pool() system call reading from network. Using netstat can give you an idea what it can be but if you do not like guessing you can use gdb -p . It will not print you exact line of code in PHP which is running but can give you some good ideas – for example in this case I could find stack trace originated from php stream functions not from libmysql or memcache.so, which means it is not MySQL or memcache connections leaving last candidate as the only choice. I also could see some of the variables in GDB “bt” command output which also hinted what could be the problem.
By the way does anyone know any debugger which can connect to PHP process or apache with mod_php and provide backtrace in PHP terms not the one for zend engine ? That would be pretty cool.
Yet another great tool which you can use is server-status if you’re running apache. This way you will see the URL which that process is processing and so get few more hints on what may be happening or even get repeatable example in some cases.
The tools I mentioned regarding figuring our what is happening with the process are not only helpful to debug sleeping connections with MySQL but many other cases when you see web application locking up or starting to runs in the tight loop consuming too much CPU time.
If you know any other tools which could be helpful in this regard would appreciate your comments. There might be some smarter tools out where for production tracing.