Tag Archives: mysql

MySQL.com compromised by SQL injection

This day just had to come:

MySQL.com compromised

MySQL.com (the official site for the MySQL database) was compromised via (shocking!) blind SQL injection. A post was sent today to the full disclosure list explaining the issue and dumping part of their internal database structure.

What is worse is that they also posted the password dump online and some people started to crack it already. Some of the findings are pretty bad, like that the password used by the MySQL director of product management is only 4 numbers (6661) and also posted multiple admin passwords for blogs.mysql.com…

MySQL have not said anything about this attack, but we will post more details as we learn more about it.

Source: Sucuri

The irony.

How to install mysql2 gem in Ubuntu

Got an error message when you install the mysql2 gem in Ubuntu? Before you install mysql2, make sure you have libmysqlclient-dev installed or you’ll get the following:

kahwee@kahwee-desktop:/$ gem install mysql2
Building native extensions.  This could take a while...
ERROR:  Error installing mysql2:
	ERROR: Failed to build gem native extension.

/home/kahwee/.rvm/rubies/ruby-1.9.2-p0/bin/ruby extconf.rb
checking for rb_thread_blocking_region()... yes
checking for mysql_query() in -lmysqlclient... no
checking for main() in -lm... yes
checking for mysql_query() in -lmysqlclient... no
checking for main() in -lz... yes
checking for mysql_query() in -lmysqlclient... no
checking for main() in -lsocket... no
checking for mysql_query() in -lmysqlclient... no
checking for main() in -lnsl... yes
checking for mysql_query() in -lmysqlclient... no
checking for main() in -lmygcc... no
checking for mysql_query() in -lmysqlclient... no
*** extconf.rb failed ***
Could not create Makefile due to some reason, probably lack of
necessary libraries and/or headers.  Check the mkmf.log file for more
details.  You may need configuration options.

Provided configuration options:
	--with-opt-dir
	--without-opt-dir
	--with-opt-include
	--without-opt-include=${opt-dir}/include
	--with-opt-lib
	--without-opt-lib=${opt-dir}/lib
	--with-make-prog
	--without-make-prog
	--srcdir=.
	--curdir
	--ruby=/home/kahwee/.rvm/rubies/ruby-1.9.2-p0/bin/ruby
	--with-mysql-config
	--without-mysql-config
	--with-mysql-dir
	--without-mysql-dir
	--with-mysql-include
	--without-mysql-include=${mysql-dir}/include
	--with-mysql-lib
	--without-mysql-lib=${mysql-dir}/lib
	--with-mysqlclientlib
	--without-mysqlclientlib
	--with-mlib
	--without-mlib
	--with-mysqlclientlib
	--without-mysqlclientlib
	--with-zlib
	--without-zlib
	--with-mysqlclientlib
	--without-mysqlclientlib
	--with-socketlib
	--without-socketlib
	--with-mysqlclientlib
	--without-mysqlclientlib
	--with-nsllib
	--without-nsllib
	--with-mysqlclientlib
	--without-mysqlclientlib
	--with-mygcclib
	--without-mygcclib
	--with-mysqlclientlib
	--without-mysqlclientlib


Gem files will remain installed in /home/kahwee/.rvm/gems/ruby-1.9.2-p0/gems/mysql2-0.2.6 for inspection.
Results logged to /home/kahwee/.rvm/gems/ruby-1.9.2-p0/gems/mysql2-0.2.6/ext/mysql2/gem_make.out

To resolve it, install libmysqlclient-dev:

sudo apt-get install libmysqlclient-dev

And now you can install mysql2 successful:

kahwee@kahwee-desktop:/$ gem install mysql2
Building native extensions.  This could take a while...
Successfully installed mysql2-0.2.6
1 gem installed
Installing ri documentation for mysql2-0.2.6...
Installing RDoc documentation for mysql2-0.2.6...

Great!

[I tried this on Ubuntu 10.10.]

Oracle shuts down open source test servers

Oracle shuts down open source test servers that PostgreSQL uses to test their builds. PostgreSQL is competing with Oracle’s MySQL and Oracle Database.

Oracle shuts down open source test servers

Like most open source platforms, PostgreSQL relies on an army of distributed volunteers. It is volunteers that, for example, operate the PostgreSQL Build farm, a “distributed, automated build and verify system” built by enthusiast Andrew Dunstan.

Oracle has shut down servers Sun Microsystems was contributing to the build farm for open source database software, PostgreSQL, forcing enthusiasts to scramble to find new hosts to test updates to their software on the Solaris operating system.

“It’s a vital piece of the infrastructure for developing PostgreSQL,” Dunstan told iTnews. “Before it existed, if some change we made broke on some platform, it was often weeks or months before we found out about it. Now we know within hours.”

At the start of July, Oracle shut down its three PostgreSQL build farm servers without warning, leaving the PostgreSQL community rushing to find replacements.

Dunstan said he “suspects” Oracle does view PostgreSQL as a competitor. (Source: IT News)

I love to see Oracle post their financial results, they have made quite a bit of cost cutting moves since the acquisition. You can hardly blame them too; they’ve got shareholders to report to. After all, doing too much charity work on open source is partly why Sun Microsystems failed in the first place.

How to log slow MySQL queries

I haven’t been generous enough to spend money on higher RAM for my blogs. I started to notice slow downs in the blog recent, particularly caused by a particular plugin that does related posts. I found out by checking on slow queries log. Here’s how to get MySQL to log slow queries:

If you are using Ubuntu of Debian-based operating systems, it’s found in /etc/mysql/my.cnf. You may need to use

sudo nano /etc/mysql/my.cnf

Press CTRL + W, that’s search in nano. Find “slow” for the section for logging slow queries.

# Here you can see queries with especially long duration
#log_slow_queries        = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes

I uncommented “log_slow_queries” and “long_query_time”. I put long_query_time to 8. That requires a bit of experimentation. It depends on your environment.

I run the following command to check the slow queries:

sudo cat /var/log/mysql/mysql-slow.log

After you change the configuration in my.cnf, you need to restart. You can do so using:

sudo /etc/init.d/mysql restart

Hope it helps.

How to repair MySQL tables

There are generally 2 ways of repairing MySQL tables using the command line. First is to use “mysqlcheck“, the other is to use “myisamchk” (only for MyISAM). This is more for my own information.

This has been tested on Ubuntu 9.10 with MySQL 5

mysqlcheck

This is how you can check if your database tables are fine:

mysqlcheck -uUsername -pSecret --all-databases

Change “Username” to your username and “Secret” to your password.

To repair them, use:

mysqlcheck -uUsername -pSecret --all-databases --auto-repair

This requires of your MySQL daemon to be running. If it isn’t running use this to start:

sudo /etc/init.d/mysql start

myisamchk

If you have trouble running ‘mysqlcheck’, you can consider using ‘myisamchk’ instead if your tables are MyISAM. This command can be performed without MySQL daemon running. This fixes the data in your file system directly and this has saved me once.

sudo myisamchk --max-record-length=1048576 -o -f /var/lib/mysql/db_name/table_name.MYI

Change “db_name” to your database name and “table_name” to the name of the table you wish to repair.

My disk got full

I just realized I totally used up my disk space in my slice:

briecheese ~: sudo /etc/init.d/mysql start
[sudo] password for me: 
 * /etc/init.d/mysql: ERROR: The partition with /var/lib/mysql is too full!

It has never occurred to me that this would happen. I start to do some clearing up and delete some backups that I no longer need.

briecheese ~/www: df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             9.4G  6.7G  2.3G  75% /
varrun                129M   40K  129M   1% /var/run
varlock               129M     0  129M   0% /var/lock
udev                  129M   16K  129M   1% /dev
devshm                129M     0  129M   0% /dev/shm
overflow              1.0M     0  1.0M   0% /tmp

I managed to free more than 2 Gb.

How to import and export MySQL database into an SQL file

Or Gzip for the matter. Here’s the command to run in your UNIX-based server to import or export via an SQL file, this is useful for performing backup and restoring of a MySQL database. (I wrote a similar import and export guide for PostgreSQL.) The mysqldump utility performs just that:

Exporting using mysqldump:

mysqldump -u[Username] -p[Password] [Database] > output.sql

For example, my username is ‘kahwee’, my password being ‘secret’ and database being ‘justrealized_db’, I would run the following to export my database to a SQL file:

mysqldump -ukahwee -psecret justrealized_db > output.sql

And to Gzip:

mysqldump -u[Username] -p[Password] [Database] | gzip > output.sql.gz

Importing using mysql:

To import back, we can use the mysql utility in a similar fashion, note that the > (greater than) has change to a < (lesser than).

mysql -u[Username] -p[Password] [Database] < output.sql

For example, my username is 'kahwee', my password being 'secret' and database being 'justrealized_db', I would run the following to import my database:

mysql -ukahwee -psecret justrealized_db < output.sql

And to Ungzip:

gunzip < output.sql.gz | mysql -u[Username] -p[Password] [Database]

Backing up and restoring MySQL databases in Windows

Unfortunately, you can't use gzip here. So all those commands above with gzip can't work. The rest, however, still works. However, mysqldump and mysql may not be set in your system environment variables. These are instructions on how to add them for Windows Vista:

Editing system environment variables in Windows Vista.

Click on 'Edit the system environment variables', a dialog box will pop up. Click on 'Environment Variables...', you should be greeted with the following dialog box:

Editing the path for environment variables

My path looks like this before I add anything:

%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem

Append your MySQL bin directory at the back of what is already there. I use XAMPP (XAMPP lite to be specific) which has its MySQL bin folder located here ';C:\xampplite\mysql\bin\', so I would be appending this:

;C:\xampplite\mysql\bin\

That's all I guess, hope it is helpful for you.