|
|
< Day Day Up > |
|
7.5 Administration and MaintenanceConfiguring replication isn't something you do every day (unless you're writing a book about MySQL, of course.) Once replication is set up and running, you're likely just to let it run. But there are a few administrative tasks you'll likely need to deal with at some point. You should, of course, consider automating them as much as possible. 7.5.1 MonitoringReplication increases the complexity of monitoring MySQL's health. Are all the slaves replicating? Have the slaves encountered errors? How far behind is the slowest slave? As you may have come to expect, MySQL provides all the data necessary to answer those questions (and many questions you probably haven't even considered), but extracting and understanding the data is something it won't do for you. In Section 7.5.4, later in this chapter, we'll try to provide some details to help you make sense of all the data MySQL provides, which should help you understand the tools that are helpful in processing that data. 7.5.1.1 Master statusUsing the SHOW MASTER STATUS command, the master will tell you about its replication status: mysql> SHOW MASTER STATUS \G
*************************** 1. row ***************************
File: binary-log.004
Position: 635904327
Binlog_do_db:
Binlog_ignore_db:
1 row in set (0.00 sec)
The output includes the current binary log filename and the position (or offset) into the binary log where the next query will be written. The other two fields correspond to the binlog-do-db and binlog-ignore-db filtering options in the server's my.cnf file. If you are filtering binary log records on the master, one or both of these will list the database names affected by your filters. You can also ask the master which binary logs still exist on disk: mysql> SHOW MASTER LOGS; +----------------+ | Log_name | +----------------+ | binary-log.001 | | binary-log.002 | | binary-log.003 | | binary-log.004 | +----------------+ 4 rows in set (0.02 sec) But the output is quite limited. It would be helpful to know the sizes and ages of the files as well. MySQL is doing little more than reading and displaying the contents of the log-bin.index file. To get more information, you need to log on to the server and examine the files by hand. 7.5.1.2 Slave statusThere is significantly more information available on the slave side of replication, mostly because the slaves have more information to keep track of. To start, the SHOW SLAVE STATUS command provides a good summary of the information from both the master.info and relay-log.info files: mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Master_Host: master.example.com
Master_User: repl
Master_Port: 3306
Connect_retry: 15
Master_Log_File: binary-log.004
Read_Master_Log_Pos: 635904807
Relay_Log_File: relay-log.004
Relay_Log_Pos: 846096118
Relay_Master_Log_File: binary-log.004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_do_db:
Replicate_ignore_db:
Last_errno: 0
Last_error:
Skip_counter: 0
Exec_master_log_pos: 635904807
Relay_log_space: 846096122
1 row in set (0.00 sec)
In addition, there is some other metadata in the output. The Last_errno and Last_error fields provide information about the most recent replication-related error, if any. The Relay_log_space tells you how much space the relay log is consuming. The two most important fields are Slave_IO_Running and Slave_SQL_Running. They tell you if the IO and slave threads are running. 7.5.1.3 Replication heartbeatWatching the values produced by SHOW MASTER STATUS and SHOW SLAVE STATUS can give you a rough idea of how up to date a slave is. The trouble with relying on that information is that you're only looking at bytes. You can determine how many more bytes of log data the slave needs to execute before it is current. However, that doesn't tell you how many queries need to be executed. There's no good way to figure that out, short of running the binary log through mysqlbinlog and counting queries. It is possible to determine how out of date the slave is with some degree of accuracy by implementing a simple heartbeat system. The heartbeat principle is easy. At a fixed interval, say 20 seconds, a process on the master inserts a record with the latest timestamp into a table. On the slave, a corresponding process reads the most recent record every 20 seconds. Assuming that the system clocks on both machines are in sync, you can tell how far behind the slave is to within 20 seconds of accuracy. See the write_heartbeat and read_heartbeat scripts in Section 7.5.4 for a sample implementation. 7.5.2 Log RotationBinary log files accumulate on the server until they are explicitly removed. The SHOW MASTER LOGS command tells you how many logs there are at any given time. To remove one or more logs, use the PURGE MASTER LOGS TO ... command. It removes all the logs up to but not including the given log name. Here's an example: mysql> SHOW MASTER LOGS; +----------------+ | Log_name | +----------------+ | binary-log.001 | | binary-log.002 | | binary-log.003 | | binary-log.004 | +----------------+ 4 rows in set (0.02 sec) mysql> PURGE MASTER LOGS TO 'binary-log.004'; The command tells MySQL to remove binary-log.001, binary-log.002, and binary-log.003. Be careful not to remove logs too quickly. If a slave is offline for a significant period of time, there's a chance that it still needs one or more of the logs you removed. If you're in doubt, run SHOW SLAVE STATUS on each slave to verify which log it is using. To automate this process, see the purge_binary_logs script in Section 7.5.4. 7.5.3 Changing MastersSooner or later you'll need to point your slaves at a new master. Maybe the old one is being replaced with a newer, faster computer; perhaps there was a failure, and you are promoting a slave to master. In MySQL 3.23 and 4.0, you need to inform the slaves about their new master. A future version of MySQL is supposed to include a fail-safe replication feature that automates the process. A planned changing of masters is a straightforward process. (In the event of a master failure, it may not be so easy.) You simply need to issue the CHANGE MASTER TO ... command on each slave. In doing so, you inform the slave of the new master's parameters—the same ones specified in the my.cnf file. The slave will begin replicating from its new master, and MySQL will also update the master.info with the new information. 7.5.3.1 Using the right valuesAs usual, the devil is in the details. How do you decide which values to use? What if you get them wrong? First, let's consider the easy case. If you are in control of the situation, the process is easy. Follow these steps:
The RESET MASTER command tells the master to flush all its binary logs and start fresh. By starting with a clean slate on the new master, there's no guesswork involved in determining the right log position. Since it's a brand new log, we know the position is 4, because each binary log has a 4-byte header that consumes positions 0-3. The complete CHANGE MASTER TO ... command looks like this: mysql> CHANGE MASTER TO
-> MASTER_HOST='newmaster.example.com',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='MySecret!',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='log-bin.001',
-> MASTER_LOG_POS=4;
If, on the other hand, the master crashes and you can't bring it back online in a reasonable amount of time, things aren't so clear-cut. If you have only one slave, of course, there's no decision to make. You use the slave. But if you have multiple slaves, you need to determine which one is the most up to date. By examining the output of SHOW SLAVE STATUS on each slave, you can easily determine which one is closest to matching the master at the time it crashed. Once you know the log name and position, you can construct a CHANGE MASTER TO ... command to run on the remaining slaves. In doing so, however, you'll likely cause some the slaves to be slightly out of sync with their new master. To illustrate why, assume that each query is assigned an increasing unique ID number. The original master had just executed query 500 when it crashed. The "most up-to-date" slave, the new master, had executed query 496. That means that your best slave is missing four queries, and there's no way to recover them unless your application logs every query it writes, which is unlikely. Now, let's assume that there are two more slaves, slave2 and slave3; slave2 executed query 490, and slave3 executed query 493. You have a choice. You can either point both slaves at the new master's current position (query 496) or you can try to figure the corresponding offsets for each slave in the new master's binary log. That will take more time, but it means you lose less data. To find the matching log position for each slave, you need to have the binary log enabled on each slave. Use the mysqlbinlog command (described in Section 7.5.4) to locate the last query executed. Then locate exactly the same query in the new master's binary log. Once you find the query, you'll have the offset you need. The output of mysqlbinlog always includes the offset in a comment right before the query. For example: $ mysqlbinlog log-bin.001 ... # at 683 #021103 18:36:33 server id 1 log_pos 683 Query thread_id=288 exec_time=0 error_code=0 SET TIMESTAMP=1036377393; insert into test1 values (8); The # at 683 line lists the position of the insert into test1 values (8) query in the log. 7.5.4 ToolsIn this section, we'll look at some tools that can make dealing with replication a bit easier. A couple of the tools come straight out of the MySQL distribution, while others are home-grown and often ripe for improvement. The home-grown tools can serve as a starting point for solving your specific needs; such tools are available (and kept up to date) at http://highperformancemysql.com. 7.5.4.1 mysqlbinlog: Viewing data in logsThe mysqlbinlog utility has been mentioned several times in this chapter. It is used to decode the binary formats used by the binary log and relay log. Given a log file, it outputs the SQL queries contained in the log. Furthermore, it precedes each query with several pieces of metadata as comments. $ mysql log-bin.001 ... # at 683 #021103 18:36:33 server id 1 log_pos 683 Query thread_id=288 exec_time=0 error_code=0 SET TIMESTAMP=1036377393; insert into test1 values (8); The first line contains the offset (or position) of the query in the log. The second line begins with a date and timestamp followed by the server ID of the server that first executed the query. The log position is repeated on this line and followed by the event type. Finally, there's the ID of the thread that executed the query, followed by the time the query took to execute (in seconds) and the error code generated by the query. You can use mysqlbinlog to pull the logs from a remote server by specifying a hostname, username, and password. Using the -o command-line option, you can specify the offset from which you'd like to start reading. For example: $ mysqlbinlog -h slave3.example.com -u root -p -o 35532 log-bin.032 7.5.4.2 check_repl: Ensuring that replication takes placeAs discussed earlier, it's important to check that your slaves are replicating properly when you expect them to. The following script connects to the local MySQL server and makes sure that replication is running by examining the output of SHOW SLAVE STATUS and checking for the both the 3.23.xx and 4.x values: #!/usr/bin/perl -w
## On a slave server, check to see that the slave hasn't stopped.
use strict;
use DBIx::DWIW;
my $conn = DBIx::DWIW->Connect(
DB => "mysql",
User => "root",
Pass => "password",
Host => "localhost",
) or exit;
my $info = $conn->Hash("SHOW SLAVE STATUS");
if (exists $info->{Slave_SQL_Running} and $info->{Slave_SQL_Running} eq 'No')
{
warn "slave SQL thread has stopped\n";
}
elsif (exists $info->{Slave_IO_Running} and $info->{Slave_IO_Running} eq 'No')
{
warn "slave IO thread has stopped\n";
}
elsif (exists $info->{Slave_Running} and $info->{Slave_Running} eq 'No')
{
warn "slave has stopped\n";
}
This script makes no effort to repair a problem; it simply reports when something is wrong. Without knowing why the failure occurred, it's probably not wise to blindly restart replication. To skip the problem query and restart replication, see the next section. 7.5.4.3 fix_repl: Skipping a bad query to continue replicationIn the event that replication has stopped on a slave, you should tell the slave to skip the problem query and continue, unless the problem warrants further investigation. No restart of MySQL is necessary. In MySQL 3.23.xx, execute: SET SQL_SLAVE_SKIP_COUNTER=1 SLAVE START In Versions 4.0.0-4.0.2, execute: SET SQL_SLAVE_SKIP_COUNTER=1 SLAVE START SQL_THREAD In Version 4.0.3 and beyond, execute: SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1 SLAVE START SQL_THREAD Yuck. If you're using a mixture of 3.23.xx and 4.0.x servers, it may be difficult to remember the exact syntax for each version. It's much easier to have a copy of the following fix_repl script on hand to do the hard work for you: #!/usr/local/bin/perl -w
#
# fix mysql replication if it encounters a problem
$|=1; # unbuffer stdout
use strict;
use DBIx::DWIW;
my $host = shift || 'localhost';
my $conn = DBIx::DWIW->Connect(
DB => "mysql",
User => "root",
Pass => "pa55word",
Host => $host,
) or die "Couldn't connect to database!";
print "checking $host ... ";
my $info = $conn->Hash("SHOW SLAVE STATUS");
my $version = $conn->Scalar("SHOW VARIABLES LIKE 'Version'");
my $fix_cmd;
my $start_cmd;
# 3.23
if ($version =~ /^3\.23/ and $info->{Slave_Running} eq 'No')
{
$fix_cmd = "SET SQL_SLAVE_SKIP_COUNTER = 1";
$start_cmd = "SLAVE START";
}
# 4.0.0 - 4.0.2
elsif ($version =~ /^4\.0\.[012]/ and $info->{Slave_SQL_Running} eq 'No')
{
$fix_cmd = "SET SQL_SLAVE_SKIP_COUNTER = 1";
$start_cmd = "SLAVE START SQL_THREAD";
}
# 4.0.3 - 4.0.xx, 4.1.xx. Don't know what 5.0 will be like.
elsif ($version =~ /^4\.[01]\./ and $info->{Slave_SQL_Running} eq 'No')
{
$fix_cmd = "SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1";
$start_cmd = "SLAVE START SQL_THREAD";
}
# things are okay or unknown version?
else
{
print "GOOD\n";
exit;
}
print "FIXING ... ";
$conn->Execute($fix_cmd);
$conn->Execute($start_cmd);
print "DONE\n";
exit;
Be careful with this technique. Blindly skipping queries on a slave may cause it to become out of sync with the master. If the query is failing due to a duplicate key error, it's probably safe, but you should investigate how that happened in the first place. 7.5.4.4 purge_binary_logs: Reclaiming space used by binary logsTo make log rotation easier, you can use something like the following purge_binary_logs Perl script. It connects to the specified server and checks to see how many binary logs are sitting around. If there are more than the specified threshold, it removes any extras. #!/usr/bin/perl -w
## On a slave server, purge the replication logs if there are too many
## sitting around sucking up disk space.
use strict;
use DBIx::DWIW;
my $MIN_LOGS = 4; ## keep main log plus three old binary logs around
my $conn = DBIx::DWIW->Connect(
DB => "mysql",
User => "root",
Pass => "password",
Host => 'localhost',
);
die "Couldn't connect to database!" if not $conn;
## see if there are enough to bother, exit if not
my @logs = $conn->FlatArray("SHOW MASTER LOGS");
exit if (@logs < $MIN_LOGS);
## if so, figure out what the last one we want to keep is, then purge
## the rest
my $last_log = $logs[-$MIN_LOGS];
print "last log is $last_log\n" unless $ENV{CRON};
$conn->Execute("PURGE MASTER LOGS TO '$last_log'");
exit;
Depending on your needs, there's a lot of room for improvement in this script. It would be nice if the script took command-line arguments so you wouldn't need to hardcode the hostname, password, and so on. It would also be nice if the script could check the sizes of the log files. If a master is restarted very frequently, using the number of log files as a metric probably isn't as useful as checking the volume of log data. However, the script can't be run remotely if it checked log file sizes, because it needs to examine the files directly. A valuable but difficult addition would be for the script to remove logs only if it can tell that all slaves had already read them. That requires knowing all the slaves and contacting each one to verify its progress in the replication process. 7.5.4.5 mysqldiff: Replication sanity checksAs with anything new, you may not trust replication right away. To help convince yourself that it is really doing what it should do, it's good to perform spot checks on the data, making sure that the slaves have exactly the data they should have. This checking can be done to varying degrees of paranoia:
The first check is quite easy to implement with a bit of Perl code: #!/usr/bin/perl -w
use strict;
use DBIx::DWIW;
$|=1; # unbuffer stdout
my $db_user = 'root';
my $db_pass = 'password';
my $db_name = 'test';
my $master = 'master.example.com';
my @slaves = qw(
slave1.example.com
slave2.example.com
slave3.example.com
);
my %master_count;
for my $server ($master)
{
print "Checking master... ";
my $conn = DBIx::DWIW->Connect(User => $db_user, Host => $server,
Pass => $db_pass, DB => $db_name) or die "$!";
for my $table ($conn->FlatArray("SHOW TABLES"))
{
my $count = $conn->Scalar("SELECT COUNT(*) FROM '$table'");
$master_count{$table} = $count;
}
print "OK\n";
}
for my $server (@slaves)
{
print "Checking $server... ";
my $conn = DBIx::DWIW->Connect(User => $db_user, Host => $server,
Pass => $db_pass, DB => $db_name) or die "$!";
for my $table ($conn->FlatArray("SHOW TABLES"))
{
my $count = $conn->Scalar("SELECT COUNT(*) FROM '$table'");
if ($count != $master_count{$table})
{
print "MISMATCH (got $count on $table, expecting $master_count{$table}\n";
}
}
print "OK\n";
}
exit;
The script connects to the master and gets the number of rows in each table of the given database. Then it connects to each slave and checks to see that the counts match. If they don't, it issues a MISMATCH warning. This framework can easily be extended to handle multiple databases, perform more specific checks, and even attempt to take corrective action. It is even ready to handle multiple masters. 7.5.4.6 write_heartbeat: Generating a periodic health check heartbeatThe following script can implement a heartbeat monitoring system as described earlier. To use it, create a database named MySQL_Admin and a table named Heartbeat with the following structure: CREATE TABLE Heartbeat
(
unix_time INTEGER NOT NULL,
db_time TIMESTAMP NOT NULL,
INDEX time_idx(unix_time)
)
The unix_time field holds the timestamp that is explicitly inserted into the table. The db_time field is set automatically by MySQL. By keeping track of both times and inserting new records instead of simply running an UPDATE on a single record, you maintain historical data in the event someone wants to graph or analyze it. Let's look the script to add records: #!/usr/bin/perl -w
use strict;
use DBIx::DWIW;
my $conn = DBIx::DWIW->Connect(
DB => "MySQL_Admin",
User => "root",
Pass => "password",
Host => 'localhost',
) or die;
my $unix_time = time( );
my $sql = "INSERT INTO Heartbeat (unix_time, db_time) VALUES ($unix_time, NULL)";
$conn->Execute($sql);
exit;
Running the script at a fixed interval generates a heartbeat that can be used by the read_heartbeat script to monitor replication latency. 7.5.4.7 read_heartbeat: Measuring replication log using heartbeatThe companion to write_heartbeat reads the most recent timestamp from the database and computes how far behind the slave might be. Remember that we can't know this time exactly unless the heartbeat records are generated every second, which is probably overkill for most installations. #!/usr/bin/perl -w
use strict;
use DBIx::DWIW;
my $conn = DBIx::DWIW->Connect(
DB => "MySQL_Admin",
User => "root",
Pass => "password",
Host => 'localhost',
) or die;
my $sql = "SELECT unix_time, db_time FROM Heartbeat
ORDER BY unix_time DESC LIMIT 1";
my $info = $conn->Hash($sql);
my $time = $info->{unix_time};
my $delay = time( ) - $time;
print "slave is $delay seconds behind\n";
exit;
This script can also be extended to do far more than report on latency. If the latency is too great, it can send email or page a DBA. |
|
|
< Day Day Up > |
|