Databases Reference
In-Depth Information
The server doesn't automatically discover log files, so you'll also need to update the
server's log index file. The following command will accomplish this on Unix-like
systems:
14
#
/bin/ls −1 /var/log/mysql/somelog-bin.[0-9]* > /var/log/mysql/somelog-bin.index
Make sure the user account under which MySQL runs can read and write the log index
file. Now you can start your log server and verify that it sees the log files with
SHOW
MASTER LOGS
.
Why is a log server better than using
mysqlbinlog
for recovery? For several reasons:
• Replication is a means of applying binary logs that's been tested by millions of users
and is known to work. The
mysqlbinlog
tool isn't guaranteed to work in the same
way as replication and might not reproduce the changes from the binary log faith-
fully.
• It's faster because it eliminates the need to extract statements from the log and pipe
them into
mysql
.
• You can see the progress easily.
• You can work with errors easily. For example, you can skip statements that fail to
replicate.
• You can filter replication events easily.
• Sometimes
mysqlbinlog
might not be able to read the binary log, because of changes
to the logging format.
Replication and Capacity Planning
Writes are usually the replication bottleneck, and it's hard to scale writes with repli-
cation. You need to make sure you do the math right when you plan how much capacity
replicas will add to your system overall. It's easy to make mistakes where replication is
concerned.
For example, imagine your workload is 20% writes and 80% reads. To make the math
easy, let's grossly oversimplify and assume the following are true:
• Read and write queries involve an identical amount of work.
• All servers are exactly equal and have a capacity of exactly 1,000 queries per second.
• Replicas and masters have the same performance characteristics.
• You can move all read queries to the replicas.
If you currently have one server handling 1,000 queries per second, how many replicas
will you need to add so that you can handle twice your current load and move all read
queries to the replicas?
14. We use
/bin/ls
explicitly to avoid invoking common aliases that add terminal escape codes for coloring.
