SELECT /* top 50000 */

        [Current LSN] ,

        [Operation] ,

        [Context] ,

        [Transaction ID] ,

        [Transaction Name] ,

        [Begin Time] ,

        [End Time] ,

        [Flag Bits] ,

        [PartitionId] ,

        [Page ID] ,

        [Slot ID] ,

        [RowLog Contents 0] ,

        [Log Record] ,

        [RowLog Contents 1]

 FROM   sys.fn_dump_dblog(@P1, NULL, NULL, 11,

                          'M:\DB_BACKUP\LOG_BACKUP\xxxx_[2018-08-01_06h06m19_Wed]_logs.TRN',

                          NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

                          NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

                          NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

                          NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

                          NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

                          NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

                          NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)

 WHERE  [Current LSN] COLLATE SQL_Latin1_General_CP1_CI_AS >= @P2 COLLATE SQL_Latin1_General_CP1_CI_AS

        AND ( ( [Operation] IN ( 'LOP_COMMIT_XACT', 'LOP_ABORT_XACT' )

                OR ( [Operation] = 'LOP_BEGIN_XACT' /* and [Transaction SID]=SUSER_SID('') */ )

              )

              OR ( ( ( [Operation] IN ( 'LOP_INSERT_ROWS', 'LOP_DELETE_ROWS' )

                       AND [Context] IN ( 'LCX_HEAP', 'LCX_CLUSTERED',

                                          'LCX_MARK_AS_GHOST' )

                     )

                     OR ( [Operation] IN ( 'LOP_INSERT_ROWS', 'LOP_MODIFY_ROW' )

                          AND [Context] = 'LCX_TEXT_MIX'

                          AND DATALENGTH([RowLog Contents 0]) IN ( 0, 14, 28 )

                        )

                   )

                   AND [PartitionId] IN ( 72057596166537216, 72057596166471680,

                                          72057596166406144, 72057596327559168,

                                          72057596160180224, 72057596361506816,

                                          72057596119613440, 72057596119744512,

                                          72057596119678976, 72057596120006656,

                                          72057596119941120, 72057596120137728,

                                          72057596545859584, 72057596119810048,

                                          72057596231417856, 72057596418523136,

                                          72057596441067520, 72057596381364224,

                                          72057594562543616, 72057596104671232,

                                          72057594585808896, 72057594585874432,

                                          72057596242952192, 72057596168962048,

                                          72057596054994944, 72057596055453696,

                                          72057596035072000, 72057596055126016,

                                          72057596091039744, 72057596381495296,

                                          72057596381560832, 72057596524298240,

                                          72057595958067200, 72057596469116928,

                                          72057596462628864, 72057596454699008,

                                          72057596241641472, 72057595958853632,

                                          72057594563526656, 72057594563657728,

                                          72057594563723264, 72057596502802432,

                                          72057596484845568, 72057596484911104,

                                          72057596575285248, 72057596554575872,

                                          72057596485107712, 72057596485238784,

                                          72057596485304320, 72057596485369856,

                                          72057596551430144, 72057596485500928,

                                          72057594601275392, 72057594621394944,

                                          72057596548677632, 72057596561719296,

                                          72057596218638336, 72057596376842240,

                                          72057596357443584, 72057596357509120,

                                          72057596358164480, 72057596112207872,

                                          72057596023865344, 72057595959902208,

                                          72057595961081856, 72057594588889088,

                                          72057596112142336, 72057596410003456,

                                          72057595938275328, 72057596192096256,

                                          72057596192161792, 72057596367208448,

                                          72057595961868288, 72057596245508096,

                                          72057596250554368, 72057594565689344,

                                          72057596192227328, 72057596192292864,

                                          72057596248326144, 72057596248391680,

                                          72057594565754880, 2465937948672,

                                          72057596374810624, 72057594565885952,

                                          72057596133113856, 72057596563357696,

                                          48281791823872, 116258596192256,

                                          72057596132851712, 72057595963179008,

                                          72057596541861888, 72057594599047168,

                                          53296886710272, 72057595944894464,

                                          72057594566934528, 72057594567000064,

                                          72057596526395392, 72057596526592000,

                                          72057595964030976, 72057595965079552,

                                          72057594582859776, 72057594567917568,

                                          72057596525936640, 72057595908128768,

                                          72057594568048640, 119223456956416,

                                          72057594896711680, 72057594588758016,

                                          72057596133179392, 72057596132786176,

                                          72057595965931520, 72057595966455808,

                                          72057596450177024, 72057595966849024,

                                          112563398705152, 72057596184363008,

                                          72057596184428544, 72057594681425920,

                                          72057596309733376, 72057596205334528,

                                          72057596184231936, 72057595458355200,

                                          107370836852736, 72057595852423168,

                                          72057595708702720, 72057596357312512,

                                          72057596357378048, 72057596550119424,

                                          72057596301279232, 72057596357050368,

                                          72057596356984832, 72057596362293248,

                                          72057596357574656, 72057596357246976,

                                          72057596357181440, 72057596301344768,

                                          72057595809890304, 72057595873591296,

                                          72057595969011712, 72057595969536000,

                                          72057596348858368, 72057595970191360,

                                          72057596217917440, 72057596217982976,

                                          72057596526329856, 72057596332212224,

                                          114660558176256, 72057596526460928,

                                          72057596331687936, 72057596331622400,

                                          72057595970781184, 72057596574826496,

                                          72057596317859840, 72057594572242944,

                                          72057594592952320, 72057595971502080,

                                          72057595991228416, 72057596324282368,

                                          72057596220538880, 72057595972943872,

                                          72057596512370688, 72057594640334848,

                                          72057595973730304, 72057596132655104,

                                          72057594573684736, 72057594574012416,

                                          72057594776649728, 72057595974320128,

                                          72057594574798848, 45591660199936,

                                          72057594590920704, 72057595974778880,

                                          72057594593738752, 72057595975106560,

                                          72057595975696384, 72057595937882112,

                                          140014173224960, 72057594576240640,

                                          72057594576502784, 72057596501819392,

                                          72057596188360704, 72057596526264320,

                                          61775963815936, 56442625916928,

                                          72057596350365696, 14271632703488,

                                          72057596268838912, 72057596575744000,

                                          72057596442836992, 72057596459089920,

                                          72057596440281088, 72057596469444608,

                                          72057596413935616, 72057596414001152,

                                          72057596532293632, 72057596413739008,

                                          72057596414066688, 72057596548743168,

                                          72057596548612096, 72057596195110912,

                                          72057596448014336, 72057594576961536,

                                          72057594581614592, 72057594577354752,

                                          72057596310061056, 72057596488384512,

                                          72057594577813504, 72057596309995520,

                                          72057596181151744, 72057594577551360 )

                 )

              OR ( [Operation] = 'LOP_HOBT_DDL' )

            ); 

D:\xxx\xxxx\xxxxx.ndf:MSSQL_DBCC10:
Operating system error 112(failed to retrieve text for this error.
Reason: 15105) encountered.

<Exec>

C:\Windows\system32>net
start mssqlserver /m”Microsoft SQL Server Management Studio –
Query”

敞开日志记录

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1000M
binlog-format = row

 

 

 

 

功底运用

sudo apt-get install mysql-common mysql-server

由此可知利用:建库

CREATE DATABASE IF NOT EXISTS yourdbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

建表

create table MyClass(id int(4) not null primary key auto_increment,name char(20) not null,sex int(4) not null default '0',degree double(16,2));

增加

insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);

删除

delete from MyClass where id=1;

修改

update MyClass set name='Mary' where id=1;

查询

select * from MyClass;

体现全部视图

select * from information_schema.TABLES where table_type='view' AND table_schema = '数据库名';

开创客商

create user xxx identified by 'password';

重命名

rename user aaa to bbb;

除去客户

drop user aaa;

显示权限

show grants for aaa(用户);

加之权限

grant select on xxx(数据库).* to aaa(用户);

付与有个别数据库的风度翩翩体权力

grant all on  xxx(数据库).* to aaa(用户);
grant all on  xxx(数据库).* to aaa(用户)@localhost;

撤回授权

revoke all on *.* from aaa(用户)@localhost;

修正权限

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%’   WITH GRANT OPTION;

如上操作达成之后记得刷新权限:

flush privileges;

 

Exec YourSQLDba.Maint.ShowHistoryErrors @JobNo = 1227

to disk =
‘M:\DB_BACKUP\LOG_BACKUP\xxxx_[2016-11-22_01h11m05_Tue]_logs.TRN’

C:\Users>net
stop mssqlserver

四平方式操作

步入安全方式修正密码

mysqld_safe --skip-grant-tables &

select user,host,password from user where user="root"

昨今分歧版本的mysql修改顾客密码方式不平等,须要查阅mysql->user中的密码字段,借使不是password的话就是authentication_string。

authentication_string的改造章程不太同样:

use mysql;
update user set authentication_string=PASSWORD("") where User='root';
update user set plugin="mysql_native_password";
flush privileges;
quit;
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

假设不是authentication_string,则可用以下措施。

update user set password=PASSWORD("your_password") where user="root" and host=“localhost"

 

Source spid13s

</Sql>

在此种状态下,怎么做呢?
难道要拼速度? 当然不是,你必要从下面这几个地方注意:

也许现身的主题材料集:

  • 描述

    2017-05-04T01:21:32.004560Z mysqld_safe Logging to ‘/var/log/mysql/error.log’.
    2017-05-04T01:21:32.023009Z mysqld_safe A mysqld process already exists

缓慢解决情势:

$ sudo killall mysqld
  • 描述

    2017-05-04T01:22:26.486677Z mysqld_safe Logging to ‘/var/log/mysql/error.log’.
    2017-05-04T01:22:26.488204Z mysqld_safe Directory ‘/var/run/mysqld’ for UNIX socket file don’t exists.

消除措施:

sudo mkdir -p /var/run/mysqld
sudo chown -R mysql:mysql /var/run/mysqld
  • 描述

    $ sudo /etc/init.d/mysql start
    ies: No such file or directory
    job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
    [….] Starting mysql (via systemctl): mysql.servicejob-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
    Job for mysql.service failed because the control process exited with error code. See “systemctl status mysql.service” and “journalctl -xe” for details.

除恶务尽措施:

当前文件夹不是实际目录导致
cd到一个实际目录位置即可
  • 描述

    sudo /etc/init.d/mysql start
    shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
    job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
    job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
    job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
    job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
    job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
    job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
    job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
    [….] Starting mysql (via systemctl): mysql.servicejob-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
    Job for mysql.service failed because the control process exited with error code. See “systemctl status mysql.service” and “journalctl -xe” for details.

    依据提醒:See “systemctl status mysql.service” and “journalctl -xe” for details.
    可是并么有哪些卵用,直接看mysql的log:/var/log/mysql/error.log
    2017-05-04T01:37:56.583745Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.

养虎遗患措施:

杀掉所有mysqld进程:killall mysqld 
再次sudo /etc/init.d/mysql start 成功
  • 描述

    dpkg被锁定

消除方法

sudo rm /var/cache/apt/archives/lock
sudo rm /var/lib/dpkg/lock

 

图片 1

<err>Error 3202, Severity 16, level 2 : Write on
“M:\DB_BACKUP\LOG_BACKUP\xxxx_[2016-11-22_01h11m05_Tue]_logs.TRN”
failed: 112(error not found)

 

第一介绍mysql的装置和基本接纳、进级操作、解说mysql的导入导出和机关备份,然后介绍安全形式校订密码和mysql的全文本寻找作用,最终记录了民用运用mysql中蒙受的主题素材集,闲暇时自己也会多看四次,加强下根底吧。

在最焦点等级上,AWS DMS 是 AWS
云中运维复制软件的服务器。您成立源和对象连接以报告 AWS DMS
要扩充提取和加载的职位。然后,安插在此服务器上运营以动员搬迁数据的天职。AWS DMS
会创设表和涉嫌的主键 (假诺它们在对象上不设有)。如若你愿意,则能够优先手动创立目的表。恐怕,也得以动用 AWS SCT
创制部分或任何指标表、索引、视图、触发器等。

 

with noInit, checksum, name = ‘YourSQLDba:16h16:
M:\DB_BACKUP\LOG_BACKUP\xxxx_[2016-11-22_01h11m05_Tue]_logs.TRN’

The
SQL Server (MSSQLSERVER) service is stopping.

新操作

Mysql全文本寻觅

Mysql5.6从此帮衬InnoDB,汉语的全文本寻觅,内置使用n-gram为分词微电脑,还援助普通话~。

创造索引

create fulltext index ngram_idx on tag(Title) with parser ngram;
或
alter table tag add fulltext index ngram_idx(Title) with parser ngram;

获取协理的最小分词长度

SHOW VARIABLES LIKE 'ft_min_word_len';
//unix系统可在/etc/my.cnf中修改
[mysqld]
ft_min_word_len = 1

始发利用

select Title,match(Title) against('清水') from tag ;

 

<ctx>yMaint.IntegrityTesting</ctx>

图片 2

 

导入导出

导出数据和表结构:

mysqldump -uroot -p abc(数据库名) > abc.sql
敲回车后输入密码

只导出表结构

mysqldump -uroot -p -d abc > abc.sql

导入数据库
1、首先建空数据库

mysql> create database abc;

2、导入数据库

mysql -u root -p abc(数据库名) < abc.sql

 

Write to sparse file
‘xxxxxxx.ndf:MSSQL_DBCC10’ failed due to lack of disk space.

立即检查时,开采磁盘空间还剩余十多G,然后自身手工业又将代码收取,手工业试行测量检验,还是报那几个指鹿为马:

 

数据库自动备份

新建备份脚本xxx.sh,输入以下内容

#!/bin/bash

# 要备份的数据库名,多个数据库用空格分开
databases=("db1", "db2") 

# 备份文件要保存的目录,注意当前用户必须用户保存目录的读写权限
basepath='/root/backup/mysql/'

if [ ! -d "$basepath" ]; then
  mkdir -p "$basepath"
fi

# 循环databases数组
for db in ${databases[*]}
  do
    # 备份数据库生成SQL文件
    nice -n 19 /usr/bin/mysqldump -uroot -pcd32d5e86e --database $db > $basepath$db-$(date +%Y%m%d).sql

    # 将生成的SQL文件压缩
    nice -n 19 tar zPcf $basepath$db-$(date +%Y%m%d).sql.tar.gz -C $basepath $db-$(date +%Y%m%d).sql

    # 删除7天之前的备份数据
    find $basepath -mtime +7 -name "*.sql.tar.gz" -exec rm -rf {} \;
  done

  # 删除生成的SQL文件
  rm -rf $basepath/*.sql

运用crontab设置定时义务,在极限输入crontab
-e,参预以下内容,此义务为每日3点自动试行。

0 3 * * * bash xxx.sh(此处填写脚本绝对地址)

 

 

backup log [xxxx] 

to disk = 'M:\DB_BACKUP\LOG_BACKUP\xxxx_[2016-11-22_01h11m05_Tue]_logs.TRN' 

with noInit, checksum, 

NAME = 'YourSQLDba:00h10: M:\DB_BACKUP\LOG_BACKUP\xxxx_[2016-11-22_01h11m05_Tue]_logs.TRN'

 

 

2:SSQL_DBCC10: Operating system error 112(failed to retrieve text for
this error. Reason: 15105) encountered

Error 3013, Severity 16, level 1 : BACKUP LOG is terminating abnormally.

The
following services are dependent on the SQL Server (MSSQLSERVER)
service.

因为DMS不常候会读取事务日志备份文件中的内容,通过DPA监察和控制,恰巧找的了那个DMS的账号,在业务日志备份的时刻点在施行上边SQL语句(如下所示卡塔尔国。那么就是因为那么些进度在读取事务日志备份中剧情(跟普通的Replication有一点点不一致卡塔 尔(阿拉伯语:قطر‎,引致业务日志备份现身Operating
system error 32荒唐(The
process cannot access the file because it is being used by another
process卡塔尔

图片 3

backup log [gewem]

 

 

 

 

 

 

 

</err>

 

DATE/TIME:    2018/7/30
12:10:52

 

 

The
SQL Server (MSSQLSERVER) service was started successfully.

COMMENT:  (None)

 

<ctx>yMaint.backups</ctx>

 

 

Source spid129

</Exec>

 

 

<row>

末尾检查发掘这一个数据库的日记文件暴增,已经超(Jing Chao卡塔 尔(阿拉伯语:قطر‎越数据文件的朗朗上口。如下截图所示,所以便是磁盘空间还或然有众多(19G卡塔 尔(英语:State of Qatar),陈设的磁盘空间告急作业亦如常,未有现身告急,可是已经不能够丰裕帮忙事务日志备份完成。所以出现了那么些难点,难题不怎么隐瞒,不过只要留意,难点本质就能够水落石出。剔除几个老旧备份以及已经上带的备份,腾出了部分磁盘空间,重国民党的新生活运动行作业,作业推行成功!

 

 

 

其大器晚成“failed
:112”新闻风流倜傥现身,意味着早晚是磁盘空间非常不足,前日总结的那篇博客DBCC
CHECKDB 遭遇Operating system error 112(failed to retrieve text for this
error. Reason: 15105)
encountered骨子里境遇的难点也是相近的,Operating
System error 112 meaning There is not enough space on the disk.

C:\Program
Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER\MSSQL\Binn>sqlservr.ex

豆蔻年华台数据库服务器的作业日志备份作业一时会产出五遍备份战败的图景,具体的错误音讯为:

实质上在青霄白日的时候,该服务器就曾经有磁盘空间告急邮件发出,由于系统一管理理员想在礼拜日做存款和储蓄扩大体量。所以在夜间数据库做DBCC
CHECKDB时,就由于磁盘空间不足,碰到了这几个张冠李戴。DBCC
CHECKDB时会生成数据库快速照相,而数据库的快照文件是基于疏弃文件(Sparse
File),而当快速照相创立后,随着对源数据库的改造渐渐增添,荒芜文件也会逐步滋长。所以,当磁盘空间不足时,就能遇见上面错误消息:

 

Do
you want to continue this operation? (Y/N) [N]: y

DESCRIPTION:
BackupDiskFile::CreateMedia: Backup device
‘M:\DB_BACKUP\LOG_BACKUP\xxxx_[2018-07-30_06h03m10_Mon]_logs.TRN’
failed to create. Operating system error 32(failed to retrieve text for
this error. Reason: 15105).

 

 

Sqlcmd:
Error: Microsoft ODBC Driver 11 for SQL Server : Login failed for user

 

Date 2016/11/17 0:53:21

图片 4

The
SQL Server (MSSQLSERVER) service is starting.

 

Date 2016/11/17 0:53:21

 

C:\Windows\system32>

     The
process cannot access the file because it is being used by another
process.

Source spid67

<Sql>

 

      关于Operating
system error 32,那么些错误代码对应的错误音讯为:

 

明日遇见二个案例,YourSQLDba做事情日志备份时战败,检查YourSQLDba输出的错误信息如下:

an
connect at this time..

图片 5

Message

图片 6

 

 

CHECKDB From Every Angle: Why would CHECKDB run out of
space?

 

JOB
RUN: 
(None)

Date 2016/11/17 0:53:21

 

   其实早前无独有偶遇见过如此的案例,使用AplexSQL
Log去剖判专门的职业日志的备份文件,YourSQLDba的事体日志备份的时候,就能够碰着上边错误(YourSQLDba_LogBackups事务日志备份会追加到同二个备份文件卡塔尔国,可是这么些怎么固定那些荒谬呢?其实现身这几个乖谬,一定有有关进程在访谈工作日志备份文件,那么只要逐个审查那一个应用或程序就可以。适逢其会目前安插了AWS的DMS的(DMS简要介绍如下,具体仿效官方文书档案

Message

The
SQL Server (MSSQLSERVER) service was started successfully.

 

 

 

 

 

 

 

C:\Users>sqlcmd

DBCC CHECKDB (xxxx) executed by xxxxx found 0
errors and repaired 0 errors. Elapsed time: 0 hours 53 minutes 18
seconds. Internal database snapshot has split point LSN =
00623e60:00004a5b:0001 and first LSN = 00623e60:000048f3:0011. This is
an informational message only. No user action is required.

 

 

 

相关文章