Shulou(Shulou.com)06/01 Report--

Pt-archiver parameter description

Pt-archiver is a component of the Percona-Toolkit toolset and is primarily a tool for archiving and purging MySQL table data. It can archive data to another table or to a file. Pt-archiver does not affect the query performance of OLTP transactions in the process of purging table data. For data archiving, it can be archived to another table on another server or to a file, which can be loaded with LOAD DATA INFILE, which is similar to the incremental deletion of table history data.

Basic description

Pt-archiver [OPTIONS]-- source DSN-- where WHERE

Common options (OPTIONS)

-- analyze

Specifies that the tool performs a 'ANALYZE TABLE' operation on the table after the data is archived. Specify methods such as'--analyze=ds',s for source table and d for destination table, or you can specify them separately.

-- ask-pass

The command line prompts for password input to protect password security, as long as the module perl-TermReadKey is installed.

-- buffer

Specifies that the buffer data is flushed to the file specified by the option'--file' and is flushed on commit.

Only when the transaction commits disables automatic refresh to the file specified by'--file' and refresh the file to disk, which means that the file is refreshed by the operating system block, so some data is implicitly flushed to disk before the transaction commits. The default is to refresh the file to disk after each line operation.

-- bulk-delete

Specifying how a single statement deletes chunk to delete rows in batches implicitly executes the option'--commit-each'.

Use a single DELETE statement to delete the table row corresponding to each chunk. The usual practice is to delete row by row through the primary key. Batch deletion will be greatly improved in speed, but it may be slower if there are complex 'WHERE' conditions.

[no] bulk-delete-limit

Default value: yes

Specify to add the options'--bulk-delete' and'--limit' to the archived statement.

-- bulk-insert

Using the LOAD DATA LOCAL INFILE method, insert rows by inserting chunk in bulk (implicitly specify the options'--bulk-delete' and'--commit-each')

Instead of inserting individually line by line, it is faster than performing INSERT statement inserts on a single line. The rows that need to be inserted in bulk (chunk) are stored by implicitly creating temporary tables, rather than batch inserts directly, and unified data loading is carried out after each chunk is completed in the temporary table. To ensure the security of the data, this option forces the option'--bulk-delete', which effectively ensures that the deletion occurs after the insert is fully successful.

-- channel

Specify which master database data needs to be archived when the master-slave replication environment is multi-source replication, which is suitable for situations where multiple master libraries correspond to one slave library in multi-source replication.

-- charset,-A

Specifies the connection character set.

[no] check-charset

Default value: yes

Specifies that the check ensures that the character set and table character set are the same when connecting to the database.

[no] check-columns

Default value: yes

Specify a check to ensure that the source table specified by option'- source' and the destination table specified by'- dest' have the same fields.

Do not check the sort and field type of the field in the table, just check whether the field exists in both the source table and the destination table. If there are different field differences, the tool reports an error to exit. If you need to disable this check, specify'--no-check-columns'.

-- check-slave-lag

Specifies that the master-slave replication delay is greater than the value specified by the option'- max-lag'. By default, the tool checks all slave libraries, but this option works only on specified slave libraries (via DSN connections).

-- check-interval

Default value: 1s

If the option'--check-slave-lag', is also specified, the time specified by this option is the time the tool pauses when it discovers the master-slave replication delay. Check every 100 lines of operation.

-- columns,-c

Specify the table fields that need to be archived, separated by','(comma) if there are multiple.

-- commit-each

Specifies that rows are committed per fetch and archive, which disables the option'--txn-size'.

After each time the table data is fetched and archived, the transaction commit and refresh option'--files specified by sleep'-- control the transaction size through the option'--limit', before getting the next data and option'--the sleep time specified by file'.

-- host,-h

Specifies the database IP address of the connection.

-- port,-P

Specifies the database Port port for the connection.

-- user,-u

Specifies the database user to connect to.

-- password,-p

Specifies the database user password for the connection.

-- socket,-S

Specifies that a SOCKET file connection is used.

-- databases,-d

Specify the database to connect to

-- source

Specify the tables that need to be archived, which is an option that must be specified and expressed in DSN.

-- dest

Specifies the destination table to be archived to, expressed in DSN.

If this option is not specified, the default is the same table as the option'--source' specifies the source table.

-- where

Specifies that the data to be archived is specified through the WHERE conditional statement, which must be specified. You don't need to add the 'WHERE'' keyword, and if you really don't need a WHERE condition to restrict it, specify'--where 1'.

-- file

Specify the file to which the table data needs to be archived. Use a format like MySQL DATE_FORMAT () to format the naming.

The contents of the file use the same format as the SELECT INTO OUTFILE statement in MySQL, and the file naming options are as follows:

'

% Y: year, 4 digits (Year, numeric, four digits)

% m: month, 2 digits (Month, numeric (01.. 12))

% d: day, 2 digits (Day of the month, numeric (01.. 31))

% H: hours (Hour (00.23))

% I: minutes (Minutes, numeric (00.59))

% s: seconds (Seconds (00.59))

% D: database name (Database name)

% t: table name (Table name)

For example:-- file'/ var/log/archive/%Y-%m-%d-%D.%t'

'

-- output-format

Specify the option'- the format of the output of the contents of the file' file.

By default, this option is not specified as a tab delimiter for the field. If this option is specified,','(comma) is used as the field delimiter and''(double quotation marks) is used to enclose the field. Example usage:'- output-format=dump'.

-- for-update

Specifies that the FOR UPDATE clause is added to the SELECT statement for each archive execution.

-- share-lock

Specifies that the LOCK IN SHARE MODE clause is added to the SELECT statement for each archive execution.

-- header

Specifies that the field name is written as the title on the first line of the file.

-- ignore

Specifies to add the IGNORE option to the INSERT statement.

-- limit

Default value: 1

Specifies the number of rows for each statement to get the table and archive table.

-- local

Specifies that OPTIMIZE and ANALYZE statements are not written to binlog.

-- max-lag

Default value: 1s

Specifies the maximum delay allowed for master-slave replication, in seconds. If the master-slave delay exceeds the specified value after each fetch of row data, the archiving operation is paused and the hibernation time is the value specified by option'--check-interval'. The master-slave delay is checked again after the sleep time is over, which is determined by the 'Seconds_Behind_Master' value of the slave database query. If the master-slave replication delay is always greater than the value specified by this parameter or if replication is stopped from the slave library, the operation will wait until the slave library is restarted and the delay is less than the value specified by this parameter.

-- no-delete

Specifies that table data that has been archived is not deleted.

-- optimize

Specifies that the tool performs a 'OPTIMIZE TABLE' operation on the table after the data is archived. Specify methods such as'--analyze=ds',s for source table and d for destination table, or you can specify them separately.

-- primary-key-only

Specifies that only the primary key field is archived, which is an abbreviation for the option'- columns= primary key'.

If the operation of the tool archive is to perform DELETE cleanup, it is most effective because only one field of the primary key needs to be read without having to read all the fields in the row.

-- progress

Specify how many lines print progress information, print the current time, time spent, and how many lines are archived.

-- purge

Specifies the cleanup operation performed instead of the archive operation. You are allowed to ignore the options'--dest' and'--file', and if you just clear the operation, you can combine the option'--primary-key-only' will be more efficient.

-- quiet,-q

Specifies that the tool executes silently and does not output any execution information.

-- replace

Specify write option'- dest' rewrites the INSERT statement to the REPLACE statement when the target side table is specified.

-- retries

Default value: 1

Specifies the number of retries that the archive operation encountered a deadlock or timeout. When the number of retries exceeds the value specified by this option, the tool will exit with an error.

-- run-time

Specifies how long the tool archive operation needs to run before exiting. The allowed time suffix is s = seconds, m = minutes, h = hours, d = days, if not specified, the default is s.

[no] safe-auto-increment

Default value: yes

Specifies that the row corresponding to the maximum value of the increment column (AUTO_INCREMENT) is not used for archiving.

This option adds an additional WHERE clause when performing an archive cleanup to prevent the tool from deleting the data row with the maximum value of the AUTO_INCREMENT attribute for the single-column ascending field, in order to use the value corresponding to AUTO_INCREMENT after the database restart, but this can cause the row corresponding to the maximum value of the field not to be archived or cleared.

-- set-vars

Default:

Wait_timeout=10000

Innodb_lock_wait_timeout=1

Lock_wait_timeout=60

Specify parameter values when the tool is archived, if multiple are separated by','(comma). Such as'--set-vars=wait_timeout=5000'.

-- skip-foreign-key-checks

Specifies that foreign key checking is disabled using the statement SET FOREIGN_KEY_CHECKS = 0.

-- sleep

Specifies how long the tool needs to sleep when it gets archived data through the SELECT statement, and the default value is no hibernation. Transactions are not committed before hibernation, and the files specified by the option'--file' are not refreshed. If you specify the option'--commit-each', transaction commit and file refresh occur before hibernation.

-- statistics

Specifies that the tool collects and prints time statistics for the operation.

Examples of statistics are as follows:

'

Started at 2008-07-18T07:18:53, ended at 2008-07-18T07:18:53

Source: dumbdb _ dint _ tweetable

SELECT 4

INSERT 4

DELETE 4

Action Count Time Pct

Commit 10 0.1079 88.27

Select 5 0.0047 3.87

Deleting 4 0.0028 2.29

Inserting 4 0.0028 2.28

Other 0 0.0040 3.29

'

-- txn-size

Default: 1

Specifies the number of rows per transaction. If it is 0, the transaction feature is disabled.

-- version

Display the version of the tool and exit.

[no] version-check

Default value: yes

Check the latest versions of Percona Toolkit, MySQL, and other programs.

-- why-quit

Specifies the reason why the tool prints when it exits other than because of the number of rows that have been archived.

This option is handy to use with the option'--run-time' when performing an automatic archive task to determine whether the archive task is completed within a specified time. If the option'--statistics', is also specified, all reasons for exiting will be printed.

DSN option (DSN)

You can use DSN to connect to the database. The DSN option is key=value. No spaces can appear on both sides of the equal sign, and it is case-sensitive. Multiple options are separated by','(comma). The main options are as follows:

A

The library under which the archiving operation is performed, which is equivalent to the USE operation.

A

Specifies the default character set.

B

When the value is true, disable SQL_LOG_BIN, which is equivalent to SQL_LOG_BIN = 0.

D

Specify the database that contains the tables to be archived.

H

Specifies the host to which it is connected.

U

Specifies the connected user.

P

Specify the password required for the connection.

P

Specify the port to connect to.

S

Specifies the connected SOCKET file.

T

Specify the tables to be archived.

I

Specify the index you want to use.

Option usage description

The tool needs to specify at least one of the options-- dest,-- file, or-- purge

Options-ignore and-replace are mutually exclusive

Options-txn-size and-commit-each are mutually exclusive

Options-share-lock and-for-update are mutually exclusive

Analyze and-- optimize are mutually exclusive.

This test is based on the employees table and the new yoon table.

CREATE TABLE `yoon` (

`id`int (11) NOT NULL AUTO_INCREMENT

`vint` int (11) DEFAULT NULL

`v _ string` varchar (50) DEFAULT NULL

`s_ string` char (20) NOT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=80001 DEFAULT CHARSET=utf8

Create a stored procedure i_yoon to insert test data

Delimiter $$

CREATE PROCEDURE i_yoon (IN row_num INT)

BEGIN

DECLARE i INT DEFAULT 0

WHILE I < row_num DO

INSERT INTO yoon (v_int, v_string, s_string)

VALUES

(

Floor (1 + rand () * 1000000)

Substring (

MD5 (RAND ())

one,

Floor (1 + rand () * 20)

),

Substring (MD5 (RAND ()), 1,20)

)

SET I = I + 1

END

WHILE; END$$

Delimiter

Call i_yoon (200000)

Create a test library hank on the target side, with the table structure yoon:

CREATE TABLE `yoon` (

`id`int (11) NOT NULL AUTO_INCREMENT

`vint` int (11) DEFAULT NULL

`v _ string` varchar (50) DEFAULT NULL

`s_ string` char (20) NOT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=80001 DEFAULT CHARSET=utf8

Perform archiving without deleting source data:-- source source side,-- dest: destination side,-- no-delete: do not delete source side data

Pt-archiver-- source hype 127.0.0.1 pt-archiver-- charset=utf8-- where'id'id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.