数据相关工作笔记
工作笔记，仅暂未心得分享

SQL Server

删除带有镜像的库

查询此库当前所有进程

select spid from sys.sysprocesses where dbid=DB_ID('<dbname>');

关闭进程

kill <pid>;

关闭镜像复制

ALTER DATABASE <dbname> SET PARTNER OFF;

删除数据库

DROP DATABASE <dbname>;

数据收缩

DBCC SHRINKDATABASE<dbname>

mongo

mongoexport

• 不建议用于全量备份，通常用于备份或导出数据的一部分数据

常用参数介绍

Options:
--host <hostname><:port>, -h <hostname><:port>
Default: localhost:27017
 
--port <port>
Default: 27017
Specifies the TCP port on which the MongoDB instance listens for client connections.
 
--username <username>, -u <username>
Specifies a username with which to authenticate to a MongoDB database that uses authentication. Use in conjunction with the --password and --authenticationDatabase options.
 
--password <password>, -p <password>
Specifies a password with which to authenticate to a MongoDB database that uses authentication. Use in conjunction with the --username and --authenticationDatabase options.
 
--db <database>, -d <database>
Specifies the name of the database on which to run the mongoexport.
 
--collection <collection>, -c <collection>
Specifies the collection to export.
 
--fields <field1[,field2]>, -f <field1[,field2]>¶
Specifies a field or fields to include in the export. Use a comma separated list of fields to specify multiple fields.
 
 
 
--csv
Changes the export format to a comma-separated-values (CSV) format. By default mongoexport writes data using one JSON document for every MongoDB document.
 
 
--out <file>, -o <file>
Specifies a file to write the export to. If you do not specify a file name, the mongoexport writes data to standard output (e.g. stdout).
 
--jsonArray
Modifies the output of mongoexport to write the entire contents of the export as a single JSON array. By default mongoexport writes data using one JSON document for every MongoDB document

shell备份脚本如下

#!/bin/bash
# author:Charles
# date:2019-04-18
set -x

backuptime=$(/bin/date "+%Y%m%d")
user=testAdmin
password=123456
backup_dir=/backup
port=27017
database=local

data=`/usr/bin/mongo 127.0.0.1 -u${user} -p${password} <<EOF
use local;
show collections;
exit;
EOF`

for gather in ${data}
do
#json array
#/usr/bin/mongoexport --port ${port} -u${user} -p${password} -d ${database} -c ${gather}  --jsonArray -o ${backup_dir}${backuptime}/${gather}.json --authenticationDatabase test

#csv document,must specify the fields in the documents to export
#/usr/bin/mongoexport --port ${port} -u${user} -p${password} -d ${database} -c ${gather}  --csv -f hostname,startTime -o ${backup_dir}${backuptime}/${gather}.csv --authenticationDatabase test

#json document
/usr/bin/mongoexport --port ${port} -u${user} -p${password} -d ${database} -c ${gather} -o ${backup_dir}${backuptime}/${gather}.json --authenticationDatabase test
done

PostgreSQL

单库导入导出

导出

pg_dump -Upostgres <dbname> -c > /bak/name.bak

导入

psql -U postgres -d dbname < /bak/name.bak

若导入的库已存在并有数据，需要先drop库，再执行导入。若drop库时，库被占用则执行下面的语句清理，至返回值为无连接信息。

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND datname = 'dbname';

drop库

drop database dbname;

新建库并附权

create database dbname with owner=username;

修改用户属性

• 具有CREATEROLE权限的用户或者超级用户才能执行该命令，option选项的含义参见CREATE USER命令。数据库超级用户可以修改任何普通用户的属性。只用postgres才能修改其它的超级用户的属性。一个用户只能修改自己的密码，不能修改自己的其它属性

ALTER USER name [ [ WITH ] option [ ... ] ]

其中option可以是:

| SUPERUSER  | NOSUPERUSER
 
| CREATEDB   | NOCREATEDB
 
| CREATEROLE | NOCREATEROLE
 
| CONNECTION LIMIT connlimit
 
| {ENCRYPTED | UNENCRYPTED} PASSWORD ’password’
 
| VALID UNTIL ’timestamp’

• 例

使数据库用户testname失去CREATEDB、CREATEROLE和LOGIN权限

ALTER USER  testname  NOCREATEDB  NOCREATEROLE  NOLOGIN;

将用户testname的密码改为123123

ALTER  USER  testname  password '123123';

• 只读用户

新建用户

CREATE USER readall WITH PASSWORD 'passwd';

用户只读事务

alter user readall set default_transaction_read_only=on;

把所有库的语言的USAGE权限赋予用户

GRANT USAGE ON SCHEMA public to readall;

授权库中所有表的select权限（必须进库授权，同表权限授权）

grant select on all tables in schema public to readall;

新表自动附权

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readall;

• 清理归档日志

进入存放日志的目录

cd /pg_data200/11/main/pg_wal

清理日志 <00000001000000A10000006B> 前的所有日志

pg_archivecleanup . 00000001000000A10000006B

• 修改归档日志保留数量

连接数据库查看当前保留数量

show wal_keep_segments；

修改配置文件 wal_keep_segments 参数，reload服务使配置生效，下一次checkpoint时会自动清理，或手动执行checkpoint

Oracle

关于服务重启

1. Oracle启动需要经历四个状态:SHUTDOWN 、NOMOUNT 、MOUNT 、OPEN

2. Oracle关闭的四种状态：Normal, Immediate, Transactional, Abort

Oracle数据库的完整启动过程是分步骤完成的，包含以下3个步骤：

• 启动实例–>加载数据库–>打开数据库

关闭数据库

shutdown immediate;

开启数据库

startup;

启动默认监听

lsnrctl start

启动指定监听

lsnrctl start listener_name

查看当前启动状态，启动对应的状态

select name,open_mode from v$database;

根据当前启动状态启动数据库

alter database <status>

Oracle CPU使用率过高排查

查看语句top10

select *
from (select v.sql_id,
v.child_number,
v.sql_fulltext,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.elapsed_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10
ORDER BY cpu_time desc;

查看CPU使用率最高及性能查询的语句

set linesize 600
col SQL_TEXT for a500
select * from (select sql_text,sql_id,cpu_time from v$sql order by cpu_time desc) where rownum<=10 order by rownum asc;

查看使用频率最高的5个查询

select sql_text,executions
from (select sql_text,executions,
rank() over
(order by executions desc) exec_rank
from v$sql)
where exec_rank <=5;

查看消耗磁盘读取最多的sql top5

select disk_reads,sql_text
from (select sql_text,disk_reads,
dense_rank() over
(order by disk_reads desc) disk_reads_rank
from v$sql)
where disk_reads_rank <=5;

查看需要大量缓冲读取（逻辑读）操作的查询

select buffer_gets,sql_text
from (select sql_text,buffer_gets,
dense_rank() over
(order by buffer_gets desc) buffer_gets_rank
from v$sql)
where buffer_gets_rank<=5;

Oracle阻塞查询

with vw_lock AS (SELECT * FROM v$lock)
select
a.sid,
'is blocking',
(select 'sid:'||s.sid||' object:'||do.object_name||' rowid:'||
    dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
    ||' sql_id:'||s.sql_id
   from v$session s, dba_objects do
    where s.sid=b.sid
    and s.ROW_WAIT_OBJ# = do.OBJECT_ID
) blockee,
b.sid,b.id1,b.id2
from vw_lock a, vw_lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;

用户被锁

查询当前所有用户锁状态及时间

select username,account_status,lock_date from dba_users;

解锁

alter user <username> account unlock;

Mysql

mysqldump 备份数据

逻辑备份sql语句

-u 用户名
-p 密码
-A 所有的库

single-transaction INNODB存储引擎的表备份时能够做到数据一致

mysqldump -uroot -puplooking -A --single-transaction > /tmp/mysql_bak.sql

lock-all-tables MYISAM存储引擎的表备份时能够做到数据一致

mysqldump -uroot -puplooking -A --lock-all-tables > /tmp/mysql_bak.sql

通过binlog还原

mysqlbinlog --start-datetime="2019-11-07 12:01:00"  --stop-datetime="2018-19-07 12:30:00" /data/mysql/data/mysql-bin.000020 >> /home/madong/aa.sql
mysql -uroot -p <database> < /home/madong/aa.sql

批量修改字段

例： 修改test表中的guid字段，将其中的test更换为testting

update test set guid=replace(guid,'http://testting','http://test/1/2');

查看字符集、表排序

指定表查询

show create table <tablename>;

针对全局或当前库查询

show (global) variables like '%coll%';

清理mysq系统日志

• 以清理err日志为例

确定日志文件信息

show global variables like '%log%';

修改源日志名称

mv <name.err> <name.err.bak>

刷新mysql配置环境，重新加载相关文件（热加载）

FLUSH ERROR LOGS