2017-03-03

mysql slow log

慢查询日志输出格式种类

TABLE: 直接输出到数据库
FILE: 直接输出到物理磁盘文件

慢查询操作

//修改输出格式为table
mysql> set global log_output='TABLE';
mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
mysql> select * from mysql.slow_log;

//修改输出格式为file
mysql> set global log_output='FILE';
mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

//设置慢查询时间(超过时间输出)
mysql> set global long_query_time=10;
mysql> show variables like 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

//打开关闭慢查询功能
mysql> set global slow_query_log=1;
mysql> set global slow_query_log=0;
mysql> show variables like 'slow_query%';
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| slow_query_log      | OFF                              |
| slow_query_log_file | /mnt/data/mysql/3306/10-slow.log |
+---------------------+----------------------------------+

日志分析工具mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow
查看mysqldumpslow的帮助信息：

[root@DB-Server ~]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are
  --verbose    verbose
  --debug      debug
  --help       write this text to standard output
  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

说明:
-s,是表示按照何种方式排序:
   c: 访问计数
   l: 锁定时间
   r: 返回记录
   t: 查询时间
   al:平均锁定时间
   ar:平均返回记录数
   at:平均查询时间
-t, 是top n的意思,即为返回前面多少条的数据
-g, 后边可以写一个正则匹配模式，大小写不敏感的


列出返回记录集最多的10个SQL:
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

列出访问次数最多的10个SQL:
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

列出按照时间排序的前10条里面含有左连接的查询语句:
mysqldumpslow -s t -t 10 -g "left join" /database/mysql/mysql06_slow.log

2017-03-03

database►Mysql►mysql standard

mysql standard

1. 创建mysql用户

1	[root@10 app]# useradd -s /sbin/nologin mysql

2. 编译安装mysql

[root@192 ~]# wget http://cdn.mysql.com//Downloads/MySQL-5.6/mysql-5.6.35.tar.gz
[root@192 ~]# tar xzf mysql-5.6.35.tar.gz
[root@192 ~]# cd mysql-5.6.35
[root@192 mysql-5.6.35]# cmake \
> -DCMAKE_INSTALL_PREFIX=/mnt/app/mysql \
> -DWITH_MYISAM_STORAGE_ENGINE=1 \
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \
> -DENABLED_LOCAL_INFILE=1 \
> -DWITH_PARTITION_STORAGE_ENGINE=1 \
> -DEXTRA_CHARSETS=all \
> -DDEFAULT_CHARSET=utf8 \
> -DDEFAULT_COLLATION=utf8_general_ci \
> -DENABLE_DOWNLOADS=1
[root@192 mysql-5.6.35]# make
[root@192 mysql-5.6.35]# make install

cmake \
-DCMAKE_INSTALL_PREFIX=/mnt/app/mysql \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DENABLE_DOWNLOADS=1
make
make install

cmake ./
# -DCMAKE_INSTALL_PREFIX=/usr/local/mysql          \    #安装路径
# -DMYSQL_DATADIR=/usr/local/mysql/data            \    #数据文件存放位置
# -DSYSCONFDIR=/etc                                \    #my.cnf路径
# -DWITH_MYISAM_STORAGE_ENGINE=1                   \    #支持MyIASM引擎
# -DWITH_INNOBASE_STORAGE_ENGINE=1                 \    #支持InnoDB引擎
# -DWITH_MEMORY_STORAGE_ENGINE=1                   \    #支持Memory引擎
# -DWITH_READLINE=1                                \    #快捷键功能(我没用过)
# -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock               \    #连接数据库socket路径
# -DMYSQL_TCP_PORT=3306                            \    #端口
# -DENABLED_LOCAL_INFILE=1                         \    #允许从本地导入数据
# -DWITH_PARTITION_STORAGE_ENGINE=1                \    #安装支持数据库分区
# -DEXTRA_CHARSETS=all                             \    #安装所有的字符集
# -DDEFAULT_CHARSET=utf8                           \    #默认字符
# -DDEFAULT_COLLATION=utf8_general_ci

3. 设置环境变量

[root@10 mysql-5.6.35]# echo 'export MYSQL_BASE=/mnt/app/mysql' |tee /etc/profile.d/mysql.sh
[root@10 mysql-5.6.35]# echo 'export MYSQL_BIN=$MYSQL_BASE/bin' |tee -a /etc/profile.d/mysql.sh
[root@10 mysql-5.6.35]# echo 'export PATH=$MYSQL_BIN:$PATH'     |tee -a /etc/profile.d/mysql.sh
[root@10 mysql-5.6.35]# source /etc/profile

[root@10 mysql-5.6.35]# echo '/mnt/app/mysql/lib' | tee /etc/ld.so.conf.d/mysql.conf
[root@10 mysql-5.6.35]# ldconfig

echo 'export MYSQL_BASE=/mnt/app/mysql' |tee /etc/profile.d/mysql.sh
echo 'export MYSQL_BIN=$MYSQL_BASE/bin' |tee -a /etc/profile.d/mysql.sh
echo 'export PATH=$MYSQL_BIN:$PATH'     |tee -a /etc/profile.d/mysql.sh
source /etc/profile

echo '/mnt/app/mysql/lib' | tee /etc/ld.so.conf.d/mysql.conf
ldconfig

4. 创建数据和日志存放目录

[root@10 mysql-5.6.35]# mkdir -p /mnt/{data,log}/mysql
[root@10 mysql-5.6.35]# touch /mnt/log/mysql/mysqld.log
[root@10 mysql-5.6.35]# chown -R mysql.mysql /mnt/{data,log}/mysql

mkdir -p /mnt/{data,log}/mysql
touch /mnt/log/mysql/mysqld.log
chown -R mysql.mysql /mnt/{data,log}/mysql

5. 初始化数据库

[root@10 mysql-5.6.35]# chmod +x scripts/*
[root@10 mysql-5.6.35]# ./scripts/mysql_install_db --basedir=/mnt/app/mysql/ --datadir=/mnt/data/mysql --user=mysql

chmod +x scripts/*
./scripts/mysql_install_db --basedir=/mnt/app/mysql/ --datadir=/mnt/data/mysql --user=mysql

6. 编辑配置文件

[root@10 mysql-5.6.35]# mkdir -p /mnt/app/mysql/conf
[root@10 mysql-5.6.35]# cat > /mnt/app/mysql/conf/my.cnf <<EOF
[mysqld]
user    = mysql
port    = 3306
basedir = /mnt/app/mysql
datadir = /mnt/data/mysql
socket  = /mnt/data/mysql/mysql.sock
skip-name-resolve
default-storage-engine = InnoDB
innodb_file_per_table  = 1
character_set_server   = utf8
max_connections        = 5000
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/mnt/log/mysql/mysqld.log
pid-file =/mnt/data/mysql/mysqld.pid

[client]
default-character-set=utf8
socket=/mnt/data/mysql/mysql.sock

[mysql]
default-character-set=utf8
socket=/mnt/data/mysql/mysql.sock
EOF

7. 启动,关闭,连接服务

1
2
3

[root@10 mysql-5.6.35]# /mnt/app/mysql/bin/mysqld_safe --defaults-file=/mnt/app/mysql/conf/my.cnf &
[root@10 mysql-5.6.35]# /mnt/app/mysql/bin/mysqladmin -S /mnt/data/mysql/mysql.sock shutdown   
[root@10 mysql-5.6.35]# /mnt/app/mysql/bin/mysql -S /mnt/data/mysql/mysql.sock

8. mysql启动脚本

[root@10 mysql-5.6.35]# cat > /etc/init.d/mysql <<EOF
#!/bin/bash

if [ $# != 1 ];then
  echo "Please input:
/etc/init.d/$0 start
/etc/init.d/$0 stop
/etc/init.d/$0 status
"
  exit 0
fi

STATUS=$1
ROLE='mysql'
SQLAPP="/mnt/app/${ROLE}/bin/mysqld_safe"
SQLSHT="/mnt/app/${ROLE}/bin/mysqladmin"
SOCKET="/mnt/data/${ROLE}/mysql.sock"
COFIG="/mnt/app/${ROLE}/conf/my.cnf"


if [ $STATUS == "start" ];then
  ${SQLAPP} --defaults-file=${COFIG} &
  if [ $? == 0 ];then
    echo "$ROLE start success."
  else
    echo "$ROLE start fail."
  fi
fi

if [ $STATUS == "stop" ];then
  ${SQLSHT} -S ${SOCKET} shutdown
  if [ $? == 0 ];then
    echo "$ROLE stoped success."
  else
    echo "$ROLE stoped fail."
  fi
fi

if [ $STATUS == "kill" ];then
  PID=$(ps -ef|grep -w ${ROLE}|grep -v grep|grep -v bash|awk '{print $2}'|tr '\n' ' ')
  kill -9 $PID
  if [ $? == 0 ];then
    echo "$ROLE killed success."
  else
    echo "$ROLE killed fail."
  fi
fi


if [ $STATUS == "status" ];then
  NUM=$(ps -ef|grep -w ${ROLE}|grep root|grep -v grep|grep -v bash|awk '{print $2}'|wc -l)

  if [ $NUM == 0 ];then
    echo "$ROLE is stopped."
    exit 0
  fi

  if [ $NUM != 1 ];then
    echo "Please check $ROLE, There are $NUM processes."
  fi

  PID=$(ps -ef|grep -w ${ROLE}|grep -v grep|grep -v bash|awk '{print $2}'|tr '\n' ' ')
  echo "$ROLE is running. PID: $PID"
fi
EOF

[root@10 mysql-5.6.35]# chmod +x /etc/init.d/mysql
[root@10 mysql-5.6.35]# /etc/init.d/mysql start

[root@10 mysql-5.6.35]# echo '/etc/init.d/mysql start' |tee -a /etc/rc.local
[root@10 mysql-5.6.35]# chmod +x /etc/rc.d/rc.local

2017-03-03

database►Mysql►mysql master slave

mysql master slave

1. 创建mysql用户

1	[root@192 ~]# useradd -s /sbin/nologin mysql

2. 编译安装mysql

[root@192 ~]# wget http://cdn.mysql.com//Downloads/MySQL-5.6/mysql-5.6.35.tar.gz
[root@192 ~]# tar xzf mysql-5.6.35.tar.gz
[root@192 ~]# cd mysql-5.6.35
[root@192 mysql-5.6.35]# cmake \
> -DCMAKE_INSTALL_PREFIX=/mnt/app/mysql \
> -DWITH_MYISAM_STORAGE_ENGINE=1 \
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \
> -DENABLED_LOCAL_INFILE=1 \
> -DWITH_PARTITION_STORAGE_ENGINE=1 \
> -DEXTRA_CHARSETS=all \
> -DDEFAULT_CHARSET=utf8 \
> -DDEFAULT_COLLATION=utf8_general_ci \
> -DENABLE_DOWNLOADS=1
[root@192 mysql-5.6.35]# make
[root@192 mysql-5.6.35]# make install

cmake ./
# -DCMAKE_INSTALL_PREFIX=/usr/local/mysql          \    #安装路径
# -DMYSQL_DATADIR=/usr/local/mysql/data            \    #数据文件存放位置
# -DSYSCONFDIR=/etc                                \    #my.cnf路径
# -DWITH_MYISAM_STORAGE_ENGINE=1                   \    #支持MyIASM引擎
# -DWITH_INNOBASE_STORAGE_ENGINE=1                 \    #支持InnoDB引擎
# -DWITH_MEMORY_STORAGE_ENGINE=1                   \    #支持Memory引擎
# -DWITH_READLINE=1                                \    #快捷键功能(我没用过)
# -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock               \    #连接数据库socket路径
# -DMYSQL_TCP_PORT=3306                            \    #端口
# -DENABLED_LOCAL_INFILE=1                         \    #允许从本地导入数据
# -DWITH_PARTITION_STORAGE_ENGINE=1                \    #安装支持数据库分区
# -DEXTRA_CHARSETS=all                             \    #安装所有的字符集
# -DDEFAULT_CHARSET=utf8                           \    #默认字符
# -DDEFAULT_COLLATION=utf8_general_ci

3. 设置环境变量

[root@10 mysql-5.6.35]# echo 'export MYSQL_BASE=/mnt/app/mysql' |tee /etc/profile.d/mysql.sh
[root@10 mysql-5.6.35]# echo 'export MYSQL_BIN=$MYSQL_BASE/bin' |tee -a /etc/profile.d/mysql.sh
[root@10 mysql-5.6.35]# echo 'export PATH=$MYSQL_BIN:$PATH'     |tee -a /etc/profile.d/mysql.sh
[root@10 mysql-5.6.35]# source /etc/profile

4. 创建数据和日志存放目录

[root@192 mysql-5.6.35]# mkdir -p /mnt/data/mysql/{3306,3307}
[root@192 mysql-5.6.35]# mkdir -p /mnt/log/mysql/{3306,3307}
[root@192 mysql-5.6.35]# touch /mnt/log/mysql/{3306,3307}/mysqld.log
[root@192 mysql-5.6.35]# chown -R mysql.mysql /mnt/{data,log}/mysql

5. 初始化数据库

1
2
3

[root@192 mysql-5.6.35]# chmod +x scripts/*
[root@192 mysql-5.6.35]# ./scripts/mysql_install_db --basedir=/mnt/app/mysql/ --datadir=/mnt/data/mysql/3306/ --user=mysql
[root@192 mysql-5.6.35]# ./scripts/mysql_install_db --basedir=/mnt/app/mysql/ --datadir=/mnt/data/mysql/3307/ --user=mysql

6.启动,关闭,连接

1
2
3

[root@192 mysql-5.6.35]# /mnt/app/mysql/bin/mysqld_safe --defaults-file=/mnt/app/mysql/conf/my_3306.cnf &
[root@192 mysql-5.6.35]# /mnt/app/mysql/bin/mysqladmin -S /mnt/data/mysql/3306/mysql.sock shutdown
[root@192 mysql-5.6.35]# /mnt/app/mysql/bin/mysql -S /mnt/data/mysql/3306/mysql.sock

6. Master 修改配置文件

[root@192 mysql-5.6.35]# cat /mnt/app/mysql/conf/my_3306.cnf
[mysqld]
user    = mysql
port    = 3306
basedir = /mnt/app/mysql
datadir = /mnt/data/mysql/3306
socket  = /mnt/data/mysql/3306/mysql.sock
default-storage-engine = InnoDB
character_set_server   = utf8
max_connections        = 2000
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

server-id              = 111
log-bin                = /mnt/log/mysql/3306/mysql-bin
expire_logs_days       = 7
binlog_format          = MIXED
max_binlog_size        = 100m
binlog_cache_size      = 4m
max_binlog_cache_size  = 512m

innodb_file_per_table  = 1

[mysqld_safe]
log-error=/mnt/log/mysql/3306/mysqld.log
pid-file =/mnt/app/mysql/mysqld.3306.pid

[client]
default-character-set=utf8
socket=/mnt/data/mysql/3306/mysql.sock

[mysql]
default-character-set=utf8
socket=/mnt/data/mysql/3306/mysql.sock

6. Master 启动,停止,连接服务

1
2
3

[root@192 mysql-5.6.35]# /mnt/app/mysql/bin/mysqld_safe --defaults-file=/mnt/app/mysql/conf/my_3306.cnf &
[root@192 mysql-5.6.35]# /mnt/app/mysql/bin/mysqladmin -S /mnt/data/mysql/3306/mysql.sock shutdown
[root@192 mysql-5.6.35]# /mnt/app/mysql/bin/mysql -S /mnt/data/mysql/3306/mysql.sock

7. Slave 修改配置文件

[root@192 mysql-5.6.35]# cat /mnt/app/mysql/conf/my_3306.cnf
[mysqld]
user    = mysql
port    = 3306
basedir = /mnt/app/mysql
datadir = /mnt/data/mysql/3306
socket  = /mnt/data/mysql/3306/mysql.sock
default-storage-engine = InnoDB
character_set_server   = utf8
max_connections        = 2000
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

server-id              = 108
log-bin                = /mnt/log/mysql/3306/mysql-bin
expire_logs_days       = 7
binlog_format          = MIXED
max_binlog_size        = 100m
binlog_cache_size      = 4m
max_binlog_cache_size  = 512m

master-host            = 192.168.1.111
master-user            = repl
master-password        = repl123
master-port            = 3306
master-connect-retry   = 60

innodb_file_per_table  = 1

[mysqld_safe]
log-error=/mnt/log/mysql/3306/mysqld.log
pid-file =/mnt/data/mysql/3306/mysqld.pid

[client]
default-character-set=utf8
socket=/mnt/data/mysql/3306/mysql.sock

[mysql]
default-character-set=utf8
socket=/mnt/data/mysql/3306/mysql.sock

8. Slave 启动,停止,连接服务

1
2
3

[root@192 mysql-5.6.35]# /mnt/app/mysql/bin/mysqld_safe --defaults-file=/mnt/app/mysql/conf/my_3306.cnf &
[root@192 mysql-5.6.35]# /mnt/app/mysql/bin/mysqladmin -S /mnt/data/mysql/3306/mysql.sock shutdown
[root@192 mysql-5.6.35]# /mnt/app/mysql/bin/mysql -S /mnt/data/mysql/3306/mysql.sock

9. Master 创建同步用户

1
2
3

[root@192 mysql-5.6.35]# /mnt/app/mysql/bin/mysql -S /mnt/data/mysql/3306/mysql.sock
mysql> GRANT REPLICATION SLAVE ON *.* to 'repl'@'%' identified by 'repl123';
mysql> flush privileges;

10. Master 锁表,查看binlog ID

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      397 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

11. Master 打开另一个终端进行备份数据库

1	[root@192 mysql-5.6.35]# mysqldump --all-databases --master-data > dbdump.db

12. Master 备份完数据库后,进行表解锁

1	mysql> UNLOCK TABLES;

13. Slave 将在Master上备份的数据库,在Slave服务器上还原

1	[root@192 mysql-5.6.35]# /mnt/app/mysql/bin/mysql -S /mnt/data/mysql/3306/mysql.sock < fulldb.dump

13. Slave 数据库还原后,登录到数据库,与Master进行binlog同步

[root@192 mysql-5.6.35]# /mnt/app/mysql/bin/mysql -S /mnt/data/mysql/3306/mysql.sock
mysql> change master to master_host='192.168.1.111',master_port=3306,master_user='repl',master_password='repl123',master_log_file='mysql-bin.000001',master_log_pos=397;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.111
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 397             //同步读取二进制日志的位置,大于等于Exec_Master_Log_Pos
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes   //必须是YES
            Slave_SQL_Running: Yes   //必须是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: 397
              Relay_Log_Space: 457
              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: 0
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: 111
                  Master_UUID: a2207dde-caaf-11e6-bc89-080027770ec0
             Master_Info_File: /mnt/data/mysql/3306/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified

14. Master 登录mysql,进行验证主从同步是否正常

2017-03-02

database►Postgresql►postgresql tablespace database schema table user role relation

postgresql tablespace database schema table user role relation

1. 用户和角色的关系

1.创建角色
postgres=# create role irole PASSWORD 'iuser' ;                      
CREATE ROLE

2.创建用户
postgres=# create user iuser PASSWORD 'iuser';        
CREATE ROLE

3.使用角色登录
[smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d postgres -U irole
psql: FATAL:  role "irole" is not permitted to log in

4.使用用户登录
[smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d postgres -U iuser
psql (9.6.1)
Type "help" for help.
postgres=> \q

由上,我们可以看出用户和角色的差别在于：用户有登录权限,角色没有登录权限

2. 数据库和模式的关系

1. 创建数据库
[smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d postgres
psql (9.6.1)
Type "help" for help.
postgres=# create database mydb;
CREATE DATABASE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 mydb      | smallasa | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | smallasa | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | smallasa | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/smallasa          +
           |          |          |             |             | smallasa=CTc/smallasa
 template1 | smallasa | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/smallasa          +
           |          |          |             |             | smallasa=CTc/smallasa
(4 rows)
postgres=# \q

2.对用户和角色授权
postgres=# grant all privileges on database mydb to iuser;          
GRANT
postgres=# grant all privileges on database mydb to irole;
GRANT

3.使用用户登录数据库mydb,查看当前的模式
[smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d mydb -U iuser
psql (9.6.1)
Type "help" for help.
mydb=> \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | smallasa
(1 row)

说明: 数据库默认有一个public模式

4.在mydb中创建一张表,并查看表的映射关系
mydb=> CREATE TABLE test(id integer not null);
CREATE TABLE
mydb=> \d
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | test | table | iuser
(1 row)

说明:默认表创建在了public模式中

5.创建一个新模式
mydb=> create schema myschema authorization iuser;
CREATE SCHEMA

mydb=> create schema mydb authorization irole;      
ERROR:  must be member of role "irole"

说明: 我们创建一个模式myschema,并给它授权iuser的权限; 上面创建模式时为它授权irole的权限失败,是因为当前登录用户为iuser,iuser用户没有在irole中
重要说明: role相当于一个组,理想创建方式为:首先创建一个role,其次对role授权,然后创建N个用户,将用户加入到role.此时加入到role的用户拥有role的权限

6.在指定的模式内创建表
mydb=> create table myschema.test(id integer not null);   
CREATE TABLE

7.切换到创建的新模式
mydb=> set search_path to myschema;
SET
mydb=> show search_path;           
 search_path
-------------
 myschema
(1 row)
说明,如果不切换到新模式下,无法看到表的列表信息

8.查看列表信息
mydb=> \dt
        List of relations
  Schema  | Name | Type  | Owner
----------+------+-------+-------
 myschema | test | table | iuser
(1 row)

9.补充一:删除schema中的表
mydb=> drop table myschema.test;
or:
mydb=> set search_path to myschema;
mydb=> drop table test;

10.补充二: 删除schema
mydb=> drop schema myschema;   //删除空的schema
or
mydb=> drop schema myschema cascade;  //强制删除非空schema

综上,模式是数据库的逻辑分割.一个数据库可以有多个模式.数据库内的对象是被创建在模式内的

注意: 用户登录到系统,连接到一个数据库后,是通过该数据库的search_path来寻找schema的搜索顺序,可以通过命令”show search_path;”.具体的顺序,也可以通过”SET search_path TO ‘schema_name’” 来修改顺序.

3. 表空间和数据库关系

数据库创建语句”CREATE DATABASE dbname”默认的数据库所有者是当前创建数据库的角色,默认的表空间是系统的默认表空间–”pg_default”
在PostgreSQL中,数据的创建是通过克隆数据库模板来实现的,这与SQL SERVER是同样的机制
由于”CREATE DATABASE dbname”并没有指明数据库模板,所以系统将默认克隆template1数据库,得到新的数据库dbname.而template1数据库的默认表空间是pg_default,这个表空间是在数据库初始化时创建的,所以所有template1中的对象将被同步克隆到新的数据库中.
相对完整的语法应该是这样的：”CREATE DATABASE dbname OWNER kanon TEMPLATE template1 TABLESPACE tablespacename;”

1.创建表空间存放位置
[smallasa@localhost ~]$ mkdir /mnt/data/pgsql/tablespace

2.创建表空间
[smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d postgres
postgres=# create tablespace tspace owner smallasa location '/mnt/data/pgsql/tablespace';

3.创建数据库
postgres=# create database dbname template template1 owner smallasa tablespace tspace;

总结

表空间是一个存储区域,在一个表空间中可以存储多个数据库,尽管PostgreSQL不建议这么做,但我们这么做完全可行
一个数据库并不知直接存储表结构等对象的,而是在数据库中逻辑创建了至少一个模式,在模式中创建了表等对象,将不同的模式指派该不同的角色,可以实现权限分离,又可以通过授权,实现模式间对象的共享,并且,还有一个特点就是：public模式可以存储大家都需要访问的对象.

注意:
既然一个表在创建的时候可以指定表空间,那么,是否可以给一个表指定它所在的数据库表空间之外的表空间呢？
完全可以,但这不是违背了表属于模式,而模式属于数据库,数据库最终存在于指定表空间这个网的模型了吗?是的,看上去这确实是不合常理的,但这么做又是有它的道理的,而且现实中,我们往往需要这么做:将表的数据存在一个较慢的磁盘上的表空间，而将表的索引存在于一个快速的磁盘上的表空间.但我们再查看表所属的模式还是没变的,它依然属于指定的模式.所以这并不违反常理.实际上,PostgreSQL并没有限制一张表必须属于某个特定的表空间,我们之所以会这么认为,是因为在关系递进时,偷换了一个概念:模式是逻辑存在的,它不受表空间的限制!!!

2017-03-02

database►Postgresql►postgresql book use

postgresql book use

1. 连接数据库

[root@localhost ~]# su - smallasa
[smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d postgres
psql (9.6.1)
Type "help" for help.
postgres=# select version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
(1 row)
postgres=# \q

说明:

连接PG数据库时,必须先切换到PG启动用户,此时是”su - smallasa”
-d 表示需要连接的数据库
\q 表示退出PG控制台

2. 创建数据库

[smallasa@localhost ~]$ /mnt/app/pgsql/bin/createdb mydb

[smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d mydb
psql (9.6.1)
Type "help" for help.
mydb=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 mydb      | smallasa | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | smallasa | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | smallasa | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/smallasa          +
           |          |          |             |             | smallasa=CTc/smallasa
 template1 | smallasa | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/smallasa          +
           |          |          |             |             | smallasa=CTc/smallasa
(4 rows)
mydb=# \q

说明:

\l 表示显示数据库列表

3. 删除数据库

[smallasa@localhost ~]$ /mnt/app/pgsql/bin/dropdb mydb

[smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d mydb
psql: FATAL:  database "mydb" does not exist

[smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d postgres
psql (9.6.1)
Type "help" for help.
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | smallasa | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | smallasa | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/smallasa          +
           |          |          |             |             | smallasa=CTc/smallasa
 template1 | smallasa | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/smallasa          +
           |          |          |             |             | smallasa=CTc/smallasa
(3 rows)
postgres=# \q

2017-03-02

database►Postgresql►postgresql standard

postgresql standard

1. 源码安装

[root@localhost app]# tar xzf postgresql-9.6.1.tar.gz
[root@localhost app]# cd postgresql-9.6.1
[root@localhost postgresql-9.6.1]# ./configure --prefix=/mnt/app/pgsql
[root@localhost postgresql-9.6.1]# make
[root@localhost postgresql-9.6.1]# make install

2. 插件安装

1 2	[root@localhost postgresql-9.6.1]# cd contrib/ [root@localhost contrib]# make install

补充: 安装一个插件

1 2	[root@localhost postgresql-9.6.1]# cd contrib/pgcrypto/ [root@localhost pgcrypto]# mkdir install

3. 环境变量设置

[root@localhost postgresql-9.6.1]# echo 'export PGSQL_HOME=/mnt/app/pgsql' | tee /etc/profile.d/postgresql.sh
[root@localhost postgresql-9.6.1]# echo 'export PGSQL_BIN=${PGSQL_HOME}/bin' | tee -a /etc/profile.d/postgresql.sh                   
[root@localhost postgresql-9.6.1]# echo 'export PATH=${PGSQL_BIN}:$PATH' | tee -a /etc/profile.d/postgresql.sh                        
[root@localhost postgresql-9.6.1]# source /etc/profile

4. 创建数据和日志存放位置

1 2	[root@localhost postgresql-9.6.1]# mkdir -p /mnt/{data,log}/pgsql [root@localhost postgresql-9.6.1]# chown -R smallasa.smallasa /mnt/{data,log}/pgsql

5. 初始化数据库

1 2	[root@localhost postgresql-9.6.1]# su - smallasa [smallasa@localhost ~]$ /mnt/app/pgsql/bin/initdb -D /mnt/data/pgsql/

6. 设置访问权限

[smallasa@localhost ~]$ vim /mnt/data/pgsql/pg_hba.conf
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
host    all             all             0.0.0.0/0               md5

7. 修改配置文件

[smallasa@localhost ~]$ vim /mnt/data/pgsql/postgresql.conf
listen_addresses = '\*'
port = 5432
max_connections = 2000

补充:设置PG连接数,需要修改内核参数kernel.sem

[root@localhost postgresql-9.6.1]# cat >> /etc/sysctl.conf <<EOF
> kernel.sem = 50100 128256000 50100 2560
> EOF
[root@localhost postgresql-9.6.1]# sysctl -p

8. 启动服务

1	[smallasa@localhost ~]$ /mnt/app/pgsql/bin/pg_ctl -D /mnt/data/pgsql/ -l /mnt/log/pgsql/pgsql.log start

9. 登录数据库进行验证

1	[smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d postgres

2017-03-02

database►Postgresql►postgresql master/slave

postgresql master slave

1. 源码安装

[root@localhost app]# tar xzf postgresql-9.6.1.tar.gz
[root@localhost app]# cd postgresql-9.6.1
[root@localhost postgresql-9.6.1]# ./configure --prefix=/mnt/app/pgsql
[root@localhost postgresql-9.6.1]# make
[root@localhost postgresql-9.6.1]# make install

2. 插件安装

1 2	[root@localhost postgresql-9.6.1]# cd contrib/ [root@localhost contrib]# make install

补充: 安装一个插件

1 2	[root@localhost postgresql-9.6.1]# cd contrib/pgcrypto/ [root@localhost pgcrypto]# mkdir install

3. 环境变量设置

[root@localhost postgresql-9.6.1]# echo 'export PGSQL_HOME=/mnt/app/pgsql' | tee /etc/profile.d/postgresql.sh
[root@localhost postgresql-9.6.1]# echo 'export PGSQL_BIN=${PGSQL_HOME}/bin' | tee -a /etc/profile.d/postgresql.sh                   
[root@localhost postgresql-9.6.1]# echo 'export PATH=${PGSQL_BIN}:$PATH' | tee -a /etc/profile.d/postgresql.sh                        
[root@localhost postgresql-9.6.1]# source /etc/profile

4. 创建数据和日志存放位置

1 2	[root@localhost postgresql-9.6.1]# mkdir -p /mnt/{data,log}/pgsql [root@localhost postgresql-9.6.1]# chown -R smallasa.smallasa /mnt/{data,log}/pgsql

5. PG master服务器上操作

1. 初始化数据库

1 2	[root@localhost postgresql-9.6.1]# su - smallasa [smallasa@localhost ~]$ /mnt/app/pgsql/bin/initdb -D /mnt/data/pgsql/

2. 设置访问权限

[smallasa@localhost ~]$ vim /mnt/data/pgsql/pg_hba.conf
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
host    replication     repl            192.168.0.0/16          md5     =>追加语句
host    all             all             192.168.0.0/16          md5     =>其它用户访问策略

3. 修改配置文件

[smallasa@localhost ~]$ vim /mnt/data/pgsql/postgresql.conf
listen_addresses = '\*'
port = 5432
wal_level = replica
max_wal_senders = 5
wal_keep_segments = 64
synchronous_standby_names = '\*'
archive_mode = on
archive_command = 'DIR=/mnt/log/pgsql/archive/$(date +%Y%m%d);test ! -d $DIR && mkdir -p $DIR;test ! -f $DIR/%f && cp %p $DIR/%f'

补充:设置PG连接数,需要修改内核参数kernel.sem

[root@localhost postgresql-9.6.1]# cat >> /etc/sysctl.conf <<EOF
> kernel.sem = 50100 128256000 50100 2560
> EOF
[root@localhost postgresql-9.6.1]# sysctl -p

4. 启动服务

1	[smallasa@localhost ~]$ /mnt/app/pgsql/bin/pg_ctl -D /mnt/data/pgsql/ -l /mnt/log/pgsql/pgsql.log start

5. 登录PG数据库,创建同步账号

[smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d postgres
postgres=# create user repl superuser password 'klip2[gE0_%y';
postgres-# \q
[smallasa@localhost ~]$ /mnt/app/pgsql/bin/pg_ctl -D /mnt/data/pgsql/ -l /mnt/log/pgsql/pgsql.log restart

5. PG slave服务器上操作

1. 同步master数据

[root@localhost postgresql-9.6.1]# su - smallasa
[smallasa@localhost ~]$ /mnt/app/pgsql/bin/pg_basebackup -F p --progress -R -D /mnt/data/pgsql/ -h 192.168.18.226 -p 5432 -U repl --password
Password:
22824/22824 kB (100%), 1/1 tablespace
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived

2. 修改recovery配置文件

[smallasa@localhost ~]$ vim /mnt/data/pgsql/recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=repl password=klip2[gE0_%y host=192.168.18.226 port=5432 sslmode=disable sslcompression=1'
recovery_target_timeline = 'latest'

3. 修改配置文件

[smallasa@localhost ~]$ vim /mnt/data/pgsql/postgresql.conf
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 1s
hot_standby_feedback = on

4. 启动服务

1	[smallasa@localhost ~]$ /mnt/app/pgsql/bin/pg_ctl -D /mnt/data/pgsql/ -l /mnt/log/pgsql/pgsql.log start

6. 登录数据库,创建数据库进行主从验证

1	[smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d postgres

2017-03-02

system service►CentOS►Base env

Linux base env

1. 查看系统版本

1 2	[root@localhost ~]# cat /etc/redhat-release CentOS Linux release 7.0.1406 (Core)

2. 设置主机名

1
2
3

[root@localhost ~]# hostname smallasa && echo smallasa |tee /etc/hostname
[root@localhost ~]# $SHELL
[root@smallasa ~]# echo '192.168.13.188 smallasa' |tee -a /etc/hosts

3. 硬盘格式化

1
2
3

[root@smallasa ~]# mkfs.xfs /dev/vdb
[root@smallasa ~]# echo '/dev/vdb /mnt xfs defaults 0 0' | tee -a /etc/fstab
[root@smallasa ~]# mount -a

4. 设置打开最大文件数

[root@smallasa ~]# echo '* - nproc  65535' | tee -a /etc/security/limits.conf
[root@smallasa ~]# echo '* - nofile 65535' | tee -a /etc/security/limits.conf
[root@smallasa ~]# ls /etc/security/limits.d/*|xargs rm -f

echo '* - nproc  65535' | tee -a /etc/security/limits.conf
echo '* - nofile 65535' | tee -a /etc/security/limits.conf
ls /etc/security/limits.d/*|xargs rm -f

5. 修改yum源

[root@smallasa ~]# mkdir /etc/yum.repos.d/backup && mv /etc/yum.repos.d/{*,backup}
[root@smallasa ~]# curl -o /etc/yum.repos.d/epel-7.repo http://mirrors.aliyun.com/repo/epel-7.repo
[root@smallasa ~]# curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
[root@smallasa ~]# curl -O https://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-7
[root@smallasa ~]# rpm --import RPM-GPG-KEY-CentOS-7
[root@smallasa ~]# rm -f RPM-GPG-KEY-CentOS-7
[root@smallasa ~]# yum clean all
[root@smallasa ~]# yum makecache

6. 安装基础软件库和常用软件包

//安装基础软件包(ALL)
CentOS6:
[root@[ALL] ~]# yum -y groupinstall "Development Tools"
[root@[ALL] ~]# yum -y install \
cmake \
bison-devel\
bzip2 bzip2-devel bzip2-libs \
zlib zlib-devel \
openssl openssl-devel openssl-static openssl-static \
pcre pcre-devel pcre-static \
ncurses ncurses-devel ncurses-libs ncurses-static \
bzip2 bzip2-devel bzip2-libs \
openldap openldap-devel \
readline readline-devel readline-static \
libssh2 libssh2-devel \
unixODBC unixODBC-devel \
sqlite sqlite-devel \
tcl tcl-devel \
perl-Digest-SHA1 \
python-libs python-devel python-pip python-crypto \
perl-libs \
GeoIP GeoIP-devel \
gperftools gperftools-devel gperftools-libs \
libatomic_ops-devel \
gtest gtest-devel \
gdk-pixbuf2 gdk-pixbuf2-devel \
libffi libffi-devel \
libcurl libcurl-devel \
http-parser http-parser-devel
[root@[ALL] ~]# yum -y install bash-completion fop lftp ntp ntpdate vim wget telnet dstat tree lrzsz net-tools nmap-ncat nmap sysstat dmidecode bc

CentOS7:
[root@[ALL] ~]# yum -y groupinstall "Development Tools"
[root@[ALL] ~]# yum -y install \
make cmake \
bison-devel \
bzip2-devel \
zlib zlib-devel \
openssl openssl-devel openssl-libs openssl-static \
pcre pcre-devel pcre-static \
ncurses ncurses-devel ncurses-libs \
curl-devel \
expat-devel \
gettext-devel \
openldap openldap-devel \
readline readline-devel readline-static \
libssh2 libssh2-devel \
unixODBC unixODBC-devel \
sqlite sqlite-devel \
tcl tcl-devel \
perl-Digest-SHA1 \
python-libs python-devel python2-pip python-crypto \
perl-libs \
perl-ExtUtils-MakeMaker \
GeoIP GeoIP-devel \
gperftools gperftools-devel gperftools-libs \
libatomic_ops-devel \
gtest gtest-devel \
gdk-pixbuf2 gdk-pixbuf2-deve \
libffi libffi-devel \
libcurl libcurl-devel \
http-parser http-parser-devel

[root@[ALL] ~]# yum -y install bash-completion fop lftp ntp ntpdate vim wget telnet dstat tree lrzsz net-tools nmap-ncat nmap sysstat dmidecode bc

7. 关闭selinux

[root@smallasa ~]# setenforce 0
[root@smallasa ~]# sed -i s/'SELINUX=enforcing'/'SELINUX=disabled'/g /etc/selinux/config

setenforce 0
sed -i s/'SELINUX=enforcing'/'SELINUX=disabled'/g /etc/selinux/config

8. 关闭防火墙

CentOS6:
[root@smallasa ~]# /etc/init.d/iptables stop && chkconfig iptables off

CentOS7:
[root@smallasa ~]# systemctl stop firewalld && systemctl disable firewalld

9. 设置系统时区

[root@smallasa ~]# [ -f /etc/localtime ] && cp -f /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
[root@smallasa ~]# [ -f /etc/sysconfig/clock ] && echo 'ZONE="Asia/Shanghai"' | tee /etc/sysconfig/clock
[root@smallasa ~]# [ -f /etc/timezone ] && echo 'Asia/Shanghai' | tee /etc/timezone
[root@smallasa ~]# [ -f /etc/sysconfig/ntpd ] && echo 'SYNC_HWCLOCK=yes' | tee -a /etc/sysconfig/ntpd
[root@smallasa ~]# ntpdate cn.pool.ntp.org
[root@smallasa ~]# cp -f /etc/{ntp.conf,ntp.conf.bak}
[root@smallasa ~]# cat > /etc/ntp.conf <<EOF
> driftfile /var/lib/ntp/drift
> restrict default nomodify notrap nopeer noquery
> restrict 127.0.0.1
> restrict ::1
> server cn.pool.ntp.org prefer
> server 0.centos.pool.ntp.org iburst
> server 1.centos.pool.ntp.org iburst
> server 2.centos.pool.ntp.org iburst
> server 3.centos.pool.ntp.org iburst
> includefile /etc/ntp/crypto/pw
> keys /etc/ntp/keys
> disable monitor
> EOF
[root@smallasa ~]# cp -f /etc/ntp/{step-tickers,step-tickers.bak}
[root@smallasa ~]# cat > /etc/ntp/step-tickers <<EOF
> cn.pool.ntp.org
> 0.centos.pool.ntp.org
> 1.centos.pool.ntp.org
> 2.centos.pool.ntp.org
> 3.centos.pool.ntp.org
> EOF

CentOS6:
[root@smallasa ~]# /etc/init.d/ntpd start && chkconfig ntpd on

CentOS7:
[root@smallasa ~]# systemctl start ntpd && systemctl enable ntpd


[ -f /etc/localtime ] && cp -f /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
[ -f /etc/sysconfig/clock ] && echo 'ZONE="Asia/Shanghai"' | tee /etc/sysconfig/clock
[ -f /etc/timezone ] && echo 'Asia/Shanghai' | tee /etc/timezone
[ -f /etc/sysconfig/ntpd ] && echo 'SYNC_HWCLOCK=yes' | tee -a /etc/sysconfig/ntpd
ntpdate cn.pool.ntp.org
cp -f /etc/{ntp.conf,ntp.conf.bak}
cat > /etc/ntp.conf <<EOF
driftfile /var/lib/ntp/drift
restrict default nomodify notrap nopeer noquery
restrict 127.0.0.1
restrict ::1
server cn.pool.ntp.org prefer
server 0.centos.pool.ntp.org iburst
server 1.centos.pool.ntp.org iburst
server 2.centos.pool.ntp.org iburst
server 3.centos.pool.ntp.org iburst
includefile /etc/ntp/crypto/pw
keys /etc/ntp/keys
disable monitor
EOF
cp -f /etc/ntp/{step-tickers,step-tickers.bak}
cat > /etc/ntp/step-tickers <<EOF
cn.pool.ntp.org
0.centos.pool.ntp.org
1.centos.pool.ntp.org
2.centos.pool.ntp.org
3.centos.pool.ntp.org
EOF

/etc/init.d/ntpd start && chkconfig ntpd on
systemctl start ntpd && systemctl enable ntpd

//此种方法已经不被推荐使用
[root@smallasa ~]# systemctl stop ntpd
[root@smallasa ~]# systemctl disable ntpd
[root@smallasa ~]# echo '1 1 * * *     /usr/sbin/ntpdate -s cn.pool.ntp.org' | tee /var/spool/cron/root

修改系统python pip源

[root@smallasa ~]# mkdir ~/.pip
[root@smallasa ~]# cat > ~/.pip/pip.conf <<EOF
> [global]
> trusted-host=mirrors.aliyun.com
> index-url=http://mirrors.aliyun.com/pypi/simple/
> [list]
> format=columns
> EOF
[root@smallasa ~]# pip install --upgrade pip
[root@smallasa ~]# python -V
Python 2.7.5

mkdir ~/.pip
cat > ~/.pip/pip.conf <<EOF
[global]
trusted-host=mirrors.aliyun.com
index-url=http://mirrors.aliyun.com/pypi/simple/
[list]
format=columns
EOF

设置开机启动文件权限

1
2
3

[root@smallasa ~]# chmod +x /etc/rc.d/rc.local

chmod +x /etc/rc.d/rc.local

创建常用目录

1
2
3

[root@smallasa ~]# mkdir -p /mnt/{app,data,log,web,ops/{app,data,cron}}

mkdir -p /mnt/{app,data,log,web,ops/{app,data,cron}}

创建一个普通用户用于启动服务

1 2	[root@localhost ~]# useradd smallasa [root@localhost ~]# echo 'smallasa' \| passwd --stdin smallasa

关闭IPv6

[root@localhost ~]# echo 'net.ipv6.conf.all.disable_ipv6 = 1'|tee -a /etc/sysctl.conf
[root@localhost ~]# sysctl -p

[root@localhost ~]# vim /etc/hosts
#::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

关闭swap

1 2	[root@localhost ~]# echo 'vm.swappiness=0' \|tee -a /etc/sysctl.conf [root@localhost ~]# sysctl -p

开启DNS缓存

[root@localhost ~]# yum -y install nscd
[root@localhost ~]# cat /etc/nscd.conf
#
# /etc/nscd.conf
#
# An example Name Service Cache config file.  This file is needed by nscd.
#
# Legal entries are:
#
#       logfile                 <file>
#       debug-level             <level>
#       threads                 <initial #threads to use>
#       max-threads             <maximum #threads to use>
#       server-user             <user to run server as instead of root>
#               server-user is ignored if nscd is started with -S parameters
#       stat-user               <user who is allowed to request statistics>
#       reload-count            unlimited|<number>
#       paranoia                <yes|no>
#       restart-interval        <time in seconds>
#
#       enable-cache            <service> <yes|no>
#       positive-time-to-live   <service> <time in seconds>
#       negative-time-to-live   <service> <time in seconds>
#       suggested-size          <service> <prime number>
#       check-files             <service> <yes|no>
#       persistent              <service> <yes|no>
#       shared                  <service> <yes|no>
#       max-db-size             <service> <number bytes>
#       auto-propagate          <service> <yes|no>
#
# Currently supported cache names (services): passwd, group, hosts, services
#


#       logfile                 /var/log/nscd.log
#       threads                 4
#       max-threads             32
        server-user             nscd
#       stat-user               somebody
        debug-level             0
#       reload-count            5
        paranoia                no
#       restart-interval        3600

        enable-cache            passwd          yes
        positive-time-to-live   passwd          600
        negative-time-to-live   passwd          20
        suggested-size          passwd          211
        check-files             passwd          yes
        persistent              passwd          yes
        shared                  passwd          yes
        max-db-size             passwd          33554432
        auto-propagate          passwd          yes

        enable-cache            group           yes
        positive-time-to-live   group           3600
        negative-time-to-live   group           60
        suggested-size          group           211
        check-files             group           yes
        persistent              group           yes
        shared                  group           yes
        max-db-size             group           33554432
        auto-propagate          group           yes

        enable-cache            hosts           yes
        positive-time-to-live   hosts           3600
        negative-time-to-live   hosts           20
        suggested-size          hosts           211
        check-files             hosts           yes
        persistent              hosts           yes
        shared                  hosts           yes
        max-db-size             hosts           33554432

        enable-cache            services        yes
        positive-time-to-live   services        28800
        negative-time-to-live   services        20
        suggested-size          services        211
        check-files             services        yes
        persistent              services        yes
        shared                  services        yes
        max-db-size             services        33554432

        enable-cache            netgroup        yes
        positive-time-to-live   netgroup        28800
        negative-time-to-live   netgroup        20
        suggested-size          netgroup        211
        check-files             netgroup        yes
        persistent              netgroup        yes
        shared                  netgroup        yes
        max-db-size             netgroup        33554432
[root@localhost ~]# systemctl start nscd
[root@localhost ~]# systemctl enable nscd

[root@localhost ~]# nscd -g 查看缓存

2017-02-28

system service►vsftpd►vsftpd info

vsftpd info

FTP是什么?

FTP Transfer Protocol 是文件传输协议的缩写,在RFC 959中具体说明

FTP会话时包含了两个通道:**一个叫控制通道,一个叫数据通道**
* 控制通道
  控制通道是和FTP服务器进行沟通的通道,连接FTP,发送FTP指令都是通过控制通道来完成的
* 数据通道
  数据通道是和FTP服务器进行文件传输或者列表的通道

FTP协议中,控制连接均有客户端发起,而数据连接有两种工作方式:**PORT方式和PASV方式**
1. PORT模式(主动方式)
   FTP客户端首先和FTP Server的TCP 21端口建立连接,通过这个通道发送命令,客户端需要接收数据的时候在这个通道上发送PORT命令.
   PORT命令包含了客户端用什么端口(一个大于1024的端口)接收数据.
   在传送数据的时候,服务器端通过自己的TCP 20端口发送数据.
   FTP server必须和客户端建立一个新的连接用来传送数据
2. PASV模式(被动方式)
   在建立控制通道的时候和PORT模式类似,当客户端通过这个通道发送PASV命令的时候,
   FTP server打开一个位于1024和5000之间的随机端口并且通知客户端在这个端口上传送数据的请求,
   然后FTP server将通过这个端口进行数据的传送,这个时候FTP server不再需要建立一个新的和客户端之间的连接传送数据
3. 如果从C/S模型这个角度来说,PORT对于服务器来说是OUTBOUND,而PASV模式对于服务器是INBOUND,
   这一点请特别注意,尤其是在使用防火墙的企业里,这一点非常关键,如果设置错了,那么客户将无法连接

vsftpd的xinetd模式和initd模式
standalone一次性启动,运行期间一直驻留在内存中,优点是对接入信号反应快,缺点是损耗了一定的系统资源,因此经常应用于对实时反应要求较高的专业FTP服务器.inetd恰恰相反,由于只在外部连接发送请求时才调用FTP进程,因此不适合应用在同时连接数量较多的系统.此外,inetd模式不占用系统资源.除了反应速度和占用资源两方面的影响外,vsftpd还提供了一些额外的高级功能,如inetd模式支持per_IP(单一IP)限制,而standalone模式则更有利于PAM验证功能的应用
1.xinetd运行模式
  大多数较新的系统采用的是xinetd超级服务守护进程
  使用"vi /etc/xinetd.d/vsftpd"看一下它的内容,如下：
    disable = no
    socket_type = stream
    wait = no
    #这表示设备是激活的,它正在使用标准的TCP Sockets
  如果"/etc/vsftpd.conf"中的有选项为"listen=YES",注销它
  最后,重启xinetd: /etc/rc.d/init.d/xinetd restart
  需要注意的是,"/etc/xinetd.d"目录中仅能开启一个FTP服务
2.standalone模式
  standalone模式便于实现PAM验证功能
  进入这种模式首先要关闭xinetd下的vsftpd,设置
  "disable = yes"
  或者注销掉"/etc/inetd.conf"中相应的行
  然后修改""/etc/vsftpd.conf"中的选项为"listen=YES"

如果是standlone模式,那么它是作为单独的一个服务启动的,不需要系统协作,不作为系统服务
如果要是成为xinetd模式,那么它的服务就要受系统服务的限制,比如创建一个新的服务进程,但是也有缺点,如果xinetd服务本身出了问题,那么相关的服务也是会受到影响的

xinetd模式和standalone模式的区别:
* 以xinetd模式运行的服务表示该服务进程并不以守护进程执行
* 以xinetd模式运行这个服务,本身FTP服务是会监听21号端口的,但是以这种模式运行这个服务的话,21号端口则由xinetd进程来监听(此时FTPD服务并没有运行),如果你的网卡接收到有21号端口请求,则有xinetd进程会去调用FTPD程序,将在21号端口接收到的请求数据移交给FTPD进程去处理,处理完后FTPD进程退出,而xinetd进程继续监听21号端口,这有点类似windows的svhost进程
* 以standalone模式运行的服务则是服务进程,如ftpd以守护进程在内存中运行,接收到21号端口的请求后由FTPD进程fork出一个子进程进行处理,而原进程继续监听21号端口

2017-02-28

golang►go env

golang env

[root@gitserver ~]# mkdir -p /mnt/app
[root@gitserver ~]# tar xzf go1.7.4.linux-amd64.tar.gz -C /mnt/app/
[root@gitserver ~]# echo 'export GOROOT=/mnt/app/go' |tee /etc/profile.d/go.sh
[root@gitserver ~]# echo 'export GOPATH=$HOME/work'  |tee -a /etc/profile.d/go.sh
[root@gitserver ~]# echo 'export PATH=$GOROOT/bin:$PATH' |tee -a /etc/profile.d/go.sh
[root@gitserver ~]# source /etc/profile