PLSC2016

gistfile1.txt

Part I

Slide 06
sudo su -
service mysql start
grep ERROR /var/log/mysqld.log | tail
!ps # Alias for `ps aux | grep mysql`

Slide 07
grep tmpd /etc/my.cnf
grep tmpd /etc/mysql/my.cnf
 
Slide 08 
# with no options: strace /usr/sbin/mysqld
strace -e trace=open,stat /usr/sbin/mysqld 2>&1 >/dev/null | grep '^stat'



Slide 09
strace -e stat64 /usr/sbin/mysqld --print-defaults


Slide 10
cat /usr/etc/my.cnf
sed -i -e 's/tmpd1r=/tmpdir=/' /usr/etc/my.cnf
cat /usr/etc/my.cnf


Slide 11 & 12
service mysql start
# tail -n 100 /var/log/mysqld.log
tail -n 100 /var/log/mysqld.log | grep -v "Note"
egrep "ERROR|Errcode" /var/log/mysqld.log | tail


Slide 13 & 14
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 | grep -v Note



Slide 15
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 16
service mysql start
tail -n 100 /var/log/mysqld.log | grep -v Note


Slide 17
ls -ld /var/tmp
chmod a+rwx /var/tmp
ls -ld /var/tmp
service mysql start

Slide 18
tail -n 100 /var/log/mysqld.log | grep -v Note 
# Or, Found mmap errorcode, let's do a more focussed grep:
grep -B2 "errno 12" /var/log/mysqld.log

Slide 19 & 20
perror 12
free -m
grep innodb_buffer_pool_size /etc/my.cnf
sed -i -e 's/100G/256M/' /etc/my.cnf
grep innodb_buffer_pool_size /etc/my.cnf

service mysql start
egrep "ERROR|Errcode" /var/log/mysqld.log | tail


Slide 21 & 22
ls -l /var/lib/mysql/ibdata1
ls -l /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
service mysql start
service mysql status
egrep "ERROR|Errcode" /var/log/mysqld.log | tail


Slide 23

mysql
perror 2
ls -l /tmp/mysql.sock


Slide 24
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 25
mysql
strace -e trace=open mysql
cat ~/.my.cnf

Slide 26
mysql --no-defaults
mysql -p

exit

Slide 27
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 28
mysql 
mysql> UPDATE mysql.user SET password=PASSWORD('newpass') WHERE user='root';
mysql> FLUSH PRIVILEGES;
mysql> quit;

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 29
grep "ERROR" /var/log/mysqld.log |tail -n 3
mysql
mysql> SELECT COUNT(1) column_count FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user';
# If you get 43, MySQL 5.6
# If you get 42, MySQL 5.5
# If you get 39, MySQL 5.1
# If you get 37, MySQL 5.0
mysql> select version()
mysql_upgrade
service mysql restart
tail -n 10 /var/log/mysqld.log

slide 30

( 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 31
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 32
#screen
mysql -e "SELECT SLEEP(1000);" & 
#ctrl a+d

kill -6 `pidof mysqld`


#screen -r
#ctrl d 



slide 33
grep -A 50 "signal 6" /var/log/mysqld.log

----
slide 36

mysql -e "SHOW GLOBAL VARIABLES LIKE 'local_infile'"

slide 37

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 38

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 40
cd /var/lib/mysql

rm -rf ib_logfile*

lsof -n | grep ib_logfile
#or
ll /proc/`pidof mysqld`/fd/


slide 41
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 43
rm -f ibdata1

lsof -n | grep ibdata1


Slide 44
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