- id
- 第一个字段必须是 id 自增长
- 主键
- 只有一个主键 ==> id，但针对具体业务可以对其他列增加约束项
- time
- 表中建议是有
createTime和updateTime字段
- 表中建议是有
- 业务字段加注释
- 指定底层引擎
InnoDB：默认都使用这个- ` MyISAM
：不建议使用，MySQL` 自身的表会使用
- 指定字符集
下面一个常规表的创建 sql
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) DEFAULT NULL COMMENT '用户名称',
`age` int(3) DEFAULT NULL COMMENT '用户年龄',
`createuser` varchar(200) DEFAULT NULL,
`createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updateuser` varchar(200) DEFAULT NULL,
`updatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `rzdata_un` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
案例
使用
MySQLbinlog恢复数据
- 先备份当前数据(相当于做每周的数据库备份操作)
# 备份 tmp 数据库
[danner@iZbp110y4xddxaifhzq3qiZ arch]$ mysqldump -uroot -p tmp > ~/data/test.sql
# 查看当前 binlog 中的 position 点
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 322 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
- 插入数据
mysql> insert into tab1(cx,cy) values('F',25),('G',26);
# 现实中这个 position 545 是根据当前数据内容在 binlog 中确定的
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 545 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
- 删除数据
mysql> delete from tab1 where cx in ('F','G');
Query OK, 2 rows affected (0.00 sec)
- 再此增加数据
mysql> insert into tab1(cx,cy) values('H',27);
- 恢复之前被误删的数据
# 指定起始和结束 position，当然也可以用时间
[danner@iZbp110y4xddxaifhzq3qiZ arch]$ sudo mysqlbinlog --no-defaults --start-position=322 --stop-position=545 /usr/local/mysql/arch/mysql-bin.000001 > ~/data/bak.sql
# 导入
[danner@iZbp110y4xddxaifhzq3qiZ arch]$ sudo mysql -uroot -p < ~/data/bak.sql
恢复单表
思考题：如何恢复指定的单表呢？
mysqlbinlog 只支持到 database 级别，若恢复时两个 position 之间还涉及其他表的修改。那么在 mysqlbinlog 抽取时其他表的写 sql语句也会被抽取，导致重刷是出错(其他表的数据没被删除，重刷会多数据或写数据失败)。
提供两个解决方案：
-
创建新用户只赋予指定表的权限，然后在指定恢复时指定新用户来执行。这样的话就不会操作同个库里的其他表数据，但需要加
-f# 创建新用户 hcy ，只赋予操作 test.t1 表权限 GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.`t1` TO 'hcy'@'localhost' identified by '123456'; GRANT SUPER ON `test`.`t1` TO 'hcy'@'localhost' identified by '123456'; # 重刷，指定 hcy 用户，加 -f mysqlbinlog --no-defaults -vv --base64-output=decode-rows -d test \ mysql-bin.000001 | mysql -S /tmp/mysql3308.sock -uhcy -p123456 -f -
在
binlog中挑选出指定表的sql语句
# 过滤带行号的 t1 表的insert，update和delete
cat binlog.log -n |awk '/# insert t1/{f=1} f; /*/{f=0}' |grep -v '*' >binlog.sqlcat
cat binlog.log -n |awk '/# update t1/{f=1} f; /*/{f=0}' |grep -v '*' >>binlog.sqlcat
cat binlog.log -n |awk '/# delete from t1/{f=1} f; /*/{f=0}' |grep -v '*'>>binlog.sql
