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:

[code lang=”bash”]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[/code]

To resolve it, install libmysqlclient-dev:

[code lang=”bash”]sudo apt-get install libmysqlclient-dev[/code]

And now you can install mysql2 successful:

[code lang=”bash”]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…[/code]

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

[code lang=”bash”]sudo nano /etc/mysql/my.cnf[/code]

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

[code lang=”bash”]# 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[/code]

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:

[code lang=”bash”]sudo cat /var/log/mysql/mysql-slow.log[/code]

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

[code lang=”bash”]sudo /etc/init.d/mysql restart[/code]

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:

[code lang=”bash”]mysqlcheck -uUsername -pSecret –all-databases[/code]

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

To repair them, use:

[code lang=”bash”]mysqlcheck -uUsername -pSecret –all-databases –auto-repair[/code]

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

[code lang=”bash”]sudo /etc/init.d/mysql start[/code]

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.

[code lang=”bash”]sudo myisamchk –max-record-length=1048576 -o -f /var/lib/mysql/db_name/table_name.MYI[/code]

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:

[code lang=”bash”]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![/code]

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.

[code lang=”bash”]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[/code]

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:

[code lang=”bash”]mysqldump -u[Username] -p[Password] [Database] > output.sql[/code]

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:

[code lang=”bash”]mysqldump -ukahwee -psecret justrealized_db > output.sql[/code]

And to Gzip:

[code lang=”bash”]mysqldump -u[Username] -p[Password] [Database] | gzip > output.sql.gz[/code]

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

[code lang=”bash”]mysql -u[Username] -p[Password] [Database] < output.sql[/code]

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

[code lang=”bash”]mysql -ukahwee -psecret justrealized_db < output.sql[/code]

And to Ungzip:

[code lang=”bash”]gunzip < output.sql.gz | mysql -u[Username] -p[Password] [Database][/code]

[ad#simple]

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:

[code lang=”bash”]%SystemRoot%system32;%SystemRoot%;%SystemRoot%System32Wbem[/code]

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:xampplitemysqlbin’, so I would be appending this:

[code lang=”bash”];C:xampplitemysqlbin[/code]

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

How to optimize MySQL database with a command

When you delete large portions for data from your MySQL table, the database becomes more fragmented. It is always a good idea to re-optimize your MySQL database especially if you have a lot of varying characters (VARCHAR). Here’s the command to optimize just one table:

[code lang=”sql”]OPTIMIZE TABLE foo[/code]

[ad#highlight]

But I am looking for a more command line solution and here it is:

[code lang=”bash”]mysqlcheck -op database_name[/code]

The ‘o’ refers to optimize. And the ‘p’ is the option for password requirement. You’ll be prompted for the password.

If you’re logged in as user ‘john’ in your SSH, the MySQL username will be ‘john’. If that’s not what you want, use option ‘u’ like this:

[code lang=”bash”]mysqlcheck -op -u user_name database_name[/code]

And to optimize all databases:

[code lang=”bash”]mysqlcheck -op -u user_name –all-databases[/code]

You can also use option ‘r’ to repair.

Note that the optimization only works for MyISAM, InnoDB, and ARCHIVE tables.

Some problems with XAMPP 1.7.0 and Windows 7

For some reason, I couldn’t get XAMPP 1.7.0 working in Windows 7. There appears to be some sort of issues. I tried the zip package or the 7z package and both have the same result. Instead I found XAMPP 1.6.8 to be working fine for Windows 7 and is using that for web development using PHP and MySQL. I can confirm that cURL and sendmail is working fine in the Windows 7 environment with XAMPP 1.6.8.

MySQL founder leaves Sun due to dissatisfaction

MySQL founder leaves Sun. “The main reason for leaving was that I am not satisfied with the way the MySQL server has been developed,” Widenuis blogged. Sun acquired MySQL last year.

MySQL Founder Monty leaves Sun

Widenuis was livid about the way that the MySQL 5.1 release happened which he thought was incomplete and loaded with bugs. Sun countered that Widenuis was entitled to his opinion, but in their view MySQL 5.1 was a solid release.

“The main reason for leaving was that I am not satisfied with the way the MySQL server has been developed,” Widenuis blogged.

He added that he parted on good termes with Sun and he expects to continue to do business and work together with them. In parting Widenuis had kind words for Sun which in part were a faint echo of things he said last year during a keynote at OSCON.

“I still think that Sun was the best possible buyer for MySQL and I feel sad that things didn’t work out together. Sun has a lot of good things going on and I hope that they will continue their path to create and promote Open source. I will be available for Sun in helping them with their goals in the Open source space,” Widenuis wrote. (Source: InternetNews)