sqlite提示near select: syntax error_SQLite数据备份

数据库故障的种类

1. 事务内部的故障
   事务内部故障是指事务运行没有达到预期的终点（COMMIT或者显式的ROLLBACK)，未能成功地提交事务，使数据库处于不正确状态。事务内部故障有的可以通过事务程序本身发现，是可预期的故障，但更多的是不可预期的故障，如数据溢出等。当发生事务内部故障时，可强行回滚（ROLLBACK）该事务，即撤销该事务已经作出的任何对数据库的修改，使得该事务好像根本没有启动一样，这类恢复操作称为撤消（UNDO）。
2. 系统故障
   造成系统停止运行的任何事件，使得系统重新启动，例如，特定类型的硬件错误（CPU故障）、操作系统故障、DBMS代码错误、系统断电等。这类故障影响正在运行的所有事务，但不破坏数据库。此时主存内容，尤其是数据库缓冲区（在内存）中的内容会丢失，所有运行事务都非正常终止。发生系统故障时，一些尚未完成的事务的结果可能已送入物理数据库，从而造成数据库可能处于不正常的状态。为了保证数据一致性，需要清除这些事务对数据库的所有修改。
   若发生系统范围的故障，恢复子系统必须在系统重新启动时让所有非正常终止的事务回滚，若事务只作一半便发生故障，必须先撤消该事务，然后重做。
3. 介质故障
   系统故障又称软故障，存储介质故障称为硬故障。硬故障发生的可能性小，但破坏性极大。如硬盘损坏等。这类故障将破坏数据库或部分数据库，并影响正在存取这部分数据的所有事务。
4. 计算机病毒
   计算机病毒主要破坏计算机软件系统，由计算机病毒引起的故障属于系统范围的故障。

总的来说，各类故障对数据库的影响有两种可能性

• 数据库本身被破坏；
• 数据库没有被破坏，但数据可能不正确。

SQLite数据恢复

先介绍SQLite本身的数据恢复技术：

SQLite数据库是事务性数据库，事务数据库是其中所有更改和查询看起来都是原子的，一致的，隔离的和持久的（ACID）。 即使事务因程序崩溃，操作系统崩溃或计算机电源故障而中断，SQLite 仍可实现 原子，一致，隔离和持久的可序列化事务。

因此对于一般性系统崩溃的情况，数据库会在下次连接的自动回滚。

回滚日志

当一个进程要改变数据库文件的时候（并且不在WAL模式下），它首先将未改变之前的内容记录到回滚日志文件中。回滚日志是普通磁盘文件，通常与数据库文件位于同一目录或文件夹中，并且与数据库文件具有相同的名称，并带有-journal 后缀。回滚日志还记录数据库的初始大小，因此，如果数据库文件增大，则可以在回滚时将其截断为原始大小。

如果SQLite同时使用多个数据库（使用ATTACH命令），则每个数据库都有自己的回滚日志。但是，还有一个单独的汇总日志称为主日志。主日志不包含用于回滚更改的页面数据。而是，主日志包含每个ATTACHed数据库的各个数据库回滚日志的名称。每个单独的数据库回滚日志还包含主日志的名称。如果没有ATTACHed数据库（或者没有ATTACHed数据库参与当前事务），则不会创建主日志，并且正常回滚日志在通常保留用于记录主日志名称的位置包含一个空字符串。

如果应用程序或主机在事务完成之前崩溃，则回滚日志或预写日志包含将主数据库文件还原到一致状态所需的信息。当回滚日志或预写日志包含恢复数据库状态所需的信息时，它们称为“热日志”或“热WAL文件”

回滚日志操作

从数据库文件读取之前，SQLite始终检查该数据库文件是否具有热日志。如果文件确实有热日志，则在读取文件之前会回滚日志。这样，我们确保在读取数据库文件之前，该文件处于一致状态。

当某个进程要从数据库文件中读取时，它遵循以下步骤序列：

1. 打开数据库文件并获得共享锁。如果无法获得SHARED锁定，请立即失败并返回SQLITE_BUSY。
2. 检查数据库文件是否具有热日志。如果该文件没有热日志，那么我们就完成了。立即返回。如果存在热日志，则必须通过此算法的后续步骤来回滚该日志。
3. 在数据库文件上获取PENDING锁，然后获得EXCLUSIVE锁。（注意：不要获取RESERVED锁，因为这会使其他进程认为日志不再很热。）如果我们未能获取这些锁，则意味着另一个进程已经在尝试回滚。在这种情况下，请丢弃所有锁，关闭数据库，然后返回SQLITE_BUSY。
4. 阅读日志文件并回滚更改。
5. 等待将回滚的更改写入持久性存储。万一发生另一次电源故障或崩溃，这可以保护数据库的完整性。
6. 删除日志文件（如果设置了PRAGMA journal_mode = TRUNCATE，则将日志的长度截断为零字节；如果设置了PRAGMA journal_mode = PERSIST，则将日志的标题 截断为零）。
7. 如果安全的话，请删除主日记文件。此步骤是可选的。这只是为了防止陈旧的主日志使磁盘驱动器混乱。有关详细信息，请参见下面的讨论。
8. 放下EXCLUSIVE和PENDING锁，但保留SHARED锁。

上面的算法成功完成后，可以安全地从数据库文件中读取。一旦完成所有读取，就将删除SHARED锁定。

WAL

WAL (Write-Ahead Logging) 预写日志

SQLite实现原子提交和回滚的默认方法 是回滚日志。从版本3.7.0开始，提供了新的“预写日志”选项。

传统的回滚日志的工作方式是将原始未更改的数据库内容的副本写入单独的回滚日志文件，然后将更改直接写入数据库文件。在发生崩溃或ROLLBACK的情况下，回滚日志中包含的原始内容将被会放到数据库文件中，以将数据库文件还原为原始状态。当回滚日志被删除时该COMMIT完成。

WAL的方法反过来了。原始内容保留在数据库文件中，而更改将附加到单独的WAL文件中。当提交一个指示，一个特殊的记录被追加到WAL，COMMIT就会发生。因此，即使不写入原始数据库也可以发生COMMIT，这允许在更改数据库的同时继续从原始未更改的数据库进行操作。可以将多个事务附加到单个WAL文件的末尾。

WAL如何工作

在引入WAL机制之前，SQLite使用rollback journal机制实现原子事务。

rollback journal机制的原理是：在修改数据库文件中的数据之前，先将修改所在分页中的数据备份在另外一个地方，然后才将修改写入到数据库文件中；如果事务失败，则将备份数据拷贝回来，撤销修改；如果事务成功，则删除备份数据，提交修改。

WAL机制的原理是：修改并不直接写入到数据库文件中，而是写入到另外一个称为WAL的文件中；如果事务失败，WAL中的记录会被忽略，撤销修改；如果事务成功，它将在随后的某个时间被写回到数据库文件中，提交修改。

同步WAL文件和数据库文件的行为被称为checkpoint（检查点），它由SQLite自动执行，默认是在WAL文件积累到1000页修改的时候；当然，在适当的时候，也可以手动执行checkpoint，SQLite提供了相关的接口。执行checkpoint之后，WAL文件会被清空。

在读的时候，SQLite将在WAL文件中搜索，找到最后一个写入点，记住它，并忽略在此之后的写入点（这保证了读写和读读可以并行执行）；随后，它确定所要读的数据所在页是否在WAL文件中，如果在，则读WAL文件中的数据，如果不在，则直接读数据库文件中的数据。

在写的时候，SQLite将之写入到WAL文件中即可，但是必须保证独占写入，因此写写之间不能并行执行。

WAL在实现的过程中，使用了共享内存技术，因此，所有的读写进程必须在同一个机器上，否则，无法保证数据一致性。

总结

也就是说，对于数据库没有被破坏，但数据可能不正确的情况，SQLite提供回滚日志和预写日志的操作，恢复数据的一致性。

而对于数据库被破坏的情况，只能通过数据转存来建立冗余数据。

数据库备份恢复

1、备份和还原数据库 先打开数据库test.db

sqlite>sqlite3 test.db
sqlite> .backup test.bak

恢复数据库 先打开数据库test.db

sqlite>sqlite3 test.db
sqlite> .restore test.bak

因为数据库文件存在磁盘中，因此要定时备份，防止数据库文件损坏

2、导出及导入SQL脚本

shell方式：

# sqlite3 sqa.db ".dump [mytabl%]" > sqa.sql 
# sqlite3 sqb.db < sqa.sql

命令行方式：

sqlite> .output backup.sql 
sqlite> .dump [mytabl%]//利用dump命令，将数据导出到文件。如果dump不带参数，则导出整个数据库：
sqlite> .read backup.sql

此处只是导出SQL脚本，因此运行脚本的时候，由于数据库存在，会出现ERROR

3、导出成数据库 sqa.db 和sqb.db都包含表testtable

 # sqlite3 sqa.db ".dump" | sqlite3 //sqc.db 可生成sqc.db，内容与sqa.db相同
 # sqlite3 sqb.db ".dump" | sqlite3 sqc.db
 # Error: near line 3: table testtable already exists 

猜测：执行此shell命令会生成包含sql语句的临时文件，然后将其读入到数据库。类似“2、导出及导入SQL脚本”的操作。

修改如下：

# sqlite3 sqb.db
# sqlite> alter table testtable rename to new_testtable; 
# sqlite3 sqb.db ".dump" | sqlite3 sqc.db 

执行成功，sqc.db包含 testtable 和 new_testtable 两个表。 此方法可以将不同名称的表及数据合并到一个数据库中，相同名称的表及数据则无法合并。

该.dump命令仅在事务内部使用一堆SELECT语句读取整个数据库。该事务自动锁定数据库以进行写入，但允许并发读取。
这些.backup命令不会对SQL产生影响。它将数据库页面直接复制到新文件。所有页面读取都通过常规机制来访问数据库文件，并包装在事务中。使用多个sqlite3_backup_step()调用时，该事务将结束，以允许其他连接访问数据库，但是会检测到任何更改，在这种情况下，将自动重新启动整个备份

4、htm格式输出

输出到屏幕：

# sqlite3 -html sqa.db "select * from testtable" 

输出到html文件：

# sqlite3 -html sqa.db "select * from testtable" > sqa.html

5、导入命令

1）、创建表

sqlite3> create table ct(name text, id int, score float);

2）、创建数据文件data.dat

cc,1,98
dd,2,94 
ee,3,88 

3）、导入数据

sqlite3> .separator ","

sqlite3> .import data.dat ct

4）、查看数据

sqlite3> .header on

sqlite3> .mode column

sqlite3> select * from ct;

这里需要注意一点,在数据导入之前，先要根据数据的具体分的格式，设置数据导入的间隔符，例如在文本数据中采用的是‘,’来间隔数据，因此应先调用.seperator 设置‘,’ 为间隔符。

外部导入数据

---

关注公众号，日常分享，随遇而安！