MYSQL/MARIADB chema changes with pt-online-tools

MySQL and MARIADB cheet sheet.md

Percona Toolkit

pt-online-schema-change

Add Column:

pt-online-schema-change --no-check-replication-filters --execute --alter "ADD COLUMN cost DECIMAL(13,3) UNSIGNED NOT NULL DEFAULT '0.000'" u=compare,p=**,D=vrtb,t=playersDailyStat

Add Index:

pt-online-schema-change --recursion-method=none --no-check-replication-filters --execute --alter "add KEY day (day)" u=root,p=**,D=vrtb,t=playerVastsStatsByDomain --set-vars="SQL_LOG_BIN=0,innodb_lock_wait_timeout=3"

How to use pt-query-digest to Analyze MySQL queries from logs, processlist, and tcpdump.

1. Report the slowest queries from slow.log:

pt-query-digest slow.log

2. Report the slowest queries from the processlist on host1:

pt-query-digest --interval=1  --processlist h=localhost,D=db,u=root,p=*** --output slowlog

3. TCPDump

tcpdump -s 65535 -x -nn -q -tttt -i any -c 10000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt

RESTORE REPLICATION BY SYNCING TABLES BETWEEN MASTER / SLAVE SERVER

1. Create checksum between MASTER AND SLAVE servers

pt-table-checksum h=<MASTER>,u=compare,p=<PASSWORD>,D=vrtb,t=<TABLE> --chunk-size=100000 --nocheck-replication-filters --set-vars innodb_lock_wait_timeout=50 --no-check-binlog-format --empty-replicate-table --replicate db.checksums --databases=db --tables=<TABLE> --where="day=20170507"

MASTER - for compare user

PASSWORD> - for compare user

DATABASE - database

TABLE - table we want to checksum

2. Perform dry run for sync process to see actual SQL queries will be executed on MASTER

pt-table-sync --replicate db.checksums h=<MASTER>,u=compare,p=<PASSWORD> --chunk-size=10000 --set-vars innodb_lock_wait_timeout=180 --databases=db --tables=<TABLE> --print --verbose

3. Perform execution for sync process

pt-table-sync --replicate db.checksums h=<MASTER>,u=compare,p=<PASSWORD> --chunk-size=10000 --set-vars innodb_lock_wait_timeout=180 --databases=db --tables=<TABLE> --execute --verbose

Restore MySQL TABLE FROM XTRABACKUP

Dependencies

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
yum install qpress percona-xtrabackup-22

Copy files

scp db.local:/mnt/backup/mysql/full/2018-04-22_00-02-32/db/table* ./

Uncompress

innobackupex --decompress ./

Create table (example)

show create table table \G;

Empty table

LOCK TABLES _tmp_table WRITE;
ALTER TABLE _tmp_table DISCARD TABLESPACE;

Copy files

cp /root/mnt/backup/mysql/full/2018-04-22_00-02-32/db/table.ibd /var/lib/mysql/vrtb/_tmp_table.ibd
chown mysql:mysql /var/lib/mysql/vrtb/_tmp_table.ibd
chmod 660 /var/lib/mysql/vrtb/_tmp_table.ibd

Reload data

ALTER TABLE _tmp_table IMPORT TABLESPACE;
UNLOCK TABLES;

Export restored table

SELECT * FROM _tmp_table INTO OUTFILE '/tmp/_tmp_table' FIELDS TERMINATED BY ','  ENCLOSED BY '"'  LINES TERMINATED BY '\n';

Import

create table table_tmp like table;
truncate table_tmp;
LOAD DATA INFILE '/tmp/_table' INTO TABLE table_tmp FIELDS TERMINATED BY ','  ENCLOSED BY '"' LINES TERMINATED BY '\n';

Mysqlbinlogs

Statistic  By Operations per table into mysqlbinlogs

mysqlbinlog /tmp/db-02-bin.* -v |grep -E 'INSERT|UPDATE|DELETE'|grep -vE '^$' |awk '{print}'|sort|uniq -c| sort -k 1 -n -r
2690696 ### UPDATE `db`.`table1`
1915097 ### INSERT INTO `db`.`table2`

mysqlbinlog --base64-output=decode-rows -vv db1-bin.057004 | awk \
'BEGIN {s_type=""; s_count=0;count=0;insert_count=0;update_count=0;delete_count=0;flag=0;} \
{if(match($0, /#.*Table_map:.*mapped to number/)) {printf "Timestamp : " $1 " " $2 " Table : " $(NF-4); flag=1} \
else if (match($0, /(### INSERT INTO .*..*)/)) {count=count+1;insert_count=insert_count+1;s_type="INSERT"; s_count=s_count+1;}  \
else if (match($0, /(### UPDATE .*..*)/)) {count=count+1;update_count=update_count+1;s_type="UPDATE"; s_count=s_count+1;} \
else if (match($0, /(### DELETE FROM .*..*)/)) {count=count+1;delete_count=delete_count+1;s_type="DELETE"; s_count=s_count+1;}  \
else if (match($0, /^(# at) /) && flag==1 && s_count>0) {print " Query Type : "s_type " " s_count " row(s) affected" ;s_type=""; s_count=0; }  \
else if (match($0, /^(COMMIT)/)) {print "[Transaction total : " count " Insert(s) : " insert_count " Update(s) : " update_count " Delete(s) : " \
delete_count "] \n+----------------------+----------------------+----------------------+----------------------+"; \
count=0;insert_count=0;update_count=0; delete_count=0;s_type=""; s_count=0; flag=0} } '
# Produce an output:
#+----------------------+----------------------+----------------------+----------------------+
#Timestamp : #161119 13:35:11 Table : `db`.`tmp_table` Query Type : INSERT 79807 row(s) affected
#[Transaction total : 79807 Insert(s) : 79807 Update(s) : 0 Delete(s) : 0]

MySQL Multi-Source Replication:
https://www.percona.com/live/data-performance-conference-2016/sites/default/files/slides/MySQL%20Multi-Source%20Replication%20for%20PL2016.pdf