MySQL and SQL Server

Tuesday, 29 March 2016

SQL Server rights for basic application user

create role db_exector;
grant exec,view definition to db_exector;
GRANT SELECT, INSERT, UPDATE on SCHEMA::dbo to db_exector
EXEC sp_addrolemember 'db_exector', 'loginuser'

create role db_readonly;
grant exec,view definition to db_readonly;
GRANT SELECT on SCHEMA::dbo to db_readonly
EXEC sp_addrolemember 'db_readonly', 'loginuser'

Tuesday, 16 February 2016

libev.so.4()(64bit) is needed by percona-xtrabackup-2.3.3-1.el6.x86_64

percona xtrabackup on centos 5.6

[root@twpdb29 local]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.3/binary/redhat/6/x86_64/percona-xtrabackup-2.3.3-1.el6.x86_64.rpm
--2016-02-17 14:57:08-- https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.3/binary/redhat/6/x86_64/percona-xtrabackup-2.3.3-1.el6.x86_64.rpm
Resolving www.percona.com... 74.121.199.234
Connecting to www.percona.com|74.121.199.234|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 5681880 (5.4M) [application/x-redhat-package-manager]
Saving to: “percona-xtrabackup-2.3.3-1.el6.x86_64.rpm”

100%[================================================================================================================================================================>] 5,681,880    315K/s   in 17s

2016-02-17 14:57:31 (328 KB/s) - “percona-xtrabackup-2.3.3-1.el6.x86_64.rpm” saved [5681880/5681880]

[root@twpdb29 local]# ls -lah
total 296M
drwxr-xr-x. 13 root root 4.0K Feb 17 14:57 .
drwxr-xr-x. 14 root root 4.0K Jan 28 07:52 ..
drwxr-xr-x. 2 root root 4.0K Sep 23 2011 bin
drwxr-xr-x. 2 root root 4.0K Sep 23 2011 etc
drwxr-xr-x. 2 root root 4.0K Sep 23 2011 games
drwxr-xr-x. 2 root root 4.0K Sep 23 2011 include
drwxr-xr-x. 2 root root 4.0K Sep 23 2011 lib
drwxr-xr-x. 2 root root 4.0K Sep 23 2011 lib64
drwxr-xr-x. 2 root root 4.0K Sep 23 2011 libexec
drwxr-xr-x. 13 mysql mysql 4.0K Feb 17 11:12 mysql
-rw-r--r--. 1 root root 291M Feb 16 14:54 mysql-5.6.11-linux-glibc2.5-x86_64.tar.gz
-rw-r--r--. 1 root root   33K Feb 16 14:54 mytop-1.7-3.b737f60.el6.noarch.rpm
-rw-r--r--. 1 root root 5.5M Dec 15 04:57 percona-xtrabackup-2.3.3-1.el6.x86_64.rpm
drwxr-xr-x. 2 root root 4.0K Sep 23 2011 sbin
drwxr-xr-x. 5 root root 4.0K Dec 29 18:49 share
drwxr-xr-x. 2 root root 4.0K Sep 23 2011 src
[root@twpdb29 local]# rpm -Uvh percona-xtrabackup-2.3.3-1.el6.x86_64.rpm
warning: percona-xtrabackup-2.3.3-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
error: Failed dependencies:
        libev.so.4()(64bit) is needed by percona-xtrabackup-2.3.3-1.el6.x86_64
[root@twpdb29 local]# wget https://dl.fedoraproject.org/pub/epel/6/x86_64/libev-4.03-3.el6.x86_64.rpm
--2016-02-17 15:05:20-- https://dl.fedoraproject.org/pub/epel/6/x86_64/libev-4.03-3.el6.x86_64.rpm
Resolving dl.fedoraproject.org... 209.132.181.27, 209.132.181.24, 209.132.181.25, ...
Connecting to dl.fedoraproject.org|209.132.181.27|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 115352 (113K) [application/x-rpm]
Saving to: “libev-4.03-3.el6.x86_64.rpm”

100%[================================================================================================================================================================>] 115,352      224K/s   in 0.5s

2016-02-17 15:05:27 (224 KB/s) - “libev-4.03-3.el6.x86_64.rpm” saved [115352/115352]

[root@twpdb29 local]# ls -lah
total 296M
drwxr-xr-x. 13 root root 4.0K Feb 17 15:05 .
drwxr-xr-x. 14 root root 4.0K Jan 28 07:52 ..
drwxr-xr-x. 2 root root 4.0K Sep 23 2011 bin
drwxr-xr-x. 2 root root 4.0K Sep 23 2011 etc
drwxr-xr-x. 2 root root 4.0K Sep 23 2011 games
drwxr-xr-x. 2 root root 4.0K Sep 23 2011 include
drwxr-xr-x. 2 root root 4.0K Sep 23 2011 lib
drwxr-xr-x. 2 root root 4.0K Sep 23 2011 lib64
-rw-r--r--. 1 root root 113K Jul 23 2011 libev-4.03-3.el6.x86_64.rpm
drwxr-xr-x. 2 root root 4.0K Sep 23 2011 libexec
drwxr-xr-x. 13 mysql mysql 4.0K Feb 17 11:12 mysql
-rw-r--r--. 1 root root 291M Feb 16 14:54 mysql-5.6.11-linux-glibc2.5-x86_64.tar.gz
-rw-r--r--. 1 root root   33K Feb 16 14:54 mytop-1.7-3.b737f60.el6.noarch.rpm
-rw-r--r--. 1 root root 5.5M Dec 15 04:57 percona-xtrabackup-2.3.3-1.el6.x86_64.rpm
drwxr-xr-x. 2 root root 4.0K Sep 23 2011 sbin
drwxr-xr-x. 5 root root 4.0K Dec 29 18:49 share
drwxr-xr-x. 2 root root 4.0K Sep 23 2011 src
[root@twpdb29 local]# rpm -Uvh libev-4.03-3.el6.x86_64.rpm
warning: libev-4.03-3.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
Preparing...                ########################################### [100%]
   1:libev                  ########################################### [100%]
[root@twpdb29 local]# rpm -Uvh percona-xtrabackup-2.3.3-1.el6.x86_64.rpm
warning: percona-xtrabackup-2.3.3-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing...                ########################################### [100%]
   1:percona-xtrabackup     ########################################### [100%]
[root@twpdb29 local]#

Tuesday, 1 December 2015

MySQLdump individual records

For simple query:
SELECT * FROM side_limit where operator = 'aby';
mysqldump -uroot -p demo side_limit --where="operator = 'aby'" > > side_limit20151202.sql

For join or subquery:
Step 1:
create table side_limit_detail_dba like side_limit_detail;
Step 2:
insert into side_limit_detail_dba
select * from side_limit_detail where side_limit_id in
(SELECT id FROM side_limit where operator = 'aby');

Step 3:
mysqldump -uroot -p demo side_limit_detail_dba --where="operator = 'aby'" > side_limit_detail_dba20151202.sql

Step 4:
drop table side_limit_detail_dba

Thursday, 19 November 2015

MySQL backup user Minimize rights

GRANT RELOAD ON *.* TO 'mysqlbackup'@'localhost' identified by '**********';
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, SELECT, DROP, UPDATE ON mysql.backup_history TO 'mysqlbackup'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'mysqlbackup'@'localhost';
GRANT SUPER ON *.* TO 'mysqlbackup'@'localhost';
GRANT LOCK TABLES, SELECT, CREATE, ALTER ON *.* TO 'mysqlbackup'@'localhost';

Tuesday, 22 September 2015

Install MongoDB on centos6

[root@Master local]# ls -lah /usr/local/mongodb-linux-x86_64-3.0.6.gz

-rw-r--r--. 1 root root 39M Sep 22 12:39 /usr/local/mongodb-linux-x86_64-3.0.6.gz

[root@Master local]# tar -zxvf mongodb-linux-x86_64-3.0.6.gz

mongodb-linux-x86_64-3.0.6/README
mongodb-linux-x86_64-3.0.6/THIRD-PARTY-NOTICES
mongodb-linux-x86_64-3.0.6/GNU-AGPL-3.0
mongodb-linux-x86_64-3.0.6/bin/mongodump
mongodb-linux-x86_64-3.0.6/bin/mongorestore
mongodb-linux-x86_64-3.0.6/bin/mongoexport
mongodb-linux-x86_64-3.0.6/bin/mongoimport
mongodb-linux-x86_64-3.0.6/bin/mongostat
mongodb-linux-x86_64-3.0.6/bin/mongotop
mongodb-linux-x86_64-3.0.6/bin/bsondump
mongodb-linux-x86_64-3.0.6/bin/mongofiles
mongodb-linux-x86_64-3.0.6/bin/mongooplog
mongodb-linux-x86_64-3.0.6/bin/mongoperf
mongodb-linux-x86_64-3.0.6/bin/mongod
mongodb-linux-x86_64-3.0.6/bin/mongos
mongodb-linux-x86_64-3.0.6/bin/mongo

[root@Master local]# mv mongodb-linux-x86_64-3.0.6 mongodb

[root@Master local]# cd mongodb

[root@Master mongodb]# ls -lah

total 76K
drwxr-xr-x. 3 root root 4.0K Sep 23 11:26 .
drwxr-xr-x. 19 root root 4.0K Sep 23 11:28 ..
drwxr-xr-x. 2 root root 4.0K Sep 23 11:26 bin
-rw-r--r--. 1 1046 1046 34K Aug 24 08:43 GNU-AGPL-3.0
-rw-r--r--. 1 1046 1046 1.4K Aug 24 08:43 README
-rw-r--r--. 1 1046 1046 23K Aug 24 08:43 THIRD-PARTY-NOTICES

[root@Master mongodb]# groupadd mongo_grp

[root@Master mongodb]# useradd -r -g mongo_grp mongo_user

[root@Master mongodb]# ls -lah

[root@Master mongodb]# mkdir db

[root@Master mongodb]# mkdir logs

[root@Master mongodb]# ls -lah

total 84K
drwxr-xr-x. 5 root root 4.0K Sep 23 11:30 .
drwxr-xr-x. 19 root root 4.0K Sep 23 11:28 ..
drwxr-xr-x. 2 root root 4.0K Sep 23 11:26 bin
drwxr-xr-x. 2 root root 4.0K Sep 23 11:30 db
-rw-r--r--. 1 1046 1046 34K Aug 24 08:43 GNU-AGPL-3.0
drwxr-xr-x. 2 root root 4.0K Sep 23 11:30 logs
-rw-r--r--. 1 1046 1046 1.4K Aug 24 08:43 README
-rw-r--r--. 1 1046 1046 23K Aug 24 08:43 THIRD-PARTY-NOTICES

[root@Master mongodb]# chown -R mongo_user .

[root@Master mongodb]# chgrp -R mongo_grp .

[root@Master mongodb]# echo "export PATH=$PATH:/usr/local/mongodb/bin" >>/etc/profile

[root@Master mongodb]# source /etc/profile

[root@Master mongodb]# cd /etc/init.d/

[root@Master init.d]# vim mongod

#!/bin/bash

# mongodb Startup script for the mongodb server

# chkconfig: - 64 36

# description: MongoDB Database Server

# processname: mongodb

# Source function library

. /etc/rc.d/init.d/functions

if [ -f /etc/sysconfig/mongodb ]; then

. /etc/sysconfig/mongodb

prog="mongod"

mongod="/usr/local/mongodb/bin/mongod"

RETVAL=0

start() {

echo -n $"Starting $prog: "

daemon $mongod "--dbpath /usr/local/mongodb/db --fork --logpath /usr/local/mongodb/logs/mongodb.log --logappend 2>&1 >>/usr/local/mongodb/logs/mongodb.log"

RETVAL=$?

echo

[ $RETVAL -eq 0 ] && touch /var/lock/subsys/$prog

return $RETVAL

}

stop() {

echo -n $"Stopping $prog: "

killproc $prog

RETVAL=$?

echo

[ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/$prog

return $RETVAL

}

reload() {

echo -n $"Reloading $prog: "

killproc $prog -HUP

RETVAL=$?

echo

return $RETVAL

}

case "$1" in

start)

start

;;

stop)

stop

;;

restart)

stop

start

;;

condrestart)

if [ -f /var/lock/subsys/$prog ]; then

stop

start

;;

reload)

reload

;;

status)

status $mongod

RETVAL=$?

;;

echo $"Usage: $0 {start|stop|restart|condrestart|reload|status}"

RETVAL=1

esac

exit $RETVAL

[root@Master init.d]# chmod +x mongod

[root@Master init.d]# chkconfig --add mongod

[root@Master init.d]# chkconfig --level 345 mongod on

[root@Master init.d]# service mongod status

mongod is stopped

[root@Master init.d]# service mongod start

Starting mongod:

[ OK ]

[root@Master init.d]#

[root@Master init.d]# mongo

MongoDB shell version: 3.0.6
connecting to: test
Server has startup warnings:
2015-09-23T11:44:33.212+0800 I CONTROL [initandlisten] ** WARNING: You are running this process as the root user, which is not recommended.
2015-09-23T11:44:33.212+0800 I CONTROL [initandlisten]
2015-09-23T11:44:33.212+0800 I CONTROL [initandlisten]
2015-09-23T11:44:33.212+0800 I CONTROL [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/enabled is 'always'.
2015-09-23T11:44:33.212+0800 I CONTROL [initandlisten] ** We suggest setting it to 'never'
2015-09-23T11:44:33.212+0800 I CONTROL [initandlisten]
2015-09-23T11:44:33.212+0800 I CONTROL [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'.
2015-09-23T11:44:33.212+0800 I CONTROL [initandlisten] ** We suggest setting it to 'never'
2015-09-23T11:44:33.212+0800 I CONTROL [initandlisten]
> show databases;
local 0.078GB
> exit
bye

[root@Master init.d]# service mongod stop

Stopping mongod: [ OK ]

------------------------------------------The End--------------------------------------------------

Thursday, 17 September 2015

Upgrade MySQL 5.6 to 5.7

1. Create demo DB on MySQL 5.6

2. Stop MySQL5.6 Service

[root@Master ~]# service mysql stop

Shutting down MySQL.. SUCCESS!

[root@Master local]# ls -lah

total 880M

drwxr-xr-x. 13 mysql mysql 4.0K Sep 17 14:42 mysql

-rw-r--r--. 1 root root 291M Apr 8 2013 mysql-5.6.11-linux-glibc2.5-x86_64.tar.gz

-rw-r--r--. 1 root root 588M Sep 17 12:35 mysql-5.7.8-rc-linux-glibc2.5-x86_64.tar.gz

3. Rename the old mysql folder as mysql_56

[root@Master local]# mv mysql mysql_56

4. Install MySQL 5.7 on the same Server

[root@Master local]# tar -zxvf mysql-5.7.8-rc-linux-glibc2.5-x86_64.tar.gz

mysql-5.7.8-rc-linux-glibc2.5-x86_64/

mysql-5.7.8-rc-linux-glibc2.5-x86_64/bin/

mysql-5.7.8-rc-linux-glibc2.5-x86_64/bin/mysqladmin

mysql-5.7.8-rc-linux-glibc2.5-x86_64/bin/mysql_config_editor

mysql-5.7.8-rc-linux-glibc2.5-x86_64/bin/myisam_ftdump

………………………………………..

[root@Master local]# ls -lah

total 880M

drwxr-xr-x. 13 mysql mysql 4.0K Sep 17 14:42 mysql_56

-rw-r--r--. 1 root root 291M Apr 8 2013 mysql-5.6.11-linux-glibc2.5-x86_64.tar.gz

drwxr-xr-x. 9 7161 wheel 4.0K Jul 20 20:54 mysql-5.7.8-rc-linux-glibc2.5-x86_64

-rw-r--r--. 1 root root 588M Sep 17 12:35 mysql-5.7.8-rc-linux-glibc2.5-x86_64.tar.gz

[root@Master local]# cd mysql

[root@Master mysql]# ls -lah bin

total 1.3G

drwxr-xr-x. 2 7161 wheel 4.0K Jul 20 20:54 .

drwxr-xr-x. 9 7161 wheel 4.0K Jul 20 20:54 ..

-rwxr-xr-x. 1 7161 wheel 6.1M Jul 20 20:42 innochecksum

-rwxr-xr-x. 1 7161 wheel 8.6M Jul 20 20:42 myisamchk

-rwxr-xr-x. 1 7161 wheel 11M Jul 20 20:42 mysql_install_db

……..

-rwxr-xr-x. 1 7161 wheel 5.4M Jul 20 20:41 resolveip

-rwxr-xr-x. 1 7161 wheel 5.4M Jul 20 20:41 resolve_stack_dump

[root@Master mysql]# bin/mysql_install_db --user=mysql

2015-09-17 15:24:30 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize

2015-09-17 15:24:30 [ERROR] The data directory needs to be specified.

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

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

[root@Master mysql]# bin/mysql_install_db --user=mysql

2015-09-17 15:51:26 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize

2015-09-17 15:51:26 [ERROR] The data directory needs to be specified.

[root@Master mysql]# bin/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

2015-09-17 15:57:35 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize

2015-09-17 15:57:58 [WARNING] The bootstrap log isn't empty:

2015-09-17 15:57:58 [WARNING] mysqld:

OpenSSL 1.0.1e-fips 11 Feb 2013

server-cert.pem: OK

client-cert.pem: OK

[root@Master mysql]# bin/mysqld_safe --user=mysql &

[1] 46040

[root@Master mysql]# 150917 16:09:15 mysqld_safe Logging to '/usr/local/mysql/data/Master.err'.

150917 16:09:16 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

#MySQL 5.7 will auto generate a temporary password and stored in mysql_secret

[root@Master mysql]# find / -name '*mysql_secret'

/root/.mysql_secret

[root@Master mysql]# cat /root/.mysql_secret

# Password set for user 'root@localhost' at 2015-09-17 15:57:35

McTVpdK+Ugwd

[root@Master mysql]# mysql -uroot -p

Enter password:

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

Your MySQL connection id is 317

Server version: 5.7.8-rc-log

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;

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> alter user user() identified by '123123';

Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.04 sec)

5. Point the mysql57 datadir to old data folder mysql_56

6. Start mysql 5.7 service

[root@Master local]# service mysql start

Starting MySQL.... SUCCESS!

7. Run mysql_upgrade