How to Optimize MySQL

CentOS
There is a default my.cnf that comes with mysql (4+5) that will make mysql run a bit quicker if you have 2+ gig of ram cp -f /usr/share/mysql/my-large.cnf /etc/my.cnfThere is also my-huge.cnf, or my-medium.cnf depending on your hardware setup.   Check the contents of these my*.cnf files for the one that’s right for you.

*NOTE* the log-bin option is enabled  by default.  This will quickly use a lot of disk space.  It’s recommended to comment out the log-bin line from your /etc/my.cnf, if it exists.

 

Remember to restart mysql when you are done with your my.cnf tweaking:

Redhat:/sbin/service mysqld restart

FreeBSD:/usr/local/etc/rc.d/mysqld restart

Multiple vulnerabilities mysql – Mandriva

  Problem Description:
  Multiple vulnerabilities has been discovered and corrected in mysql:
  Unspecified vulnerability in MySQL 5.5.x before 5.5.23 has unknown
  impact and attack vectors related to a Security Fix, aka Bug
  #59533. NOTE: this might be a duplicate of CVE-2012-1689, but as of
  20120816, Oracle has not commented on this possibility (CVE-2012-2750).
  Unspecified vulnerability in the MySQL Server component in Oracle
  MySQL 5.1.70 and earlier, 5.5.32 and earlier, and 5.6.12 and earlier
  allows remote authenticated users to affect availability via unknown
  vectors related to Optimizer (CVE-2013-3839).
  The updated packages have been upgraded to the 5.1.72 version which
  is not vulnerable to these issues.
  _______________________________________________________________________
  References:
  _______________________________________________________________________
  Updated Packages:
  Mandriva Enterprise Server 5:
  ba2a7994838db84ffdc554e6897ec6b8  mes5/i586/libmysql16-5.1.72-0.1mdvmes5.2.i586.rpm
  f761773fd2dd239a9982e41488a01589  mes5/i586/libmysql-devel-5.1.72-0.1mdvmes5.2.i586.rpm
  abfdfe6c0c1af08a146002d41c65ccf7  mes5/i586/libmysql-static-devel-5.1.72-0.1mdvmes5.2.i586.rpm
  5a356e9080a7e351c34d69615b67138f  mes5/i586/mysql-5.1.72-0.1mdvmes5.2.i586.rpm
  ceea7d8c944d46832cd7d1715a0b9faa  mes5/i586/mysql-bench-5.1.72-0.1mdvmes5.2.i586.rpm
  0c534ad2edd6e3a19ab619bff7e28411  mes5/i586/mysql-client-5.1.72-0.1mdvmes5.2.i586.rpm
  1b8da9ced8bb0f1b641f4a610da6dfc1  mes5/i586/mysql-common-5.1.72-0.1mdvmes5.2.i586.rpm
  1cf5ea7c2186cae90ca188fe5ee4d96b  mes5/SRPMS/mysql-5.1.72-0.1mdvmes5.2.src.rpm
  Mandriva Enterprise Server 5/X86_64:
  412d97676eff68f560968bfb499342ca  mes5/x86_64/lib64mysql16-5.1.72-0.1mdvmes5.2.x86_64.rpm
  d53dc8b107a306df0da123b00fef42e4  mes5/x86_64/lib64mysql-devel-5.1.72-0.1mdvmes5.2.x86_64.rpm
  3f65e5f322b7d0cb98bfb3d5c92937a1  mes5/x86_64/lib64mysql-static-devel-5.1.72-0.1mdvmes5.2.x86_64.rpm
  5237d5ee69b11bb576f117dd9477ec56  mes5/x86_64/mysql-5.1.72-0.1mdvmes5.2.x86_64.rpm
  db8fe6784e34ddb88b7e020db79d1272  mes5/x86_64/mysql-bench-5.1.72-0.1mdvmes5.2.x86_64.rpm
  9a15c79afd52d0a5794d52d06eef1146  mes5/x86_64/mysql-client-5.1.72-0.1mdvmes5.2.x86_64.rpm
  bb37ec21d892efe9950f1dc4b09fda6b  mes5/x86_64/mysql-common-5.1.72-0.1mdvmes5.2.x86_64.rpm
  1cf5ea7c2186cae90ca188fe5ee4d96b  mes5/SRPMS/mysql-5.1.72-0.1mdvmes5.2.src.rpm
  _______________________________________________________________________
  To upgrade automatically use MandrivaUpdate or urpmi.  The verification
  of md5 checksums and GPG signatures is performed automatically for you.
  All packages are signed by Mandriva for security.  You can obtain the
  GPG public key of the Mandriva Security Team by executing:
  gpg –recv-keys –keyserver pgp.mit.edu 0x22458A98
  You can view other update advisories for Mandriva Linux at:

How to upgrade mysql with custombuild

To upgrade mysql using the custombuild script, do the following:

cd /usr/local/directadmin/custombuild
./build set mysql 5.1
./build set mysql_inst yes
./build set mysql_backup yes
./build update
./build mysql

Where mysql can be 5.0, 5.1 or 5.5.

A full raw sql backup will be run prior to the upgrade if you have mysql_backup=yes set.  It goes without saying, always make backups, either with this tool, or with other means.

After the mysql update, always recompile php.

./build php n

MySQL Socket errors

This is an error that many people who run PHP and MySQL are familiar with:

ERROR 2002: Can’t connect to local MySQL sever through socket

‘/var/lib/mysql/mysql.sock’ (2)

Luckily, this is relatively easy to solve in most cases. Here’s what to do.

·First of all, we need to decide where the MySQL socket file should be. For this text we will assume that you would like the socket file to be placed in the MySQL default location for a Redhat system which is /var/lib/mysql/mysql.sock.

·Second, is MySQL running? This is easily overlooked, but check and make sure that MySQL is running with a command such as:

[[email protected] ~]# ps aux | grep mysql

root67220.00.146561132 ?S08:200:00 /bin/sh /usr/bin/mysqld_safe –datadir=/var/lib/mysql –socket=/var/lib/mysql/mysql.sock –log-error=/var/log/mysqld.log –pid-file=/var/run/mysqld/mysqld.pid

mysql67640.12.6 128328 16904 ?Sl08:200:01 /usr/libexec/mysqld –basedir=/usr –datadir=/var/lib/mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –skip-external-locking –port=3306 –socket=/var/lib/mysql/mysql.sock

root70510.00.14044676 pts/0R+08:400:00 grep mysql

Good, it’s running so we can move on. If MySQL is not running, try to start it with “/etc/init.d/mysqld start”

·Alright, so MySQL is humming along, but it is pretty useless if we cannnot connect to it. The easiest way to see what MySQL thinks it should be using for socket file is by running the following command:

[[email protected] ~]# /usr/libexec/mysqld –print-defaults

/usr/libexec/mysqld would have been started with the following arguments:

–port=3306 —socket=/var/lib/mysql/mysqld.sock –skip-locking –key_buffer=16K

–max_allowed_packet=1M –table_cache=4 –sort_buffer_size=64K –read_buffer_size=256K

–read_rnd_buffer_size=256K –net_buffer_length=2K –thread_stack=64K –server-id=1

–port=3306 –socket=/var/lib/mysql/mysql99999.sock –skip-locking –key_buffer=16K

–max_allowed_packet=1M –table_cache=4 –sort_buffer_size=64K –read_buffer_size=256K

–read_rnd_buffer_size=256K –net_buffer_length=2K –thread_stack=64K –server-id=1

[[email protected]  ~]#

I’ve highlighted the part that you should look at to find what you need to know. It looks like I must have had a bad keystroke when editing my “/etc/my.cnf” the other day.

·Here’s an excerpt from my “/etc/my.cnf”. Make sure that when you edit this file that you update the correct socket specification. You will notice that there two of them, one of them is for the MySQL client, the second is for the MySQL daemon. Update the one from the “[mysqld]” section.

——-cut———

[client]

#password= your_password

port= 3306

socket= /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server

[mysqld]

port= 3306

socket= /var/lib/mysql/mysqld.sock

skip-locking

key_buffer = 16K

max_allowed_packet = 1M

——-cut———

Change it to:

——-cut———

[client]

#password= your_password

port= 3306

socket= /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server

[mysqld]

port= 3306

socket= /var/lib/mysql/mysql.sock

skip-locking

key_buffer = 16K

max_allowed_packet = 1M

——-cut———

·Okay, now that this is updated, we should restart MySQL.

[[email protected] ~]# /etc/init.d/mysqld restart

Stopping MySQL:[OK]

Starting MySQL:[OK]

[[email protected] ~]#

·Okay, is everything working now? If you were using the command line mysql client, give it a try. If you were using php, try your webpage again. If you are still experiencing problems with php not being able to find the socket, then you should check where php thinks the socket file lives.

[[email protected] ~]# php -i | grep mysql.default_socket

mysql.default_socket => no value => no value

[[email protected] ~]# php -i | grep php.ini

Configuration File (php.ini) Path => /etc

Loaded Configuration File => /etc/php.ini

It appears that php does not have a socket location set, meaning that it will use what MySQL tells it is the default (in this case being /var/lib/mysql/mysql.sock), but to make sure lets specify a location.

·Notice that in the last step, we also wanted to find out where php’s php.ini configuration file is located and we know that it is in “/etc/php.ini”. Let’s fire up our favorite text editor and fix this.

——-cut———

; compile-time value defined MYSQL_PORT (in that order).Win32 will only look

; at MYSQL_PORT.

mysql.default_port =

; Default socket name for local MySQL connects.If empty, uses the built-in

; MySQL defaults.

mysql.default_socket =

; Default host for mysql_connect() (doesn’t apply in safe mode).

mysql.default_host =

; Default user for mysql_connect() (doesn’t apply in safe mode).

mysql.default_user =

——-cut———

Changes to:

——-cut———

; compile-time value defined MYSQL_PORT (in that order).Win32 will only look

; at MYSQL_PORT.

mysql.default_port =

; Default socket name for local MySQL connects.If empty, uses the built-in

; MySQL defaults.

mysql.default_socket = “/var/lib/mysql/mysql.sock”

; Default host for mysql_connect() (doesn’t apply in safe mode).

mysql.default_host =

; Default user for mysql_connect() (doesn’t apply in safe mode).

mysql.default_user =

——-cut———

Now both the command line MySQL client as well as php should be able to connect to MySQL just fine!

 

Welcome to our blog

This blog will cover the following topics:

  • Attacker.NET offers & News
  • Security Advisories
  • Tutorials & How-To’s
  • Genetal IT news, Issues and Best practices