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);
*/
Tuesday, 16 September 2014
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)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All
rights reserved.
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:
read: 11354
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
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
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
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003,
2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
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
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003,
2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
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)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All
rights reserved.
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)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All
rights reserved.
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)
Subscribe to:
Posts (Atom)