内存数据库

  • Redis
  • Memercache

常规数据库

  • Mysql
  • Oracle
  • Mogodb
  • Sqlserver

对文件，图片，二进制数据不存储在数据库： 1.数据库备份变大，耗时长。 2.大字段加重数据库负担，拖慢数据库，不适合大并发。 3.增加复杂度。对文件的访问需要穿越你的应用层和数据库层。

mysql备份还原

mysqldump -u root -p --databases dbname2 dbname2 > Backup.sql mysql -u root -p < backup.sql

数据库还原需要

  • 完备数据+二进制日志
  • 完备数据相当于原材料，二进制日志相当于操作信息。

备份

mysqldump导出丢数据

服务器在哪里，就是用哪里的时区，也就是 SYSTEM 时区，在做SQL查询以及mysqldump备份数据时，也使用服务器上的时间，而不使用我们本地时间。

mysqldump备份大数据出错

　为防止数据丢失而造成重大损失，所以需要定期自动备份数据库。一开始数据库备份都是成功的，但是现在数据越来越大了，mysqldump备份时出现备份失败，报错内容：mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table table_name at row: 1413048 。

　　备份失败的原因：在向NFS上备份的时候，数据的流向是这样的：MySQL Server 端从数据文件中检索出数据，然后分批将数据返回给mysqldump 客户端，然后 mysqldump 将数据写入到NFS上。一般地，向 NFS 上写入数据的速度较之Server端检索发送数据的速度要慢得多，这就会导致 mysqldump 无法及时的接受 Server 端发送过来的数据，Server 端的数据就会积压在内存中等待发送，这个等待不是无限期的，当 Server 的等待时间超过 net_write_timeout（默认是60秒）时它就失去了耐心，mysqldump 的连接会被断开，同时抛出错误 Got error: 2013: Lost connection。其实该错误不是说数据库文件太多而导致出错，而是单张表数据量太大导致备份失败。比如我备份失败的错误信息里报错的表就有18.1G数据量。

　　问题的解决方案：增加 net_write_timeout 可以解决上述的问题的。在实践中发现，在增大 net_write_timeout 后，Server 端会消耗更多的内存，有时甚至会导致 swap 的使用（并不确定是不是修改 net_write_timeout 所至）。建议在mysqldump 之前修改 net_write_timeout 为一个较大的值（如1800），在 mysqldump 结束后，在将这个值修改到默认的60。（备注：net_write_timeout不是mysqldump的配置参数，而是mysql的参数）

恢复

如果遇到灾难事件，应该用最近一次制作的完整备份恢复数据库，然后使用备份之后的binlog日志件把数据库恢复到最接近现在的可用状态。

使用binlog日志进行恢复时需要依次进行，即最早生成的日志文件要最先恢复。恢复数据的命令格式如下：

mysqlbinlog xxx-bin.000001|mysql -u root –p password dataname

主从复制

mysql半同步复制

异步复制（Asynchronous replication） MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。

全同步复制（Fully synchronous replication） 指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

半同步复制（Semisynchronous replication） 介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。

mysql会把用户对所有数据库的内容和结构的修改情况记入XXX-bin.n文件，但是不会记录SELECT和没有实际更新的UPDATE语句。

如果是slave_sql_running no了，那么也有两种可能，一种是slave机器上这个表中出现了其他的写操作，就是程序写了，这个是会有问题的，今天我想重现，但是有时候会有问题，有时候就没有问题，现在还不是太明了，后面再更新，还有一种占绝大多数可能的是slave进程重启，事务回滚造成的，这也是mysql的一种自我保护的措施，像关键时候只读一样 STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;START SLAVE;

下面针对MySQL主从复制原理的重点小结：

  • 主从复制是异步的逻辑的SQL语句级的复制
  • 复制时，主库有一个I/O线程，从库有两个线程，I/O和SQL线程。
  • 作为复制的所有MySQL节点的server-id都不能相同。
  • binlog文件只记录对数据库有更改的SQL语句（来自数据库内容的变更），不记录任何查询（select，slow）语句。

MASTER

vim /etc/my.cnf

log-bin=mysql-bin
#将mysql二进制日志取名为mysql-bin
binlog_format=mixed
#二进制日志的格式，有三种：statement/row/mixed,具体分别不多做解释，这里使用mixed
server-id=101
#为服务器设置一个独一无二的id便于区分，这里使用ip地址的最后一位充当server-id

GRANT replication slave ON *.* TO 'slave'@'%' IDENTIFIED BY '111111';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;

SLAVE vim /etc/my.cnf server-id=102

change master to
master_host='192.168.10.130',
master_user='rep1',
master_password='password',
master_log_file='mysql-bin.000005',
master_log_pos=261;

START SLAVE;
SHOW SLAVE STATUS \G;

半同步复制

主 SET GLOBAL rpl_semi_sync_master_enabled = 1; 从 SET GLOBAL rpl_semi_sync_slave_enabled = 1; stop slave; start slave; 检查 SHOW GLOBAL VARIABLES LIKE '%semi%'; SHOW GLOBAL STATUS LIKE 'rpl_semi%';

备份常用命令

FLUSH TABLES WITH READ LOCK 全局读锁定，执行了命令之后所有库所有表都被锁定只读。一般都是用在数据库联机备份，这个时候数据库的写操作将被阻塞，读操作顺利进行。

LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} 表级别的锁定，可以定制锁定某一个表。例如： lock tables test read; 不影响其他表的写操作。

两个语句在执行的时候都需要注意个特点，就是 隐式提交的语句。在退出mysql终端的时候都会隐式的执行unlock tables。也就是如果要让表锁定生效就必须一直保持对话

查看表结构 desc table;

查看创建表

创建表

CREATE TABLE zzc
(
date int not null primary key,
abstract varchar(255),
account varchar(255),
passwd varchar(255),
encryption_tool varchar(255),
encryption_algorithm varchar(255),
encoding_format varchar(255),
secret_key varchar(255),
remark varchar(255)
);

插入数据

INSERT INTO 表名（字段1，字段2，...）
VALUES (值1，值2，...);
INSERT INTO zzc (date,abstract,account,passwd,encryption_tool,encryption_algorithm,encoding_format,secret_key) VALUE ( 2019051510,"xxx","xxx","xx","openssl","aes-256-cfb","base64","xx");

更新数据 update table set xx where xx ;

更新字段 update zzc set remark=null where date=2019051401;

删除某条数据 DELETE FROM table where xx;

查看正在执行语句 show processlist;