In this chapter, we will discuss:
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.
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:
In the preceding 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 can 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.
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.
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.
my.ini
on Windows and my.cnf
on other operating systems. [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.
master> grant replication slave on *.* to 'repl'@'%' identified by 'slavepass';
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
sakila_master.sql
dump file to each slave you want to set up, for example, by using an external drive or network copy. [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 slaves.
/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;
slave> SHOW SLAVE STATUS\G
************************** 1. row ***************************
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
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 are hard to debug.
Moreover, the master must be configured to write binlogs—a record of all statements manipulating data (this is what the slaves will receive).
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.
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.
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.
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
[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
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.
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.
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.
Beware that this approach will lose all data that was changed on the slave alone. So make sure that this is what you want!
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.
[mysqld]
section and will look similar to this:[mysql] ... log-bin=master-bin ...
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. mysql
folder from the data directory. You also need not copy the binary logs (in this example called master-bin.*)
. new-master-bin.000001
. 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. mysql
database directory if you decided not to copy it from the master. 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.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.
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
.
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.
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!
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.
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. 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.
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.
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(); ...
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
$ java -cp .:mysql-connector-java-5.1.7-bin.jar MySQLBalancingDemo Master: 1000 Slave: 13308
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.
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.
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.
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.
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.
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.
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.
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.
posts
table.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.
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.
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.
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.
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.
In order to follow along, you must have some key data points available because otherwise there is not much to calculate. You will need:
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. |
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 are two more general considerations you might want to think about when planning a replication setup.
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.
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.
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.
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.
~/blacktest$ mkdir data.master ~/blacktest$ mkdir data.slave ~/blacktest$ mkdir data.black
~/blacktest$ cp -R data/mysql data.master ~/blacktest$ cp -R data/mysql data.slave ~/blacktest$ cp -R data/mysql data.black
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.
~/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.
~/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.
master> GRANT REPLICATION SLAVE -> ON *.* -> TO 'repblack'@'localhost' -> IDENTIFIED BY 'blackpass';
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
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.
~/blacktest$ xterm -T BLACK -e bin/mysqld \ > --defaults-file=my.black \ > --console &
~/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
~/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.
~/blacktest$ head -n 30 master_data.sql | grep 'CHANGE MASTER TO'
Write down that information; we will need it in a moment.
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.
~/blacktest$ bin/mysqladmin -uroot -S black.sock shutdown ~/blacktest$ xterm -T BLACK -e bin/mysqld \ > --defaults-file=my.black \ > --console &
SELECT 1
; command.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;
SHOW MASTER STATUS
command, they will be needed later:black> FLUSH LOGS; black> SHOW MASTER STATUS; +------------------+----------+---+---+ | File | Position | … | … | +------------------+----------+---+---+ | black-bin.000003 | 98 | | | +------------------+----------+---+---+
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.
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
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.
~/blacktest$ bin/mysqlbinlog black-bin.000003
This looks precisely as expected—the INSERT
is present, the UPDATE
is nowhere to be seen.
[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
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.
~/blacktest$ xterm -T SLAVE -e bin/mysqld \ > --defaults-file=my.slave \ > --console &
~/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.
slave> source master_struct.sql; ... slave> source master_data.sql; ...
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.
black> GRANT REPLICATION SLAVE -> ON *.* -> TO 'repslave'@'localhost' -> IDENTIFIED BY 'slavepass';
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)
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
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.
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.
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.
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.
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.
--master-data
option and feed it into the temporary server.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 unpack 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.
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: 8 MB). 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 50 MB or 100 MB. The bigger this is, the less often InnoDB will have to extend the file. See the manual section on InnoDB configuration for more info.
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.
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.
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 2 GB, 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.
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.
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.
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.
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.
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.
sakila
the default database. sakila
the default schema here as well.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.
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.
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
).
slave> UPDATE category SET name='Inserted On Master' WHERE category_id=17;
Now the data on master and slave are identical again.
INSERT
statement that came from the master and that cannot be executed:slave> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
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.
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.
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.
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
as described in the Checking if servers 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!
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.
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.
mk-table-checksum
is in the current directory and executable:$ ./mk-table-checksum h=serverA,u=userA,p=passwordA
h=serverB,u=userB,p=passwordB | ./mk-checksum-filter
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.
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.
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.
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.
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.
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.
10.0.159.22
. 0a.00.9f.16
0a009f16HEX=167812886DEC
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.
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!
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.
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.
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
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.
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.
[mysqld]
section:report-host=slave_1701.example.com
mysql> show slave hosts \G
You might of course see many more slaves here, depending on how they are configured.
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.
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:
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!
Where there is an eBook version of a title available, you can buy it from the book details for that title. Add either the standalone eBook or the eBook and print book bundle to your shopping cart. Your eBook will show in your cart as a product on its own. After completing checkout and payment in the normal way, you will receive your receipt on the screen containing a link to a personalised PDF download file. This link will remain active for 30 days. You can download backup copies of the file by logging in to your account at any time.
If you already have Adobe reader installed, then clicking on the link will download and open the PDF file directly. If you don't, then save the PDF file on your machine and download the Reader to view it.
Please Note: Packt eBooks are non-returnable and non-refundable.
Packt eBook and Licensing When you buy an eBook from Packt Publishing, completing your purchase means you accept the terms of our licence agreement. Please read the full text of the agreement. In it we have tried to balance the need for the ebook to be usable for you the reader with our needs to protect the rights of us as Publishers and of our authors. In summary, the agreement says:
If you want to purchase a video course, eBook or Bundle (Print+eBook) please follow below steps:
Our eBooks are currently available in a variety of formats such as PDF and ePubs. In the future, this may well change with trends and development in technology, but please note that our PDFs are not Adobe eBook Reader format, which has greater restrictions on security.
You will need to use Adobe Reader v9 or later in order to read Packt's PDF eBooks.
Packt eBooks are a complete electronic version of the print edition, available in PDF and ePub formats. Every piece of content down to the page numbering is the same. Because we save the costs of printing and shipping the book to you, we are able to offer eBooks at a lower cost than print editions.
When you have purchased an eBook, simply login to your account and click on the link in Your Download Area. We recommend you saving the file to your hard drive before opening it.
For optimal viewing of our eBooks, we recommend you download and install the free Adobe Reader version 9.