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

I just stumbled across this interesting mysql tuning script called MySQLTuner. Don’t see this (or any other similar script) as the magical tool that will optimize your mysel server… There is no such thing, because there are so many different applications and each will require its specific database tunings.
Still, mysqltuner can be very useful tool in many cases, like for ex:

• it gives a quick overview on your running mysql configuration and some common sense recommendations.
• i really liked how it computes the total amount of memory needed for the current configuration (assuming all global buffers will be used and the maximum number of threads being connected).
• very simple usage: you just have to download and run it.

Read the rest of this entry »

MySQL announced that they plan to release by the end of the month a ‘release candidate’ of MySQL 5.1 a major new upgrade of the world’s most popular open source database server.

During the announcement that was made at the inaugural ‘Japanese MySQL Users Conference’ earlier last week, MySQL mentioned that several GPL-licensed products are scheduled to be available for download by the end of September: Read the rest of this entry »

One of the most important steps in optimizing and tuning mysql is to identify the queries that are causing problems. How can we find out what queries are taking a long time to complete? How can we see what queries are slowing down the mysql server? Mysql has the answer for us and we only need to know where to look for it…
Normally from my experience if we take the most ‘expensive’ 10 queries and we optimize them properly (maybe running them more efficiently, or maybe they are just missing a simple index to perform properly), then we will immediately see the result on the overall mysql performance. Then we can iterate this process and optimize the new top 10 queries. This article shows how to identify those ’slow’ queries that need special attention and proper optimization. Read the rest of this entry »

I often need to make a quick backup of one mysql database. This happens right before there will be some major changes to the database, or before performing an upgrade on the mysql server software. Now I don’t recommend this to be done regularly, but still, I use this extensively in this kind of situations… If you are looking for an automated solution to backup your databases you might want to check my other post: “Backup your MySQL databases automatically”
MySQL provides us the tool required to do this: mysqldump. As the name implies, this can make a dump of one database or even to all the databases on the server. You can see all the options on its help manual or on the mysql site.

Basically this is used like:

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] –databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] –all-databases [OPTIONS]

Read the rest of this entry »