Before you can use Point in Time Restoration there are a few Prerequisites that you need to have in place for your cluster.
- Step 1 - リストアしたいデータベース/テーブルへの書き込みを停止することでクラスタを準備してください。
- Step 2 - Clustrixの並行バックアップからデータベースおよび/またはテーブルをリストアします。.
- Step 3 - 並行バックアップからbinlogのファイル名と場所を抽出します。
- Step 4 - 回復したいイベントまたは時間を含むbinlogを見つけます。
- Step 5 - Repclientを使ってbinlogの正しい場所を抽出します。
- Step 6 - クラスタのサーバIDを変更します。
- Step 7 - Repserverを使って抽出したbinlogを提供します。
- Step 8 - 新しいスレーブを作成します。
- Step 9 - リストアされるデータベースおよび/またはテーブルのためのリプリケーションポリシーを設定します。
- Step 10 - スレーブの停止場所を決定します。
- Step 10A - mysqlbinlogまたはrepclientを使ってbinlogから正確な停止場所を抽出します。
- Step 11 - Repserverによって提供されるbinlogを読むために、新しいスレーブを開始することでリストアします。
- Step 12 - クリーンアップ
Step 1 - リストアしたいデータベース/テーブルへのwriteを停止します。
リストアされる必要がありbinlogが無効になっているデータベース/テーブルとやり取りをしている全てのスレーブプロセスを停止します。You are doing this so that the database(s)/table(s) do not take any writes while you are restoring it/them. You also do not want any of the changes you make while setting up Point in Time Restoration to replicate to any slaves, so you are setting the sql_log_bin to false. Please note that setting sql_log_bin to false is for the current transaction only, so you should include that before every operation that modifies a table, just to make sure nothing replicates unexpectedly. The RESTORE operations will not replicate so no need to be concerned with them.
Step 2 - Restore Database or Table
Restore a database (or table) from the most recent backup. You are going to use the lastest backup to restore the database(s) and/or table(s) that you need to recover. You can do this one of two ways. The first method is to drop the database (or table) to be restored and write the database (or table) fresh from backup like this:
The second method is to restore to an alternate database (or table) and rename the table(s) to the original after verifying the data.
Verify the data by any means you like then drop the old database and rename the new table(s) over to it.
Step 3 - Extract the Binlog Name and Positions
Extract the binlog file and position from the metadata/binlogs folder on the ftp server. This will indicate the start position from the binlog from the point where the backup was created. This is to be used in Step 5 and Step 7.
On your FTP server run:
Step 4 - Find the Correct Binlog
Find the binlog containing the event you want to recover up to using the show binlog files command from a mysql client pointed to clustrix. To find the correct binlog locate the closest timestamp prior to but not exceeding the event (or time) that you want to restore up to. This makes sure that you get the binlog containing the event you are restoring to. Record this binlog name as it will be used as the stop position in end_logname position in step 5 and the logname in step 10.
To find the timestamps run:
If the event you're looking for was at 2013-01-24 08:50:14, you will want to select the binlog 'foobin.000836' as an ending point, as 036 is the closest timestamp before 8:50 and 837 starts after the event.
Step 5 - Dump Binlog with Repclient
Clustrix ships with two tools to interact with binlogs. Repclient, which is to dump and read the binlogs, and Repserver, which can re-serve these binlogs, in this step you will dump the raw binlog files with repclient. First you are going to create a new directory inside the /clustrix directory and then change directory to the newly created directory, you will then run repclient to extract the correct portion of binlog to replay in Step 6. You are using the start position from Step 3 and the end position from Step 4. 次の構文を使います:
This will take some time to run, depending on the amount of logs which need to be dumped. Wait for the command to exit and return you back to a bash prompt. If it completed successfully, you should see a set of binlog files in your current working directory.
Step 6 - Change Server ID
Change the servers id. The reason for the change in id is so when you replay the events later the cluster will not discard them as in master/master replication. It is critical that the server id also be unique in your infrastructure.
Show the clusters server id and make a note of it so that you can reset it later.
Then change that variable to something unique.
Step 7 - Replay Binlog with Repserver
Start an instance of repserver, the supplied binlog player utility. This will set up a replication master process to serve binlogs back to the cluster. It is a good idea to run repserver in screen, or a separate terminal as the command will need to run continuously.
You should see output like the following:
Step 8 - Create a New Slave
Create a new slave on Clustrix to read from Repserver. Note that the Master_log_file and master_log_pos are from step 3 above.
Step 9 - Setup Replication Policy
IF YOU ARE NOT RESTORING THE ENTIRE CLUSTER TO POINT-IN-TIME: You will need to set up the system.mysql_db_replication_policy and table_replication_policy tables to include ONLY the database(s) and table(s) you want replicated. To do this, first record all values already in these tables (these will need to be reset later). If you are restoring the whole cluster, you can go ahead and skip to Step 10.
RECORD THE OUTPUT FROM THESE TABLES and then drop any data that is in them:
For each database/table that you want restored point_in_time:
Step 10 - Determine Binlog Stop Position
Determine the stop position of the slave. For this step there is no "one size fits all" solution, as the required information location can change depending the type of query that was run, or the type of event you are trying to recover from. The most basic method is to run a join on several tables to get the required information. The information needing to be supplied are binlog name, and the time you want to restore up to. This query will bring you to within 500 transactions prior to the time you specify.
The <timestamp> needs to be in the format of '2013-02-01 14:00', this timestamp is the time you want to recover to. You also need to replace two instances of <binlog_name> with the name of your binlog, in this case 'foobin'. This will give you the the offset, which is required for the next step.
Record the output of the "offset" column. This will be used as your MASTER_LOG_POS in Step 11. In most cases this will give you enough information to do your point in time restoration. MOVE ON TO STEP 11.
Step 10A - Extract the Exact Binlog Stop Position with mysqlbinlog or repclient
This step is optional: If you do not need recovery to the exact transaction please skip to Step 11.
IT IS HIGHLY RECOMMENDED THAT YOU CONTACT CLUSTRIX SUPPORT AND LET THEM PERFORM THE NEXT OPERATION!
If you need to recover to a specific transaction you will need to manually inspect the binlog using mysqlbinlog or repclient. This is a fairly simple task if you are using SBR (statement-based replication), but can be rather tricky for RBR (row-based replication) as any non-DDL events show up in RBR as encoded hex. If the event you are trying to recover from is a DDL (ALTER, DROP, CREATE) this will always be SBR. To locate a specific transaction in an RBR stream that is not a DDL please contact Clustrix Support for assistance. To inspect binlogs use the filename from step 4 in the logname argument, like the example below:
If you are trying to recover from a dropped database named "foobase" you would run a command like the below.
You will see an output like the below:
As the event you are trying to recover from is a "DROP DATABASE foobase" in the example above, you would look for that command like this: "12429583:drop database foobase" and then look for a transaction position before this. So from the above example you want the line that looks like this:
12429376-# at 45116913
The number after the "at" is the number to record:45116913
Below is a condensed code snippet showing the drop database and the line you are looking to extract. Record this information.
Step 11 - Read Binlogs with New Slave
Now you need to replay the binlogs up to the point you specified in the previous step you do this by starting the new slave, and ONLY that slave. The UNTIL command is run with the stop position extracted during in Step 10.
The slave will run up until the transaction before (if LOG_POS is defined properly) the catastrophic ALTER/UPDATE/DELETE found in the binlog.
Step 12 - Clean Up
This last step is the cleanup of all the settings and changes made previously:
- Set mysql_default_db_replication_policy and mysql_default_table_replication policy variables to the values found in step 9
- Stop and drop the slave you created earlier 'foo'.
- Re-start any other slaves which were previously running.
Set the server id back to what it was originally. This was recorded in Step 1.
Re-enable writing to binlogs:
If you put the cluster in to read only mode, enable writes again.
- Go back to the window (or screen) running Repserver and stop that with ctrl-c.