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

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]# 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

fi

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

                fi

                ;;

        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
Copyright (c) 2000, 2015, 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;
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