MySQL Admin Cookbook

4 (1 reviews total)
By Daniel Schneller , Udo Schwedt
  • Instant online access to over 8,000+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Replication

About this book

MySQL is the most popular open-source database and is also known for its easy set up feature. However, proper configuration beyond the default settings still is a challenge, along with some other day-to-day maintenance tasks such as backing up and restoring, performance tuning, and server monitoring. These tasks have not been covered thoroughly in the default documentation.

This book provides both step-by-step recipes and relevant background information on these topics and more. It covers everything from basic to advanced aspects of MySQL administration and configuration. One of the things you are really going to love about this book is that all recipes are based on real-world experience and were derived from proven solutions used in an enterprise environment.

This book shows you everything you need to know about MySQL Administration. You will learn to set up MySQL replication to manage load balancing and deal with online backup and fail-over scenarios. As you consider the benefits of backing up, you might like to back up your database efficiently with advanced techniques covered in this book.

The book demonstrates how to create, modify, and delete indexes. You will also learn to identify duplicate indexes, which hinder your MySQL server performance. This book focuses on administration tasks and will help you as an administrator to optimize the database for efficiency and reliability.

You will learn to manage data efficiently by inserting data in existing database content and importing and exporting databases. The sooner you learn about taking advantage of metadata from this book, the sooner you can start using the space efficiently. Get to know about managing users and assigning privileges and regaining lost administrative user credentials. Finally, learn to manage the database schema by customizing it to automate database schema evolution in the context of application updates.

Publication date:
March 2010
Publisher
Packt
Pages
376
ISBN
9781847197962

 

Chapter 1. Replication

In this chapter, we will discuss:

  • Setting up automatically updated slaves of a selection of tables based on a SQL dump

  • Setting up automatically updated slaves using data file copy

  • Sharing read load across multiple machines

  • Using replication to provide full-text indexing for InnoDB tables

  • Estimating network and slave I/O load

  • Limiting network and slave I/O load in heavy write scenarios using the blackhole storage engine

  • Setting up slaves via network streaming

  • Skipping problematic queries

  • Checking if servers are in sync

  • Avoiding duplicate server IDs

  • Setting up slaves to report custom information about themselves to the master

 

Introduction


Replication is an interesting feature of MySQL that can be used for a variety of purposes. It can help to balance server load across multiple machines, ease backups, provide a workaround for the lack of fulltext search capabilities in InnoDB, and much more.

The basic idea behind replication is to reflect the contents of one database server (this can include all databases, only some of them, or even just a few tables) to more than one instance. Usually, those instances will be running on separate machines, even though this is not technically necessary.

Traditionally, MySQL replication is based on the surprisingly simple idea of repeating the execution of all statements issued that can modify data—not SELECT—against a single master machine on other machines as well. Provided all secondary slave machines had identical data contents when the replication process began, they should automatically remain in sync. This is called Statement Based Replication (SBR).

With MySQL 5.1, Row Based Replication (RBR) was added as an alternative method for replication, targeting some of the deficiencies SBR brings with it. While at first glance it may seem superior (and more reliable), it is not a silver bullet—the pain points of RBR are simply different from those of SBR.

Even though there are certain use cases for RBR, all recipes in this chapter will be using Statement Based Replication.

While MySQL makes replication generally easy to use, it is still important to understand what happens internally to be able to know the limitations and consequences of the actions and decisions you will have to make. We assume you already have a basic understanding of replication in general, but we will still go into a few important details.

Statement Based Replication

SBR is based on a simple but effective principle: if two or more machines have the same set of data to begin with, they will remain identical if all of them execute the exact same SQL statements in the same order.

Executing all statements manually on multiple machines would be extremely tedious and impractical. SBR automates this process. In simple terms, it takes care of sending all the SQL statements that change data on one server (the master) to any number of additional instances (the slaves) over the network.

The slaves receiving this stream of modification statements execute them automatically, thereby effectively reproducing the changes the master machine made to its data originally. That way they will keep their local data files in sync with the master's.

One thing worth noting here is that the network connection between the master and its slave(s) need not be permanent. In case the link between a slave and its master fails, the slave will remember up to which point it had read the data last time and will continue from there once the network becomes available again.

In order to minimize the dependency on the network link, the slaves will retrieve the binary logs (binlogs) from the master as quickly as they can, storing them on their local disk in files called relay logs. This way, the connection, which might be some sort of dial-up link, can be terminated much sooner while executing the statements from the local relay-log asynchronously. The relay log is just a copy of the master's binlog.

The following image shows the overall architecture:

Filtering

In the image you can see that each slave may have its individual configuration on whether it executes all the statements coming in from the master, or just a selection of those. This can be helpful when you have some slaves dedicated to special tasks, where they might not need all the information from the master.

All of the binary logs have to be sent to each slave, even though it might then decide to throw away most of them. Depending on the size of the binlogs, the number of slaves and the bandwidth of the connections in between, this can be a heavy burden on the network, especially if you are replicating via wide area networks.

Even though the general idea of transferring SQL statements over the wire is rather simple, there are lots of things that can go wrong, especially because MySQL offers some configuration options that are quite counter-intuitive and lead to hard-to-find problems.

For us, this has become a best practice:

"Only use qualified statements and replicate-*-table configuration options for intuitively predictable replication!"

What this means is that the only filtering rules that produce intuitive results are those based on the replicate-do-table and replicate-ignore-table configuration options. This includes those variants with wildcards, but specifically excludes the all-database options like replicate-do-db and replicate-ignore-db. These directives are applied on the slave side on all incoming relay logs.

The master-side binlog-do-* and binlog-ignore-* configuration directives influence which statements are sent to the binlog and which are not. We strongly recommend against using them, because apart from hard-to-predict results they will make the binlogs undesirable for server backup and restore. They are often of limited use anyway as they do not allow individual configurations per slave but apply to all of them.

For these reasons you will not find any use of these options in this book.

 

Setting up automatically updated slaves of a server based on a SQL dump


In this recipe, we will show you how to prepare a dump file of a MySQL master server and use it to set up one or more replication slaves. These will automatically be updated with changes made on the master server over the network.

Getting ready

You will need a running MySQL master database server that will act as the replication master and at least one more server to act as a replication slave. This needs to be a separate MySQL instance with its own data directory and configuration. It can reside on the same machine if you just want to try this out. In practice, a second machine is recommended because this technique's very goal is to distribute data across multiple pieces of hardware, not place an even higher burden on a single one.

For production systems you should pick a time to do this when there is a lighter load on the master machine, often during the night when there are less users accessing the system. Taking the SQL dump uses some extra resources, but unless your server is maxed out already, the performance impact usually is not a serious problem. Exactly how long the dump will take depends mostly on the amount of data and speed of the I/O subsystem.

You will need an administrative operating system account on the master and the slave servers to edit the MySQL server configuration files on both of them. Moreover, an administrative MySQL database user is required to set up replication.

We will just replicate a single database called sakila in this example.

Tip

Replicating more than one database

In case you want to replicate more than one schema, just add their names to the commands shown below. To replicate all of them, just leave out any database name from the command line.

How to do it...

  1. At the operating system level, connect to the master machine and open the MySQL configuration file with a text editor. Usually it is called my.ini on Windows and my.cnf on other operating systems.

  2. On the master machine, make sure the following entries are present and add them to the [mysqld] section if not already there:

    server-id=1000
    log-bin=master-bin

    If one or both entries already exist, do not change them but simply note their values. The log-bin setting need not have a value, but can stand alone as well.

  3. Restart the master server if you need to modify the configuration.

  4. Create a user account on the master that can be used by the slaves to connect:

    master> grant replication slave on *.* to 'repl'@'%' identified by 'slavepass'; 
  5. Using the mysqldump tool included in the default MySQL install, create the initial copy to set up the slave(s):

        $ mysqldump -uUSER -pPASS --master-data --single-transaction 
        sakila > sakila_master.sql
  6. Transfer the sakila_master.sql dump file to each slave you want to set up, for example, by using an external drive or network copy.

  7. On the slave, make sure the following entries are present and add them to the [mysqld] section if not present:

    server-id=1001 
    replicate-wild-do-table=sakila.%

    When adding more than one slave, make sure the server-id setting is unique among master and all clients.

  8. Restart the slave server.

  9. Connect to the slave server and issue the following commands (assuming the data dump was stored in the /tmp directory):

    slave> create database sakila;
    slave> use sakila;
    slave> source /tmp/sakila_master.sql;
    slave> CHANGE MASTER TO master_host='master.example.com', master_port=3306, master_ user='repl', master_password='slavepass';
    slave> START SLAVE;
  10. Verify the slave is running with:

    slave> SHOW SLAVE STATUS\G
    ************************** 1. row ***************************
                            ...
               Slave_IO_Running: Yes          Slave_SQL_Running: Yes
                            ...

How it works...

Some of the instructions discussed in the previous section are to make sure that both master and slave are configured with different server-id settings. This is of paramount importance for a successful replication setup. If you fail to provide unique server-id values to all your server instances, you might see strange replication errors that a

Moreover, the master must be configured to write binlogs—a record of all statements manipulating data (this is what the slaves will receive).

Note

Before taking a full content dump of the sakila demo database, we create a user account for the slaves to use. This needs the REPLICATION SLAVE privilege.

Then a data dump is created with the mysqldump command line tool. Notice the provided parameters --master-data and --single-transaction. The former is needed to have mysqldump include information about the precise moment the dump was created in the resulting output. The latter parameter is important when using InnoDB tables, because only then will the dump be created based on a transactional snapshot of the data. Without it, statements changing data while the tool was running could lead to an inconsistent dump.

The output of the command is redirected to the /tmp/sakila_master.sql file. As the sakila database is not very big, you should not see any problems. However, if you apply this recipe to larger databases, make sure you send the data to a volume with sufficient free disk space—the SQL dump can become quite large. To save space here, you may optionally pipe the output through gzip or bzip2 at the cost of a higher CPU load on both the master and the slaves, because they will need to unpack the dump before they can load it, of course.

If you open the uncompressed dump file with an editor, you will see a line with a CHANGE MASTER TO statement. This is what --master-data is for. Once the file is imported on a slave, it will know at which point in time (well, rather at which binlog position) this dump was taken. Everything that happened on the master after that needs to be replicated.

Finally, we configure that slave to use the credentials set up on the master before to connect and then start the replication. Notice that the CHANGE MASTER TO statement used for that does not include the information about the log positions or file names because that was already taken from the dump file just read in.

From here on the slave will go ahead and record all SQL statements sent from the master, store them in its relay logs, and then execute them against the local data set.

Note

This recipe is very important because the following recipes are based on this! So in case you have not fully understood the above steps yet, we recommend you go through them again, before trying out more complicated setups.

See also

  • Avoiding duplicate server IDs

 

Setting up automatically updated slaves of a selection of tables based on a SQL dump


Often you might not need to replicate everything, but only a subset of tables in a database. MySQL allows exercising fine-grained control over what to replicate and what to ignore. Unfortunately, the configuration settings are not as obvious as they might seem at first glance.

In this recipe, you will see how to replicate only a few select tables from a database.

Getting ready

The setup for this recipe is the same as for the previous one, Setting up automatically updated slaves of a server based on a SQL dump. Only the configuration options on the slave need to be changed. So instead of repeating everything here, we just present the important differences.

How to do it...

  1. Follow the steps of the previous recipe up to the point where the mysqldump tool is used to extract the initial data set from the master. Use this command instead:

    $ mysqldump -uUSER -pPASS --master-data --single-transaction sakila address country city > sakila_master.sql
  2. Go on with the steps of the previous recipe up to the point where it tells you to edit the slave machine's configuration. Change the configuration as follows instead in the [mysqld] section:

    server-id=1001 
    replicate-wild-ignore-table=sakila.%
    replicate-do-table=sakila.address
    replicate-do-table=sakila.country
    replicate-do-table=sakila.city
  3. Continue with the rest of the instructions as in the Setting up automatically updated slaves of a server based on a SQL dump recipe.

How it works...

The SQL dump file taken on the master is limited to three tables: address, country, and city. The slave's configuration also tells it to only execute statements coming from the master that targets one of these three tables (replicate-do-table directives), while overtly ignoring any other changes in the sakila database (replicate-wild-ignore-table). Even though all other statements are still retrieved from the master and stored temporarily in the relay log files on the slave, only those with modifications to one of the three tables explicitly configured are actually run. The rest are discarded.

You can choose any subset of tables, but you need to make sure to take Foreign key relationships between tables into account. In this example, the address table has a reference to the city table via the city_id column, while city in turn has a relationship with country. If you were to exclude either one of the latter and your storage engine on the slave was InnoDB, replication would break because of Foreign key violations when trying to insert an address, since its dependencies were not fulfilled.

MySQL does not help you in this respect; you must make sure to identify all tables and their relationships manually before setting up the replication.

There's more...

In this example, we clearly specified three tables by their full names. There are more options available, not only to include but also to exclude tables. See the MySQL online manual's chapter 16.1.3.3 on Replication Slave Options and Variables for more information on these at http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html.

 

Setting up automatically updated slaves using data file copy


Even though replication is designed to keep your data in sync, circumstances might require you to set up slaves afresh. One such scenario might be severely changing the master data, making replication too expensive. Using a SQL dump to re-initialize the slaves might be too time-consuming, depending on the size of the data set and the power of the slave machines.

In cases where master and slave databases are the same size anyway (meaning, you do not have filters in place to sync data only partially) and if you can afford a downtime on the master database, there is another way of providing slaves with a fresh starting point: copying the master's data files to the slave.

Note

Beware that this approach will lose all data that was changed on the slave alone. So make sure that this is what you want!

Getting ready

To follow along with this recipe you will need privileges to shut down both master and slave MySQL instances and access the data and log directories on both machines. Depending on the size of your database you will have to judge which method of copying will be the best between the machines. If both are part of a local area network, copying via a shared drive or something like FTP will probably be the fastest way. You might, however, need to resort to other means of data transfer like external hard disks or the like, when only limited bandwidth is available.

Moreover, you will need administrative MySQL user accounts on both sides to execute the necessary statements to control replication.

How to do it...

  1. Open the master's configuration file with a text editor. Locate the line controlling the name of the binlog files. It is located in the [mysqld] section and will look similar to this:

    [mysql]
    ...
    log-bin=master-bin
    ...
  2. Change the value of that setting to a different name. In this example, we will use log-bin=new-master-bin. This will cause the master MySQL server to start with a new sequence of binlogs upon its next launch, making a convenient starting point for the replication.

  3. Shut down the master database.

  4. Navigate to the MySQL data directory. The exact location can be found in the MySQL configuration file. Make sure to find both InnoDB data and log locations.

  5. Optionally, copy data and log files to another location locally on the master. This might be faster than copying via network or USB drives, and allows for a quick restart of the master. If you do this, use this temporary location to copy the data in the next step.

  6. Copy the data to the slave machine. We recommend a temporary target location on the slave because this allows the slave to continue running for the time the copying takes. Unless you want to bring along all the user accounts and privilege information from the master to the slaves, you should exclude the mysql folder from the data directory. You also need not copy the binary logs (in this example called master-bin.*).

  7. Stop the slave server.

  8. You can restart the master once the original data and transaction log files have been copied. Make sure it starts with a new sequence of binlogs called new-master-bin.000001.

  9. Write down the names and sizes of InnoDB data and log files you copied to the slave. These will have to be entered into the slave's configuration because, otherwise, InnoDB will not start up. Also, pay attention to an autoextend option, which the last of the data files might have attached. Make sure you carry over this option, should it be there on the master. You can also take these values from the master's configuration file, of course.

  10. Replace the original slave data and log files with those of the master. Make sure you keep the mysql database directory if you decided not to copy it from the master.

  11. Make sure you delete the master.info file and any relay-logs from the slave—those do not match the current state anymore and would cause trouble when the slave is restarted.

  12. Edit the slave's configuration file to match the names and sizes of the data files you wrote down a minute ago.

  13. Start the slave server again. It should come up without any problems using the new data files. If not, make sure you got everything right in the config file regarding names and sizes of the data files.

  14. Re-initialize the replication on the slave. This is rather easy because we altered the master's configuration to log any changes that occurred after the snapshot copy was taken to a new series of binlog files. Fill in the appropriate host name, user, and password values for your master:

    slave> CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='repl', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='new-master-bin.000001';
    slave> START SLAVE;

    As we want the slave to start reading the new-master-bin.000001 file from the beginning, no MASTER_LOG_POS has to be specified.

  15. Verify whether the slave is running with:

    slave> SHOW SLAVE STATUS\G
    ************************** 1. row ***************************
                            ...
               Slave_IO_Running: Yes          Slave_SQL_Running: Yes
                            ... 
    

How it works...

The principle of this recipe is very simple: replication needs a common starting point on master and slave. What could be better than a 1:1 copy of the original master's data? As the master is shut down during the process, no more writes can happen. Configuring it to start with a new binlog file on its next start makes it trivial to point the slave to the right position because it is right at the new file's beginning.

If you cannot change the master binlogs' file names, the process is slightly more complicated. First you need to make sure nobody can modify any data for a short period of time. You do so with a FLUSH TABLES WITH READ LOCK; statement. Then issue a SHOW MASTER STATUS; and note the values. Now, without closing the client connection or releasing the lock, shut down the master server. Only if the lock is kept while shutting down the master can you be sure no write operations take place and invalidate the binlog position you just gathered.

Copy the data and transaction log files as described above. The remaining steps are the same, except of course, when you issue the CHANGE MASTER TO on the slave. Here you need to insert the MASTER_LOG_FILE and MASTER_LOG_POS you got from SHOW MASTER STATUS.

There's more...

The steps described above require you to take down both master and slave databases, albeit not necessarily at the same time. Nevertheless, this might not be an option if you are dealing with a production system that cannot be shut down easily.

In these cases, you have some other options that are, however, not explained in detail here.

Conserving data file by using LVM snapshots

If your data and log files are stored on a logical volume managed by LVM, you can use its snapshot feature to conserve the data files' state once you have got the SHOW MASTER STATUS information. As soon as the snapshot has been taken, you can release the lock again and proceed as described above, copying not the most current version but the snapshot files. Be advised, however, that this approach might take a significant hit on the I/O performance of you master!

Backing up data using Percona xtrabackup

At the time of writing, an open-source alternative to the commercially available innobackup tool (available from http://www.innodb.com/products/hot-backup/) is under active development. While being primarily a backup tool that allows backing up InnoDB databases while the server is up and running, the documentation contains a (currently empty) section on setting up a slave from a backup in replication. Experience tells that Percona—the company behind xtrabackup—is very engaged in the MySQL ecosystem and might very well have completed its set of instructions by the time you read this. To check on the current status of the project go to https://launchpad.net/percona-xtrabackup.

 

Sharing read load across multiple machines


Often you have a very unequal distribution of read and write operations on a database. Websites usually get many more visitors just browsing and reading contents than actually contributing contents. This results in the database server being mainly busy reading information instead of adding or modifying existing material.

Replication can be used to alleviate scalability issues when your site reaches a certain size and a single machine might reach the limits of its performance reserves.

Unfortunately, MySQL does not offer this load-balancing functionality itself, so you will need to take appropriate actions on the application level.

In this recipe, we will show you the general procedure to follow when sharing read accesses between two slave machines while still aiming writes at the master. Beware that due to the asynchronous nature of MySQL replication, your application must be able to handle slightly out-of-date results because issuing an INSERT, UPDATE, or DELETE against the master will not mean that you can read the modified data back immediately as the slave might take some time to catch up. Usually, on a local network this should be a couple of seconds at most, but nevertheless the application code must be ready for that.

To simplify the scheme, you should design your application to exclusively read from the slaves and only use the master for modifications. This brings the additional benefit of being able to keep the overall system up and running while switching to a read-only mode temporarily, backing up the master server. This is not part of this recipe, however.

The example used in this recipe uses three database servers. The sample application is written in Java, using the MySQL Connector/J JDBC database driver. Depending on what application platform you are using, syntax and function names will differ, but the general principle is language independent.

Note

The source code shown later has been abbreviated to show only the most relevant portions. You can find the complete file on the book's website.

Getting ready

Depending on your application infrastructure, you will need privileges to change its database connectivity configuration and the source code. This is usually a task that requires cooperation with application developers.

To follow along with this example you should be familiar with the Java language and its basic constructs.

Moreover, you will need three MySQL servers—one configured as the master and two others as slaves. They will be referred to as master, slave1, and slave2 in this example. Substitute your concrete host names appropriately.

You will also need the Java Standard Edition development tools available from http://java.sun.com, and the MySQL Connector/JDBC driver available from http://dev.mysql.com. Download and install both if you do not already have them.

How to do it...

  1. Download the file called MySQLBalancingDemo.java from the book's website. It contains the following code:

                   … 
    Connection conn = driver.connect("jdbc:mysql://master:3306,slave1:3307,slave2:3308/sakila?user=testuser&password=testpass&roundRobinLoadBalance=true", null);
    
    conn.setReadOnly(false); // target the MASTER
    
    rs = conn.createStatement().executeQuery(
      "SELECT @@server_id;");
    rs.next();
    System.out.println("Master: " + rs.getString(1));
    
    conn.setReadOnly(true); // switch to one of the slaves
    
    rs = conn.createStatement().executeQuery(
      "SELECT @@server_id;");
    rs.next();
    System.out.println("Slave: " + rs.getString(1));
    conn.close();
                   …
  2. Compile the file using the javac compiler. Alternatively, an integrated development environment like Eclipse or Netbeans can take care of this for you:

    $ javac -cp mysql-connector-java-5.1.7-bin.jar MySQLBalancingDemo.java
  3. Run the sample application and see how it automatically distributes the read requests between the two slaves:

    $ java -cp .:mysql-connector-java-5.1.7-bin.jar MySQLBalancingDemo
    Master: 1000
    Slave: 13308

How it works…

You just compiled and ran a small program that demonstrates round-robin load balancing.

The first line of output is the master's server-ID setting, because the first connection was not set to read only. The connection is then declared to be targeted at the slaves via setReadOnly(true). The next query will then return the server ID of the particular slave it was balanced to. You might need to run the demo a few times to see a different slave being used because the algorithm that balances the load does not strictly toggle each time, but might direct a few connections against the same slave.

There's more...

While the JDBC driver makes it relatively easy to use read load balancing across several slaves, it only helps you take the first step on the way. You must take care that the application knows which connection to use for write operations and which for read. It must also cope with slaves and master possibly being slightly out of sync all the time. Concentrating that this special logic in a class of its own, is advisable to limit the effect on the rest of the application.

Working with connection pools

When working with connection pooling, be sure to initialize any connection you get to the correct mode using the setReadOnly() method, to be sure you know what state it is in. You might be handed a connection that was set to the wrong mode when it was put back into the pool.

Working on other programming environments

In development environments not using Java, you might have to take care of managing the cycling between slaves yourself. Independent of the actual language or environment you are using, a good practice is to channel all database operations through a set of functions or methods that centrally manage the balancing. You could provide functions that handle INSERT, UPDATE, and DELETE operations, always connecting to the master and a SELECT function going to the slaves for data reads.

In case you need to select something back that you just wrote and cannot allow for the replication lag, you might also provide a read function querying the master machine. You should use this sparingly, however, because it definitely counteracts the intention of relieving the master from the read load.

Considering efficiency while adding slaves

Of course, the slaves have to perform write operations as well to keep up with the master. This means their performance is not fully available for reads, limiting scalability.

So adding more slaves does not proportionally improve performance of the overall system.

 

Using replication to provide full-text indexing for InnoDB tables


The InnoDB storage engine is the one most commonly used nowadays because it provides more enterprise-level features than MyISAM and most other engines. However, InnoDB tables do have a major drawback: they do not support full-text indexing. This can be a significant obstacle when you have to design any sort of application that relies on atomic operations and must store text data in a searchable manner.

While there are third-party products available to redress this shortcoming, there are times you may need to refrain from using these and stick to the out-of-the-box functionality. If you are willing to provide an additional server and make slight adjustments to your application code, replication can help you provide a full-text index for InnoDB tables indirectly.

This recipe is similar to the one about Sharing read load across multiple machines in this chapter. In contrast, only queries that are targeted at the full-text index need to be sent to a slave machine. This will require slight changes to the application code.

Getting ready

To follow along with this recipe, you will need two MySQL servers available—a master and a slave. For testing, these might reside on the same physical machine. In a production environment we do, however, recommend two separate pieces of equipment.

They will be referred to as master and slave in this example. Substitute your concrete host names appropriately.

You will need privileges to change the application source code. This is usually a task that requires cooperation with the application developers.

How to do it...

  1. On the master, identify the table that contains the column(s) that you want to equip with a full-text index. In this example, we use the following table definition from a fictional forum application of some sort:

    CREATE TABLE `posts` (
      `id` int(11) NOT NULL auto_increment,
      `title` varchar(100) NOT NULL,
      `posttext` text NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=InnoDB; 

    The posttext column contains the text of forum posts. As the table is created with ENGINE=InnoDB, we cannot add a full-text index to it.

  2. On the slave, create the same table, but with a slightly modified definition:

    CREATE TABLE `posts` (
      `id` int(11) NOT NULL auto_increment,
      `title` varchar(100) NOT NULL,
      `posttext` text NOT NULL,
      PRIMARY KEY  (`id`),
      FULLTEXT KEY `FT_text` (`posttext`)
    ) ENGINE=MyISAM; 

    The storage engine is set to MyISAM, allowing the FULLTEXT KEY `FT_text` (`posttext`) definition. Trying to add this on the master would result in an error message.

  3. Make sure the replication rules between master and slave include the posts table.

  4. Modify your application to access the slave when doing full-text queries. It is generally considered a good practice to concentrate all database access to a dedicated module or class, so that you can easily modify your application's interaction with the underlying data store.

How it works...

In this replication setup, whenever you make changes to the master's posts table, those will be replicated to the slave, but the target table uses a different storage engine than the master. As SBR simply sends over SQL statements without any information about the origin, the slave will execute the instructions blindly. While this can be a problem in other circumstances because it makes the whole process somewhat fragile, it plays to our advantage in this case.

Upon UPDATE or INSERT to the posttext column the MyISAM engine will update the full-text index appropriately. This enables the application to issue queries using the full-text query syntax against the slave.

Note

An important drawback you must take into account is that you cannot JOIN tables between different MySQL servers!

A workaround is required when you have to, for example, join the posts with a user accounts table via the posts.id column. To implement this you will need to issue two separate queries. The first one using the full-text search on the slave will bring up all posts containing the search terms. From the resulting rows you can then take the id column values and run a second query against the master database, substituting the text search with an id lookup.

There's more...

MyISAM's full-text index has existed for several years, but has not been improved a great deal over time. If you have many concurrent requests you will notice significant resource usage, limiting scalability.

Over the past few years, several third-party vendors have stepped up with alternative solutions to the problem of full-text search, offering more features and better performance.

One of those products, offering tight integration with MySQL and PHP, is Sphinx—an open-source product available for free from http://www.sphinxsearch.com. If you find that MySQL's built-in capabilities are either too slow or too limited in other respects to meet your application's requirements, you should definitely have a look at it.

Setting up new slaves in this scenario

You should not simply use a regular SQL dump to initialize the slave, as it will contain a create table statement that specifies InnoDB and does not include the full-text index. Of course, you could change the table type after the import is complete. However, this can be time consuming. Instead, we recommend you first create the target schema on the slave, making sure the tables in question are created with ENGINE=MyISAM.

Then go ahead and import the data into the table. Only after that, add the full-text index. This is typically much faster than having the index in place beforehand because MySQL must then update it all the way through the bulk insert of rows. This is a very expensive operation compared to the delayed index creation.

See also

  • Adding a full-text index in Chapter 2

 

Estimating network and slave I/O load


Especially when using replication over a wide-area network connection with limited bandwidth, it is interesting to be able to predict the amount of data that needs to be transported between master and slaves.

While MySQL does not use the most efficient strategy to deliver data, it is at least relatively easy to calculate the requirements in advance.

This is less of a step-by-step recipe than an annotated walkthrough of the basic formula that can be used to estimate the traffic you will have to be prepared for.

Getting ready

In order to follow along, you must have some key data points available because otherwise there is not much to calculate. You will need:

  • The number of slaves (to be) connected to the master.

  • An idea about the average amount of binlogs written when using the master under regular load. Knowing about peak times can be interesting as well.

  • The bandwidth of the connection between master and slaves. This includes the speed of the network interfaces on the master and, in general, the whole route between them (possibly including Internet connections).

We assume that there are no other network-intensive applications running on the master or slaves, so that practically all the speed your network card can provide is usable for MySQL.

In this example, we will keep matters simple, assuming the following:

Data point

Value

Master's Connectivity

Gigabit LAN interface (approx. 100MB/s)

Slaves' Connectivity

2MBit/s DSL line, of which 1MBit/s can be assumed available for MySQL. 1MBit/s comes down to approximately 100kb/s.

Average amount of binlogs created on master

175MB per hour, approx. 50kb/s.

Number of Slaves

5

Speed of the slowest link in the connection between Master and Slaves.

Master is connected to the Internet via a 10MBit/s connection, approx. 1MB/s.

How to do it...

  1. Check the master's network speed: Multiply the number of slaves with the average amount of binlogs: 5x175MB/hour = 875MB/hour or about 250kb/second. The gigabit connection can handle this easily.

  2. Check individual slaves' network speed: The 1MBit portion of the DSL line is sufficient for an average amount of data of 50kb/second. Often binlog production is not linear over time—there might be peaks, but there is still a reserve.

  3. Check if the slowest part of the route between master and slaves can handle the load: 250kb/second should be no problem for the 10MBit/second Internet connection.

  4. Disk I/O load on each slave, caused by the replication, is the amount of relay logs being written. This is equivalent to the amount of binlogs the master produces. Provided the slave's I/O is not already saturated by other things, an additional 175MB per hour should not pose a problem either.

How it works...

Basically, replication simply needs sufficient resources to copy the master's binlogs to the slaves. This really all there is to it. Depending on the network route between them this can be easily done (say most LANs), or can be tricky (as in cases with slow Internet connections).

In this example, we see there should be no problem on any part of the system, as there is still room for a higher load on each resource. The most limiting factor in this scenario seems to be the master's outgoing Internet connection. If you add more slaves to the scenario, each new one will add another 50KB per second of outgoing bandwidth. Assuming replication can use the full 1MB/s outgoing speed, which is not very realistic, that part of the route could theoretically service 20 slaves at most. In reality, it will be more like 10 to 15.

There's more...

There are two more general considerations you might want to think about when planning a replication setup.

Handling intermittent connectivity between master and slave

If the connection between master and slaves is only available for a limited period of time, the slaves will start to lag behind while disconnected. The slaves will download new data as quickly as possible when the connection is resumed and store it locally in the relay logs, asynchronously executing the statements. So expect higher network load during these times.

You will also want to take that into account when there are multiple slaves trying to catch up at the same time. Under such circumstances, the route between master and slaves might become saturated more quickly.

Enabling compression with the slave_compressed_protocol option

Particularly useful for low bandwidth connections between master and slaves is the compression feature for replication traffic. Provided it is switched on on both master and slave machines, it can significantly reduce the amount of data that is actually transferred over the network at the cost of increased CPU loads. The master will then send out the binlog information in a compressed format.

In a simple comparison, measuring the network traffic while creating and loading the sakila sample database, 3180kb of binlogs were created. However, with the compressed protocol switched on, only about 700KB of data per slave were sent over the network.

To enable compression, add the following line to the [mysqld] section in the configuration files on both master and slave:

slave_compressed_protocol=1

Then restart the servers to enable the feature. Verify whether it was switched on successfully by issuing a SHOW VARIABLES LIKE 'slave_compressed%'; command on both master and slaves.

You can achieve a similar effect with SSH compression. As we generally do not recommend replicating over the Internet without encryption, that option might even be more appealing in such scenarios as it does not require any configuration changes to MySQL.

Note

Naturally, the level of compression heavily depends on the data you are handling. If you store JPEG images in BLOB fields, for example, those cannot be compressed much more than they already are!

See also

  • Encrypting a MySQL server connection with SSH in Chapter 3

  • Creating an encrypted MySQL console via SSH in Chapter 3

  • Using a PuTTY template connection for SSH secured connections in Chapter 3

 

Limiting network and slave I/O load in heavy write scenarios using the blackhole storage engine


If you have a large number of slaves and a rather busy master machine, the network load can become significant, even when using compression. This is because all statements that are written to the binlog are transferred to all the slaves. They put them in their relay logs and asynchronously process them.

The main reason for the heavy network load is the filter on the slave paradigm that MySQL employs. Everything is sent to every one of the slaves and each one decides which statements to throw away and which to apply based on its particular configuration. In the worst case, you have to transmit every single change to a database to replicate only a single table.

Getting ready

The following procedure is based on Linux. So in order to repeat it on Windows, you need to adapt the path names and a little shell syntax accordingly.

To follow along, you will need a MySQL daemon with the blackhole storage engine enabled. Verify this with the following command:

mysql> show variables like '%blackhole%';

Even though you only strictly need a blackhole-enabled MySQL server on the actual filter instance, for this example we will be using only a single machine and just a single server version, but with different configuration files and data directories.

In the following steps, we assume you have installed a copy of MySQL in a folder called blacktest in your home directory. Modify accordingly if your setup differs.

How to do it...

  1. Create three distinct data directories—one for the master, one for the blackhole filter engine, and one for a slave.

    ~/blacktest$ mkdir data.master
    ~/blacktest$ mkdir data.slave
    ~/blacktest$ mkdir data.black
  2. Into each of those, copy the MySQL accounts database. Ideally, you should take an empty one from a freshly downloaded distribution to make sure you do not accidentally copy users you do not want.

    ~/blacktest$ cp -R data/mysql data.master
    ~/blacktest$ cp -R data/mysql data.slave
    ~/blacktest$ cp -R data/mysql data.black
  3. Configure the master instance. To do so, create a configuration file called my.master and make sure that it contains the following settings:

    [client]
        port            = 3307
    socket          = /home/ds/blacktest/master.sock
    
    [mysqld_safe]
    socket          = /home/ds/blacktest/master.sock
    
    [mysqld]
    user            = mysql
    pid-file        = /home/ds/blacktest/master.pid
    socket          = /home/ds/blacktest/master.sock
       port            = 3307
    basedir         = /home/ds/blacktest
    datadir         = /home/ds/blacktest/data.master
    tmpdir          = /tmp
    language        = /home/ds/blacktest/share/mysql/english
    
    bind-address    = 127.0.0.1
    
    server-id       = 1
    log-bin         = /home/ds/blacktest/master-bin.log
    

    Everything that is specific to the master instance has been highlighted—those values are going to be different for filter and slave instances.

  4. Start the master daemon for the first time to make sure everything works so far. We recommend a dedicated window for this daemon. For example:

    ~/blacktest$ xterm -T MASTER -e bin/mysqld \
    >        --defaults-file=my.master \
    >        --console &

    This will start the daemon in the background and show its output in a new window:

    The warning about the --user switch can be ignored for now. Should you not get a message very similar to the one above (especially concerning the ready for connections part) go back and find the error in your setup before going on. Usually, the error messages issued by MySQL are rather verbose and bring you back on track pretty soon.

  5. Insert some test data to be able to verify the correct function of the filter later. To do so, connect to the master instance just started and create some tables and data:

    ~/blacktest$ bin/mysql -uroot -S master.sock --prompt='master>'
    
    master> CREATE DATABASE repdb;
    master> USE repdb;
    master> CREATE TABLE tblA (
        ->       id INT(10) PRIMARY KEY NOT NULL,
        ->       label VARCHAR(30)
        ->  ) ENGINE=InnoDB;
    master> CREATE TABLE tblB (
        ->       name VARCHAR(20) PRIMARY KEY NOT NULL,
        ->       age INT(3)
        ->  ) ENGINE=InnoDB;
    master> INSERT INTO tblA VALUES
        ->      (1, 'label 1'),
        ->      (2, 'label 2'),
        ->      (3, 'label 3');
    master> INSERT INTO tblB VALUES 
        ->      ('Peter', 55),
        ->      ('Paul', 43), 
        ->      ('Mary', 25);

    Inserting this data already creates binlog information. You can easily verify this by looking at the file system. The master-bin.000001 file should have grown to around 850 bytes now. This might vary slightly if you did not enter the commands above with the exact same number of spaces—the binlog will store commands in the exact way you typed them. For example, we will only replicate changes to table tblB but ignore anything that happens to table tblA. We will assume that tblB needs to be written by an application on the slave. So the table should be present, but empty on the slaves to avoid key collisions.

  6. Create a user account on the master for the filter to connect with:

    master> GRANT REPLICATION SLAVE
        -> ON *.*
        -> TO 'repblack'@'localhost'
        -> IDENTIFIED BY 'blackpass';
  7. Configure the filter (blackhole) instance with a configuration file named my.black that contains at least the following :

    [client]
    port            = 3308
    socket          = /home/ds/blacktest/black.sock
    
    [mysqld_safe]
    socket          = /home/ds/blacktest/black.sock
    
    [mysqld]
    log-slave-updates
    skip-innodb
    default-storage-engine=blackhole
    
    user            = mysql
    pid-file        = /home/ds/blacktest/black.pid
    socket          = /home/ds/blacktest/black.sock
    port            = 3308
    basedir         = /home/ds/blacktest
    datadir         = /home/ds/blacktest/data.black
    tmpdir          = /tmp
    language        = /home/ds/blacktest/share/mysql/english
    
    bind-address    = 127.0.0.1
    
    server-id       = 2
    log-bin         = /home/ds/blacktest/black-bin.log
    relay-log       = /home/ds/blacktest/black-relay.log
    

    Note

    Notice that all occurrences of master have been replaced with black!

    Moreover, the server-id setting has been changed and the log-slave-updates, skip-innodb, and default-storage-engine options have been added. The second one prevents this instance from creating ibdata table space files, which would not be used later anyway. The last one specifies which storage engine to use when a CREATE TABLE statement does not explicitly specify one or if the specified engine is not available. We will come back to this soon.

  8. Make sure this instance basically works by starting it the same way as the master before (you will not see the InnoDB messages here, of course).

    ~/blacktest$ xterm -T BLACK -e bin/mysqld \
    >        --defaults-file=my.black \
    >        --console &
  9. Create a set of dump files from the master to set up both the blackhole filter and an example slave. The details on why we need two and in which ways they are different will be explained later. Use these commands to create the files needed:

    ~/blacktest$ bin/mysqldump -S master.sock -uroot \
    >                      --master-data \
    >                      --single-transaction \
    >                      --no-create-info \
    >                      --ignore-table=repdb.tblA \
    >                      repdb > master_data.sql
    
    ~/blacktest$ bin/mysqldump -S master.sock -uroot \
    >                      --no-data \
    >                      repdb > master_struct.sql
  10. Connect to the filter server, create the database, make it the default database, and finally, import the structure information created before:

    ~/blacktest$ bin/mysql -uroot -S black.sock --prompt='black> '
    
    black> CREATE DATABASE repdb;
    black> USE repdb;
    black> source master_black.sql;

    At this point we now have the structure of the master transferred to the filter engine adapted to use the blackhole engine for all the tables.

  11. Set up the replication between master and filter engine. To do so, we need to know the exact position from where the filter will start replicating. Extract this information from the previously taken data dump like this:

    ~/blacktest$ head -n 30 master_data.sql | grep 'CHANGE MASTER TO'

    Write down that information; we will need it in a moment.

  12. Modify the my.black configuration file to contain the following in the [mysqld] section:

    replicate-ignore-table=repdb.tblA
    replicate-do-table=repdb.tblB

    This is a very simple filter setup; in a real application scenario these rules will probably be more complex.

  13. Restart the filter engine to activate the new configuration:

    ~/blacktest$ bin/mysqladmin -uroot -S black.sock shutdown
    
    ~/blacktest$ xterm -T BLACK -e bin/mysqld \
    >        --defaults-file=my.black \
    >        --console &
  14. Reconnect the client connected to the blackhole engine. To do this, just issue a SELECT 1; command.

  15. Execute the following command to hook up the filter to the master. Be sure to fill in the values you wrote down a moment ago in the statement:

    black> CHANGE MASTER TO
        -> master_host='localhost',
        -> master_port=3307,
        -> master_user='repblack',
        -> master_password='blackpass',
        -> master_log_file='master-bin.000001',
        -> master_log_pos=1074;
  16. Retrieve information required to set up the filter/slave portion. Write down the results of the SHOW MASTER STATUS command, they will be needed later:

    black> FLUSH LOGS;
    black> SHOW MASTER STATUS;
    +------------------+----------+---+---+
    | File             | Position | … | … |
    +------------------+----------+---+---+
    | black-bin.000003 |       98 |   |   |
    +------------------+----------+---+---+
  17. Start the slave thread on the filter engine and verify that everything is going well:

    black> START SLAVE;
    black> SHOW SLAVE STATUS \G
    ************************** 1. row ***************************
                 Slave_IO_State: Waiting for master to send event
                    Master_Host: localhost
                    Master_User: repblack
                    Master_Port: 3307
                  Connect_Retry: 60
                Master_Log_File: master-bin.000001
            Read_Master_Log_Pos: 1074
                 Relay_Log_File: black-relay.000003
                  Relay_Log_Pos: 236
          Relay_Master_Log_File: master-bin.000001
               Slave_IO_Running: Yes
              Slave_SQL_Running: Yes
                            ...
             Replicate_Do_Table: repdb.tblB
         Replicate_Ignore_Table: repdb.tblA
                            ...
                     Last_Errno: 0
                     Last_Error:
                   Skip_Counter: 0
            Exec_Master_Log_Pos: 1074
                Relay_Log_Space: 236
                            ...
          Seconds_Behind_Master: 0

    At this point we have successfully established a replication connection between the master database and the blackhole-based filter instance.

  18. Check that nothing has yet been written to the filter's binlogs. Because we issued a FLUSH LOGS command on the filter instance, there should be nothing in the most recent binlog file. Verify this as follows:

    ~/blacktest$ bin/mysqlbinlog black-bin.000003
  19. Test the filter setup with some statements issued on the master:

    master> UPDATE repdb.tblA
        -> SET label='modified label 3'
        -> WHERE id=3;
    master> INSERT INTO repdb.tblB
        -> VALUES ('John', 39);

    We would expect to see the INSERT in the binlog file of the filter instance, but not the UPDATE statement, because it modifies tblA, which is to be ignored.

  20. Verify that the rules work as expected by having another look at the filter's binlogs:

    ~/blacktest$ bin/mysqlbinlog black-bin.000003

    This looks precisely as expected—the INSERT is present, the UPDATE is nowhere to be seen.

  21. Set up the configuration of a slave using these settings:

    [client]
    port            = 3309
    socket          = /home/ds/blacktest/slave.sock
    
    [mysqld_safe]
    socket          = /home/ds/blacktest/slave.sock
    
    [mysqld]
    user            = mysql
    pid-file        = /home/ds/blacktest/slave.pid
    socket          = /home/ds/blacktest/slave.sock
    port            = 3309
    basedir         = /home/ds/blacktest
    datadir         = /home/ds/blacktest/data.slave
    tmpdir          = /tmp
    language        = /home/ds/blacktest/share/mysql/english
    
    bind-address    = 127.0.0.1
    
    server-id       = 3
    relay-log       = /home/ds/blacktest/slave-relay.log
    

    Note

    Notice that all occurrences of master have been replaced with slave!

    Again the server-id setting has been changed and the log-slave-updates, skip-innodb, and default-storage-engine options that were part of the filter instance's configuration are not included. Also, the log-bin parameter has been removed because changes on the slave need not be recorded separately.

  22. Start up the slave engine. You will see the familiar messages about InnoDB filling up the data files and finally, the Ready for connections line:

    ~/blacktest$ xterm -T SLAVE -e bin/mysqld \
    >        --defaults-file=my.slave \
    >        --console &
  23. Then connect a client to the slave and create the database:

    ~/blacktest$ bin/mysql -uroot -S slave.sock --prompt='slave> '
    
    slave> CREATE DATABASE repdb;
    slave> USE repdb;

    At this point, the slave is set up and has an empty repdb database.

  24. Fill up the slave database with the initial snapshot of the master. We need to load two files here. The details of why are explained further down in the How it works... section.

    slave> source master_struct.sql;
    ...
    
    slave> source master_data.sql;
    ...
  25. Verify that you can find the data from the master on the slave now by doing a SELECT * FROM first table repdb.tblA and then repdb.tblB.

    The first SELECT shows no records because tblA was excluded from the dump. Table tblB contains the three records we inserted on the master.

  26. Create a replication user account on the filter instance for the slaves to use:

    black> GRANT REPLICATION SLAVE
        -> ON *.*
        -> TO 'repslave'@'localhost'
        -> IDENTIFIED BY 'slavepass';
  27. Connect the slave to the filter engine. Be sure to insert the correct values for MASTER_LOG_FILE and MASTER_LOG_POS in the statement. Those are the values you wrote down when you issued the SHOW MASTER STATUS command on the filter server before starting the replication there:

    slave> CHANGE MASTER TO
        -> master_host='localhost',
        -> master_port=3308,
        -> master_user='repslave',
        -> master_password='slavepass',
        -> master_log_file='black-bin.000003',
        -> master_log_pos=98;
    Query OK, 0 rows affected (0.01 sec)
  28. Start the slave and verify that it starts up correctly:

    slave> START SLAVE
    slave> SHOW SLAVE STATUS \G
    ************************** 1. row ***************************             Slave_IO_State: Waiting for master to send event
                            ...
          Relay_Master_Log_File: black-bin.000003
               Slave_IO_Running: Yes          Slave_SQL_Running: Yes
                             ...
          Seconds_Behind_Master: 0
  29. As soon as the previous step is complete, the replication should already have updated tblB on the slave and inserted the new ("John", 39) record. Verify it like this:

    slave> SELECT * FROM repdb.tblB;

    Apparently, the replication works. You can now try to modify some data on the master and check if the results match on the slave. Anything you do to modify tblB should be reflected on the slave. Remember to use fully qualified statements; otherwise changes will not match the replication rules.

How it works...

Though MySQL did not implement a filter on the master feature literally, another way of doing similar things was provided. While MyISAM and InnoDB implement ways of storing data on disk, another engine was created that is basically an empty shell. It just answers OK to all INSERT, UPDATE, or DELETE requests coming from the SQL layer above. SELECT statements always return an empty result set. This engine is suitably called the blackhole storage engine, as everything you put into it just vanishes.

In the upper part you see the main master server. All modifying statements are written on the master's binlog files and sent over the network to subscribed slaves. In this case, there is only a single slave: the filter server in the middle. The thick arrow in between them represents the large amount of data that is sent to it.

In the lower part of the picture, there are a number of slaves. In a regular setup, a thick arrow would be drawn from the master to each of those—meaning that the same massive amount of replication data would be sent over the network multiple times. In this picture, the filter server is configured to ignore statements for certain tables. It is also configured to write the statements received from a replication master to its own binlogs. This is different from regular slaves because usually those do not write replicated statements to their binlogs again. The filter server's binlogs are much smaller than those of the main master because lots of statements have been left out. This would normally have taken place on each and every regular slave.

The regular slaves are configured against the filter server. That means they only receive the pre-filtered stream of statements that have made it into the filter's binlogs through the replicate-ignore-* and replicate-do-* directives. This is represented by thin arrows in the picture.

Because slaves can go offline for extended amounts of time, binlogs could easily mount up to dozens of gigabytes in a few days. With the much smaller filtered binlogs you can more often purge the large main master's binlogs as soon as you have made a full backup, in the end freeing more space than is needed by the additional filter instance.

Other storage engines than InnoDB

Be advised that if you are using a different storage engine than InnoDB for your tables (especially MyISAM), you will need to do a little more tweaking. This is because the InnoDB example relies on MySQL's being very lenient concerning errors in many cases. We put the skip-innodb option into the my.black config file. This means that InnoDB will not be available at runtime. Because the master_struct.sql dump file contains CREATE TABLE … ENGINE=InnoDB statements, MySQL falls back to the default storage engine that we configured to be the blackhole engine.

If you are using MyISAM tables, there is no need for the server to automatically change the table type because MyISAM is always available (MySQL stores its user information apart from other things in MyISAM tables). So you would need to adapt the master_struct.sql dump file before sourcing it into the filter server. I recommend using sed, like this:

~/blacktest$ sed -e 's/ENGINE=InnoDB/ENGINE=BLACKHOLE/g' \
>      master_struct.sql > master_black.sql

This will replace all occurrences of the InnoDB engine with the blackhole engine and put the result into a new file. Please keep the original file, too, as it will be needed for the slave machines.

 

Setting up slaves via network streaming


If you need to reset one or more slaves regularly, say every morning before business hours begin, importing a SQL dump might take too much time, especially if the slaves are relatively low-end machines without a sophisticated I/O subsystem.

In this recipe, we will present a way to set up a MySQL slave with minimal I/O load on the hard drive and the network adapter of the slave. The example assumes a Linux-based slave machine; however, you should be able to apply this to Windows as well. but you will need to download some free tools most Linux distributions come with out of the box.

The general idea is to have a more powerful machine, which can be the master if resources allow, to prepare a complete set of data files for the slaves and later stream them directly to the slave's disk from a web server.

Getting ready

To try this out, you will need a master server with at least one slave. Additionally, a machine with a web server installed is required. Depending on your setup, the master server might be suitable for this task. In the example that follows, we will assume that the master server has a web server running.

How to do it...

  1. Set up a fresh temporary MySQL daemon with a configuration similar to the clients.

  2. Dump the data from the master with the --master-data option and feed it into the temporary server.

  3. Shut down the temporary server and compress its data.

  4. Transfer the archive to the slaves and unpack.

  5. Adapt the slaves' config files.

  6. Run the slaves and let them connect and catch up with the master.

How it works...

This recipe is based on the fact that you can quite easily copy MySQL's data files (including InnoDB table space files) from one machine to another, as long as you copy them all. So, we first create a ready-to-go set of slave data files on a relatively powerful machine and transfer them to multiple slaves with weaker hardware. Usually, those files will be bigger than a simple SQL dump file that is usually used for slave setups. But no parsing and processing is required on the target system. This makes the whole thing mostly network and linear disk I/O bound.

The idea behind this concept is to relieve the individual slaves from importing SQL files themselves. As their hardware is rather slow and MySQL only supports single threaded slave SQL execution, this can be very time consuming. Instead, we use the master's better resources temporarily as a single power-slave and let it handle the process of importing. We then provide any number of identical slaves with its data files. This will reduce the burden of the other slaves to simply unpacking some files.

While this does not really save anything in terms of bytes that need to be written to each slave's disk, the access pattern is much more sensible. The following table compares the disk transfers for a regular SQL import from local disk and the proposed alternative for a 60MB gzipped SQL file, which will lead to approximately 2GB of InnoDB table space files:

Regular SQL Import

Prepared Data File Deployment

Linear write 60MB download to local disk

Download 60MB, directly streamed to 2GB data files, written linearly

Linear write 2GB initial creation of InnoDB data files

n/a

Linear read 60MB SQL.gz, interleaved with random write 2GB to data files

n/a

4GB total read/written randomly

2GB linear write

Importing a SQL file from the local hard disk means there are continual seeks between the current position in the SQL text file and the server's data files. Moreover, as the database schema may define lots of indexes, there is even more random disk write activity when executing simple INSERT statements.

In contrast unpacking ready-made InnoDB table spaces (or MyISAM table files for that matter)is basically just linear writing.

Temporary daemon

The SQL dump needs to be executed at least once. So, we set up a temporary MySQL daemon with a stripped down configuration that is close to the actual slaves—meaning all the parameters that affect the storage files must match the slaves to create compatible data files.

Every time you want to prepare such a new slave installation image, the temporary daemon should be started with an empty data directory. While not strictly necessary, we prefer to delete the table space and transaction log files every time because it allows for better compression rates later.

The data files should be created close to the size that will be needed, maybe a little more to prevent the need for them to grow. Nevertheless, specify the last data file to be auto-extending. Otherwise the process of importing the SQL data may lead to filling the table space prematurely, especially when used in an automated process that can be difficult to handle.

Also, you should allow InnoDB to add larger chunks to the last data file if needed (default: 8MB). Extending the files is associated with some overhead, but using bigger chunks reduces the impact on the I/O subsystem. You should be fine with 50MB or 100MB. The bigger this is, the less often InnoDB will have to extend the file. See the manual section on InnoDB configuration for more info.

Dumping master data

Once you have the temporary daemon running, use the mysqldump tool with the --master-data and --single-transaction options to create a dump of the database(s) you need to replicate. In order to save time and disk space, you may find it useful to pipe the output directly through the mysql command-line client and feed it into the target temporary server.

Shutting down and compressing

You can now shut down the temporary server. Compress the data directory. Depending on how you want to configure permissions, you may include or exclude the mysql schema. We usually have the temporary server set up with as low permissions as possible and do not move the mysql schema along.

For compression, you should not use the ZIP format. It contains a catalog of all files included at its very end; so piping it through a decompression program on the fly will not work. Instead, we use a gzipped tarball. This allows us to download and to pipe the data stream through gunzip before directing it to disk.

Transferring to the slave and uncompressing

On the slave we suggest curl as a download tool. It is important that the tool you choose be able to output the downloaded file directly to standard out. With curl that is quite simple—it is its default behavior. It also handles files larger than 2GB, which some versions of wget have problems with. The command line should look similar to this:

curl http://the.server/mysql_data.tgz | tar -C /the/target/datadir -xzf -

curl will download the file and pipe it to tar to decompress into the target data directory.

Note

Do not miss the final - at the end of the command!

You will find that on a local area network, downloading and unpacking will be considerably faster than having MySQL to first create the empty data file and then import the SQL, for the reasons stated above.

Adjusting slave configuration

When the data files have reached their destination on the slave, you may need to adjust the slave settings. This especially depends on whether you copied fixed size data files (in which case you can prepare the config file in advance) or used the autoextend option on the last table space file. In that case, you could write a little script that takes a template my.cnf file with your basic settings and replaces some placeholders for the data file-related settings via sed. One of those is the size of the last InnoDB data file from the archive. It will become a fixed size file on the slave. Another file will then be added at the first slave start.

Connecting to the master

One last thing that needs to be done is to read the master's current binlog file name and position from the master.info file. This is required because once the slave server has been started you will need to provide correct credentials for the replication user. You must also explicitly tell the slave which master host to connect to. Unfortunately, when issuing a CHANGE MASTER TO command on the slave, which includes a master host name, all information about previous master binlogs—the corresponding offset—is discarded (see MySQL online manual, chapter 12.6.2.1 CHANGE MASTER TO Syntax at http://dev.mysql.com/doc/refman/5.1/en/change-master-to.html).

Therefore, you will need to tell the slave again where to begin replication.

One possible solution is to read the contents of the master.info file that was brought along with the data files into a bash script array and inject the values into the statement:

arr = ( $(cat master.info) )
mysql -e "CHANGE MASTER TO master_host='the.master.server', master_user='replication_user', master_password='the_password',
master_log_file='${arr[2]}', master_log_pos=${arr[3]}"

The format of the master.info file is described in the MySQL manual.

Starting the slave

As soon as you issue a START SLAVE statement, the slave will connect to the master and begin to catch up with whatever has happened since the time when the dump was taken.

 

Skipping problematic queries


There are occasions where something goes wrong and a problem prevents one or more slave servers from updating. The reasons for this can be several, but most often some sort of discrepancy between the master's and the slave's data set will cause a statement to fail on the slave that was executed properly on the master (otherwise it would not have made it to the binlog).

This is where the basic principle of assuming master and slave being equal becomes a little too simple. It can lead to a potentially long series of statements executing on the slave, but on a different set of data than the master has. Depending on how long this goes unnoticed, the master and slave can drift out of sync unnoticed, until a statement cannot be executed successfully on the slave—for example because a foreign key constraint fails on the slave.

Fortunately, not every problem stems from such a serious error, which can often only be repaired by resetting the affected slaves to a known good state.

Often a slave stops the replication because a record to be inserted is already present, resulting in key uniqueness violation error. This is especially likely when (accidentally or on purpose) you are working on the master and the slaves, modifying data on both sides maybe even to fix a replication problem.

In this recipe, we will show you how to skip one or more problematic queries—meaning instructions replicated from the master that will not execute correctly on the slave machine.

Getting ready

We will demonstrate the skipping of problematic queries in a contrived error scenario. To try this for yourself, you will need two MySQL servers set up as master and slave, being currently in sync. As an example, we will use the sakila sample database to demonstrate a record INSERT that fails on the slave because it was previously inserted manually by accident.

How to do it...

  1. Connect to the master using a MySQL client. Make sakila the default database.

  2. With a second client, connect to the slave. Make sakila the default schema here as well.

  3. On the slave, enter the following command to insert a new category:

    slave> INSERT INTO category (name) VALUES ('Inserted On Slave');

    In this case, the category_id column was automatically set because it is defined as auto-incrementing. At this point, the master and the slave are already out of sync because this record does not exist on the master.

  4. On the master, insert a new record as well:

    master> INSERT INTO category (name) VALUES ('Inserted On Master');

    You can see that the master also picked 17 as the category_id. It has been written to the binlog and has by now probably been replicated to the slave.

  5. Have a look at the replication status on the slave:

    slave> SHOW SLAVE STATUS \G
    ************************** 1. row ***************************
                             ...
               Slave_IO_Running: Yes
              Slave_SQL_Running: No
                             ...
          Seconds_Behind_Master: NULL

    You can see that the replicated insert has failed. No more statements replicated from the master will be executed (Slave_SQL_Running: No).

  6. Repair the damage by making sure the slave records are identical:

    slave> UPDATE category SET name='Inserted On Master' WHERE category_id=17;

    Now the data on master and slave are identical again.

  7. Tell the slave that you want to skip the (one) INSERT statement that came from the master and that cannot be executed:

    slave> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
  8. Start the slave SQL thread again and check the replication status:

    slave> START SLAVE;
    slave> SHOW SLAVE STATUS \G
    ************************** 1. row ***************************
                            ...
               Slave_IO_Running: Yes
              Slave_SQL_Running: Yes
                            ...
          Seconds_Behind_Master: 0

You can see that the replication is up and running again.

How it works...

When the slave data was out of sync in such a way that a statement from the master would fail (in this case because of a duplicate identity column value), the slave server stopped executing any more SQL statements, even though in the background they were still read from the master and stored in the relay logs. This is what the Slave_IO_State and Slave_IO_Running columns from the output of SHOW SLAVE STATUS say.

MySQL does this to give you a chance to look at the problem and determine if you can repair the situation somehow. In this very simple example, the solution is simple because we can easily bring the slave's data back in sync with the master by modifying the record in question to match the contents that were sent from the master and then skip the INSERT replicated from the master using the SQL_SLAVE_SKIP_COUNTER global variable. This will skip exactly one statement from the relay logs, when the slave is next told to start. In our case, this is the problematic INSERT.

After that the replication is back in sync, as master and slave are now based on identical data sets again, allowing the following statements to be replicated normally.

There's more...

Another solution in this particular case could have been to delete the record on the slave and then restart the replication with START SLAVE. As the INSERT from the master has not been executed yet, replication would continue as if nothing had happened.

However, under more realistic circumstances, when confronted with a situation like this, you might not have a chance to delete the row on the slave due to foreign key constraints. Only because we immediately took care of the problem and we were sure that in the meantime no programs could have written to the slave, possibly creating new references to that record, were we able to remove it.

Depending on your application architecture and how fast you noticed the problem, some process might have been writing data to the slaves to tables that are designed for this purpose, linking to the now present category_id 17 and effectively preventing you from deleting it.

While in this simple case, we would be sure that the replication setup is now back to normal again, you often will not be able to tell for certain at which point in time a replication problem originated. INSERT statements of duplicate keys will immediately cause an error to become apparent. UPDATE or DELETE statements will often succeed in executing, but would have different effects on the slave than on the master, when they were previously out of sync.

Note

Problems like this can corrupt the data on your slaves silently for extended periods of time. When you find out in the end, it is often too late to recover without resorting to setting up the slave afresh.

When in doubt, we recommend to first use mk-table-checksum aservers are in sync recipe in this chapter, or more generally to set up the slave from a well-known good state to be completely sure!

 

Checking if servers are in sync


As MySQL cannot detect if two servers are in sync (that is they contain the same records and tables), one would often like to verify that master and slave are still working on identical data sets to be sure no corruption has occurred yet.

For this purpose, the excellent Maatkit suite of programs (see http://www.maatkit.org) contains a handy tool called mk-table-checksum. It automatically calculates checksums of tables on one or more servers, which can then be compared. Should the checksums differ, then the table in question is not identical on the machines involved in the check.

The servers involved need not necessarily be a replication master and slaves, but can be any set of servers you wish to compare. mk-table-checksum has an additional alternative means of checking the special case in replication environments to see if a master and its slaves are in sync. See the There's more... section at the end of this recipe for more details on this feature.

Getting ready

Maatkit is written in Perl. While on most Unix-like systems this scripting language is already installed by default or can be easily downloaded and set up, Windows users will not be so lucky in general. If you are stuck with Windows, you might want to take a look at ActivePerl, a mature Perl implementation for Windows.

Moreover, you are definitely going to need the Maatkit mk-table-checksum tool. You can get it directly from http://www.maatkit.org. Also, download the mk-checksum-filter companion tool and put it in the same directory as mk-table-checksum.

In this example, we will compare two MySQL servers that differ in the sakila database's country table located on machines called serverA and serverB.

You will need to have user accounts for both machines that have permission to connect and execute statements remotely.

The command lines in this recipe might change with newer versions of Maatkit, as it is under active development. Double-check with the online manual that the instructions printed here are still current before trying them out.

How to do it...

  1. On a command shell prompt, enter the following line, assuming mk-table-checksum is in the current directory and executable:

    $ ./mk-table-checksum h=serverA,u=userA,p=passwordAh=serverB,u=userB,p=passwordB | ./mk-checksum-filter
    
  2. Check the output of this command (formatted and abbreviated for printing):

    Database

    Table

    Chunk

    Host

    Engine

    Count

    Checksum

    sakila

    country

    0

    serverA

    InnoDB

    NULL

    2771817858

    sakila

    country

    0

    serverB

    InnoDB

    NULL

    3823671353

Notice the last column: The checksums do not match—the tables are not identical.

How it works...

mk-table-checksum connects to all servers listed on the command line and calculates checksums for all tables. Identical table contents result in identical checksums. So if the checksums from two servers do not match for any given table, there must be a difference in their contents. The mk-checksum-filter tool removes all lines from the output that do not indicate a checksum mismatch.

Note

It is important to know that the checksums are different if you employ different versions of MySQL across servers. In this case, a different checksum might just be the result of the different versions!

mk-table-checksum offers several algorithms for checksumming, each with different speeds and different levels of resilience against certain kinds of data differences that might cancel each other out, leading to identical checksums, but for different data. The Maatkit online manual contains detailed and current information on this topic.

There's more...

Due to the asynchronous nature of MySQL replication, executing the checksum statements remotely from a single machine may not yield reliable results. This is because the master database might already contain modifications that have not been executed by each slave yet.

To compensate, mk-table-checksum offers a special mode to check slaves and masters. Instead of executing the calculations remotely, the statements to do so are written to the master's binlog and then sent off to the slaves via the regular replication mechanism. This ensures that each slave will calculate the checksum at the correct time with respect to the transaction order. The results are then stored in a table on the slave that can be retrieved with a second command remotely later on. To use this feature, you need a user with sufficient privileges to create a table for this purpose on the slaves.

For more details, see the --replicate and --create-replicate-table options in the Maatkit online manual.

 

Avoiding duplicate server IDs


A key configuration item in any replication setup is server IDs. They must be unique across all participating master and slave machines. Unfortunately, there is no official way to verify this reliably. Instead, when you introduce duplicates by mistake, strange behavior may surface. Generally, this happens when cloning the machines from an image.

Most importantly, on the master server you will not see any indication of the problem. The problem arises only on the slaves without clearly stating the root cause of the problem. See the There's more... section of this recipe for more details.

Getting ready

The server-id setting does not carry any meaning in and of itself, but is only used to internally distinguish servers from each other. Generally, administrators setting up new MySQL servers enter sequential or random values for this field. This requires a list of server IDs already issued, preferably including the host name. As with most things in life that need to be done manually, maintaining this list is likely to become a burden and will be forgotten.

Instead, you can assign server IDs based on features of the individual machines that are usually unique already, for example, the network interface's MAC address or the IP address, which should remain reasonably fixed for any server machine as well.

IP addresses are usually shown in a dotted notation of four numbers between 0 and 255. Because MySQL requires server-id to be specified as single decimal value, you need to convert it first.

How to do it…

  1. Determine your server's IP address. Make sure not to use the loop-back adapter or a similar pseudo-interface. In this example we assume an IP address of 10.0.159.22.

  2. Convert the 4 bytes of the address to hexadecimal. Mostly any calculator application can do this for you. You enter each of the four numbers in decimal mode and then switch to hexadecimal mode. Just replace each individual decimal value with its hexadecimal counterpart. For the address above you will come up with: 0a.00.9f.16

  3. Append the bytes (that is just remove the dots between them) and convert them back to decimal by switching modes: 0a009f16HEX=167812886DEC

  4. Insert that final value as the server ID in the [mysqld] section of that server's configuration file:

       [mysqld]
       server-id=167812886
    

How it works...

The IP address serves to uniquely identify a network interface (and therefore a machine) on a network. We leverage this uniqueness by recycling the IP address as the server ID. Most operating systems will issue a warning when an IP address conflict is detected, so this indirectly points to a replication problem as well.

Note

Of course, traditional IPv4 addresses (those usually noted in the above notation) are only unique in their respective subnet. That means you should not rely on this recipe alone for your server IDs if master and slave machines are located in different locations from a network topology point of view!

There's more...

The IP address is only one possible unique value you can use. Anything that you can fit in the valid numeric range of the server-id setting can be used. Ideally that value should never change over the lifetime of a server, much like a good Primary key, just not for a single database record, but the server as a whole.

You could use any sort of serial number your hardware vendor already assigns to the machine, if it is purely numeric and fits the valid range of 4 bytes. However, this ties you to the vendor's idea of uniqueness, which you cannot verify reliably. Alternatively, the last 4 bytes of the server's MAC address (those are 6 bytes long, starting with a vendor specific prefix) could be used as well. However, beware that unless you exclusively use network adapter chip sets from a single vendor, there remains a certain danger of duplicates.

Recognizing symptoms of duplicate server IDs

Despite all care, errors can happen and duplicate server-ids can be issued. Unfortunately, MySQL will not tell you explicitly when you have non-unique server-ids in your replication setup. While on the master, you will not see any evidence in the log files that something is wrong, slaves will show strange behavior and issue seemingly unrelated error messages to their log files in short succession:

Of course, the names of machines, log files, and positions will vary, but the message of an assumed shutdown of the master, followed by immediate retries and failing again is a clear indication of a problem with replication server-ids.

 

Setting up slaves to report custom information about themselves to the master


When you issue a SHOW SLAVE HOSTS command on a replication master server, you will get a list of slaves connected, provided that they are set up correctly.

Unfortunately, by default they are not, so unless you specifically configure them to do so, slaves will not register themselves with the master. In a default setup you might not see any slave in the output of the above command or in the Replication Status pane in MySQL Administrator at all, even though there might be several configured against this master.

In this recipe, we will show you how to configure a slave to tell its master some details that might come in handy when troubleshooting

Note

Please note that due to a bug in MySQL the output of the SHOW SLAVE HOSTS command is not always reliable! Sometimes it will report hosts being available when in fact they are currently not. The only way that seems to fix an erroneous display is to stop and start the master server.

This effectively makes this feature unsuitable for the purpose of the actual current health of the slaves. It, nevertheless, provides a way to gather some inventory information on their location and some other details described below.

The bug report is tracked at http://bugs.mysql.com/bug.php?id=13963.

Getting ready

To follow along, you will need sufficient operating system privileges to modify the slave's configuration file (my.cnf or my.ini depending on your operation system). To actually see that status on the master you will need a MySQL user there as well.

How to do it...

  1. Shut down the slave.

  2. Open its configuration file in a text editor.

  3. Make sure the following line is present in the [mysqld] section:

    report-host=slave_1701.example.com
  4. Save the configuration.

  5. Restart the slave.

  6. On the master, issue this command to verify your change was successful:

    mysql> show slave hosts \G

You might of course see many more slaves here, depending on how they are configured.

Tip

Should you ask yourself what the Rpl_recovery_rank line in the output means, you may simply ignore it. It seems it was introduced some years ago but never put to active use.

How it works...

Usually, slaves do not report any details about themselves when they connect to the master. By adding some options in their configuration you can, however, make them announce details about themselves.

We strongly recommend setting up all your slaves to register with the master, especially when you are dealing with many masters and slaves. This can be very helpful to keep on top of things.

MySQL Administrator allows you to remember all slaves it has seen once and display a warning on its Replication Status pane when a machine previously known does not register later. This particular piece of information is not reliable. however; see the warning in this recipe's introduction for more information.

There's more...

The general idea behind the report-host setting is to give an idea about how to reach the slave machine via the network. As the slave might be located behind some sort of firewall or NAT router, its IP address might not be very helpful. So, in general, it can be helpful to have the slave report a fully qualified domain name that can be used to reach it, if applicable.

However, it is by no means mandatory to do so. If you do not intend to access the slave remotely, you might just enter any other piece of information you like to see in the output of the command mentioned in this recipe or the MySQL Administrator pane. As you can see in the previous screenshot, I set up the slaves to report back a name suffixed with the server-ID value. Doing so works around a bug in MySQL Administrator that knows how to remember slaves it has seen before, but sometimes forgets their server-id.

Showing slaves currently unavailable is a feature of MySQL Administrator; the SHOW SLAVE HOSTS command will not mention them at all. To leverage this you must click the Add Host to Monitoring List button for each slave once it is connected. Otherwise, they will not appear at all when they are not connected.

Apart from the report-host configuration setting there are three more options you should know about:

Setting

Description

report-port

Informs about the port that must be used to reach the slave on the domain name reported by report-host. This can be sensible if port forwarding has been set up.

report-user

Report a username that can be used to connect to the slave. Not recommended to use!

report-password

Report a password that can be used to connect to the slave. Not recommended to use!

For completeness, this is what the output of SHOW SLAVE HOSTS will look like if you go against our advice and configure the slave to report a set of login credentials and the master has been started with the show-slave-auth-info option:

Note

While the report-port setting might be useful, we strongly suggest to refrain from using the report-user and report-password options for security reasons.

Even though the master server will only display these values when it is started with the show-slave-auth-info option, it is still very risky to send login credentials over the network in this manner. You should always use more secure ways to exchange login information!

About the Authors

  • Daniel Schneller

    Daniel Schneller works as a software developer, database administrator, and general IT professional for an independent software vendor in the retail sector. After successfully graduating from the University of Cooperative Education in Heidenheim, Germany with a degree in Business Computer Science, he started his career as a professional software developer, focused on the Microsoft technology stack. In 2002 he started focusing on enterprise-level Java development and has since gained extensive knowledge and experience implementing large scale systems based on Java EE and relational databases, especially MySQL since version 4.0.

    Currently he is mostly involved with the ongoing development of framework-level functionality, including customization and extension of an ORM-based persistence layer. He is involved in different open source projects such as FindBugs, Eclipse, and Checkstyle and infrequently blogs about Java, MySQL, Windows, Linux and other insanities at http://www.danielschneller.com.

    Browse publications by this author
  • Udo Schwedt

    Udo Schwedt is head of the Java architecture team and deputy head of the Java development department at the IT service provider for Germany's market leader in the Do-It-Yourself sector. After finishing his studies at RWTH Aachen, Germany with a degree in Computer Science, he started his career as a professional Java developer in a leading software company in the banking sector. In 2003, he joined his current company as a framework developer, subsequently taking the role of a lead architect.

    Both authors are involved in a large scale international software project, which encompasses development of a Java-based merchandise management software solution. This decentralized multi-platform environment is based on more than 500 decentralized MySQL server instances with more than 5,500 replication slaves. Daniel and Udo are responsible for configuration and management of this infrastructure.

    Browse publications by this author

Latest Reviews

(1 reviews total)
Didn't go in depth in the book but I use it as a desk reference as needed.
Book Title
Access this book, plus 8,000 other titles for FREE
Access now