Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Part I
- Slide 05
- sudo su -
- service mysql start
- tail -n 100 /var/log/mysqld.log
- !ps
- Slide 06
- grep tmpd /etc/my.cnf
- grep tmpd /etc/mysql/my.cnf
- Slide 07
- strace /usr/sbin/mysqld
- strace -e trace=open,stat /usr/sbin/mysqld
- Slide 08
- strace -e stat64 /usr/sbin/mysqld --print-defaults
- Slide 09
- cat /usr/etc/my.cnf
- sed -i -e 's/tmpd1r/tmpdir/' /usr/etc/my.cnf
- cat /usr/etc/my.cnf
- Slide 10
- service mysql start
- tail -n 100 /var/log/mysqld.log
- Slide 12
- ls -l /var/lib/mysql/mysql/plugin.*
- chown -R mysql:mysql /var/lib/mysql/mysql/
- service mysql start
- tail -n 100 /var/log/mysqld.log
- Slide 14
- grep datadir /etc/my.cnf
- sed -i -e 's/datadir=\/var\/lib\/msql/datadir=\/var\/lib\/mysql/' /etc/my.cnf
- grep datadir /etc/my.cnf
- Slide 15
- service mysql start
- tail -n 100 /var/log/mysqld.log
- Slide 16
- ls -ld /var/tmp
- chmod a+rwx /var/tmp
- ls -ld /var/tmp
- service mysql start
- tail -n 100 /var/log/mysqld.log
- Slide 18
- perror 12
- grep 100 /etc/my.cnf
- sed -i -e 's/100G/256M/' /etc/my.cnf
- grep innodb_buffer_pool_size /etc/my.cnf
- service mysql start
- tail -n 100 /var/log/mysqld.log
- Slide 20
- ls -l /var/lib/mysql/ibdata1
- ls -l /var/lib/mysql
- chown -R mysql:mysql /var/lib/mysql
- service mysql start
- tail -n 100 /var/log/mysqld.log
- Slide 22
- mysql
- perror 2
- ls -l /tmp/mysql.sock
- Slide 23
- grep socket /var/log/mysqld.log | tail -n 1
- lsof -n | grep mysql | grep unix
- grep -B 1 socket /etc/my.cnf
- sed -i -e 's/\/tmp\/mysql.sock/\/var\/lib\/mysql\/mysql.sock/' /etc/my.cnf
- grep -B 1 socket /etc/my.cnf
- Slide 24
- mysql
- strace -e trace=open mysql
- cat ~/.my.cnf
- Slide 25
- mysql --no-defaults
- mysql -p
- exit
- Slide 26
- echo "SET PASSWORD=PASSWORD('$RANDOM$RANDOM')" | mysql --no-defaults
- mysql --no-defaults
- sed -i 's/\[mysqld\]/&\nskip-grant-tables/' /etc/my.cnf
- cat /etc/my.cnf
- service mysql restart
- Slide 27
- mysql
- mysql> UPDATE mysql.user SET password=PASSWORD('newpass') WHERE user='root';
- mysql> FLUSH PRIVILEGES;
- sed -i 's/skip-grant-tables//' /etc/my.cnf
- service mysql restart
- mysql -pnewpass
- cat /root/.my.cnf
- sed -i 's/password=adummypassword/password=newpass/' ~/.my.cnf
- mysql
- Slide 28
- grep "ERROR" /var/log/mysqld.log |tail -n 3
- mysql_upgrade
- service mysql restart
- tail -n 50 /var/log/mysqld.log
- slide 29
- ( echo -n "SELECT '" ; for i in `seq 1 1` ; do echo -n "1234567890" ; done ; echo -n "' a") | mysql
- ( echo -n "SELECT '" ; for i in `seq 1 2` ; do echo -n "1234567890" ; done ; echo -n "' a") | mysql
- ( echo -n "SELECT '" ; for i in `seq 1 400000` ; do echo -n "1234567890" ; done ; echo -n "' a") | mysql | wc
- ( echo -n "SELECT '" ; for i in `seq 1 450000` ; do echo -n "1234567890" ; done ; echo -n "' a") | mysql | wc
- Slide 30
- mysql -e "SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet'"
- mysql -e "SET GLOBAL max_allowed_packet=5242880"
- mysql -e "SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet'"
- ( echo -n "SELECT '" ; for i in `seq 1 450000` ; do echo -n "1234567890" ; done ; echo -n "' a") | mysql |wc
- slide 31
- screen
- mysql -e "SELECT SLEEP(1000);"
- #ctrl a+d
- kill -6 `pidof mysqld`
- screen -r
- #ctrl d
- slide 32
- grep -A 50 "signal 6" /var/log/mysqld.log
- ----
- slide 35
- mysql -e "SHOW GLOBAL VARIABLES LIKE 'local_infile'"
- slide 36
- mysql -e "show grants for evil@localhost"
- mysql -u evil -p4242 test
- select user();
- CREATE TABLE store (col1 text, col2 text,col3 text);
- select @@datadir;
- LOAD DATA LOCAL INFILE '/var/lib/mysql/mysql/user.MYD' INTO TABLE store LINES TERMINATED BY '*';
- slide 37
- select * from store\G
- LOAD DATA LOCAL INFILE '/root/.my.cnf' INTO TABLE store LINES TERMINATED BY '\n';
- LOAD DATA LOCAL INFILE '/root/.bashrc' INTO TABLE store LINES TERMINATED BY '\n';
- LOAD DATA LOCAL INFILE '/etc/passwd' INTO TABLE store LINES TERMINATED BY '\n';
- select * from store\G
- Slide 39
- cd /var/lib/mysql
- rm -rf ib_logfile*
- lsof -n | grep ib_logfile
- #or
- ll /proc/`pidof mysqld`/fd/
- slide 38
- mysql
- mysql> use test;
- mysql> create table tbl1 (id int auto_increment primary key, v varchar(100)) engine=innodb;
- mysql> insert into tbl1 values(null,'aa');
- mysql> insert into tbl1 select null, v from tbl1;
- mysql> SELECT * FROM tbl1;
- exit
- service mysql restart
- tail -n 50 /var/log/mysqld.log
- Slide 42
- rm -f ibdata1
- lsof -n | grep ibdata1
- Slide 43
- mysql
- mysql> use test;
- mysql> insert into tbl1 values(null,'aa');
- mysql> insert into tbl1 select null, v from tbl1;
- mysql> SELECT * FROM tbl1;
- service mysql restart
- tail -n 50 /var/log/mysqld.log
- Part II
- cd /home/user-lab/sandboxes/repl_test
- ls
- ./start_all
- cd master
- ls -la
- ls data/
- cd ../node1
- ls -al data/
- cd /home/user-lab/sandboxes/repl_test
- ./m
- show processlist;
- exit
- ./s1
- show processlist;
- cd /home/user-lab/sandboxes/dupl_server_id1
- ./start_all
- ./s1 -e "start slave;show slave status \G"
- find ./ -name msandbox.err |xargs tail -n2
- ./s1
- select @@server_id;
- show global variables like 'server_id';
- set global server_id = 102 ;
- select @@server_id;
- show slave status \G
- stop slave sql_thread;
- start slave;
- show slave status \G
- cd node1
- grep server-id my.sandbox.cnf
- sed -i 's/server-id=1/server-id=102/g' my.sandbox.cnf
- grep server-id my.sandbox.cnf
- cd ../
- ./s1
- select now();
- cd /home/user-lab/sandboxes/dupl_key
- ./start_all
- ./s1
- show slave status \G
- use test;
- show create table dupe_test \G
- select * from dupe_test where id =3;
- exit
- cd master/
- mysqlbinlog data/mysql-bin.000002
- cd ../
- cd node1
- mysqlbinlog --server-id=101 data/mysql-bin.000002 |grep dupe_test
- cd ../
- ./s1
- stop slave ;set global sql_slave_skip_counter = 1; start slave;
- show slave status \G
- cd /home/user-lab/sandboxes/dupl_key_gtid
- ./start_all
- ./s1
- show slave status \G
- set GTID_NEXT='987abf63-5454-11e4-ae18-22000af80ef3:4';
- begin;commit;
- set GTID_NEXT='AUTOMATIC';
- start slave;show slave status\G
- exit
- cd ../
- cd dupl_key
- ./m
- use test
- show tables
- show create table dupe_test \G
- insert into dupe_test (val) values (uuid());
- select * from test.dupe_test order by id desc limit 1;
- insert into dupe_test (val) values (uuid());
- show warnings\G
- ./s1
- select * from test.dupe_test order by id desc limit 1;
- exit
- sudo sed -i 's/localhost.localdomain/& SBslave1/' /etc/hosts
- pt-table-checksum -uroot --ask-pass --replicate test.checksum --host 127.0.0.1 --port 20000
- ./s1
- SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks
- FROM test.checksum
- WHERE (
- master_cnt <> this_cnt
- OR master_crc <> this_crc
- OR ISNULL(master_crc) <> ISNULL(this_crc))
- GROUP BY db, tbl;
- select distinct db, tbl from test.checksum where master_crc <> this_crc;
- pt-table-sync -uroot --ask-pass --print --replicate test.checksum --sync-to-master --socket=/tmp/mysql_sandbox20001.sock D=test,t=dupe_test
- pt-table-sync -uroot --ask-pass --replicate test.checksum --sync-to-master --socket=/tmp/mysql_sandbox20001.sock D=test,t=dupe_test --execute
- ./m -e "checksum table test.dupe_test;"
- ./s1 -e "checksum table test.dupe_test;"
- ./m -e "select * from test.dupe_test;"
- ./s1 -e "select * from test.dupe_test;"
- cd /home/user-lab/sandboxes/corrupt_relay/node1/data/
- ls -la mysql_sandbox24001-relay-bin.000009
- truncate -s 10 mysql_sandbox24001-relay-bin.000009
- cd ../../
- ./start_all
- tail node1/data/msandbox.err
- Part III
- Slide 07-
- $ vmstat 1 10
- Slide 09-
- $ iostat -k -d -x 1 3 /dev/sd?
- Slide 11-
- $ top
- Slide 13-
- $ ifstat -i eth0 5 8
- Slide 16-
- $ for i in `seq 1 120` ; do mysql –pOpsdba -e "SHOW ENGINE INNODB STATUS\G" | grep "Checkpoint age " ; sleep 1 ; done > checkpoint.txt
- Slide 17-
- $ show global status like '%conn%';
- Slide 18-
- mysql> pager cut -d '|' -f 4|cut -d ':' -f 1|sort|uniq -c
- mysql> show processlist;
- Slide 19-
- mysql> \s
- Slide 20-
- $ while true; do echo "show engine innodb status" | mysql –pOpsdba -A -N -r | grep -i "history"; sleep 0.5; done
- Slide 21-
- $ mysqladmin –pOpsdba extended -i 1 -r -c 120 | egrep '(Innodb_(os_log|data)_written|Com_insert )'
- Slide 22-
- $ mysqladmin -r -i 5 extend status >> $myadminFILENAME.txt
- Slide 23-
- $ mysqladmin -p -r -i 5 extended-status | grep Innodb_os_log_written
- Slide 24-
- $ mysqlbinlog proddb2-433574-bin-log.000420 | egrep '^#.*exec_time' | egrep -v 'exec_time=(4294967295|0)' | sed -e 's/exec_time=//' | sort -r -n -k 10 | head -n 20
- Slide 25-
- $ mysqlbinlog al-db2.001079| pt-query-digest --type=binlog --group-by=distill > /tmp/writes.txt
- $ head –n 10000 /tmp/writes.txt > /tmp/writes_10000.txt
- $ egrep '^#’ /tmp/writes_1000.txt | awk '{print $10}' | grep exec_time | sort | uniq -c
- $ mysqlbinlog pathtobinlog | pt-query-digest --type binlog --limit 30 --order-by 'Query_time:cnt' > output.txt
- Slide 26-
- mysql >SET GLOBAL log_slow_verbosity='standard';
- mysql >SET GLOBAL slow_query_log_use_global_control='long_query_time';
- mysql> SET GLOBAL long_query_time=0;
- mysql >\! mv /var/log/mysql/mysql-slow.log /var/log/mysql/mysql-slow.log__
- mysql> FLUSH LOGS;
- Slide 27-
- #pt-query-digest --limit 100% /var/log/mysql/mysql-slow.log > /root/bb/mysql-slow-db1.time.digest
- #pt-query-digest --limit 100% /var/log/mysql/mysql-slow.log --order-by 'Query_time:cnt' > /root/bb/mysql-slow-db1.cnt.digest
- #pt-query-digest --limit 100% /var/log/mysql/mysql-slow.log --order-by 'Rows_examined:sum' > /root/palominodb/mysql-slow-db1.rows.digest
- #pt-query-digest mysql-slow.log.1 --filter '$event->{Query_time} > 1' > /tmp/mysql-slow.log.1_1sec.txt
- Slide 28-
- #pt-query-digest --limit 100% --since "2013-09-10 13:00:00" --until "2013-09-10 15:00:00" > spike10sept.digest
- #zcat slow-log.2.gz | pt-query-digest --limit 100% --since "2013-09-10 13:00:00" --until "2013-09-10 15:00:00" > spike10sept.digest
- #pt-query-digest --limit 100% mysql_slow.log --since "2014-01-29 19:00:00" --until "2014-01-30 03:00:00" --order-by 'Rows_examined:sum' > 013014.spike.txt
- Slide 29-
- #tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000000 port 3306 > mysql.tcp.txt
- #pt-query-digest --output tcpdump.slow.log --no-report --type tcpdump mysql.tcp.txt
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement