Category Archives: Snippets

Intercepting arbitrary network traffic with socat

Leave a reply

Socat can be used to intercept view and then forward arbitrary network traffic.

Viewing MySQL protocol

Run this in one terminal:

$ socat -v TCP-LISTEN:3307 TCP:localhost:3306

This will cause socat to listen to port 3307 and forward traffic to localhost port 3306, where you presumably have a MySQL running.

Now you can connect to MySQL via.

$ mysql -u root -h 127.0.0.1 -P 3307 -p

This will cause the MySQL client to connect to the socat listener, which in turn will forward traffic to your MySQL instance and print the data, in this case some of the data are unprintable binary bytes so they are represented as periods but the parts of the MySQL protocol which are text can be gleaned. 

...
\f....show tables< 2018/06/04 23:02:24.011748  length=117 from=463 to=579
.....W....def.information_schema\vTABLE_NAMES\vTABLE_NAMES.Tables_in_database
TABLE_NAME\f!................users\a......"...> 2018/06/04 23:02:24.012169  length=11 from=291 to=301
\a....users.< 2018/06/04 23:02:24.061067  length=61 from=580 to=640
.....def.database.users.users.name.name\f!............\a..........> 2018/06/04 23:02:31.717434  length=24 from=302 to=325
.....select * from users< 2018/06/04 23:02:31.767557  length=102 from=641 to=742
.....-....def.database.users.users.name.name\f!................Tom.....Jeff.....Scott\a..\a..."...

Setup MySQL replication

Leave a reply

Setup MySQL replication


CHANGE MASTER TO 
  MASTER_HOST = 'mydb.dsn.com',
  MASTER_PORT = 3306,
  MASTER_USER = 'root',
  MASTER_PASSWORD = 'password',
  MASTER_LOG_FILE = 'mysql-bin-changelog.00000x',
  MASTER_LOG_POS = 12345;
START SLAVE;

Show MySQL replication status

MariaDB [production]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: production-db
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-changelog.000021
          Read_Master_Log_Pos: 106457060
               Relay_Log_File: mysqld-relay-bin.000031
                Relay_Log_Pos: 64715845
        Relay_Master_Log_File: mysql-bin-changelog.000014
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 64715536
              Relay_Log_Space: 1045994443
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 114732
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 65224191
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Write_rows_log_event::write_row(-1)
1 row in set (0.00 sec)

ERROR: No query specified

Show master bin log position

mysql> show master status;
+----------------------------+-----------+--------------+------------------+-------------------+
| File                       | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------+-----------+--------------+------------------+-------------------+
| mysql-bin-changelog.000083 | 118809022 |              |                  |                   |
+----------------------------+-----------+--------------+------------------+-------------------+

Test Disk IOPS and Read/Write Bandwidth

Leave a reply

Command to test disk IOPS

fio --randrepeat=1 --ioengine=libaio --direct=1 --gtod_reduce=1 --name=test --filename=test --bs=4k --iodepth=64 --size=4G --readwrite=randrw --rwmixread=75

Sample Output

test: (g=0): rw=randrw, bs=4K-4K/4K-4K/4K-4K, ioengine=libaio, iodepth=64
fio-2.2.10
Starting 1 process
test: Laying out IO file(s) (1 file(s) / 4096MB)
bs: 1 (f=1): [m(1)] [78.1% done] [8707KB/2753KB/0KB /s] [2176/688/0 iops] [eta 01m:23s]
fio: terminating on signal 2
test: (groupid=0, jobs=1): err= 0: pid=2864: Sun Apr 22 05:38:14 2018
read : io=2398.4MB, bw=8297.9KB/s, iops=2074, runt=295968msec
write: io=819616KB, bw=2769.3KB/s, iops=692, runt=295968msec
cpu : usr=0.49%, sys=1.70%, ctx=200905, majf=0, minf=9
IO depths : 1=0.1%, 2=0.1%, 4=0.1%, 8=0.1%, 16=0.1%, 32=0.1%, >=64=100.0%
submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
complete : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.1%, >=64=0.0%
issued : total=r=613972/w=204904/d=0, short=r=0/w=0/d=0, drop=r=0/w=0/d=0
latency : target=0, window=0, percentile=100.00%, depth=64

Run status group 0 (all jobs):
 READ: io=2398.4MB, aggrb=8297KB/s, minb=8297KB/s, maxb=8297KB/s, mint=295968msec, maxt=295968msec
 WRITE: io=819616KB, aggrb=2769KB/s, minb=2769KB/s, maxb=2769KB/s, mint=295968msec, maxt=295968msec

Disk stats (read/write):
 xvde: ios=613645/214996, merge=0/57, ticks=4398044/4579196, in_queue=8977428, util=100.00%

Test Disk Read Bandwidth

hdparm -t /dev/xvdc

Sample Output

$ hdparm -t /dev/xvdc
/dev/xvdc:
 Timing buffered disk reads: 168 MB in 3.02 seconds = 55.60 MB/sec

Test Disk Write Bandwidth

Note: You should probably run this several times or increase the count until the result stabilizes, this is to eliminate the impact of any disk caches.

dd if=/dev/zero of=/mnt/throughtput_hdd/output bs=8k count=100k

Sample Output

$ dd if=/dev/zero of=/mnt/throughtput_hdd/output bs=8k count=100k
102400+0 records in
102400+0 records out
838860800 bytes (839 MB, 800 MiB) copied, 16.476 s, 50.9 MB/s