您的位置:1010cc时时彩经典版 > 1010cc安卓版 > 1010cc时时彩经典版:mysql实践总结,单用户模式启

1010cc时时彩经典版:mysql实践总结,单用户模式启

发布时间:2019-09-23 14:22编辑:1010cc安卓版浏览(171)

    一台数据库服务器的事务日志备份作业偶尔会出现几次备份失败的情况,具体的错误信息为:

    昨天遇到一个案例,YourSQLDba做事务日志备份时失败,检查YourSQLDba输出的错误信息如下:

    我们一个SQL Server服务器在执行YourSQLDBa的作业YourSQLDba_FullBackups_And_Maintenance时遇到了错误:

     

    首先介绍mysql的安装和基本使用、进阶操作、讲解mysql的导入导出和自动备份,然后介绍安全模式修改密码和mysql的全文本搜索功能,最后记录了个人使用mysql中遇到的问题集,闲暇时我也会多看几次,巩固下基础吧。

     

     

     

    在SQL Server的数据库维护过程中,有时候在一些特殊情况下需要在单用户模式下启动SQL Server实例。 下面总结一下单用户模式启动SQL Server的几种方式:

    基础使用

    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>

    Exec YourSQLDba.Maint.ShowHistoryErrors @JobNo = 1227

     

    导入导出

    导出数据和表结构:

    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
    

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

    <ctx>yMaint.backups</ctx>

    <row>

    1:命令模式(sqlservr.exe)启动

    数据库自动备份

    新建备份脚本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(此处填写脚本绝对地址)
    

     

    <Sql>

    <ctx>yMaint.IntegrityTesting</ctx>

     

    开启日志记录

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

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

    backup log [gewem]

    <Sql>DBCC checkDb('xxxx') </Sql>

    首先在命令窗口中切换到Binn目录(这个要视SQL Server实际安装路径情况而定,另外,多实例情况下,必须切换到对应路径),如果你对sqlservr.exe命令不熟悉,可以查看相关帮助信息。如下所示:

    安全模式操作

    进入安全模式修改密码

    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"
    

     

    to disk = 'M:DB_BACKUPLOG_BACKUPxxxx_[2016-11-22_01h11m05_Tue]_logs.TRN'

    <err>In case of non-completion of this command check SQLServer Error Log at 2016-11-17 00:00:03.327 for Spid 67</err>

     

    新操作

    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 ;
    

     

    with noInit, checksum, name = 'YourSQLDba:16h16: M:DB_BACKUPLOG_BACKUPxxxx_[2016-11-22_01h11m05_Tue]_logs.TRN'

    </row>

    C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBinn>sqlservr.exe /?
    
    usage: sqlservr
    
            [-a<L2 buffer pool directory>,<size in GB>]       (adding an L2 buffer pool file)
    
            [-c] (not as a service)
    
            [-d file] (alternative master data file)
    
            [-l file] (alternative master log file)
    
            [-e file] (alternate errorlog file)
    
            [-f] (minimal configuration mode)
    
            [-m] (single user admin mode)
    
            [-g number] (stack MB to reserve)
    
            [-k <decimal number>] (checkpoint speed in MB/sec)
    
            [-n] (do not use event logging)
    
            [-s name] (alternate registry key name)
    
            [-T <number>] (trace flag turned on at startup)
    
            [-x] (no statistics tracking)
    
            [-y number] (stack dump on this error)
    
            [-B] (breakpoint on error (used with -y))
    
            [-K] (force regeneration of service master key (if exists))
    
            [-v] (list version information)
    
     
    
    See documentation for details.
    
    2018-04-06 11:28:00.52             SQL Server shutdown has been initiated
    

    可能出现的问题集:

    • 描述

      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
    

    COMMENT:  (None)

    </Sql>

     

     

     

    <err>Error 3202, Severity 16, level 2 : Write on "M:DB_BACKUPLOG_BACKUPxxxx_[2016-11-22_01h11m05_Tue]_logs.TRN" failed: 112(error not found)

    错误日志里面有下面一些错误信息,如下所示:

     

    JOB RUN:  (None)

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

     

    sqlservr.ex启动时,当前环境有多实例,而你有没有指定参数-s,那么就会提示类似如下信息, 需要你指定-s参数的SQL Server服务名称。

     

    </err>

    Date 2016/11/17 0:53:21

     

     

    </Exec>

    Log SQL Server (Archive #8 - 2016/11/18 0:00:00)

     

          关于Operating system error 32,这个错误代码对应的错误信息为:

     

     

    C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBinn>sqlservr.ex

     

    这个“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.

    Source spid67

    e -c -m

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

     

     

    2018-04-06 11:40:54.15 Server      Multiple instances of SQL server are installe

     

    1010cc时时彩经典版 1

    Message

    d on this computer. Renter the command, specifying the -s parameter with the nam

     

     

    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.

    e of the instance that you want to start.

       其实以前刚好遇到过这样的案例,使用AplexSQL Log去分析事务日志的备份文件,YourSQLDba的事务日志备份的时候,就会遇到上面错误(YourSQLDba_LogBackups事务日志备份会追加到同一个备份文件),但是这个怎么定位这个错误呢?其实出现这个错误,一定有相关进程在访问事务日志备份文件,那么只要排查这些应用或程序即可。刚好最近配置了AWS的DMS的(DMS简单介绍如下,具体参考官方文档

    当时检查时,发现磁盘空间还剩下十多G,然后自己手工又将代码取出,手工执行测试,依然报这个错误:

     

    2018-04-06 11:40:54.16 Server      SQL Server shutdown has been initiated

     

    backup log [xxxx] 
    
    to disk = 'M:DB_BACKUPLOG_BACKUPxxxx_[2016-11-22_01h11m05_Tue]_logs.TRN' 
    
    with noInit, checksum, 
    
    NAME = 'YourSQLDba:00h10: M:DB_BACKUPLOG_BACKUPxxxx_[2016-11-22_01h11m05_Tue]_logs.TRN'
    

    Date 2016/11/17 0:53:21

     

     

    1010cc时时彩经典版 2

    Log SQL Server (Archive #8 - 2016/11/18 0:00:00)

    sqlservr.exe -c -m  -s{instancename}

    在最基本级别上,AWS DMS 是 AWS 云中运行复制软件的服务器。您创建源和目标连接以告知 AWS DMS 要进行提取和加载的位置。然后,计划在此服务器上运行以迁移数据的任务。AWS DMS 会创建表和关联的主键 (如果它们在目标上不存在)。如果您愿意,则可以预先手动创建目标表。或者,也可以使用 AWS SCT 创建部分或全部目标表、索引、视图、触发器等。

     

     

     

     

    后面检查发现这个数据库的日志文件暴增,已经超过数据文件的大小。如下截图所示,所以即使磁盘空间还有不少(19G),部署的磁盘空间告警作业亦正常,没有出现告警,但是已经不能足够支撑事务日志备份完成。所以出现了这个问题,问题有点隐蔽,不过只要细心,问题真相就能水落石出。剔除几个老旧备份以及已经上带的备份,腾出了一些磁盘空间,重新运行作业,作业执行成功!

    Source spid13s

     

     

     

     

     

    1010cc时时彩经典版 3

    1010cc时时彩经典版 4

    Message

    sqlservr.ex启动时,如果SQL Server服务本身还在运行,就会报“Operating system error = 32(The process cannot access the file because it is being used by another process.).

     

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

     

     

     

     

    因为DMS有时候会读取事务日志备份文件中的内容,通过DPA监控,刚好找的了这个DMS的账号,在事务日志备份的时间点在执行下面SQL语句(如下所示)。那么就是因为这个进程在读取事务日志备份中内容(跟普通的Replication有点不同),导致事务日志备份出现Operating system error 32错误(The process cannot access the file because it is being used by another process)

     

    C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBinn>sqlservr.ex

     

    SQL Server

    e -c -m -sMSSQLSERVER

     
    
     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_BACKUPLOG_BACKUPxxxx_[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' )
    
                ); 
    

    Date 2016/11/17 0:53:21

    2018-04-06 11:41:59.01 Server      Error: 17058, Severity: 16, State: 1.

    Log SQL Server (Archive #8 - 2016/11/18 0:00:00)

    2018-04-06 11:41:59.01 Server      initerrlog: Could not open error log file 'C:

     

    Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLLogERRORLOG'. Ope

    Source spid129

    rating system error = 32(The process cannot access the file because it is being

    本文由1010cc时时彩经典版发布于1010cc安卓版,转载请注明出处:1010cc时时彩经典版:mysql实践总结,单用户模式启

    关键词: