删除重复记录,只保留id最大的一条记录的性能测试

 

  8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括

环境

测试表的id为是唯一的,或是自增的主键。

mysql不能直接写循环,只能写在存储过程里。

存储过程usp_batch_insert的参数num_count为插入总行数,参数batch_commit为每批提交的行数。

由于是测试,先把bin
log关闭。在生产上做删除重复记录操作,不能随意关闭,根据业务而定。

SET session sql_log_bin = 0;

创建测试表t3

CREATE TABLE `t3` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `f1` varchar(32) DEFAULT NULL,
  `f2` varchar(32) DEFAULT NULL,
  `ctime` datetime(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

每批量提交的记录表t3_log

CREATE TABLE `t3_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `i` bigint(20) DEFAULT NULL,
  `ctime` datetime(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

构造数据的存储过程

DELIMITER $$
DROP PROCEDURE IF EXISTS `usp_batch_insert`;
CREATE PROCEDURE `usp_batch_insert`(IN `num_count` int,IN `batch_commit` int)
BEGIN
DECLARE i INT; 
SET i = 1;
SET AUTOCOMMIT = 0;

WHILE i <= num_count DO 
  INSERT INTO t3 (f1, f2, ctime) 
  SELECT REPLACE(UUID(), '-', '') AS a, REPLACE(UUID(), '-', '') AS b, NOW(3) AS c;
  SET i = i + 1;
  IF MOD(i, batch_commit) <= 0 THEN
    INSERT INTO t3_log (i, ctime) VALUES (i, NOW(3));
      COMMIT;
    END IF;
END WHILE; 

SET AUTOCOMMIT = 1;
END; $$
DELIMITER ;

生成200万的测试数据

CALL usp_batch_insert(2000000, 5000);

把一部分数据重复

INSERT INTO t3 (f1,f2,ctime) 
SELECT f1, f2, NOW(3) FROM t3 LIMIT 123456,10000;

INSERT INTO t3 (f1,f2,ctime) 
SELECT f1, f2, NOW(3) FROM t3 LIMIT 15234567,254321;

INSERT INTO t3 (f1,f2,ctime) 
SELECT f1, f2, NOW(3) FROM t3 LIMIT 345678,654321;

INSERT INTO t3 (f1,f2,ctime) 
SELECT f1, f2, NOW(3) FROM t3 LIMIT 654321,45678;

INSERT INTO t3 (f1,f2,ctime) 
SELECT f1, f2, NOW(3) FROM t3 LIMIT 886,123456;

INSERT INTO t3 (f1,f2,ctime) 
SELECT f1, f2, NOW(3) FROM t3 LIMIT 15,2000;

数据已经构造完,全表280多万行记录,需要删除的数据有80多万。

下面就来测试下全过程的时间,总耗时是216秒,其中删除部分约29秒。

如果要保留最小id的那行记录,则把max()函数修改为min()函数。

CREATE OR REPLACE TABLE _tmp_t3 (id INT NOT NULL PRIMARY KEY);

INSERT INTO _tmp_t3 (id)
SELECT id
FROM t3 
WHERE id NOT IN (
        SELECT maxid FROM 
                (SELECT max(id) AS  maxid FROM t3
                        GROUP BY f1, f2             
                ) b
);

DELETE a FROM t3 as a INNER JOIN _tmp_t3 as b on b.id = a.id;

如果29秒可能会影响业务,可以做成存储过程,分批删除。

DELIMITER $$
DROP PROCEDURE IF EXISTS `usp_batch_delete`;

CREATE PROCEDURE `usp_batch_delete`(IN `batch_commit` int)
BEGIN
DECLARE i INT; 
DECLARE num_count INT;
SET i = 1; 
SELECT MAX(id) INTO num_count FROM _tmp_t3;
SET AUTOCOMMIT = 0;

WHILE i <= num_count DO

DELETE a FROM t3 as a INNER JOIN _tmp_t3 as b on b.id = a.id AND b.id = i;
SET i = i + 1;

IF MOD(1, batch_commit) >= 0 THEN
COMMIT;
END IF;

END WHILE;

SET AUTOCOMMIT = 1;
END; $$
DELIMITER ;

 

  RESTORE VERIFYONLY from disk=’E:\dvbbs.bak’

 1 (root@localhost mysql3306.sock)[zlm]09:28:50>show binary logs;
 2 +------------------+-----------+
 3 | Log_name         | File_size |
 4 +------------------+-----------+
 5 | mysql-bin.000001 |       177 |
 6 | mysql-bin.000002 |       177 |
 7 | mysql-bin.000003 |   1012934 |
 8 +------------------+-----------+
 9 3 rows in set (0.00 sec)
10 
11 (root@localhost mysql3306.sock)[zlm]09:30:07>flush logs;
12 Query OK, 0 rows affected (0.02 sec)
13 
14 (root@localhost mysql3306.sock)[zlm]09:30:21>delete from flash_test;
15 Query OK, 10000 rows affected (0.05 sec)
16 
17 (root@localhost mysql3306.sock)[zlm]09:30:39>select count(*) from flash_test;
18 +----------+
19 | count(*) |
20 +----------+
21 |        0 |
22 +----------+
23 1 row in set (0.00 sec)
24 
25 (root@localhost mysql3306.sock)[zlm]09:30:47>show binary logs;
26 +------------------+-----------+
27 | Log_name         | File_size |
28 +------------------+-----------+
29 | mysql-bin.000001 |       177 |
30 | mysql-bin.000002 |       177 |
31 | mysql-bin.000003 |   1012981 |
32 | mysql-bin.000004 |    130974 |
33 +------------------+-----------+
34 4 rows in set (0.00 sec)
35 
36 (root@localhost mysql3306.sock)[zlm]09:31:33>

  B:create table tab_new as select col1,col2… from tab_old definition only

    according to the readme.md
doc,using “git” command to download it at the beginning:

  insert into test (userid) values(@i)

 

  具体实现:

1 ns-notepc399+jujun@ns-notepc399 MINGW64 /d/vagrant
2 $ git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
3 Cloning into 'binlog2sql'...
4 remote: Counting objects: 294, done.
5 remote: Total 294 (delta 0), reused 0 (delta 0), pack-reused 294
6 Receiving objects: 100% (294/294), 142.05 KiB | 108.00 KiB/s, done.
7 Resolving deltas: 100% (152/152), done.
8 ns-notepc399+jujun@ns-notepc399 MINGW64 /d/vagrant/binlog2sql (master)

  24、说明:选择从10到15的记录

 

  end

 

  A:create table tab_new like tab_old (使用旧表创建新表)

 

  select max(column_b) from tablename group by column1,column2,…)

website:

  一张表,一旦分组 完成后,查询后只能得到组相关的信息。

 

  DECLARE curObject CURSOR FOR

  
it’s the right time using the binlog2sql to flashback the missing
data:

  16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表

 

  CONVERT(VARCHAR(30),(size*8/1024)) + ‘MB’

 1 [root@zlm3 08:54:58 /root/binlog2sql]
 2 #pwd
 3 /vagrant/binlog2sql
 4 
 5 [root@zlm3 08:56:00 /root/binlog2sql]
 6 #ls -l
 7 total 52
 8 drwxrwxrwx 2 root root    69 Jun  6 07:21 binlog2sql
 9 drwxrwxrwx 2 root root    53 Jun  6 07:21 example
10 -rwxrwxrwx 1 root root 35815 Jun  6 07:21 LICENSE
11 -rwxrwxrwx 1 root root  9749 Jun  6 07:21 README.md
12 -rwxrwxrwx 1 root root    57 Jun  6 07:21 requirements.txt
13 drwxrwxrwx 2 root root    36 Jun  6 07:21 tests
14 
15 [root@zlm3 08:56:20 /root/binlog2sql]
16 #cat requirements.txt 
17 PyMySQL==0.7.11
18 wheel==0.29.0
19 mysql-replication==0.13
20 
21 [root@zlm3 08:57:05 /root/binlog2sql]
22 #pip install -r requirements.txt
23 Collecting PyMySQL==0.7.11 (from -r requirements.txt (line 1))
24   Downloading https://files.pythonhosted.org/packages/c6/42/c54c280d8418039bd2f61284f99cb6d9e0eae80383fc72ceb6eac67855fe/PyMySQL-0.7.11-py2.py3-none-any.whl (78kB)
25     100% |?..?..?..?..?..?..?..?..?..?..?..?..?..?..?..?..| 81kB 145kB/s 
26 Collecting wheel==0.29.0 (from -r requirements.txt (line 2))
27   Downloading https://files.pythonhosted.org/packages/8a/e9/8468cd68b582b06ef554be0b96b59f59779627131aad48f8a5bce4b13450/wheel-0.29.0-py2.py3-none-any.whl (66kB)
28     100% |?..?..?..?..?..?..?..?..?..?..?..?..?..?..?..?..| 71kB 356kB/s 
29 Collecting mysql-replication==0.13 (from -r requirements.txt (line 3))
30   Downloading https://files.pythonhosted.org/packages/dd/23/384047702e694139e9fe75a8ba7ad007e8942fd119ebadabc32ce19f70f2/mysql-replication-0.13.tar.gz
31 Installing collected packages: PyMySQL, wheel, mysql-replication
32   Running setup.py install for mysql-replication ... done
33 Successfully installed PyMySQL-0.7.11 mysql-replication-0.13 wheel-0.29.0
34 
35 [root@zlm3 08:56:20 /root/binlog2sql]
36 #

  where user_name(uid)=@OldOwner

 1 [root@zlm3 07:46:15 /vagrant/pip-10.0.1]
 2 #pwd
 3 /vagrant/pip-10.0.1
 4 
 5 [root@zlm3 07:46:24 /vagrant/pip-10.0.1]
 6 #ls -l
 7 total 104
 8 -rwxrwxrwx 1 vagrant vagrant 14750 Jun  6 07:46 AUTHORS.txt
 9 drwxrwxrwx 1 vagrant vagrant     0 Jun  6 07:47 build
10 drwxrwxrwx 1 vagrant vagrant     0 Jun  6 07:47 dist
11 drwxrwxrwx 1 vagrant vagrant  4096 Apr 19 20:52 docs
12 -rwxrwxrwx 1 vagrant vagrant  1110 Jun  6 07:46 LICENSE.txt
13 -rwxrwxrwx 1 vagrant vagrant   663 Jun  6 07:46 MANIFEST.in
14 -rwxrwxrwx 1 vagrant vagrant 75033 Jun  6 07:46 NEWS.rst
15 -rwxrwxrwx 1 vagrant vagrant  2972 Jun  6 07:46 PKG-INFO
16 -rwxrwxrwx 1 vagrant vagrant   886 Jun  6 07:46 pyproject.toml
17 -rwxrwxrwx 1 vagrant vagrant  1472 Jun  6 07:46 README.rst
18 -rwxrwxrwx 1 vagrant vagrant   627 Jun  6 07:46 setup.cfg
19 -rwxrwxrwx 1 vagrant vagrant  2879 Jun  6 07:46 setup.py
20 drwxrwxrwx 1 vagrant vagrant     0 Apr 19 20:52 src
21 
22 [root@zlm3 07:46:37 /vagrant/pip-10.0.1]
23 #python install setup.py
24 python: can't open file 'install': [Errno 2] No such file or directory
25 
26 [root@zlm3 07:46:51 /vagrant/pip-10.0.1]
27 #python setup.py install
28 /usr/lib64/python2.7/distutils/dist.py:267: UserWarning: Unknown distribution option: 'python_requires'
29   warnings.warn(msg)
30 running install
31 running bdist_egg
32 running egg_info
33 writing requirements to src/pip.egg-info/requires.txt
34 writing src/pip.egg-info/PKG-INFO
35 writing top-level names to src/pip.egg-info/top_level.txt
36 writing dependency_links to src/pip.egg-info/dependency_links.txt
37 writing entry points to src/pip.egg-info/entry_points.txt
38 reading manifest file 'src/pip.egg-info/SOURCES.txt'
39 reading manifest template 'MANIFEST.in'
40 ... -- Here,i omitted the output 'cause too many rows.

  exec sp_changeobjectowner @OwnerName, @NewOwner

 

  (DummyColumn char (8000) not null)

  then,pretend to delete all the records
in the table “flash_table” by accidentally:

  5、说明:删除新表

 

  注:索引是不可更改的,想更改必须删除重新建。

 

  2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用)

 

  关于数据库分页:

    then,use the “pip” command to install
necessary python package below:

  DECLARE @OwnerName as NVARCHAR(128)

    throught the evidence above,i’m afaid
the binlog2sql tool didn’t recognize the right path of the binlog files
and  cast an exception at all.Am i right?

  @StartTime DATETIME,

    what’s amazing is that there’re no
delete operation found in the existent binlog files at all,how can that
happen?let’s use the original tool of MySQL itself’s to see the
details:

  A、left (outer) join:

  • ***binlog2sql is coded by python,in order to use it you need to
    install python and relevant packages.


  • ***some parameter such as “binlog_format=row”,”binlog_row_image=full” etc. in MySQL
    Server should be satisfied with.


  • ***some privileges such as “super”/”replication client”,”replication slave” etc. in MySQL
    Server should be satisfied with too.


  • it seems more inconvenient rather than the MyFlash tool does.

  type vender pcs

  the binlog2sql tool has been
installed,let’s begin the test now:

  DECLARE @OriginalSize int

  1 [root@zlm3 09:36:57 ~/binlog2sql/binlog2sql]
  2 #python binlog2sql.py -h192.168.1.102 -P3306 -uroot -pPassw0rd -dzlm -tflash_table --start-file='mysql-bin.000004'
  3 Traceback (most recent call last):
  4   File "binlog2sql.py", line 149, in <module>
  5     back_interval=args.back_interval, only_dml=args.only_dml, sql_type=args.sql_type)
  6   File "binlog2sql.py", line 46, in __init__
  7     self.connection = pymysql.connect(**self.conn_setting)
  8   File "/usr/lib/python2.7/site-packages/pymysql/__init__.py", line 90, in Connect
  9     return Connection(*args, **kwargs)
 10   File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 706, in __init__
 11     self.connect()
 12   File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 932, in connect
 13     self._request_authentication()
 14   File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1152, in _request_authentication
 15     auth_packet = self._read_packet()
 16   File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1014, in _read_packet
 17     packet.check_error()
 18   File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 393, in check_error
 19     err.raise_mysql_exception(self._data)
 20   File "/usr/lib/python2.7/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
 21     raise errorclass(errno, errval)
 22 pymysql.err.OperationalError: (1045, u"Access denied for user 'root'@'zlm3' (using password: YES)")  --It seems the connection failure.
 23 
 24 [root@zlm3 09:37:32 ~/binlog2sql/binlog2sql]
 25 #mysql
 26 Welcome to the MySQL monitor.  Commands end with ; or \g.
 27 Your MySQL connection id is 15
 28 Server version: 5.7.21-log MySQL Community Server (GPL)
 29 
 30 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 31 
 32 Oracle is a registered trademark of Oracle Corporation and/or its
 33 affiliates. Other names may be trademarks of their respective
 34 owners.
 35 
 36 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 37 
 38 (root@localhost mysql3306.sock)[(none)]09:43:33>alter user 'root'@'192.168.1.102' identified by 'Passw0rd';
 39 ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'192.168.1.102'
 40 (root@localhost mysql3306.sock)[(none)]09:44:10>create user 'root'@'192.168.1.102' identified by 'Passw0rd';
 41 Query OK, 0 rows affected (0.00 sec)
 42 
 43 (root@localhost mysql3306.sock)[(none)]09:47:37>exit
 44 Bye
 45 
 46 [root@zlm3 09:47:40 ~/binlog2sql/binlog2sql]
 47 #python binlog2sql.py -h192.168.1.102 -P3306 -uroot -pPassw0rd -dzlm -tflash_table --start-file='mysql-bin.000004'
 48 Traceback (most recent call last):
 49   File "binlog2sql.py", line 149, in <module>
 50     back_interval=args.back_interval, only_dml=args.only_dml, sql_type=args.sql_type)
 51   File "binlog2sql.py", line 48, in __init__
 52     cursor.execute("SHOW MASTER STATUS")
 53   File "/usr/lib/python2.7/site-packages/pymysql/cursors.py", line 166, in execute
 54     result = self._query(query)
 55   File "/usr/lib/python2.7/site-packages/pymysql/cursors.py", line 322, in _query
 56     conn.query(q)
 57   File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 856, in query
 58     self._affected_rows = self._read_query_result(unbuffered=unbuffered)
 59   File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1057, in _read_query_result
 60     result.read()
 61   File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1340, in read
 62     first_packet = self.connection._read_packet()
 63   File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1014, in _read_packet
 64     packet.check_error()
 65   File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 393, in check_error
 66     err.raise_mysql_exception(self._data)
 67   File "/usr/lib/python2.7/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
 68     raise errorclass(errno, errval)
 69 pymysql.err.InternalError: (1227, u'Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation')  --The user 'root'@'192.168.1.102' is short of imperative privileges.
 70 
 71 [root@zlm3 09:47:43 ~/binlog2sql/binlog2sql]
 72 #mysql
 73 Welcome to the MySQL monitor.  Commands end with ; or \g.
 74 Your MySQL connection id is 17
 75 Server version: 5.7.21-log MySQL Community Server (GPL)
 76 
 77 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 78 
 79 Oracle is a registered trademark of Oracle Corporation and/or its
 80 affiliates. Other names may be trademarks of their respective
 81 owners.
 82 
 83 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 84 
 85 (root@localhost mysql3306.sock)[(none)]09:49:43>grant select,replication slave,replication client on *.* to 'root'@'192.168.1.102';
 86 Query OK, 0 rows affected (0.00 sec)
 87 
 88 (root@localhost mysql3306.sock)[(none)]09:50:24>exit
 89 Bye
 90 
 91 [root@zlm3 09:50:29 ~/binlog2sql/binlog2sql]
 92 #python binlog2sql.py -h192.168.1.102 -P3306 -uroot -pPassw0rd -dzlm -tflash_table --start-file='mysql-bin.000004'
 93 CREATE USER 'root'@'192.168.1.102' IDENTIFIED WITH 'mysql_native_password' AS '*DDFB542AA0BD1D251995D81AEBEB96DEEAD1132F';
 94 GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'192.168.1.102';
 95 
 96 [root@zlm3 09:50:32 ~/binlog2sql/binlog2sql]
 97 #python binlog2sql.py -h192.168.1.102 -P3306 -uroot -pPassw0rd -dzlm -tflash_table --start-file='mysql-bin.000003'
 98 CREATE USER 'repl'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*872ECE72A7EBAC6A183C90D7043D5F359BD85A9E';
 99 GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
100 USE zlm;
101 create table flash_test(
102 id int unsigned not null auto_increment,
103 code bigint unsigned not null default '0',
104 primary key(id)
105 ) engine=innodb charset utf8mb4;
106 USE zlm;
107 CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_insert`(count int)
108 begin
109 declare i int unsigned default 0;
110 start transaction;
111 while i < count do
112 insert into flash_test(code) values(i);
113 set i=i+1;
114 end while;
115 commit;
116 end;
117 
118 [root@zlm3 09:51:06 ~/binlog2sql/binlog2sql]
119 #python binlog2sql.py -h192.168.1.102 -P3306 -uroot -pPassw0rd -dzlm -tflash_table --start-file='mysql-bin.000004' --only-dml --sql-typedelete --stop-never > flashback.sql
120 usage: binlog2sql.py [-h HOST] [-u USER] [-p PASSWORD] [-P PORT]
121                      [--start-file START_FILE] [--start-position START_POS]
122                      [--stop-file END_FILE] [--stop-position END_POS]
123                      [--start-datetime START_TIME] [--stop-datetime STOP_TIME]
124                      [--stop-never] [--help] [-d [DATABASES [DATABASES ...]]]
125                      [-t [TABLES [TABLES ...]]] [--only-dml]
126                      [--sql-type [SQL_TYPE [SQL_TYPE ...]]] [-K] [-B]
127                      [--back-interval BACK_INTERVAL]
128 binlog2sql.py: error: unrecognized arguments: --sql-typedelete
129 
130 [root@zlm3 10:00:12 ~/binlog2sql/binlog2sql]
131 #python binlog2sql.py -h192.168.1.102 -P3306 -uroot -pPassw0rd -dzlm -tflash_table --start-file='mysql-bin.000004' --only-dml --sql-type delete --stop-never > flashback.sql
132 
133 [root@zlm3 10:00:35 ~/binlog2sql/binlog2sql]
134 #cat flashback.sql
135 
136 [root@zlm3 10:00:47 ~/binlog2sql/binlog2sql]
137 #ls -l
138 total 36
139 -rwxrwxrwx 1 root root  7882 Jun  6 07:21 binlog2sql.py
140 -rwxrwxrwx 1 root root 11696 Jun  6 07:21 binlog2sql_util.py
141 -rw-r--r-- 1 root root 10344 Jun  6 09:35 binlog2sql_util.pyc
142 -rw-r--r-- 1 root root     0 Jun  6 10:00 flashback.sql
143 -rwxrwxrwx 1 root root    98 Jun  6 07:21 __init__.py
144 
145 [root@zlm3 10:00:52 ~/binlog2sql/binlog2sql]
146 #python binlog2sql.py -h192.168.1.102 -P3306 -uroot -pPassw0rd -dzlm -tflash_table --start-file='mysql-bin.000003' --only-dml --sql-type delete --stop-never > flashback.sql
147 
148 [root@zlm3 10:01:26 ~/binlog2sql/binlog2sql]
149 #cat flashback.sql
150 
151 [root@zlm3 10:01:32 ~/binlog2sql/binlog2sql]
152 #

  5、检查备份集

 

  SELECT @OriginalSize = size

   
secondly,”pip” command need to be installed,which can be downloaded on
the official

  12、说明:使用外连接

Summury:

  order by name

 

  select * from table1 where time between time1 and time2

 

  我们可以直接写成

 1 [root@zlm3 10:32:40 ~/binlog2sql/binlog2sql]
 2 #python binlog2sql.py -h192.168.1.102 -P3306 -uroot -pPassw0rd -dzlm -tflash_table --start-file='/data/mysql/mysql3306/logs/mysql-bin.000004' --only-dml --sql-type delete --stop-never > flashback3.sql
 3 Traceback (most recent call last):
 4   File "binlog2sql.py", line 149, in <module>
 5     back_interval=args.back_interval, only_dml=args.only_dml, sql_type=args.sql_type)
 6   File "binlog2sql.py", line 53, in __init__
 7     raise ValueError('parameter error: start_file %s not in mysql server' % self.start_file)
 8 ValueError: parameter error: start_file /data/mysql/mysql3306/logs/mysql-bin.000004 not in mysql server
 9 
10 [root@zlm3 10:32:54 ~/binlog2sql/binlog2sql]
11 #ls -l /data/mysql/mysql3306/logs/mysql-bin.000004
12 -rw-r----- 1 mysql mysql 131467 Jun  6 09:50 /data/mysql/mysql3306/logs/mysql-bin.000004  --The binlog file is right there,it shows that it's no need to specify the absolute path in parameter "--start-file"
13 
14 [root@zlm3 10:34:07 ~/binlog2sql/binlog2sql]
15 #

  19、说明:删除重复记录

 

  BEGIN — update

 

  GO

 

  select newid()

 

  add column_b int identity(1,1)

 1 (root@localhost mysql3306.sock)[zlm]09:27:16>create table flash_test(
 2     -> id int unsigned not null auto_increment,
 3     -> code bigint unsigned not null default '0',
 4     -> primary key(id)
 5     -> ) engine=innodb charset utf8mb4;
 6 Query OK, 0 rows affected (0.02 sec)
 7 
 8 (root@localhost mysql3306.sock)[zlm]09:27:26>delimiter $$
 9 (root@localhost mysql3306.sock)[zlm]09:27:41>create procedure pro_insert (count int)
10     -> begin
11     -> declare i int unsigned default 0;
12     -> start transaction;
13     -> while i < count do
14     -> insert into flash_test(code) values(i);
15     -> set i=i+1;
16     -> end while;
17     -> commit;
18     -> end;
19     -> $$
20 Query OK, 0 rows affected (0.00 sec)
21 
22 (root@localhost mysql3306.sock)[zlm]09:27:41>delimiter ;
23 (root@localhost mysql3306.sock)[zlm]09:27:42>call pro_insert(10000);
24 Query OK, 0 rows affected (0.33 sec)
25 
26 (root@localhost mysql3306.sock)[zlm]09:27:55>select count(*) from flash_test;
27 +----------+
28 | count(*) |
29 +----------+
30 |    10000 |
31 +----------+
32 1 row in set (0.01 sec)
33 
34 (root@localhost mysql3306.sock)[zlm]09:28:43>select * from flash_test limit 5;
35 +----+------+
36 | id | code |
37 +----+------+
38 |  1 |    0 |
39 |  2 |    1 |
40 |  3 |    2 |
41 |  4 |    3 |
42 |  5 |    4 |
43 +----+------+
44 5 rows in set (0.00 sec)
45 
46 (root@localhost mysql3306.sock)[zlm]09:28:50>

  删除视图:drop view viewname

    unfortunately it still doesn’t
work,i’m confused and have no idea now,maybe i’ll test it again text
time.Let’s see then.

  8、说明:更改某个表

    the
latest version is 10.0.1 now,i choose the tar package to
downl**
oad.**

  右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。

  1 [root@zlm3 10:12:51 ~/binlog2sql/binlog2sql]
  2 #mysqlbinlog -vv --base64-output=decode-rows /data/mysql/mysql3306/logs/mysql-bin.000004 > flashback1.sql
  3 
  4 [root@zlm3 10:13:32 ~/binlog2sql/binlog2sql]
  5 #ls -l
  6 total 1536
  7 -rwxrwxrwx 1 root root    7882 Jun  6 07:21 binlog2sql.py
  8 -rwxrwxrwx 1 root root   11696 Jun  6 07:21 binlog2sql_util.py
  9 -rw-r--r-- 1 root root   10344 Jun  6 09:35 binlog2sql_util.pyc
 10 -rw-r--r-- 1 root root 1532363 Jun  6 10:13 flashback1.sql
 11 -rw-r--r-- 1 root root       0 Jun  6 10:01 flashback.sql
 12 -rwxrwxrwx 1 root root      98 Jun  6 07:21 __init__.py
 13 
 14 [root@zlm3 10:20:49 ~/binlog2sql/binlog2sql]
 15 #cat flashback1.sql | sed -n '1,100p'
 16 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
 17 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 18 DELIMITER /*!*/;
 19 # at 4
 20 #180606  9:30:21 server id 1023306  end_log_pos 123 CRC32 0xea71a6c0     Start: binlog v 4, server v 5.7.21-log created 180606  9:30:21
 21 # Warning: this binlog is either in use or was not closed properly.
 22 # at 123
 23 #180606  9:30:21 server id 1023306  end_log_pos 194 CRC32 0x7fe5f98f     Previous-GTIDs
 24 # 5c77c31b-4add-11e8-81e2-080027de0e0e:1-12
 25 # at 194
 26 #180606  9:30:38 server id 1023306  end_log_pos 259 CRC32 0x7792ab23     GTID    last_committed=0    sequence_number=1    rbr_only=yes
 27 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
 28 SET @@SESSION.GTID_NEXT= '5c77c31b-4add-11e8-81e2-080027de0e0e:13'/*!*/;
 29 # at 259
 30 #180606  9:30:38 server id 1023306  end_log_pos 330 CRC32 0x3eb43079     Query    thread_id=10    exec_time=0    error_code=0
 31 SET TIMESTAMP=1528270238/*!*/;
 32 SET @@session.pseudo_thread_id=10/*!*/;
 33 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 34 SET @@session.sql_mode=1436549152/*!*/;
 35 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 36 /*!\C utf8 *//*!*/;
 37 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
 38 SET @@session.lc_time_names=0/*!*/;
 39 SET @@session.collation_database=DEFAULT/*!*/;
 40 BEGIN
 41 /*!*/;
 42 # at 330
 43 #180606  9:30:38 server id 1023306  end_log_pos 383 CRC32 0x31c37710     Table_map: `zlm`.`flash_test` mapped to number 177
 44 # at 383
 45 #180606  9:30:38 server id 1023306  end_log_pos 8595 CRC32 0xd8a33ccb     Delete_rows: table id 177
 46 # at 8595
 47 #180606  9:30:38 server id 1023306  end_log_pos 16807 CRC32 0x1105fcdc     Delete_rows: table id 177
 48 # at 16807
 49 #180606  9:30:38 server id 1023306  end_log_pos 25019 CRC32 0x4702cdba     Delete_rows: table id 177
 50 # at 25019
 51 #180606  9:30:38 server id 1023306  end_log_pos 33231 CRC32 0xf49afbfa     Delete_rows: table id 177
 52 # at 33231
 53 #180606  9:30:38 server id 1023306  end_log_pos 41443 CRC32 0x8fc2a48e     Delete_rows: table id 177
 54 # at 41443
 55 #180606  9:30:38 server id 1023306  end_log_pos 49655 CRC32 0x947c8ca2     Delete_rows: table id 177
 56 # at 49655
 57 #180606  9:30:38 server id 1023306  end_log_pos 57867 CRC32 0x4d1b4f53     Delete_rows: table id 177
 58 # at 57867
 59 #180606  9:30:38 server id 1023306  end_log_pos 66079 CRC32 0x76275b27     Delete_rows: table id 177
 60 # at 66079
 61 #180606  9:30:38 server id 1023306  end_log_pos 74291 CRC32 0x99c0b33c     Delete_rows: table id 177
 62 # at 74291
 63 #180606  9:30:38 server id 1023306  end_log_pos 82503 CRC32 0x132f757d     Delete_rows: table id 177
 64 # at 82503
 65 #180606  9:30:38 server id 1023306  end_log_pos 90715 CRC32 0x047a5f63     Delete_rows: table id 177
 66 # at 90715
 67 #180606  9:30:38 server id 1023306  end_log_pos 98927 CRC32 0xf5026c08     Delete_rows: table id 177
 68 # at 98927
 69 #180606  9:30:38 server id 1023306  end_log_pos 107139 CRC32 0xd58c93f3     Delete_rows: table id 177
 70 # at 107139
 71 #180606  9:30:38 server id 1023306  end_log_pos 115351 CRC32 0x8a7ea487     Delete_rows: table id 177
 72 # at 115351
 73 #180606  9:30:38 server id 1023306  end_log_pos 123563 CRC32 0xe0450750     Delete_rows: table id 177
 74 # at 123563
 75 #180606  9:30:38 server id 1023306  end_log_pos 130943 CRC32 0x336c7539     Delete_rows: table id 177 flags: STMT_END_F
 76 ### DELETE FROM `zlm`.`flash_test`
 77 ### WHERE
 78 ###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
 79 ###   @2=0 /* LONGINT meta=0 nullable=0 is_null=0 */
 80 ### DELETE FROM `zlm`.`flash_test`
 81 ### WHERE
 82 ###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
 83 ###   @2=1 /* LONGINT meta=0 nullable=0 is_null=0 */
 84 ### DELETE FROM `zlm`.`flash_test`
 85 ### WHERE
 86 ###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
 87 ###   @2=2 /* LONGINT meta=0 nullable=0 is_null=0 */
 88 ### DELETE FROM `zlm`.`flash_test`
 89 ### WHERE
 90 ###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
 91 ###   @2=3 /* LONGINT meta=0 nullable=0 is_null=0 */
 92 ### DELETE FROM `zlm`.`flash_test`
 93 ### WHERE
 94 ###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
 95 ###   @2=4 /* LONGINT meta=0 nullable=0 is_null=0 */
 96 ### DELETE FROM `zlm`.`flash_test`
 97 ### WHERE
 98 ###   @1=6 /* INT meta=0 nullable=0 is_null=0 */
 99 ###   @2=5 /* LONGINT meta=0 nullable=0 is_null=0 */
100 ### DELETE FROM `zlm`.`flash_test`
101 ### WHERE
102 ###   @1=7 /* INT meta=0 nullable=0 is_null=0 */
103 ###   @2=6 /* LONGINT meta=0 nullable=0 is_null=0 */
104 ### DELETE FROM `zlm`.`flash_test`
105 ### WHERE
106 ###   @1=8 /* INT meta=0 nullable=0 is_null=0 */
107 ###   @2=7 /* LONGINT meta=0 nullable=0 is_null=0 */
108 ### DELETE FROM `zlm`.`flash_test`
109 ### WHERE
110 ###   @1=9 /* INT meta=0 nullable=0 is_null=0 */
111 ###   @2=8 /* LONGINT meta=0 nullable=0 is_null=0 */
112 ### DELETE FROM `zlm`.`flash_test`
113 ### WHERE
114 ###   @1=10 /* INT meta=0 nullable=0 is_null=0 */
115 ###   @2=9 /* LONGINT meta=0 nullable=0 is_null=0 */  -- This is the deletion of record no.10 in the original table "flash_table".
116 
117 [root@zlm3 10:22:32 ~/binlog2sql/binlog2sql]
118 #

  FROM sysfiles

    here’s the github
address:

  DECLARE @LogicalFileName sysname,

 

  SELECT ‘Final Size of ‘ + db_name() + ‘ LOG is ‘ +

    today,i’ll using the open source
tool named “binlog2sql” which is release by danfengchao to do some
flashback test.

  9、说明:创建视图:create view viewname as select statement

  8、说明:创建索引:create [unique] index idxname on tabname(col….)

  最大:select max(field1) as maxvalue from table1

  TRUNCATE TABLE table1

  INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。

  GO

  EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。

  select type,sum(case vender when ‘A’ then pcs else 0 end),sum(case vender when ‘C’ then pcs else 0 end),sum(case vender when ‘B’ then pcs else 0 end) FROM tablename group by type

  exec sp_changeobjectowner ‘tablename’,’dbo’

  SELECT @Counter = @Counter + 1

  –重建索引

  select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

  drop table tabname

  USE tablename — 要操作的数据库名

  insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件

  exec sp_change_users_login ‘update_one’,’newname’,’oldname’

  @OldOwner as NVARCHAR(128),

  DBCC REINDEX

  DBCC SHRINKFILE

  10、SQL SERVER中直接循环写入数据

  3、压缩数据库

  电脑 A 1

  说明:删除主键: Alter table tabname drop primary key(col)

  set @i=1

  14、说明:前10条记录

  from sysobjects

  select name from syscolumns where id=object_id(‘TableName’)

  Setup / initialize

  CONVERT(VARCHAR(30),size) + ‘ 8K pages or ‘ +

  end

  1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用)

  注意:在top后不能直接跟一个变量,所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列,如果top后还有具体的字段,这样做是非常有好处的。因为这样可以避免 top的字段如果是逻辑索引的,查询的结果后实际表中的不一致(逻辑索引中的数据有可能和数据表中的不一致,而查询时如果处在索引则首先查询索引)

  5、说明:显示文章、提交人和最后回复时间

  @MaxMinutes INT,

  BEGIN — Outer loop.

  (select a from tableA ) except (select a from tableB) except (select a from tableC)

  13、对数据库进行操作:

  15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)

  set @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘] where ‘ + @strWhere

  4、转移数据库给新用户以已存在用户权限

  手机 B 3

  SQL: select * from 日程安排 where datediff(‘minute’,f开始时间,getdate())>5

  6、修复数据库

  select * from (SELECT a,b,c FROM a) T where t.a > 1;

  17、说明:随机取出10条数据

  “where 1=1” 是表示选择全部 “where 1=2”全部不选,

  B: EXCEPT 运算符

发表评论

电子邮件地址不会被公开。 必填项已用*标注