MySQL and SQL Server: 2014

Tuesday 16 September 2014

SQL Server side trace example

DECLARE @TraceID INT
DECLARE @ON BIT
DECLARE @RetVal INT
SET @ON = 1
exec @RetVal = sp_trace_create @TraceID OUTPUT, 2, N'S:\trace\TraceFile'
print 'This trace is Trace ID = ' + CAST(@TraceID AS NVARCHAR)
print 'Return value = ' + CAST(@RetVal AS NVARCHAR)
/*
-- 10 = RPC:Completed
exec sp_trace_setevent @TraceID, 10, 1, @ON     -- Textdata
exec sp_trace_setevent @TraceID, 10, 3, @ON     -- DatabaseID
exec sp_trace_setevent @TraceID, 10, 6, @ON     -- NTUserName
exec sp_trace_setevent @TraceID, 10, 7, @ON     -- NTDomainName
exec sp_trace_setevent @TraceID, 10, 11, @ON    -- LoginName
exec sp_trace_setevent @TraceID, 10, 12, @ON    -- SPID
exec sp_trace_setevent @TraceID, 10, 13, @ON    -- Duration
exec sp_trace_setevent @TraceID, 10, 14, @ON    -- StartTime
exec sp_trace_setevent @TraceID, 10, 15, @ON    -- EndTime
*/
-- 12 = SQL:BatchCompleted
exec sp_trace_setevent @TraceID, 12, 1, @ON     -- Textdata
exec sp_trace_setevent @TraceID, 12, 3, @ON     -- DatabaseID
exec sp_trace_setevent @TraceID, 12, 6, @ON     -- NTUserName
exec sp_trace_setevent @TraceID, 12, 7, @ON     -- NTDomainName
exec sp_trace_setevent @TraceID, 12, 11, @ON    -- LoginName
exec sp_trace_setevent @TraceID, 12, 12, @ON    -- SPID
exec sp_trace_setevent @TraceID, 12, 13, @ON    -- Duration
exec sp_trace_setevent @TraceID, 12, 14, @ON    -- StartTime
exec sp_trace_setevent @TraceID, 12, 15, @ON    -- EndTime
--------------
exec sp_trace_setfilter @TraceID, 1, 0, 6, N'Update%'; --(6 =like ,7= not like)(0=and, 1=or)
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%drop%';
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%delete%';
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'create%';
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'%clmns%';
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'%SHOW_STATISTICS%';
exec sp_trace_setfilter @TraceID, 11,0, 7, N'%xxx_user%';

/*
You can find the ID for each trace-event, columns, etc from Books Online; just search for the sp_trace_create, sp_trace_setevent and sp_trace_setfiler sprocs. You can then control the trace as follows:
exec sp_trace_setstatus 2, 0       -- Stop the trace,2 is the trace id, can get from sys.traces table
exec sp_trace_setstatus 2, 1       -- Start the trace
exec sp_trace_setstatus 2, 2       -- Close the trace file and delete the trace settings
SELECT * FROM sys.traces
SELECT * FROM fn_trace_gettable( 'S:\trace\TraceFile.trc',default);
*/

Thursday 28 August 2014

my_print_defaults: command not found

[root@centos7 mysql]# service mysql status

/etc/init.d/mysql: line 256: my_print_defaults: command not found

ERROR! MySQL is not running

[root@centos7 mysql]# find / -name my_print_defaults

/home/mysql/bin/my_print_defaults

[root@centos7 mysql]# cp /home/mysql/bin/my_print_defaults /usr/bin

[root@centos7 mysql]# service mysql status

ERROR! MySQL is not running

[root@centos7 mysql]# service mysql restart

ERROR! MySQL server PID file could not be found!

/etc/init.d/mysql: line 276: cd: /usr/local/mysql: No such file or directory

Starting MySQL ERROR! Couldn't find MySQL server (/usr/local/mysql/bin/mysqld_safe)

[root@centos7 mysql]# vi /etc/init.d/mysql

Replace those /usr/local/mysql to /home/mysql(/home/mysql is my mysql default path)

[root@centos7 mysql]# service mysql stop

Shutting down MySQL..140827 16:25:40 mysqld_safe mysqld from pid file /home/mysql/data/centos7.pid ended

SUCCESS!

[1]+ Done bin/mysqld_safe --user=mysql

[root@centos7 mysql]# service mysql start

Starting MySQL.. SUCCESS!

[root@centos7 mysql]#

Sysbench 0.5 on CENTOS7

[root@centos7 home]# wget http://www.lefred.be/files/sysbench-0.5-3.el6_.x86_64.rpm

[root@centos7 home]# ls -lah

-rw-r--r--. 1 root root 180K Jan 18 2013 sysbench-0.5-3.el6_.x86_64.rpm

[root@centos7 home]# rpm -ivh sysbench-0.5-3.el6_.x86_64.rpm

Preparing... ################################# [100%]

Updating / installing...

1:sysbench-0.5-3.el6 ################################# [100%]

[root@centos7 home]# sysbench --test=oltp help

sysbench 0.5: multi-threaded system evaluation benchmark

PANIC: unprotected error in call to Lua API (cannot open oltp: No such file or directory)

[root@centos7 home]# ll /usr/share/doc/sysbench/tests/db

total 44

-rw-r--r--. 1 root root 3585 Sep 7 2012 common.lua

-rw-r--r--. 1 root root 340 Sep 7 2012 delete.lua

-rw-r--r--. 1 root root 830 Sep 7 2012 insert.lua

-rw-r--r--. 1 root root 2925 Sep 7 2012 oltp.lua

-rw-r--r--. 1 root root 342 Sep 7 2012 oltp_simple.lua

-rw-r--r--. 1 root root 425 Sep 7 2012 parallel_prepare.lua

-rw-r--r--. 1 root root 343 Sep 7 2012 select.lua

-rw-r--r--. 1 root root 3964 Sep 7 2012 select_random_points.lua

-rw-r--r--. 1 root root 4066 Sep 7 2012 select_random_ranges.lua

-rw-r--r--. 1 root root 343 Sep 7 2012 update_index.lua

-rw-r--r--. 1 root root 552 Sep 7 2012 update_non_index.lua

[root@centos7 home]#

[root@centos7 db]# sysbench --test='/usr/share/doc/sysbench/tests/db/oltp.lua' --oltp_tables_count=100 --report-interval=10 --oltp-table-size=1000 --num-threads=2 --mysql-user=root --mysql-password=123123 --mysql-table-engine=innodb --rand-init=on --mysql-host=192.168.1.144 --mysql-port=3306 --mysql-db=oltp prepare

sysbench 0.5: multi-threaded system evaluation benchmark

Creating table 'sbtest1'...

Inserting 1000 records into 'sbtest1'

Creating table 'sbtest2'...

…………………………………………………………

Inserting 1000 records into 'sbtest98'

Creating table 'sbtest99'...

Inserting 1000 records into 'sbtest99'

Creating table 'sbtest100'...

Inserting 1000 records into 'sbtest100'

[root@centos7 db]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.6.11 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| oltp |

| performance_schema |

| test |

+--------------------+

5 rows in set (0.01 sec)

mysql> use oltp;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+----------------+

| Tables_in_oltp |

+----------------+

| sbtest1 |

| sbtest10 |

| sbtest100 |

[root@centos7 db]# sysbench --test='/usr/share/doc/sysbench/tests/db/oltp.lua' --oltp_tables_count=100 --report-interval=5 --oltp-table-size=100 --mysql-user=root --mysql-password=123123 --oltp-connect-delay=10 --oltp-dist-type=uniform --rand-init=on --mysql-table-engine=innodb --mysql-host=192.168.1.144 --mysql-port=3306 --mysql-db=oltp --max-time=15 --max-requests=0 --num-threads=6 --thread-stack-size=256 run

sysbench 0.5: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 6

Report intermediate results every 5 second(s)

Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored

Threads started!

[ 5s] threads: 6, tps: 53.62, reads/s: 767.42, writes/s: 219.26, response time: 137.18ms (95%)

[ 10s] threads: 6, tps: 53.60, reads/s: 750.38, writes/s: 214.40, response time: 160.24ms (95%)

[ 15s] threads: 6, tps: 53.80, reads/s: 753.18, writes/s: 215.19, response time: 139.92ms (95%)

OLTP test statistics:

queries performed:

write: 3244

other: 1622

total: 16220

transactions: 811 (53.68 per sec.)

deadlocks: 0 (0.00 per sec.)

read/write requests: 14598 (966.31 per sec.)

other operations: 1622 (107.37 per sec.)

General statistics:

total time: 15.1069s

total number of events: 811

total time taken by event execution: 90.3482s

response time:

min: 49.34ms

avg: 111.40ms

max: 202.72ms

approx. 95 percentile: 144.47ms

Threads fairness:

events (avg/stddev): 135.1667/1.07

execution time (avg/stddev): 15.0580/0.04

[root@centos7 db]# sysbench --test='/usr/share/doc/sysbench/tests/db/oltp.lua' --oltp_tables_count=100 --report-interval=5 --oltp-table-size=100 --mysql-user=root --mysql-password=123123 --mysql-table-engine=innodb --mysql-host=192.168.1.144 --mysql-port=3306 --mysql-db=oltp cleanup

sysbench 0.5: multi-threaded system evaluation benchmark

Dropping table 'sbtest1'...

Dropping table 'sbtest2'...

…………………………………

Dropping table 'sbtest99'...

Dropping table 'sbtest100'...

[root@centos7 db]#

#initinal script

sysbench --test='/usr/share/doc/sysbench/tests/db/oltp.lua' --oltp_tables_count=100 --report-interval=10 --oltp-table-size=1000 --num-threads=2 --mysql-user=root --mysql-password=123123 --mysql-table-engine=innodb --rand-init=on --mysql-host=192.168.1.144 --mysql-port=3306 --mysql-db=oltp prepare

#stress test script

sysbench --test='/usr/share/doc/sysbench/tests/db/oltp.lua' --oltp_tables_count=100 --report-interval=5 --oltp-table-size=100 --mysql-user=root --mysql-password=123123 --oltp-connect-delay=10 --oltp-dist-type=uniform --rand-init=on --mysql-table-engine=innodb --mysql-host=192.168.1.144 --mysql-port=3306 --mysql-db=oltp --max-time=15 --max-requests=0 --num-threads=6 --thread-stack-size=256 run

#clean script

Tuesday 15 July 2014

Restore MySQL database from percona backup and binary logs

Restore MySQL database from percona backup and binary logs

Step 1: Copy the backup files and binary log from backup device

Here the backup files folder is /home/mysqlbackup/2014-07-12_20_00_02 and the binary log file is log-bin.000071, the backup stop at the position 1039080535.

Step 2: Restore full backup from innobackup folders

Stop mysql service, backup old data folder and create a new folder named as data.

Restore mysql database from innobackup files.

[root@localhost mysql]# innobackupex --apply-log /home/mysqlbackup/2014-07-12_20-00-02

140715 17:42:35 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-file="/home/mysqlbackup/2014-07-12_20-00-02/backup-my.cnf" --defaults-group="mysqld" --prepare --target-dir=/home/mysqlbackup/2014-07-12_20-00-02 --tmpdir=/tmp

xtrabackup_56 version 2.1.8 for MySQL server 5.6.15 Linux (x86_64) (revision id: 733)

xtrabackup: cd to /home/mysqlbackup/2014-07-12_20-00-02

xtrabackup: This target seems to be not prepared yet.

…….

[notice (again)]

If you use binary log and don't use any hack of group commit,

the binary log position seems to be:

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 121618500768

140715 17:42:46 innobackupex: completed OK!

[root@localhost mysql]#innobackupex --copy-back --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock /home/mysqlbackup/2014-07-12_20-00-02

innobackupex: Starting to copy files in '/home/mysqlbackup/2014-07-12_20-00-02'

innobackupex: back to original data directory '/usr/local/mysql/data'

……

innobackupex: Starting to copy InnoDB log files

innobackupex: in '/home/mysqlbackup/2014-07-12_20-00-02'

innobackupex: back to original InnoDB log directory '/usr/local/mysql/data'

innobackupex: Copying '/home/mysqlbackup/2014-07-12_20-00-02/ib_logfile1' to '/usr/local/mysql/data/ib_logfile1'

innobackupex: Copying '/home/mysqlbackup/2014-07-12_20-00-02/ib_logfile0' to '/usr/local/mysql/data/ib_logfile0'

innobackupex: Finished copying back files.

140715 18:28:13 innobackupex: completed OK!

[root@localhost mysql]#

Change the data folder own user and start mysql service

[root@localhost mysql]# chown -R mysql .

[root@localhost mysql]# chgrp -R mysql .

[root@localhost mysql]# service mysql start

Starting MySQL..................................... [ OK ]

[root@localhost mysql]#

Recovery data from binlog

[root@localhost 2014-07-12_20-00-02]# cat xtrabackup_slave_info

CHANGE MASTER TO MASTER_LOG_FILE='log-bin.000071', MASTER_LOG_POS=1039080535

[root@localhost 2014-07-12_20-00-02]# cd ..

[root@localhost mysqlbackup]# ls -lah

total 2.2G

drwxr-xr-x. 3 root root 4.0K Jul 16 11:13 .

drwxr-xr-x. 11 root root 4.0K Jul 10 11:35 ..

-rw-r--r--. 1 root root 130M Jul 16 11:14 1.sql

drwxr-xr-x. 12 root root 4.0K Jul 16 11:11 2014-07-12_20-00-02

-rw-r-----. 1 root root 1.1G Jul 10 18:13 log-bin.000070

-rw-r-----. 1 root root 1.1G Jul 15 17:03 log-bin.000071

[root@localhost mysqlbackup]# mysqlbinlog --start-position=1039080535 log-bin.000071 | mysql -uroot -p

Enter password:

ERROR 1666 (HY000) at line 31: Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.

[root@localhost mysqlbackup]# mysqlbinlog -v --start-position=1039080535 log-bin.000071 | mysql -uroot -p

Enter password:

ERROR 1666 (HY000) at line 31: Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.

[root@localhost mysqlbackup]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.6.11-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%binlog_format%';

+---------------+-----------+

| Variable_name | Value |

+---------------+-----------+

| binlog_format | STATEMENT |

+---------------+-----------+

1 row in set (0.00 sec)

mysql> set global binlog_format='row';

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

[root@localhost mysqlbackup]# mysqlbinlog -v --start-position=1039080535 log-bin.000071 | mysql -uroot -p

Enter password:

Set back the binlog_format

[root@localhost mysqlbackup]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 7

Server version: 5.6.11-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set global binlog_format='STATEMENT';

Query OK, 0 rows affected (0.00 sec)