Consistent backup:A consistent backup is one that represents a snapshot of all data at a point in time. Consistent backups are used for disaster recovery. An inconsistent backup can be useful for retrieving partial data -- for example, if a long-time customer accidentally deletes information from his profile, you can go back to an inconsistent backup and retrieve that information. It is not important that all the data be consistent with each other when retrieving a partial amount of point-in-time data.Logical backups:

Today's contradiction:MySQL has server variables named "new" and "old".The "new" variable can be set per-session and globally, and is dynamic. The "old" variable is not dynamic, and only global in scope. Both default to FALSE in MySQL 5.1.According to the manual, the "new" variable:was used in MySQL 4.0 to turn on some 4.1

As Baron points out, Percona's Xtrabackup tool can be used just like InnoDB Hot Backup.Many are wondering, "is it good enough?" In fact, I wondered the same thing, and after a few weeks of using and testing Xtrabackup (on machines that have MyISAM and InnoDB tables), I can say:0) We have not run into any problems with

If you have a 12-server MySQL Cluster with:1 Management Node3 SQL Nodes2 Data Node Groups, 4 Data Nodes per groupAnd each machine is configured to allocate 1G of memory for its function, how much data (data + indexes) can you store in total in your cluster?You can guess, but you get bonus points if you explain why and the explanation is correct.

As folks are making their plans to go to the MySQL User Conference, I just wanted to remind folks of the schedule of MySQL Camp.One feature I put together for MySQL Camp but anyone can use is a restaurant list for the hotel area. There's very little within walking distance, but many people will be local or will rent a car, so finding someone to drive with should not be a problem. The restaurant list is on the MySQL Forge Wiki at http://forge.mysql.com/wiki/SantaClaraRestaurants -- updates are welcome!About MySQL Camp: MySQL Camp is completely free, just walk on in and enjoy the sessions. All sessions are in the Bayshore room off the Mezzanine, and there will be signs directing you to the MySQL Camp room. I describe it as being like "an additional room for the MySQL Conference, but it's free." It also has some of the more technical, hands-on talks that did not make it to the conference.

It's always the little things in life. One of the things that has been a very "little thing" that causes a lot of frustration while writing The MySQL Administrator's Bible is the prompt. Specifically, the fact that you can only change the first line of the prompt, but not the subsequent lines.

This is an issue that keeps rearing its ugly head over and over again, and since it greatly affects performance, it is most important that DBAs of any DMBS running on Linux come to grips with it. So I decided to do some research and try different settings on my notebook. Here are my findings.What can you find on the web?A Wikipedia search for the word swappiness will come up empty (any volunteers out there want to write an article?). A Google search will show some pretty old material---the best article I found is from 2004: Linux: Tuning Swappiness. This article includes a detailed discussion with some interesting remarks by Andrew Morton, a Linux kernel maintainer.So, what is swappiness?

Lenz Grimmer recently wrote two blogs about password security on MySQL. Both are worth reading in detail. You'll find them in Basic MySQL Security: Providing passwords on the command line and More on MySQL password security. Although I wrote a comment on the latter one, there is one point I thought was worth its own blog.

When using Oracle, the data dictionary provides us with tons of tables and views, allowing us to fetch information about pretty much anything within the database. We do have information like that in MySQL 5.0 (and up) in the "information_schema" database, but it's scattered through several different tables.Sometimes a client asks us to change the datatype of a column, but forgets to mention the schema name, and sometimes even the table name. As you can imagine, having this kind of information is vital to locate the object and perform the requested action. This kind of behaviour must be related to Murphy's Law.In any case, I'd like to share with you a simple stored procedure that has helped us a lot in the past.

The Oracle gateway for ODBC provides an almost seamless data integration between Oracle and other RDBMS. I won't argue about its performance, limits, or relevance. It serves a few purposes; set it up and you'll be able, for example, to create database links between Oracle and MySQL. After all, wouldn't it be nice if you could run some of the following SQL statements? "select o.col1, m.col1 from oracle_tabo, mysql_tab@mysql m where o.col1=m.col1;" "insert into oracle_tab (select * from mysql_tab@mysql);"This post is intended to share, the same way Karun did it for SQL Server last year, some tips related to the setup of the Oracle Gateway for ODBC with MySQL Connector ODBC on Linux.PrerequisitesI've installed all the configuration on my laptop to test it. It's running Ubuntu Intrepid Ibex 32bits, but I won't dig into the challenge of installing the MySQL Connector ODBC 5.1 on it. All I'll tell you is that, if  []

If you are using InnoDB Hot Backup utility and the innobackup.pl wrapper script, be very careful if you are not running backups under the system "mysql" user. There is a bug which causes InnoDB Hot Backup to sometimes report a successful backup when it actually failed.

Slaves can be used for:Horizontal read scalability -- take the load off a master database by spreading reads to a replicated slave.Disaster recovery -- some disasters, such as a hardware failure, can be solved by having a slave ready to propagate to a master. This technique also works to make offline changes to a master/slave pair without having database downtime (see below).Consistent Backups -- without disrupting production usage, a slave can be used to ta

REPLACE does the equivalent of "find and replace".

It recently came up that it would be helpful if we had a cheat sheet to find out the machine names for any given UNIX. I knew these off the top of my head but it would be great if people added more as comments. HP/HP-UX: /bin/uname -i IBM/AIX: /bin/uname -m SGI/IRIX: /sbin/sysinfo -s Sun/Solaris: /usr/ucb/hostid

The "sla" in mysqlsla stands for "statement log analyzer". This does a much better job than mysqldumpslow of analyzing your slow query log. In fact, you can sort by many different parameters -- by sheer number of times the query shows up in the slow query log, by the total or average query time, by the lock time, etc. This is really good for weeding out pesky entries in the slow query log that you do not care about. In this case, our client was using log-queries-not-using-indexes, so there was a lot of junk in the slow query log as well (for instance, every time a mysqldump backup was run, the slow query log got plenty of entries). In this case, I'm using --slow to read the slow query log at the filename specified, --flat to flatten all the text to lowercase (basically case-insensitive matching) and --sort at to sort by "average time".> ./mysqlsla --flat --slow ~mysql/var/mysql-slow.log  []

This article provides a list of the measurements the popular MySQL cacti plugin monitors, and gives a DBA a guideline of what other monitoring products or custom monitoring should be looking at providing.

From the post: "It turns out that as part of the MySQL upgrade, the path to the MySQL installation changed (or a symlink was removed, or something to that effect) - at any rate, /usr/local/mysql no longer existed." Talks about the problem and how to fix it.

Continuent CTO Robert Hodges talks about future of database clustering and how Tungsten open source project fits in that picture

Some trick : how to generate/use a table of (not necessarily sequential) numbers from a mysql server.

Several methods to generate 4 billion records quickly