金沙国际唯一官网网址Percona Toolkit使用教程之复制类工具

slave根据当前的系统时间戳(Perl中的time函数)减去heartbeat表中的记录值来判断主从的延迟情况,pt-heartbeat是用来监测主从延迟的情况的,slave根据当前的系统时间戳(Perl中的time函数)减去heartbeat表中的记录值来判断主从的延迟情况,pt-heartbeat是用来监测主从延迟的情况的,监控mysql复制延迟,测量复制落后主mysql或者主PostgreSQL多少时间

看看各参数的含义

pt-heartbeat,heartbeat

pt-heartbeat是用来监测主从延迟的动静的,威名赫赫,守旧的经过show slave
status\G命令中的Seconds_Behind_Master值来决断主从延迟并不可信。

pt-heartbeat的思绪比较神奇,它在master中插入一条带有当明日子(MySQL中的now()函数)的笔录到心跳表中,然后,该记录会复制到slave中。slave依照当前的系统时间戳(Perl中的time函数)减去heartbeat表中的记录值来判定主从的推迟情形。具体,可参看上边–skew参数的证实。

 

广泛用法:

Master上

需用–update参数

# pt-heartbeat --update -h 192.168.244.10 -u monitor -p monitor123 -D test

其间,–update会每秒更新三回heartbeat表的记录
-D指的是heartbeat表所在的database

-D是–database的缩写,–database后边即能够一直加参数,如–database
test,也可用等号加参数,如–database=test,但是缩写只可以通过-D
test加参数。

# pt-heartbeat --update -h 192.168.244.10 -u monitor -p monitor123 --database test 
# pt-heartbeat --update -h 192.168.244.10 -u monitor -p monitor123 --database=test 

注意:在第一回运维时,需带上 –create-table参数创造heartbeat表并插入第二条记下。也可增进–daemonize参数,让该脚本以往台进度启动。

 

Slave上

可用–monitor参数也许–check参数

–monitor参数是绵绵监测并出口结果

# pt-heartbeat -D test –monitor -h 192.168.244.20 –master-server-id=1
-u monitor -p monitor123

10061.00s [ 167.68s, 33.54s, 11.18s ]
10062.00s [ 335.38s, 67.08s, 22.36s ]
10063.01s [ 503.10s, 100.62s, 33.54s ]
...

–check参数是只监测叁回就淡出了

# pt-heartbeat -D test –check -h 192.168.244.20 –master-server-id=1
-u monitor -p monitor123

10039.00

瞩目:–update,
–monitor和–check三者是排斥的,–daemonize只适用于–update场景

 

探望各参数的含义

–ask-pass

    连接数据库时提示密码

Prompt for a password when connecting to MySQL.

–charset
    short form: -A

    暗中同意字符集(个人认为那么些选项很鸡肋)

short form: -A; type: string
Default character set. If the value is utf8, sets Perl’s binmode on STDOUT to utf8, passes the mysql_enable_utf8 option to DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any other value sets binmode
on STDOUT without the utf8 layer, and runs SET NAMES after connecting to MySQL.

–check

 
 检查从的推迟后脱离,假如在级联复制中,还足以钦定–recurse参数,那时候,会检查评定从库的从库的延期意况。

Check slave delay once and exit. If you also specify --recurse, the tool will try to discover slave’s of the given slave and check and print their lag, too. The hostname or IP and port for each slave is printed before its
delay. --recurse only works with MySQL.

–check-read-only

   检查server是不是是只读的,如若是只读,则会跳过插入动作。

Check if the server has read_only enabled; If it does, the tool skips doing any inserts.

–config

   Read this comma-separated list of config files; if specified, this
must be the first option on the command line.

   将参数写入到参数文件中,

   有以下几点需求小心:

   1> # pt-heartbeat –config pt-heartbeat.conf,而不能够是#
pt-heartbeat –config=pt-heartbeat.conf 

   2> 参数文件中只帮助如下写法

option
option=value

   
option眼前无法带上–,而且option只好是全拼,不可能是缩写,譬如database,不可能缩写为-D

   
具体写法可参照:

    试举一例,如下所示

# cat pt-heartbeat.conf 
host=192.168.244.20
user=monitor
password=monitor123
monitor
database=test
master-server-id=1 

–create-table

   创设heartbeat表假使该表不存在,该表由–database和–table参数来承认。

   当中–table表的概念如下所示:

CREATE TABLE heartbeat (
ts varchar(26) NOT NULL,
server_id int unsigned NOT NULL PRIMARY KEY,
file varchar(255) DEFAULT NULL, -- SHOW MASTER STATUS
position bigint unsigned DEFAULT NULL, -- SHOW MASTER STATUS
relay_master_log_file varchar(255) DEFAULT NULL, -- SHOW SLAVE STATUS
exec_master_log_pos bigint unsigned DEFAULT NULL -- SHOW SLAVE STATUS
);

Create the heartbeat --table if it does not exist.
This option causes the table specified by --database and --table to be created with the following MAGIC_create_heartbeat table definition:
CREATE TABLE heartbeat (
ts varchar(26) NOT NULL,
server_id int unsigned NOT NULL PRIMARY KEY,
file varchar(255) DEFAULT NULL, -- SHOW MASTER STATUS
position bigint unsigned DEFAULT NULL, -- SHOW MASTER STATUS
relay_master_log_file varchar(255) DEFAULT NULL, -- SHOW SLAVE STATUS
exec_master_log_pos bigint unsigned DEFAULT NULL -- SHOW SLAVE STATUS
);
The heartbeat table requires at least one row. If you manually create the heartbeat table, then you must insert a row by doing:
INSERT INTO heartbeat (ts, server_id) VALUES (NOW(), N);
or if using --utc:
INSERT INTO heartbeat (ts, server_id) VALUES (UTC_TIMESTAMP(), N);
where N is the server’s ID; do not use @@server_id because it will replicate and slaves will insert their own server ID instead of the master’s server ID.
This is done automatically by --create-table.
A legacy version of the heartbeat table is still supported:
CREATE TABLE heartbeat (
id int NOT NULL PRIMARY KEY,
ts datetime NOT NULL
);
Legacy tables do not support --update instances on each slave of a multi-slave hierarchy like “master ->slave1 -> slave2”. To manually insert the one required row into a legacy table:
INSERT INTO heartbeat (id, ts) VALUES (1, NOW());
or if using --utc:
INSERT INTO heartbeat (id, ts) VALUES (1, UTC_TIMESTAMP());
The tool automatically detects if the heartbeat table is legacy.

–create-table-engine

   钦命heartbeat表的仓库储存引擎

type: string
Sets the engine to be used for the heartbeat table. The default storage engine is InnoDB as of MySQL 5.5.5.

–daemonize

 
 脚本以守护进程运营,那样尽管脚本施行的极端断开了,脚本也不会告1段落运营。

Fork to the background and detach from the shell. POSIX operating systems only.

–database

   钦定heartbeat表所在的数据库

short form: -D; type: string
The database to use for the connection.

–dbi-driver

   pt-heartbeat不仅能检查测试MySQL之间的心跳延迟意况,还足以检验PG。

   该参数钦点连接使用的驱动,默以为mysql,也可钦定为Pg

default: mysql; type: string
Specify a driver for the connection; mysql and Pg are supported.

–defaults-file

   内定参数文件的任务,必须为相对路线。

short form: -F; type: string
Only read mysql options from the given file. You must give an absolute pathname.

–file

   将流行的–monitor新闻输出到文件中,注意最新,新的消息会覆盖旧的信息。

 
 要是不加该参数,则monitor的音信会从来出口到极限上,该选取常常和–daemonize参数一同利用。

   譬如,

   # pt-heartbeat -D test –monitor -h 192.168.244.20
–master-server-id=1 -u monitor -p monitor123 –file=result

   该命令会在当前目录下生成二个result文件,记录最新的检查评定消息

# cat result 
1376.00s [ 1126.25s, 225.25s, 75.08s ]

type: string
Print latest --monitor output to this file.
When --monitor is given, prints output to the specified file instead of to STDOUT. The file is opened, truncated,and closed every interval, so it will only contain the most recent statistics. Useful when --daemonize
is given.

–frames

 
 总括的时刻窗口,默以为壹m,伍m,一五m,即分别总括一min,五min和15min内的平均延迟情形。

 
 单位可以是s,m,h,d,注意:时间窗口越大,必要缓存的结果更多,对内部存款和储蓄器的损耗也越大。

type: string; default: 1m,5m,15m
Timeframes for averages.
Specifies the timeframes over which to calculate moving averages when --monitor is given. Specify as a comma-separated list of numbers with suffixes. The suffix can be s for seconds, m for minutes, h for hours, or d
for days. The size of the largest frame determines the maximum memory usage, as up to the specified number of per-second samples are kept in memory to calculate the averages. You can specify as many timeframes as
you like.

–help 

Show help and exit.

–host 

   钦点连接的主机,可缩写为-h

short form: -h; type: string
Connect to host.

–[no]insert-heartbeat-row

   官方表明如下:

default: yes
Insert a heartbeat row in the --table if one doesn’t exist.
The heartbeat --table requires a heartbeat row, else there’s nothing to --update, --monitor, or --check! By default, the tool will insert a heartbeat row if one is not already present. You can disable this
feature by specifying --no-insert-heartbeat-row in case the database user does not have INSERT privileges.

    事实上,在推行如下命令时,

    # pt-heartbeat -D test –update -h 192.168.244.10 -u monitor -p
monitor123

    借使,heartbeat表为空,则会自动insert一条记下。

   
但万一钦点了–no-insert-heartbeat-row参数,则不会活动创制,此时,会唤起如下音信:

# pt-heartbeat -D test --update -h 192.168.244.10 -u monitor -p monitor123 --no-insert-heartbeat-row
No row found in heartbeat table for server_id 1.
At least one row must be inserted into the heartbeat table for server_id 1.
Please read the DESCRIPTION section of the pt-heartbeat POD.

   
PS:在测试的长河中,开采合法并未完整的校验这么些参数,纵然传入–no-insert-heartbeat和–insert-heartbeat参数也不会报错,可是传入–1二叁-insert-heartbeat-ro,会报错“Unknown
option: 123-insert-heartbeat-ro”。

default: yes
Insert a heartbeat row in the --table if one doesn’t exist.
The heartbeat --table requires a heartbeat row, else there’s nothing to --update, --monitor, or --check! By default, the tool will insert a heartbeat row if one is not already present. You can disable this
feature by specifying --no-insert-heartbeat-row in case the database user does not have INSERT privileges.

–interval

   update和check heartbeat表的效用,暗中同意是一s。

type: float; default: 1.0
How often to update or check the heartbeat --table. Updates and checks begin on the first whole second then repeat every --interval seconds for --update and every --interval plus --skew seconds for
--monitor.
For example, if at 00:00.4 an --update instance is started at 0.5 second intervals, the first update happens at 00:01.0, the next at 00:01.5, etc. If at 00:10.7 a --monitor instance is started at 0.05 second intervals with
the default 0.5 second --skew, then the first check happens at 00:11.5 (00:11.0 + 0.5) which will be --skew seconds after the last update which, because the instances are checking at synchronized intervals, happened at
00:11.0.
The tool waits for and begins on the first whole second just to make the interval calculations simpler. Therefore,the tool could wait up to 1 second before updating or checking.
The minimum (fastest) interval is 0.01, and the maximum precision is two decimal places, so 0.015 will be rounded to 0.02.
If a legacy heartbeat table (see --create-table) is used, then the maximum precision is 1s because the ts column is type datetime.

–log

   在脚本以守护进度试行时,将结果输出到log钦定的公文中。

type: string
Print all output to this file when daemonized.

–master-server-id

 
 指定master的server_id,在检查评定从的延期时,必须钦赐该参数,否则会报如下错误:

The --master-server-id option must be specified because the heartbeat table `test`.`heartbeat` uses the server_id column for --update or --check but the server's master could not be automatically determined.

type: string
Calculate delay from this master server ID for --monitor or --check. If not given, pt-heartbeat attempts to connect to the server’s master and determine its server id.

–monitor

   持续的检查实验并出口从的延迟意况

   在那之中,检查实验并出口的频率有–interval参数决定,默感觉一s

   注意:与–check的界别在于:

      一> –monitor是绵绵输出的,而–check是检验3回即退出。

      贰> –monitor可与–file参数搭配,而–check与–file参数搭配无效。

Monitor slave delay continuously.
Specifies that pt-heartbeat should check the slave’s delay every second and report to STDOUT (or if --file is given, to the file instead). The output is the current delay followed by moving averages over the timeframe
given in --frames. For example,
5s [ 0.25s, 0.05s, 0.02s ]

–password

  钦赐登陆的密码,缩写为-p

short form: -p; type: string
Password to use when connecting. If password contains commas they must be escaped with a backslash:
“exam,ple”

–pid

  创建pid文件

type: string
Create the given PID file. The tool won’t start if the PID file already exists and the PID it contains is different than the current PID. However, if the PID file exists and the PID it contains is no longer running, the tool will
overwrite the PID file with the current PID. The PID file is removed automatically when the tool exits.

–port

  钦命登入的端口,缩写为-P

short form: -P; type: int
Port number to use for connection.

–print-master-server-id

  同时输出主的server_id,在–monitor情状下,私下认可输出为

1272.00s [ 21.20s,  4.24s,  1.41s ]

 假若钦定了该参数,则输出为

1272.00s [ 21.20s,  4.24s,  1.41s ] 1

Print the auto-detected or given --master-server-id. If --check or --monitor is specified, specifying this option will print the auto-detected or given --master-server-id at the end of each line.

–recurse

 在–check格局下,用于检验级联复制中从的延迟情形。在这之中,–recurse用于钦赐级联的层级。

type: int
Check slaves recursively to this depth in --check mode.
Try to discover slave servers recursively, to the specified depth. After discovering servers, run the check on each one of them and print the hostname (if possible), followed by the slave delay.
This currently works only with MySQL. See --recursion-method.

–recursion-method

  在级联复制中,找到slave的不二等秘书籍。有show processlist和show slave
hosts三种。

type: array; default: processlist,hosts
Preferred recursion method used to find slaves.
Possible methods are:
METHOD USES
=========== ==================
processlist SHOW PROCESSLIST
hosts SHOW SLAVE HOSTS
none Do not find slaves
The processlist method is preferred because SHOW SLAVE HOSTS is not reliable. However, the hosts method is required if the server uses a non-standard port (not 3306). Usually pt-heartbeat does the right thing and finds
the slaves, but you may give a preferred method and it will be used first. If it doesn’t find any slaves, the other methods will be tried.

–replace

 
在–update方式下,暗中同意是利用update操作进行记录的创新,但奇迹你不太承认heartbeat表中是不是任何笔录时,此时可选取replace操作。

  注意:要是是通过update实行记录的翻新,借使在剧本运维的进度中,truncate
heartbeat表,脚本并不会相当退出,然而heartbeat表也有不会变动新的记录。

 
但假使是经过replace格局实行记录的创新,则正是是在地点那种处境下,heartbeat表依旧会生成新的笔录。个人认为通过replace操作实行记录的创新更可信赖。

Use REPLACE instead of UPDATE for –update.
When running in --update mode, use REPLACE instead of UPDATE to set the heartbeat table’s timestamp.The REPLACE statement is a MySQL extension to SQL. This option is useful when you don’t know whether
the table contains any rows or not. It must be used in conjunction with –update.

–run-time

  内定脚本运营的时刻,无论是针对–update操作依然–monitor操作均实用。

type: time
Time to run before exiting.

–sentinel

  “哨兵”,借使钦定的文件存在则提出,默感觉/tmp/pt-heartbeat-sentinel

type: string; default: /tmp/pt-heartbeat-sentinel
Exit if this file exists.

 
经测试,尽管未有带上–sentinel参数,假诺/tmp/pt-heartbeat-sentinel文件存在,则脚本1推行时就直接退出。

  –sentinel功能在于自定义监控文件。

 
譬如在实践如下命令时, /root/1二3文书并不存在,则该脚本会继续运转,在本子运营的历程中,制造该公文,则脚本会立刻退出。

  # pt-heartbeat -D test –update -h 192.168.244.10 -u monitor -p
monitor123  –sentinel=/root/123

–slave-user

   设置连接slave的用户

type: string
Sets the user to be used to connect to the slaves. This parameter allows you to have a different user with less privileges on the slaves but that user must exist on all slaves.

–slave-password

    设置连接slave的用户密码

type: string
Sets the password to be used to connect to the slaves. It can be used with –slave-user and the password for the user must be the same on all slaves.

 –set-vars

    设置脚本在与MySQL交互进程时的对话变量,但就好像并从未什么样用

type: Array
Set the MySQL variables in this comma-separated list of variable=value pairs.
By default, the tool sets:
wait_timeout=10000
Variables specified on the command line override these defaults. For example, specifying --set-vars wait_timeout=500 overrides the defaultvalue of 10000.
The tool prints a warning and continues if a variable cannot be set.

–skew

  钦定check相对于update的延迟时间。默感觉0.5秒

 
即–update更新二次后,–check会在0.5秒后检查本次立异所对应的基本延迟情状。

 
也许有人会相比较好奇,脚本是何许明白记录是曾几何时更新的,实际上,每一趟–update的时刻都以秒的整点值,譬如,个中三回记录的值为“201陆-0九-二伍T壹三:0四:06.003130”。然后,0.五s后,脚本获取slave上的系统时间,然后减去heartbeat中记录值,来作为主导延迟的时光。那将要求,主从上的系统时间须求保持一致,不然获得的结果就平昔不参考价值。

  下边,可看看源码落成,这一个是漫天脚本的着力逻辑。

         my ($ts, $hostname, $server_id) = $sth->fetchrow_array();
         my $now = time;
         PTDEBUG && _d("Heartbeat from server", $server_id, "\n",
            " now:", ts($now, $utc), "\n",
            "  ts:", $ts, "\n",
            "skew:", $skew);
         my $delay = $now - unix_timestamp($ts, $utc) - $skew;
         PTDEBUG && _d('Delay', sprintf('%.6f', $delay), 'on', $hostname);

         # Because we adjust for skew, if the ts are less than skew seconds
         # apart (i.e. replication is very fast) then delay will be negative. 
         # So it's effectively 0 seconds of lag.
         $delay = 0.00 if $delay < 0;

type: float; default: 0.5
How long to delay checks.
The default is to delay checks one half second. Since the update happens as soon as possible after the beginning of the second on the master, this allows one half second of replication delay before reporting that the slave lags
the master by one second. If your clocks are not completely accurate or there is some other reason you’d like to delay the slave more or less, you can tweak this value. Try setting the PTDEBUG environment variable to see
the effect this has.

–socket

short form: -S; type: string
Socket file to use for connection.

–table

  钦赐心跳表的名字,默感到heartbeat

type: string; default: heartbeat
The table to use for the heartbeat.
Don’t specify database.table; use --database to specify the database.
See --create-table.

–update

  更新master中heartbeat表的记录

Update a master’s heartbeat.

–user

  内定连接的用户

short form: -u; type: string
User for login if not current user.

–utc

 
忽略系统时区,而利用UTC。假设要选取该选项,则–update,–monitor,–check中必须同时选用。

Ignore system time zones and use only UTC. By default pt-heartbeat does not check or adjust for different system or MySQL time zones which can cause the tool to compute the lag incorrectly. Specifying this option is
a good idea because it ensures that the tool works correctly regardless of time zones.
If used, this option must be used for all pt-heartbeat instances: --update, --monitor, --check, etc.
You should probably set the option in a --config file. Mixing this option with pt-heartbeat instances not using this option will cause false-positive lag readings due to different time zones (unless all your systems are
set to use UTC, in which case this option isn’t required).

–version

  打字与印刷版本音讯

–[no]version-check

  检查pt,连接的MySQL Server,Perl以及DBD::mysql的版本消息。

  并且打字与印刷那几个软件特定版本的标题

Check for the latest version of Percona Toolkit, MySQL, and other programs.
84 Chapter 2. Tools
Percona Toolkit Documentation, Release 2.2.19
This is a standard “check for updates automatically” feature, with two additional features. First, the tool checks the version of other programs on the local system in addition to its own version. For example, it checks the
version of every MySQL server it connects to, Perl, and the Perl module DBD::mysql. Second, it checks for and warns about versions with known problems. For example, MySQL 5.5.25 had a critical bug and was re-released
as 5.5.25a.
Any updates or known problems are printed to STDOUT before the tool’s normal output. This feature should never interfere with the normal operation of the tool.

 

 

 

   

 

pt-heartbeat是用来监测主从延迟的处境的,无人不知,古板的经过show slave
status\G命令中的Seconds_Behind_Master值来判断主从延…

监察mysql复制延迟

–port

在意:使用的时候选用作业地峰的时候运维,因为运营的时候会导致表的片段记录锁定。使用–max-load来钦点最大的负载情况,假诺到达丰硕负载这么些暂停止运输营。要是发掘有不均等的多寡,能够使用pt-table-sync工具来修补。

–defaults-file

范例叁:监控复制在slave上的倒退程度(监控一遍退出):

Master上

  master_cnt    int              NULL,

 
经测试,尽管未有带上–sentinel参数,纵然/tmp/pt-heartbeat-sentinel文件存在,则脚本壹试行时就径直退出。

1.  pt-heartbeat

  –sentinel功效在于自定义监察和控制文件。

  upper_boundary text            NULL,

–password

表率壹:同步三.13伍的test库的aaa表到1玖二.16八.三.玖二,在施行此前能够用–execute参数换到–print来查看会改换什么东西,前面那多少个主机必须是master,不然会报错推出。

# pt-heartbeat -D test --update -h 192.168.244.10 -u monitor -p monitor123 --no-insert-heartbeat-row
No row found in heartbeat table for server_id 1.
At least one row must be inserted into the heartbeat table for server_id 1.
Please read the DESCRIPTION section of the pt-heartbeat POD.

pt-heartbeat [OPTION…] [DSN] –update|–monitor|–check|–stop

 
即–update更新一次后,–check会在0.伍秒后检查本次革新所对应的主旨延迟意况。

05-23T16:19:29  0  0        1  1  0  0.017 test.bbb

option
option=value

轨范三:只同步钦定的表

type: string; default: /tmp/pt-heartbeat-sentinel
Exit if this file exists.

) ENGINE=InnoDB;

      一> –monitor是连连输出的,而–check是质量评定三遍即退出。

假诺不加–run-time参数会直接进行。

   pt-heartbeat不仅能检查评定MySQL之间的心跳延迟情形,仍是能够检查评定PG。

CREATE TABLE checksums (

Slave上

运营结果如下:

type: string; default: 1m,5m,15m
Timeframes for averages.
Specifies the timeframes over which to calculate moving averages when --monitor is given. Specify as a comma-separated list of numbers with suffixes. The suffix can be s for seconds, m for minutes, h for hours, or d
for days. The size of the largest frame determines the maximum memory usage, as up to the specified number of per-second samples are kept in memory to calculate the averages. You can specify as many timeframes as
you like.

pt-table-checksum  –nocheck-replication-filters –databases=test
–replicate=test.checksums –replicate-check-only
–lock-wait-timeout=120  –host=192.168.3.135  –port 3306  –user=root
[email protected]

 
 检查从的延迟后退出,假若在级联复制中,还能钦命–recurse参数,那时候,会检查评定从库的从库的推移情形。

pt-table-sync –execute –replicate test.checksums –user=root
[email protected] 
–sync-to-master h=192.168.3.92,D=test,t=aaa

 
恐怕有人会比较好奇,脚本是如何精通记录是几时更新的,实际上,每便–update的时间都是秒的整点值,譬如,个中2次记录的值为“2016-0九-贰五T一三:0四:0六.003130”。然后,0.伍s后,脚本获取slave上的系统时间,然后减去heartbeat中记录值,来作为着力延迟的光阴。那将必要,主从上的系统时间要求保持1致,不然得到的结果就不曾参考价值。

参数表达:第3回运营的时候须求增加–create-replicate-table参数,如若不加那一个就需求手工业运维增加表结构的SQL,表结构SQL如下:

–host 

0.00s [  0.00s,  0.00s,  0.00s ]

–update

  1. pt-slave-restart

pt-heartbeat是用来监测主从延迟的情事的,家弦户诵,守旧的通过show slave
status\G命令中的Seconds_Behind_Master值来推断主从延迟并不可相信。

监视mysql复制错误,并尝试重启mysql复制当复制截至的时候

   
option前面不能够带上–,而且option只好是全拼,不能是缩写,譬如database,不能够缩写为-D

percona-toolkit使用教程之复制类工具

–daemonize

用法介绍:

   注意:与–check的界别在于:

pt-slave-delay –user=root
[email protected]
–delay 1m –run-time 10m –host=192.168.3.92

  注意:借使是透过update举办记录的换代,假设在本子运维的进度中,truncate
heartbeat表,脚本并不会11分退出,可是heartbeat表也有不会变动新的笔录。

 

  在级联复制中,找到slave的办法。有show processlist和show slave
hosts三种。

选择示例:

–create-table

原理:总是在主上试行多少的改换,再一起到从上,不会平昔退换成从的数据,在主上实行改动是基于主上未来的数量,不会改换主上的数目。注意采纳此前先备份你的数码,防止变成数据的丢失.实践execute在此之前最棒先换来–print或–dry-run查看一下会改造哪些数据。

   在脚本以守护进度试行时,将结果输出到log钦命的文本中。

范例五:依据pt-table-checksum使从的多少和主的多寡一致

type: string
Sets the password to be used to connect to the slaves. It can be used with –slave-user and the password for the user must be the same on all slaves.

        榜样二:监视1玖二.16八.三.9贰的从,跳过错误代码为十6贰的失实。

–run-time

pt-slave-delay –user=root
[email protected]
–delay 1m –interval 15s –run-time 10m –host=192.168.3.92

  钦命check绝对于update的延迟时间。默感觉0.5秒

原理:连接mysql主服务器并搜求其具备的从,然后打字与印刷出全部从服务器的层级关系。

CREATE TABLE heartbeat (
ts varchar(26) NOT NULL,
server_id int unsigned NOT NULL PRIMARY KEY,
file varchar(255) DEFAULT NULL, -- SHOW MASTER STATUS
position bigint unsigned DEFAULT NULL, -- SHOW MASTER STATUS
relay_master_log_file varchar(255) DEFAULT NULL, -- SHOW SLAVE STATUS
exec_master_log_pos bigint unsigned DEFAULT NULL -- SHOW SLAVE STATUS
);

Create the heartbeat --table if it does not exist.
This option causes the table specified by --database and --table to be created with the following MAGIC_create_heartbeat table definition:
CREATE TABLE heartbeat (
ts varchar(26) NOT NULL,
server_id int unsigned NOT NULL PRIMARY KEY,
file varchar(255) DEFAULT NULL, -- SHOW MASTER STATUS
position bigint unsigned DEFAULT NULL, -- SHOW MASTER STATUS
relay_master_log_file varchar(255) DEFAULT NULL, -- SHOW SLAVE STATUS
exec_master_log_pos bigint unsigned DEFAULT NULL -- SHOW SLAVE STATUS
);
The heartbeat table requires at least one row. If you manually create the heartbeat table, then you must insert a row by doing:
INSERT INTO heartbeat (ts, server_id) VALUES (NOW(), N);
or if using --utc:
INSERT INTO heartbeat (ts, server_id) VALUES (UTC_TIMESTAMP(), N);
where N is the server’s ID; do not use @@server_id because it will replicate and slaves will insert their own server ID instead of the master’s server ID.
This is done automatically by --create-table.
A legacy version of the heartbeat table is still supported:
CREATE TABLE heartbeat (
id int NOT NULL PRIMARY KEY,
ts datetime NOT NULL
);
Legacy tables do not support --update instances on each slave of a multi-slave hierarchy like “master ->slave1 -> slave2”. To manually insert the one required row into a legacy table:
INSERT INTO heartbeat (id, ts) VALUES (1, NOW());
or if using --utc:
INSERT INTO heartbeat (id, ts) VALUES (1, UTC_TIMESTAMP());
The tool automatically detects if the heartbeat table is legacy.

作用介绍:

–slave-user

test.aaa 1 1 1 

Check slave delay once and exit. If you also specify --recurse, the tool will try to discover slave’s of the given slave and check and print their lag, too. The hostname or IP and port for each slave is printed before its
delay. --recurse only works with MySQL.

pt-slave-find –user=root
[email protected]
–host=192.168.3.135

–check参数是只监测二遍就淡出了

Differences on localhost.localdomain

–[no]version-check

  PRIMARY KEY (db, tbl, chunk),

short form: -A; type: string
Default character set. If the value is utf8, sets Perl’s binmode on STDOUT to utf8, passes the mysql_enable_utf8 option to DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any other value sets binmode
on STDOUT without the utf8 layer, and runs SET NAMES after connecting to MySQL.

从结果可以看出,只彰显了五个不一同的表。

type: Array
Set the MySQL variables in this comma-separated list of variable=value pairs.
By default, the tool sets:
wait_timeout=10000
Variables specified on the command line override these defaults. For example, specifying --set-vars wait_timeout=500 overrides the defaultvalue of 10000.
The tool prints a warning and continues if a variable cannot be set.

  server_id        int unsigned NOT NULL PRIMARY KEY,

Show help and exit.

 

short form: -F; type: string
Only read mysql options from the given file. You must give an absolute pathname.

  this_crc      char(40)    NOT NULL,

type: string; default: heartbeat
The table to use for the heartbeat.
Don’t specify database.table; use --database to specify the database.
See --create-table.

范例1:相比较test数据库同步是还是不是1致,结果显示全体的表。

   # pt-heartbeat -D test –monitor -h 192.168.244.20
–master-server-id=1 -u monitor -p monitor123 –file=result

2012-05-20T16:35:35 slave stopped at master position
mysql-bin.000032/4407264

 

  this_cnt      int          NOT NULL,

# cat pt-heartbeat.conf 
host=192.168.244.20
user=monitor
password=monitor123
monitor
database=test
master-server-id=1 

反省mysql复制一致性

Update a master’s heartbeat.

pt-table-sync [OPTION…] DSN [DSN…]

–monitor参数是无时不刻监测并出口结果

用法介绍:

    事实上,在执行如下命令时,

功用介绍:

  钦点登六的密码,缩写为-p

pt-heartbeat -D test –update –user=root
[email protected]
-h192.168.3.135 –create-table –daemonize

   update和check heartbeat表的频率,默许是1s。

  file            varchar(255) DEFAULT NULL,    — SHOW MASTER STATUS

    默许字符集(个人认为这些选项很鸡肋)

2012-05-20T16:34:50 STOP SLAVE until 2012-05-20T16:35:50 at master
position mysql-bin.000032/4392054

–master-server-id

);

  检查pt,连接的MySQL Server,Perl以及DBD::mysql的版本消息。

2012-05-20T16:35:05 slave stopped at master position
mysql-bin.000032/4397124

–log

  chunk_time    float            NULL,

–recurse

安装从服务器落后于主服务器指定期间。

1272.00s [ 21.20s,  4.24s,  1.41s ] 1

Print the auto-detected or given --master-server-id. If --check or --monitor is specified, specifying this option will print the auto-detected or given --master-server-id at the end of each line.

UPDATE `test`.`checksums` SET chunk_time = ‘0.000563’, master_crc
= ‘31012777’, master_cnt = ‘4’ WHERE db = ‘test’ AND tbl = ‘zhang’ AND
chunk = ‘1’

  打印版本音讯

pt-table-sync –execute –sync-to-master –user=root
[email protected] 
h=192.168.3.92,D=test,t=aaa

–charset
    short form: -A

pt-slave-restart –user=root
[email protected]
–host=192.168.3.92 –error-numbers=1062

   

REPLACE INTO `test`.`checksums` (db, tbl, chunk, chunk_index,
lower_boundary, upper_boundary, this_cnt, this_crc) SELECT ‘test’,
‘zhang’, ‘1’, NULL, NULL, NULL, COUNT(*) AS cnt,
COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS(‘#’, `id`,
`name`, CONCAT(ISNULL(`name`)))) AS UNSIGNED)), 10, 16)), 0) AS crc
FROM `test`.`zhang` /*checksum table*/;

  # pt-heartbeat -D test –update -h 192.168.244.10 -u monitor -p
monitor123  –sentinel=/root/123

pt-slave-restart [OPTION…] [DSN]

   当中–table表的概念如下所示:

  db            char(64)    NOT NULL,

10039.00

pt-heartbeat -D test –check –user=root
[email protected]
-h192.168.3.92 –dbi-driver Pg

    试举壹例,如下所示

  1. pt-slave-find

大规模用法:

规律:通过运行和终止复制sql线程来设置从落后于主内定时间。暗许是基于从上relay日志的二进制日志的职责来推断,由此无需连接到主服务器,假使IO进程不落后主服务器太多以来,那些检查办法专门的学业很好,假如网络通畅的话,一般IO线程落后主平时都是纳秒等级。一般是通过–delay
and
–delay”+”–interval来调节。–interval是钦命检查是还是不是运转或许终止从上sql线程的频仍度,默许的是一分钟检查2遍。