Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about what pt-archiver refers to in the Percona toolkit. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

-- 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 options'--files specified by sleep'-- control the transaction size through the option'--limit', before getting the next data and the sleep time specified by the option'--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.

The above is what the pt-archiver refers to in the Percona toolkit shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.