mysql 查询数据 并自增加1

MariaDB [test]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| jeebbs |
| mysql |
| performance_schema |
| shop |
| test |
| xtrabackup |
| zrlog |
+--------------------+
8 rows in set (0.00 sec)
MariaDB [test]> use test;
Database changed
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb1 |
| tb2 |
+----------------+
2 rows in set (0.00 sec)
MariaDB [test]> select * from tb1;
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 3 | simple | 23 |
| 4 | alex | 34 |
| 5 | simle | 23 |
+----+--------+-----+
3 rows in set (0.00 sec)
下面开始进行场景模拟
[root@node01 ~]#crontab -l
0 4 * * * /usr/bin/mysqldump -uroot -p -B test -F -R -x --master-data=2 gzip >/tmp/test_$(date +%F).sql.gz
命令的语法格式：
mysqldump [OPTIONS] database [tables]：备份单个库，或库指定的一个或多个表
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]：备份一个或多个库
mysqldump [OPTIONS] --all-databases [OPTIONS]：备份所有库
[root@node01 ~]# mysqldump -uroot -B test -F -R -x --master-data=2 |gzip >/tmp/test_$(date +%F).sql.gz
[root@node01 ~]# ll /tmp/
total 504
-rw-r--r-- 1 root root 513534 Apr 17 01:32 test_2018-04-17.sql.gz
参数说明：
-A, --all-databases  备份所有表
-x, --lock-all-tables：锁定所有表
-l, --lock-tables：锁定备份的表
--single-transaction：启动一个大的单一事务实现备份
-B, --databases db_name1 db_name2 ...：备份指定的数据库
-C, --compress：压缩传输；
-E, --events：备份指定库的事件调度器；
-R, --routines：备份存储过程和存储函数；
--master-data[=#]：
1：记录CHANGE MASTER TO语句；此语句未被注释；
2：记录为注释语句；
--flush-logs, -F：锁定表之后执行flush logs命令；
-----------------
待到数据库备份完成，就不用担心数据丢失了，因为有完全备份数据在！！
由于上面在全备份的时候使用了-F选项，那么当数据备份操作刚开始的时候系统就会自动刷新log，这样就会自动产生
一个新的binlog日志，这个新的binlog日志就会用来记录备份之后的数据库“增删改”操作
[root@node01 ~]# mysql -e "show master status"
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 245 | | |
+------------------+----------+--------------+------------------+
也就是说， mysql-bin.000003 是用来记录4:00之后对数据库的所有“增删改”操作。
2)
早上9点上班了，由于业务的需求会对数据库进行各种“增删改”操作。
比如：在ops库下member表内插入、修改了数据等等：
先是早上进行插入数据：
MariaDB [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [test]> insert into tb1(name,age)values('张三',23),('李四',24),('店小二',28);
Query OK, 3 rows affected (0.52 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [test]> update tb1 set name='诸葛亮' where id=5;
Query OK, 1 row affected (0.52 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [test]> select * from tb1;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 3 | simple | 23 |
| 4 | alex | 34 |
| 5 | 诸葛亮 | 23 |
| 6 | 张三 | 23 |
| 7 | 李四 | 24 |
| 8 | 店小二 | 28 |
+----+-----------+-----+
6 rows in set (0.00 sec)
在中午12:00的时候，误删了一条数据，过了一会才知道数据被误删了，此时表里又插入了许多数据
MariaDB [test]> delete from tb1 where id=5;
MariaDB [test]> insert into tb1(name,age)values('张三丰',23),('李奎',24),('曹操',28);
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
这种时候，一定不要慌张！！！
先仔细查看最后一个binlog日志，并记录下关键的pos点，到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步)；
先备份一下最后一个binlog日志文件
[root@node01 mysql]# cp -a mysql-bin.000003 /root/
接着执行一次刷新日志索引操作，重新开始新的binlog日志记录文件。按理说mysql-bin.000003
这个文件不会再有后续写入了，因为便于我们分析原因及查找ops节点，以后所有数据库操作都会写入到下一个日志文件
[root@node01 mysql]# mysql -e "flush logs"
[root@node01 mysql]# mysql -e "show master status"
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 245 | | |
+------------------+----------+--------------+------------------+
读取binlog日志，分析问题。
MariaDB [(none)]> show binlog events in 'mysql-bin.000003'\G;
*************************** 1. row ***************************
Log_name: mysql-bin.000003
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 245
Info: Server ver: 5.5.56-MariaDB, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000003
Pos: 245
Event_type: Query
Server_id: 1
End_log_pos: 313
Info: BEGIN
*************************** 3. row ***************************
Log_name: mysql-bin.000003
Pos: 313
Event_type: Intvar
Server_id: 1
End_log_pos: 341
Info: INSERT_ID=6
*************************** 4. row ***************************
Log_name: mysql-bin.000003
Pos: 341
Event_type: Query
Server_id: 1
End_log_pos: 479
Info: use `test`; insert into tb1(name,age)values('张三',23),('李四',24),('店小二',28)
*************************** 5. row ***************************
Log_name: mysql-bin.000003
Pos: 479
Event_type: Xid
Server_id: 1
End_log_pos: 506
Info: COMMIT /* xid=143 */
*************************** 6. row ***************************
Log_name: mysql-bin.000003
Pos: 506
Event_type: Query
Server_id: 1
End_log_pos: 574
Info: BEGIN
*************************** 7. row ***************************
Log_name: mysql-bin.000003
Pos: 574
Event_type: Query
Server_id: 1
End_log_pos: 679
Info: use `test`; update tb1 set name='诸葛亮' where id=5
*************************** 8. row ***************************
Log_name: mysql-bin.000003
Pos: 679
Event_type: Xid
Server_id: 1
End_log_pos: 706
Info: COMMIT /* xid=144 */
*************************** 9. row ***************************
Log_name: mysql-bin.000003
Pos: 706
Event_type: Query
Server_id: 1
End_log_pos: 774
Info: BEGIN
*************************** 10. row ***************************
Log_name: mysql-bin.000003
Pos: 774
Event_type: Query
Server_id: 1
End_log_pos: 863
Info: use `test`; delete from tb1 where id=5
*************************** 11. row ***************************
Log_name: mysql-bin.000003
Pos: 863
Event_type: Xid
Server_id: 1
End_log_pos: 890
Info: COMMIT /* xid=146 */
*************************** 12. row ***************************
Log_name: mysql-bin.000003
Pos: 890
Event_type: Query
Server_id: 1
End_log_pos: 958
Info: BEGIN
*************************** 13. row ***************************
Log_name: mysql-bin.000003
Pos: 958
Event_type: Intvar
Server_id: 1
End_log_pos: 986
Info: INSERT_ID=9
*************************** 14. row ***************************
Log_name: mysql-bin.000003
Pos: 986
Event_type: Query
Server_id: 1
End_log_pos: 1124
Info: use `test`; insert into tb1(name,age)values('张三丰',23),('李奎',24),('曹操',28)
*************************** 15. row ***************************
Log_name: mysql-bin.000003
Pos: 1124
Event_type: Xid
Server_id: 1
End_log_pos: 1151
Info: COMMIT /* xid=147 */
*************************** 16. row ***************************
Log_name: mysql-bin.000003
Pos: 1151
Event_type: Rotate
Server_id: 1
End_log_pos: 1194
Info: mysql-bin.000004;pos=4
16 rows in set (0.00 sec)
ERROR: No query specified
通过分析，造成数据库破坏的pos点区间是介于 774--863 之间(这是按照日志区间的pos节点算的)。
先把凌晨4点全备份的数据恢复：
[root@node01 ~]# mysql -uroot 
但是这仅仅只是恢复了当天凌晨4点之前的数据，在4:00--12:00之间的数据还没有恢复回来！！
怎么办呢？
莫慌！这可以根据前面提到的mysql-bin.000003的新binlog日志进行恢复。
恢复命令的语法格式：
mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
通过刚刚分析binlog日志，我们已经找到了造成数据库破坏的pos点区间是介于 774--863 之间
a)完全恢复(需要手动vim编辑mysql-bin.000003，将那条drop语句剔除掉)
b)指定pos结束点恢复(部分恢复)：
-stop-position=706 pos结束节点(按照事务区间算，是706)恢复截止到706
--start-position=958 pos开始点
[root@node01 ~]# mysqlbinlog mysql-bin.000003 --stop-position=706 > step1.sql
[root@node01 ~]# mysqlbinlog mysql-bin.000003 --start-position=958 > step2.sql
[root@node01 ~]# mysql -uroot --database=test 
[root@node01 ~]# mysql -uroot --database=test 
MariaDB [(none)]> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [test]> select * from tb1;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 3 | simple | 23 |
| 4 | alex | 34 |
| 5 | 诸葛亮 | 23 |
| 6 | 张三 | 23 |
| 7 | 李四 | 24 |
| 8 | 店小二 | 28 |
| 9 | 张三丰 | 23 |
| 10 | 李奎 | 24 |
| 11 | 曹操 | 28 |
+----+-----------+-----+
9 rows in set (0.00 sec)