For companies that live and die by their databases, a one-shot backup isn’t really the perfect solution. Typically for such companies (think Yahoo! or Google), database access is a near-constant process, and database content changes continually, often on a second-by-second basis. Data replication, which involves continual data transfer between two (or more) servers to maintain a replica of the original database, is a better backup solution for these situations.

MySQL supports two (or three, depending on how you look at it) different methods of replicating databases from master to slave. All of these methods use the binary log; however, they differ in the type of data that is written to the master’s binary log.

• Statement-based replication Under this method, the binary log stores the SQL statements used to …

Replication in MySQL is the dynamic process of synchronizing data between a primary (master) database server and one or more secondary (slave) database servers in near-real time. Using this process, it’s possible to create copies of one or more databases so that even if the primary server fails, data can still be recovered from one of the secondary servers.

Replication is essential for many applications, and the lack of replication support was a major drawback to MySQL compared to other relational database management systems (RDBMSs). MySQL 3.23 was the first version to introduce replication support, and support has improved continually in subsequent versions. However, MySQL is still best suited for one-way replication, where you have one master and one or more slaves.

TIP

As much as possible, try to use the same version of MySQL for both the master and slave server(s). A version …

Equally important as backing up data in a MySQL database is the ability to restor data.

Most books on the subject emphasize the importance of backing up your data regularly (and rightly so), but restoring the data is an often-overlooked aspect of this process. Backed-up files are useless if they can’t be accessed. Accordingly, you should regularly restore your files from backup to make certain they can be used in an emergency. In fact, it might not be too much to say that a backup job isn’t complete until you’ve confirmed that the backup files can be restored. Besides the peace of mind you’ll achieve, it pays to be thoroughly familiar with the process, because you certainly don’t want to waste time learning the restore procedure after the system goes down.

In the preceding section, you learned that the output of …

You might need to restore corrupted tables (or even an entire database) from your backups and use the update logs if a table gets damaged or deleted by accident. In case of relatively minor damage, however, MySQL provides several options for table repair. This next section deals with what you can do if this is the case.

Checking Tables for Errors

The first thing to do if you suspect something is wrong is to check the table for errors. The myisamchk utility is one way to check a table. To invoke this utility, execute the command myisamchk table-file.

Because myisamchk requires exclusive access to the tables, a good idea is to take the server offline before running it. This way, you needn’t worry about coordinating access between clients. In addition, you can run several options when you check a table for errors, as shown in Table 1.

The following example runs myisamchk with the …

Recently we announced MySQL Data Recovery Portal. Here we automated some data recovery routines and published web interface to them.

First, it was a tool to estimate recovery chances from corrupted InnoDB tablespaces.

Now it’s even more exciting and useful feature – recovery table structure from FRM files online.

There are several methods to recover MySQL table structure from FRM files.

On MySQL Data Recovery Portal we utilize a combination of them.

Fake table method

The idea is to create a dummy InnoDB table, replace its .frm file with .frm file we want to recover and run SHOW CREATE TABLE.

Let’s say we have actor.frm and we want to get structure of table actor.

Algorithm is following:

Create a dummy table with the same name, actor. The …

cPanel is a very popular Linux hosting control panel, used not only by hosting providers but also enterprise and goverment bodies. For large scale hosting environments hosting mission critical databases, how do you integrate cPanel with a high availability database? 

In this blog post, we will show you how to integrate Percona XtraDB Cluster into cPanel to provide a high availability database hosting solution. By default, cPanel uses localhost to host the MySQL databases created by cPanel users. It is not uncommon to find MySQL to be the culprit when it comes to overloading the server. By having a remote MySQL server or cluster, we can offload some of the burden and increase the availability of other important services on the server.

Setting up a Galera cluster to integrate with cPanel requires you to meet the following requirements:

• skip-name-resolve must be turned OFF, as some cPanel services authenticate …

MySQL 5.7 comes with a new backup tool, named mysqlpump, which is almost the same as mysqldump with the ability of extracting data in parallel threads.

I tried a little experiment. Using a server containing 11 databases, with a total of 300 tables and about 20 million rows (roughly ≈ 10GB,) I used both mysqldump and mysqlpump to get a backup.

mysqldump --all-databases  > dump.sql

mysqlpump --all-databases \
    --add-drop-database --add-drop-table --skip-watch-progress \
    --default-parallelism=10 \
    --parallel-schemas=db,db1,db2 \
    --parallel-schemas=db3,db4,db5 \
    --parallel-schemas=db6,db7,db8 \
    --parallel-schemas=db9,db10 > pump.sql

The backup with mysqldump took 3 minutes and 33 seconds. The one with mysqlpump took 2 minutes and 55 …

It’s a pretty fair assumption that if your database is big enough and complex enough to produce metrics that warrant a monitoring system, it’s also complex enough to produce tons of data that are ultimately more distracting than relevant. It’s not unusual to look at a bevy of monitoring possibilities and feel overwhelmed, uncertain about where to center your focus. Of course, every database is different, but there are some fundamental truths you should consider when you ask yourself, “What should I monitor?” Some of these ideas might seem simple, but if you don’t keep these in mind, you’d be surprised how easy it can be to lose sight of the big picture.

Know Your Goal. What are you trying to achieve by monitoring in the first place? Ultimately, the real question of effective monitoring is, “What work is getting done?” Systems do work for you; the goal of monitoring is to make sure those systems are …

Last week, 21-23 September, it took place the European MySQL Conference, or “Data performance Conference” as this year’s subtitle was “MySQL. NoSQL. Data in the cloud.”. This year, it changed its location from London to Amsterdam and, as most people I talked to agreed, the change was for good. As every year, Percona was the company organizing it, but it had the participation of all the major players in the open source MySQL/MongoDB/Cloud data world. Special mention goes to Booking.com, which had more …

This Log Buffer Edition collects and then showers around some of the information-rich blog posts from Oracle, SQL Server and MySQL.

Oracle:

• Generic Java Server for Static content and Directory Listing using API mode or command line mode.
• OEM agents on each host upload data to your management servers every few minutes.
• exitcommit … or your career down the drain.
• Encryption is the Easy Part; Managing …