search

danielcheng007 / tungsten-replicator

Automatically exported from code.google.com/p/tungsten-replicator
0 stars 0 forks source link

Add a script to list binary log contents from the point of a given sequence number #694

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
1. To which tool/application/daemon will this feature apply?

tungsten-replicator/scripts/mysqlbinlog_from_seqno.sh

2. Describe the feature in general

The script will get the host and binlog position for the given sequence number. 
It will then print the output of `mysqlbinlog -R -t --base64-output=DECODE-ROWS 
--verbose` from the source host.

3. Describe the feature interface

mysqlbinlog_from_seqno.sh --seqno=##

4. Give an idea (if applicable) of a possible implementation

5. Describe pros and cons of this feature.

5a. Why the world will be a better place with this feature.

5b. What hardship will the human race have to endure if this feature is
implemented.

6. Notes

Original issue reported on code.google.com by jeff.m...@continuent.com on 4 Sep 2013 at 2:05

GoogleCodeExporter commented 9 years ago

Original comment by jeff.m...@continuent.com on 4 Sep 2013 at 2:51

GoogleCodeExporter commented 9 years ago 
This script gives a listing of the binary log that starts immediately AFTER the 
statement we are looking for.
The binlog and position mentioned in THL refers to the END of the statement. It 
is the point that was reached in the binary log when we wrote the event to the 
THL, not the start of it.

In order to give a correct reading, the script should:
1) take the binary log and position of seqno -1 as start
2) add the option --stop-position for the current seqno binary log position.

For example, if we want the listing to seqno 45213:

$ thl list -low 45212 -high 45213
SEQ# = 45212 / FRAG# = 0 (last frag)
- TIME = 2013-09-04 08:29:24.0
- EPOCH# = 0
- EVENTID = mysql-bin.000003:0000000011364566;0
- SOURCEID = host1
- METADATA = [mysql_server_id=10;dbms_type=mysql;service=cookbook;shard=host1]
- TYPE = com.continuent.tungsten.replicator.event.ReplDBMSEvent
- OPTIONS = [##charset = ISO8859_1, autocommit = 1, sql_auto_is_null = 0, 
foreign_key_checks = 1, unique_checks = 1, sql_mode = 'STRICT_TRANS_TABLES', 
character_set_client = 8, collation_connection = 8, collation_server = 8]
- SCHEMA = host1
- SQL(0) = delete from tbl3 where k1 = 141 and k2 = 59000
SEQ# = 45213 / FRAG# = 0 (last frag)
- TIME = 2013-09-04 08:29:24.0
- EPOCH# = 0
- EVENTID = mysql-bin.000003:0000000011364813;0
- SOURCEID = host1
- METADATA = [mysql_server_id=10;dbms_type=mysql;service=cookbook;shard=host1]
- TYPE = com.continuent.tungsten.replicator.event.ReplDBMSEvent
- OPTIONS = [##charset = ISO8859_1, autocommit = 1, sql_auto_is_null = 0, 
foreign_key_checks = 1, unique_checks = 1, sql_mode = 'STRICT_TRANS_TABLES', 
character_set_client = 8, collation_connection = 8, collation_server = 8]
- SCHEMA = host1
- SQL(0) = delete from tbl3 where k1 = 31 and k2 = 5000

mysqlbinlog --defaults-file=$CONTINUENT_ROOT/share/.my.cookbook.cnf --port=3306 
--base64-output=DECODE-ROWS --verbose -R -t -hhost1 
--start-position=0000000011364566 mysql-bin.000003 
--stop-position=0000000011364813
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 11364566
#700101  1:00:00 server id 10  end_log_pos 0    Rotate to mysql-bin.000003  pos: 
11364566
# at 11364566
#130904  8:23:31 server id 10  end_log_pos 0    Start: binlog v 4, server v 
5.5.31-log created 130904  8:23:31
# at 11364566
#130904  8:29:24 server id 10  end_log_pos 11364635 
    Query   thread_id=267   exec_time=0 error_code=0
SET TIMESTAMP=1378276164/*!*/;
SET @@session.pseudo_thread_id=267/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, 
@@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=2097152/*!*/;
SET @@session.auto_increment_increment=1, 
@@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET 
@@session.character_set_client=8,@@session.collation_connection=8,@@session.coll
ation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 11364635
#130904  8:29:24 server id 10  end_log_pos 11364743 
    Query   thread_id=267   exec_time=0 error_code=0
use `host1`/*!*/;
SET TIMESTAMP=1378276164/*!*/;
delete from tbl3 where k1 = 31 and k2 = 5000
/*!*/;
# at 11364743
#130904  8:29:24 server id 10  end_log_pos 11364813 
    Query   thread_id=267   exec_time=0 error_code=0
SET TIMESTAMP=1378276164/*!*/;
COMMIT
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

You will see that the binlog listing contains the event from the second seqno, 
but not the first one.

Original comment by g.maxia on 4 Sep 2013 at 6:52

GoogleCodeExporter commented 9 years ago 
There won't be a 2.1.3.

Original comment by linas.vi...@continuent.com on 17 Sep 2013 at 10:13

GoogleCodeExporter commented 9 years ago 
tungsten_read_master_events.sh --seqno [--host] [--service]
tungsten_read_master_events.sh --low --high [--host] [--service]

Original comment by jeff.m...@continuent.com on 17 Sep 2013 at 4:44

GoogleCodeExporter commented 9 years ago

Original comment by jeff.m...@continuent.com on 18 Sep 2013 at 1:50

GoogleCodeExporter commented 9 years ago 
when using a fan-in topology, the argument " --source hostName" generates an 
error.
Let's say that I run the command from node1, where service 'alpha' is 
installed. The command runs without requiring --service because there is only 
one service in this node. 
If I run the same command with "--source host2", it fails, because the script 
attempts running thl for service 'alpha', instead of detecting which services 
may be installed there. 

If I add "--service bravo", then  I get
ERROR >> The file 
/home/tungsten/installs/alternate_cookbook/share/.my.default.cnf does not exist

Original comment by g.maxia on 18 Dec 2013 at 5:42

GoogleCodeExporter commented 9 years ago 
Validation for the --service option has been added. It should catch the above 
scenarios and when no service is specified on a node with multiple services.

Original comment by jeff.m...@continuent.com on 19 Dec 2013 at 4:37

GoogleCodeExporter commented 9 years ago 
While the tool works quite well in master/slave deployments, it still has 
issues when multi-master is involved

See for example:

multi_trepctl
| host  | servicename | role   | state  | appliedlastseqno | appliedlatency |
| host1 | alpha       | master | ONLINE |               66 |          0.096 |
| host2 | bravo       | master | ONLINE |               66 |          0.747 |
| host3 | charlie     | master | ONLINE |               66 |          0.417 |
| host4 | alpha       | slave  | ONLINE |               66 |          0.184 |
| host4 | bravo       | slave  | ONLINE |               66 |          0.833 |
| host4 | charlie     | slave  | ONLINE |               66 |          0.472 |

(Running all commands from host1)

tungsten_read_master_events --after 63 --source host1  (OK: gives right output)

tungsten_read_master_events --after 63 --source host2
ERROR >> The alpha service was not found in the replicator at 
host2:/home/tungsten/installs/cookbook
(this should have found service bravo, which is the master in host2)

tungsten_read_master_events --after 63 --source host2 -service bravo
ERROR >> The alpha service was not found in the replicator at 
host2:/home/tungsten/installs/cookbook

tungsten_read_master_events --after 63 --source host4  
This works *by chance*, because it runs the search for alpha service, which 
exists in both hosts. Instead, it should have failed, demanding that I specify 
a service.

./cookbook/tungsten_read_master_events --after 63 --source host4  -service 
charlie
This gives the wrong output: since I am running it from host1, it will query 
service 'alpha'

tungsten_read_master_events --after 63 --source host4  -service charlie -v | 
grep mysqlbinlog
DEBUG >> Execute `mysqlbinlog 
--defaults-file=/home/tungsten/installs/cookbook/share/.my.alpha.cnf 
--port=17100 --base64-output=DECODE-ROWS --verbose -R -t -hhost1 
--start-position=0000000000014073 mysql-bin.000002`

Original comment by g.maxia on 20 Dec 2013 at 9:03

GoogleCodeExporter commented 9 years ago 
Giuseppe and I agreed to move the goal posts on this one. I've removed the 
--source argument for now. The command should be run directly on the host where 
the THL events you are interested in are stored.

A followup issue will be created to look at adding --source back to this script.

Original comment by jeff.m...@continuent.com on 20 Dec 2013 at 2:40

GoogleCodeExporter commented 9 years ago 
with the --source removed, it is working without errors.

Note for documentation: amend the list of available options

Original comment by g.maxia on 20 Dec 2013 at 11:59

GoogleCodeExporter commented 9 years ago

Original comment by linas.vi...@continuent.com on 23 Dec 2013 at 9:51

GoogleCodeExporter commented 9 years ago

Original comment by linas.vi...@continuent.com on 23 Dec 2013 at 10:22

GoogleCodeExporter commented 9 years ago 
This has been documented here: 

http://docs.continuent.com/tungsten-replicator-2.2/cmdline-tools-tungsten_read_m
aster_events.html

Original comment by mc.br...@continuent.com on 23 Dec 2013 at 11:48