pt-osc使ってみよう
homebrewで入れる
➜ ~ brew install percona-toolkit
==> Installing dependencies for percona-toolkit: openssl, mysql
==> Installing percona-toolkit dependency: openssl
==> Downloading https://homebrew.bintray.com/bottles/openssl-1.0.2k.el_capitan.bottle.tar.gz
######################################################################## 100.0%
==> Pouring openssl-1.0.2k.el_capitan.bottle.tar.gz
==> Using the sandbox
==> Caveats
A CA file has been bootstrapped using certificates from the SystemRoots
keychain. To add additional certificates (e.g. the certificates added in
the System keychain), place .pem files in
/usr/local/etc/openssl/certs
and run
/usr/local/opt/openssl/bin/c_rehash
This formula is keg-only, which means it was not symlinked into /usr/local.
Apple has deprecated use of OpenSSL in favor of its own TLS and crypto libraries
If you need to have this software first in your PATH run:
echo 'export PATH="/usr/local/opt/openssl/bin:$PATH"' >> ~/.zshrc
For compilers to find this software you may need to set:
LDFLAGS: -L/usr/local/opt/openssl/lib
CPPFLAGS: -I/usr/local/opt/openssl/include
For pkg-config to find this software you may need to set:
PKG_CONFIG_PATH: /usr/local/opt/openssl/lib/pkgconfig
==> Summary
🍺 /usr/local/Cellar/openssl/1.0.2k: 1,696 files, 12MB
==> Installing percona-toolkit dependency: mysql
==> Downloading https://homebrew.bintray.com/bottles/mysql-5.7.17.el_capitan.bottle.1.tar.gz
######################################################################## 100.0%
==> Pouring mysql-5.7.17.el_capitan.bottle.1.tar.gz
==> Caveats
We've installed your MySQL database without a root password. To secure it run:
mysql_secure_installation
To connect run:
mysql -uroot
To have launchd start mysql now and restart at login:
brew services start mysql
Or, if you don't want/need a background service you can just run:
mysql.server start
==> Summary
🍺 /usr/local/Cellar/mysql/5.7.17: 321 files, 234.4MB
==> Installing percona-toolkit
==> Downloading https://www.percona.com/downloads/percona-toolkit/2.2.19/tarball/percona-toolkit-2.2.19.tar.gz
######################################################################## 100.0%
==> Downloading https://cpan.metacpan.org/authors/id/M/MI/MICHIELB/DBD-mysql-4.041.tar.gz
######################################################################## 100.0%
==> perl Makefile.PL INSTALL_BASE=/usr/local/Cellar/percona-toolkit/2.2.19_1/libexec
==> make install
==> Downloading https://cpan.metacpan.org/authors/id/M/MA/MAKAMAKA/JSON-2.90.tar.gz
######################################################################## 100.0%
==> perl Makefile.PL INSTALL_BASE=/usr/local/Cellar/percona-toolkit/2.2.19_1/libexec
==> make install
==> perl Makefile.PL INSTALL_BASE=/usr/local/Cellar/percona-toolkit/2.2.19_1
==> make test install
🍺 /usr/local/Cellar/percona-toolkit/2.2.19_1: 124 files, 6.8MB, built in 28 seconds
雑に叩く
➜ ~ pt-online-schema-change
Usage: pt-online-schema-change [OPTIONS] DSN
Errors in command-line arguments:
* A DSN must be specified
* The DSN must specify a database (D) and a table (t)
pt-online-schema-change alters a table's structure without blocking reads or
writes. Specify the database and table in the DSN. Do not use this tool before
reading its documentation and checking your backups carefully. For more
details, please use the --help option, or try 'perldoc
/usr/local/Cellar/percona-toolkit/2.2.19_1/libexec/bin/pt-online-schema-change'
for complete documentation.
はい...
ヘルプ見る
➜ ~ pt-online-schema-change --help
pt-online-schema-change alters a table's structure without blocking reads or
writes. Specify the database and table in the DSN. Do not use this tool before
reading its documentation and checking your backups carefully. For more
details, please use the --help option, or try 'perldoc
/usr/local/Cellar/percona-toolkit/2.2.19_1/libexec/bin/pt-online-schema-change'
for complete documentation.
Usage: pt-online-schema-change [OPTIONS] DSN
Options:
--alter=s The schema modification, without the ALTER
TABLE keywords
--alter-foreign-keys-method=s How to modify foreign keys so they reference
the new table
--[no]analyze-before-swap Execute ANALYZE TABLE on the new table
before swaping with the old one (default yes)
--ask-pass Prompt for a password when connecting to
MySQL
--charset=s -A Default character set
--[no]check-alter Parses the --alter specified and tries to
warn of possible unintended behavior (
default yes)
--check-interval=m Sleep time between checks for --max-lag (
default 1). Optional suffix s=seconds, m=
minutes, h=hours, d=days; if no suffix, s is
used.
--[no]check-plan Check query execution plans for safety (
default yes)
--[no]check-replication-filters Abort if any replication filter is set on
any server (default yes)
--check-slave-lag=s Pause the data copy until this replica's lag
is less than --max-lag
--chunk-index=s Prefer this index for chunking tables
--chunk-index-columns=i Use only this many left-most columns of a --
chunk-index
--chunk-size=z Number of rows to select for each chunk
copied (default 1000)
--chunk-size-limit=f Do not copy chunks this much larger than the
desired chunk size (default 4.0)
--chunk-time=f Adjust the chunk size dynamically so each
data-copy query takes this long to execute (
default 0.5)
--config=A Read this comma-separated list of config
files; if specified, this must be the first
option on the command line
--critical-load=A Examine SHOW GLOBAL STATUS after every
chunk, and abort if the load is too high (
default Threads_running=50)
--database=s -D Connect to this database
--default-engine Remove ENGINE from the new table
--defaults-file=s -F Only read mysql options from the given file
--[no]drop-new-table Drop the new table if copying the original
table fails (default yes)
--[no]drop-old-table Drop the original table after renaming it (
default yes)
--[no]drop-triggers Drop triggers on the old table. --no-drop-
triggers forces --no-drop-old-table (default
yes)
--dry-run Create and alter the new table, but do not
create triggers, copy data, or replace the
original table
--execute Indicate that you have read the
documentation and want to alter the table
--force This options bypasses confirmation in case
of using alter-foreign-keys-method = none ,
which might break foreign key constraints
--help Show help and exit
--host=s -h Connect to host
--max-flow-ctl=f Somewhat similar to --max-lag but for PXC
clusters
--max-lag=m Pause the data copy until all replicas' lag
is less than this value (default 1s).
Optional suffix s=seconds, m=minutes, h=
hours, d=days; if no suffix, s is used.
--max-load=A Examine SHOW GLOBAL STATUS after every
chunk, and pause if any status variables are
higher than their thresholds (default
Threads_running=25)
--new-table-name=s New table name before it is swapped. %T is
replaced with the original table name (
default %T_new)
--null-to-not-null Allows MODIFYing a column that allows NULL
values to one that doesn't allow them
--password=s -p Password to use when connecting
--pid=s Create the given PID file
--plugin=s Perl module file that defines a
pt_online_schema_change_plugin class
--port=i -P Port number to use for connection
--print Print SQL statements to STDOUT
--progress=a Print progress reports to STDERR while
copying rows (default time,30)
--quiet -q Do not print messages to STDOUT (disables --
progress)
--recurse=i Number of levels to recurse in the hierarchy
when discovering replicas
--recursion-method=a Preferred recursion method for discovering
replicas (default processlist,hosts)
--set-vars=A Set the MySQL variables in this comma-
separated list of variable=value pairs
--slave-password=s Sets the password to be used to connect to
the slaves
--slave-user=s Sets the user to be used to connect to the
slaves
--sleep=f How long to sleep (in seconds) after copying
each chunk (default 0)
--socket=s -S Socket file to use for connection
--statistics Print statistics about internal counters
--[no]swap-tables Swap the original table and the new, altered
table (default yes)
--tries=a How many times to try critical operations
--user=s -u User for login if not current user
--version Show version and exit
--[no]version-check Check for the latest version of Percona
Toolkit, MySQL, and other programs (default
yes)
Option types: s=string, i=integer, f=float, h/H/a/A=comma-separated list, d=DSN, z=size, m=time
Rules:
--dry-run and --execute are mutually exclusive.
This tool accepts additional command-line arguments. Refer to the SYNOPSIS and usage information for details.
DSN syntax is key=value[,key=value...] Allowable DSN keys:
KEY COPY MEANING
=== ==== =============================================
A yes Default character set
D yes Database for the old and new table
F yes Only read default options from the given file
P yes Port number to use for connection
S yes Socket file to use for connection
h yes Connect to host
p yes Password to use when connecting
t no Table to alter
u yes User for login if not current user
If the DSN is a bareword, the word is treated as the 'h' key.
Options and values after processing arguments:
--alter (No value)
--alter-foreign-keys-method (No value)
--analyze-before-swap TRUE
--ask-pass FALSE
--charset (No value)
--check-alter TRUE
--check-interval 1
--check-plan TRUE
--check-replication-filters TRUE
--check-slave-lag (No value)
--chunk-index (No value)
--chunk-index-columns (No value)
--chunk-size 1000
--chunk-size-limit 4.0
--chunk-time 0.5
--config /etc/percona-toolkit/percona-toolkit.conf,/etc/percona-toolkit/pt-online-schema-change.conf,/Users/usr0600268/.percona-toolkit.conf,/Users/usr0600268/.pt-online-schema-change.conf
--critical-load Threads_running=50
--database (No value)
--default-engine FALSE
--defaults-file (No value)
--drop-new-table TRUE
--drop-old-table TRUE
--drop-triggers TRUE
--dry-run FALSE
--execute FALSE
--force FALSE
--help TRUE
--host (No value)
--max-flow-ctl (No value)
--max-lag 1
--max-load Threads_running=25
--new-table-name %T_new
--null-to-not-null FALSE
--password (No value)
--pid (No value)
--plugin (No value)
--port (No value)
--print FALSE
--progress time,30
--quiet FALSE
--recurse (No value)
--recursion-method processlist,hosts
--set-vars
--slave-password (No value)
--slave-user (No value)
--sleep 0
--socket (No value)
--statistics FALSE
--swap-tables TRUE
--tries (No value)
--user (No value)
--version FALSE
--version-check TRUE
➜ ~ pt-online-schema-change --alter "ADD COLUMN name VARCHAR(32)" D=test,t=sql_test
Cannot connect to MySQL: Cannot connect to MySQL because the Perl DBI module is not installed or not found. Run 'perl -MDBI' to see the directories that Perl searches for DBI. If DBI is not installed, try:
Debian/Ubuntu apt-get install libdbi-perl
RHEL/CentOS yum install perl-DBI
OpenSolaris pkg install pkg:/SUNWpmdbi
なるほどね
cpanm入ってたので入れる
➜ ~ cpanm DBI DBD::mysql
--> Working on DBI
Fetching http://www.cpan.org/authors/id/T/TI/TIMB/DBI-1.636.tar.gz ... OK
Configuring DBI-1.636 ... OK
Building and testing DBI-1.636 ... OK
Successfully installed DBI-1.636
--> Working on DBD::mysql
Fetching http://www.cpan.org/authors/id/M/MI/MICHIELB/DBD-mysql-4.042.tar.gz ... OK
==> Found dependencies: Devel::CheckLib
--> Working on Devel::CheckLib
Fetching http://www.cpan.org/authors/id/M/MA/MATTN/Devel-CheckLib-1.09.tar.gz ... OK
Configuring Devel-CheckLib-1.09 ... OK
==> Found dependencies: IO::CaptureOutput
--> Working on IO::CaptureOutput
Fetching http://www.cpan.org/authors/id/D/DA/DAGOLDEN/IO-CaptureOutput-1.1104.tar.gz ... OK
Configuring IO-CaptureOutput-1.1104 ... OK
Building IO-CaptureOutput-1.1104 ... OK
Successfully installed IO-CaptureOutput-1.1104
Building Devel-CheckLib-1.09 ... OK
Successfully installed Devel-CheckLib-1.09
Configuring DBD-mysql-4.042 ... OK
==> Found dependencies: Test::Deep
--> Working on Test::Deep
Fetching http://www.cpan.org/authors/id/R/RJ/RJBS/Test-Deep-1.126.tar.gz ... OK
Configuring Test-Deep-1.126 ... OK
Building and testing Test-Deep-1.126 ... OK
Successfully installed Test-Deep-1.126
Building and testing DBD-mysql-4.042 ... OK
Successfully installed DBD-mysql-4.042
5 distributions installed
改めまして
➜ ~ pt-online-schema-change --alter "ADD COLUMN name VARCHAR(32)" D=test,t=sql_test
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Exiting without altering `test`.`sql_test` because neither --dry-run nor --execute was specified. Please read the tool's documentation carefully before using this tool.
知ってた。まずは--dry-runで
➜ ~ pt-online-schema-change --alter "ADD COLUMN name VARCHAR(32)" D=test,t=sql_test --dry-run
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Starting a dry run. `test`.`sql_test` will not be altered. Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table test._sql_test_new OK.
Altering new table...
Altered `test`.`_sql_test_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
Not swapping tables because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
2017-04-06T12:48:43 Dropping new table...
2017-04-06T12:48:43 Dropped new table OK.
Dry run complete. `test`.`sql_test` was not altered.
--executeする
➜ ~ pt-online-schema-change --alter "ADD COLUMN name VARCHAR(32)" D=test,t=sql_test --execute
You do not have the PROCESS privilege at /usr/local/Cellar/percona-toolkit/2.2.19_1/libexec/bin/pt-online-schema-change line 4324.
なるほど。というかユーザー指定してなかったので指定する。rootで
➜ ~ pt-online-schema-change --alter "ADD COLUMN name VARCHAR(32)" D=test,t=sql_test,u=root --execute
No slaves found. See --recursion-method if host PMAC258S.local has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `test`.`sql_test`...
Creating new table...
Created new table test._sql_test_new OK.
Altering new table...
Altered `test`.`_sql_test_new` OK.
2017-04-06T12:50:56 Creating triggers...
2017-04-06T12:50:56 Created triggers OK.
2017-04-06T12:50:56 Copying approximately 2 rows...
2017-04-06T12:50:56 Copied rows OK.
2017-04-06T12:50:56 Analyzing new table...
2017-04-06T12:50:56 Swapping tables...
2017-04-06T12:50:56 Swapped original and new tables OK.
2017-04-06T12:50:56 Dropping old table...
2017-04-06T12:50:56 Dropped old table `test`.`_sql_test_old` OK.
2017-04-06T12:50:56 Dropping triggers...
2017-04-06T12:50:56 Dropped triggers OK.
Successfully altered `test`.`sql_test`.
できた
まとめるとmacでpt-oscを使う場合
インストール方法
実行するとき