Here is a quick tip that will show how you can get a small sample dataset from a mysql database using mysqldump. We frequently need to get a small snapshot from a very big production database to import it into a development or staging database that will not need all the original data; let’s say we need 1,000,000 records from all the tables in the database; we will just use the option –where=”true LIMIT X”, with X the number of records we want mysqldump to stop after.

Simply we will run something like (add whatever other options you need to mysqldump):

mysqldump --opt --where="true LIMIT 1000000" mydb > mydb1M.sql
Read the rest of this entry »

The InnoDB Team just released the InnoDB Plugin version 1.0.3. From their announcement here are the main points of this release:

• Enhanced scalability: the Google SMP enhancement for synchronization
• More efficient memory allocation: ability to use platform allocator tuned for multi-core systems
• Improved out-of-the-box scalability: unlimited concurrent thread execution by default
• Dynamic tuning: at run-time, enable or disable insert buffering and adaptive hash indexing

wow… now this is indeed some great news for innodb users… I am writting this, and still I can’t believe that they’ve included the Google SMP patch in their official release. I can only assume that alternative projects as XtraDB, Drizzle, Percona patches, Google patches, etc. made Oracle to look back and try to do something with innodb besides the regular bug fixes. Even if we already use several of the great ‘unofficial alternatives’ this is good news for everyone.
Way to go Oracle! and looking forward for future performance improvements in the official innodb plugin; including existing patches that are out there already for sometime is a good start, but internal improvements from the innodb team would be also great .

Here are some performance results based on their own tests:
http://www.innodb.com/innodb_plugin/plugin-performance/

There are various methods to run multiple instances of mysql (on different ports) on the same machine. We can either compile the mysql binary with different defaults and paths, use mysqld_multi or the MySQL Sandbox project. Still the simplest solution I’ve  used in the past for such situations is to use the same binary and use a separate configuration file (with separate port, pid, socket and data directory). This post will explain this method. Read the rest of this entry »

After a long time (with the first RC-5.1.22 released more than a year ago, and the first public version back in 2005), mysql version 5.1 was finally declared ready for production use, and 5.1.30-GA is available for download.

MySQL 5.1 provides a number of new enhancements including:

• Better performance with partitioning
• Easy manageability with the event scheduler
• Improved Plugin interface
• Logs on demand
• More reliability with row-based replication.
• The embedded server, which was not maintained during MySQL 5.0, is back in service!

Read the rest of this entry »

The latest MySQL Community Edition release, 5.0.67 will not be included in Debian Lenny as the distribution is frozen meaning no upstream new releases are going to be included into testing before lenny is released. Lenny will ship with MySQL 5.0.51a, while 5.0.67 (or a newer version if that will be the case) will be added to debian testing right after the release.

In the meantime, if you want to use 5.0.67 you can either download the source packages from MySQL or use the packages Norbert Tretkowski (one of the mysql package maintainers) has made available. The deb sources allow you to recompile the package on any architecture you might need it (only amd64 binary packages are provided).

Source: http://tretkowski.de/blog/archives/418-MySQL-5.0.67-not-uploaded-to-Debian.html

Normally MySQL replication will stop whenever there is an error running a query on the slave. This happens in order for us to be able to identify the problem and fix it, and keep the data consistent with the mater that has sent the query. You can skip such errors, even if this is not recommended, as long as you know really well what are those queries and why they are failing, etc.

For example you can skip just one query that is hanging the slave using:
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

There might be cases where you will want to skip more queries. For example you might want to skip all duplicate errors you might be getting (output from show slave status;):
"1062 | Error 'Duplicate entry 'xyz' for key 1' on query. Default database: 'db'. Query: 'INSERT INTO ..."

Read the rest of this entry »

MySQL offers for download several precompiled types of packages for installation (rpm’s for various distros, tar.gz, etc.). This post will show how you can install the latest version of mysql5.0 available at this time 5.0.51a from the binary tar.gz distribution.

Fist you need to download somewhere on your system the tar.gz package from mysql (I will assume you have done this inside /usr/local/src/), mysql-5.0.51a-linux-i686-glibc23.tar.gz from the closest mysql mirror to your location. If you are running a different architecture, please download the appropriate file.

Read the rest of this entry »

Here is a quick tip that will show several methods to change the mysql root password (that is normally empty at mysql initial install).

Method 1: using the SET PASSWORD command:

mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

Method 2: using mysqladmin

mysqladmin -u root password "newpass"

Read the rest of this entry »

Here is a quick tip how to create from mysql cli a new database and a new mysql user that has full privileges on this newly created database:
mysql -uroot -p
<enter_mysql_root_pass>
CREATE DATABASE <DB_NAME>;
GRANT ALL PRIVILEGES ON <DB_NAME>.* TO 'my_user'@'localhost' IDENTIFIED BY 'my_password' WITH GRANT OPTION;

Read the rest of this entry »

“After all the industry speculation about MySQL being a “hot 2008 IPO”, this probably takes most of us by surprise — users, community members, customers, partners, and employees. And for all of these stakeholders, it may take some time to digest what this means. Depending on one’s relationship to MySQL, the immediate reaction upon hearing the news may be a mixture of various feelings, including excitement, pride, disbelief and satisfaction, but also anxiety.”

The deal is for approximately $1 billion in total consideration!

From: http://blogs.mysql.com/kaj/sun-acquires-mysql.html/
also more at Sun’s announcement:
http://www.sun.com/aboutsun/pr/2008-01/sunflash.20080116.1.xml