mysqldump: How to ensure order of tables content in generated txt files
up vote
0
down vote
favorite
I run mysqldump on a before and after picture of database before tests
mysqldump --no-create-info --no-create-db --tab=/data/tmp/db1 statsdb
and after tests
mysqldump --no-create-info --no-create-db --tab=/data/tmp/db2 statsdb
So then i do a diff
diff --brief /data/tmp/db1 /data/tmp/db2
Files /data/tmp/db1/details.txt and
/data/tmp/db2/details.txt differ
The trouble is that it results in the txt files, despite having same data created in the db1 and db2 directories do not display the lines in the same order.
Is there a way to force them to sort correctly? Here is the differing files for the record
[root@server ~]# cat /data/tmp/db2/enm_network_element_details.txt
2018-09-26 1 RNC 18.Q1-V.12.40 1 1 1 0
0
2018-09-26 1 MGW 17A-C.1.267 15 15 15 15
0
2018-09-26 1 ERBS 17.Q4-J.1.160 480 480 475 480
479
2018-09-26 1 SGSN-MME 16A-CP09 14 14 14
14 0
2018-09-26 1 MTAS MTAS-1.5 10 10 10 10
0
2018-09-26 1 SIU02 17A 56 56 56 54 0
2018-09-26 1 Router6672 R17B-GA 1 1 1 0
0
2018-09-26 1 RBS 18.Q1-U.4.750 350 350 350 350
0
2018-09-26 1 RNC 17B-V.9.1240 1 1 1 1
0
2018-09-26 1 Router6672 R18A-GA 104 104 104 104
0
2018-09-26 1 MINI-LINK-Indoor M16A-TN-5.4FP-L 18 18
18 5 5
2018-09-26 1 ERBS 18.Q1-J.1.220 1 0 0 0
0
2018-09-26 1 EPG 16B-R13C 6 6 6 6
0
2018-09-26 1 MINI-LINK-6352 M17B-6352-2.8 15 15 15
15 0
2018-09-26 1 MINI-LINK-Indoor M17.Q4-TN-6.1-L 41 41
41 4 4
2018-09-26 1 MINI-LINK-Indoor M17A-TN-6.0-LH- 9 9
9 9 9
2018-09-26 1 TCU02 17A 56 56 56 54 0
2018-09-26 1 DSC 17B-R1A 10 10 10 10 0
2018-09-26 1 RadioNode 17B-R6A19 550 550 550
550 0
2018-09-26 1 RadioNode 17Q4-R25A12 160 160 160
160 158
[root@server~]# cat /data/tmp/db1/enm_network_element_details.txt
2018-09-26 1 RadioNode 17Q4-R25A12 160 160 160
160 158
2018-09-26 1 RadioNode 17B-R6A19 550 550 550
550 0
2018-09-26 1 RNC 18.Q1-V.12.40 1 1 1 0
0
2018-09-26 1 MGW 17A-C.1.267 15 15 15 15
0
2018-09-26 1 ERBS 17.Q4-J.1.160 480 480 475 480
479
2018-09-26 1 MTAS MTAS-1.5 10 10 10 10
0
2018-09-26 1 SIU02 17A 56 56 56 54 0
2018-09-26 1 Router6672 R17B-GA 1 1 1 0
0
2018-09-26 1 SGSN-MME 16A-CP09 14 14 14
14 0
2018-09-26 1 RNC 17B-V.9.1240 1 1 1 1
0
2018-09-26 1 RBS 18.Q1-U.4.750 350 350 350 350
0
2018-09-26 1 Router6672 R18A-GA 104 104 104 104
0
2018-09-26 1 MINI-LINK-Indoor M16A-TN-5.4FP-L 18 18
18 5 5
2018-09-26 1 EPG 16B-R13C 6 6 6 6
0
2018-09-26 1 ERBS 18.Q1-J.1.220 1 0 0 0
0
2018-09-26 1 MINI-LINK-6352 M17B-6352-2.8 15 15 15
15 0
2018-09-26 1 MINI-LINK-Indoor M17.Q4-TN-6.1-L 41 41
41 4 4
2018-09-26 1 MINI-LINK-Indoor M17A-TN-6.0-LH- 9 9
9 9 9
2018-09-26 1 TCU02 17A 56 56 56 54 0
2018-09-26 1 DSC 17B-R1A 10 10 10 10 0
mysql
edited Nov 19 at 17:43
Andy Lester
66.1k1279135
asked Nov 19 at 15:13
Gill
62
add a comment |
up vote
0
down vote
favorite
I run mysqldump on a before and after picture of database before tests
mysqldump --no-create-info --no-create-db --tab=/data/tmp/db1 statsdb
and after tests
mysqldump --no-create-info --no-create-db --tab=/data/tmp/db2 statsdb
So then i do a diff
diff --brief /data/tmp/db1 /data/tmp/db2
Files /data/tmp/db1/details.txt and
/data/tmp/db2/details.txt differ
The trouble is that it results in the txt files, despite having same data created in the db1 and db2 directories do not display the lines in the same order.
Is there a way to force them to sort correctly? Here is the differing files for the record
[root@server ~]# cat /data/tmp/db2/enm_network_element_details.txt
2018-09-26 1 RNC 18.Q1-V.12.40 1 1 1 0
0
2018-09-26 1 MGW 17A-C.1.267 15 15 15 15
0
2018-09-26 1 ERBS 17.Q4-J.1.160 480 480 475 480
479
2018-09-26 1 SGSN-MME 16A-CP09 14 14 14
14 0
2018-09-26 1 MTAS MTAS-1.5 10 10 10 10
0
2018-09-26 1 SIU02 17A 56 56 56 54 0
2018-09-26 1 Router6672 R17B-GA 1 1 1 0
0
2018-09-26 1 RBS 18.Q1-U.4.750 350 350 350 350
0
2018-09-26 1 RNC 17B-V.9.1240 1 1 1 1
0
2018-09-26 1 Router6672 R18A-GA 104 104 104 104
0
2018-09-26 1 MINI-LINK-Indoor M16A-TN-5.4FP-L 18 18
18 5 5
2018-09-26 1 ERBS 18.Q1-J.1.220 1 0 0 0
0
2018-09-26 1 EPG 16B-R13C 6 6 6 6
0
2018-09-26 1 MINI-LINK-6352 M17B-6352-2.8 15 15 15
15 0
2018-09-26 1 MINI-LINK-Indoor M17.Q4-TN-6.1-L 41 41
41 4 4
2018-09-26 1 MINI-LINK-Indoor M17A-TN-6.0-LH- 9 9
9 9 9
2018-09-26 1 TCU02 17A 56 56 56 54 0
2018-09-26 1 DSC 17B-R1A 10 10 10 10 0
2018-09-26 1 RadioNode 17B-R6A19 550 550 550
550 0
2018-09-26 1 RadioNode 17Q4-R25A12 160 160 160
160 158
[root@server~]# cat /data/tmp/db1/enm_network_element_details.txt
2018-09-26 1 RadioNode 17Q4-R25A12 160 160 160
160 158
2018-09-26 1 RadioNode 17B-R6A19 550 550 550
550 0
2018-09-26 1 RNC 18.Q1-V.12.40 1 1 1 0
0
2018-09-26 1 MGW 17A-C.1.267 15 15 15 15
0
2018-09-26 1 ERBS 17.Q4-J.1.160 480 480 475 480
479
2018-09-26 1 MTAS MTAS-1.5 10 10 10 10
0
2018-09-26 1 SIU02 17A 56 56 56 54 0
2018-09-26 1 Router6672 R17B-GA 1 1 1 0
0
2018-09-26 1 SGSN-MME 16A-CP09 14 14 14
14 0
2018-09-26 1 RNC 17B-V.9.1240 1 1 1 1
0
2018-09-26 1 RBS 18.Q1-U.4.750 350 350 350 350
0
2018-09-26 1 Router6672 R18A-GA 104 104 104 104
0
2018-09-26 1 MINI-LINK-Indoor M16A-TN-5.4FP-L 18 18
18 5 5
2018-09-26 1 EPG 16B-R13C 6 6 6 6
0
2018-09-26 1 ERBS 18.Q1-J.1.220 1 0 0 0
0
2018-09-26 1 MINI-LINK-6352 M17B-6352-2.8 15 15 15
15 0
2018-09-26 1 MINI-LINK-Indoor M17.Q4-TN-6.1-L 41 41
41 4 4
2018-09-26 1 MINI-LINK-Indoor M17A-TN-6.0-LH- 9 9
9 9 9
2018-09-26 1 TCU02 17A 56 56 56 54 0
2018-09-26 1 DSC 17B-R1A 10 10 10 10 0
mysql
edited Nov 19 at 17:43
Andy Lester
66.1k1279135
asked Nov 19 at 15:13
Gill
62
SQL tables do not have an intrinsic order because you can order the records when you query them. There is no point in comparing unordered sets.
– Shadow
Nov 19 at 15:48
Again, what problem does it cause that the items are in a different order in the two files? After importing these two files into any sql database, you can specify your own ordering of the resultset on the fly.
– Shadow
Nov 19 at 16:44
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I run mysqldump on a before and after picture of database before tests
mysqldump --no-create-info --no-create-db --tab=/data/tmp/db1 statsdb
and after tests
mysqldump --no-create-info --no-create-db --tab=/data/tmp/db2 statsdb
So then i do a diff
diff --brief /data/tmp/db1 /data/tmp/db2
Files /data/tmp/db1/details.txt and
/data/tmp/db2/details.txt differ
The trouble is that it results in the txt files, despite having same data created in the db1 and db2 directories do not display the lines in the same order.
Is there a way to force them to sort correctly? Here is the differing files for the record
[root@server ~]# cat /data/tmp/db2/enm_network_element_details.txt
2018-09-26 1 RNC 18.Q1-V.12.40 1 1 1 0
0
2018-09-26 1 MGW 17A-C.1.267 15 15 15 15
0
2018-09-26 1 ERBS 17.Q4-J.1.160 480 480 475 480
479
2018-09-26 1 SGSN-MME 16A-CP09 14 14 14
14 0
2018-09-26 1 MTAS MTAS-1.5 10 10 10 10
0
2018-09-26 1 SIU02 17A 56 56 56 54 0
2018-09-26 1 Router6672 R17B-GA 1 1 1 0
0
2018-09-26 1 RBS 18.Q1-U.4.750 350 350 350 350
0
2018-09-26 1 RNC 17B-V.9.1240 1 1 1 1
0
2018-09-26 1 Router6672 R18A-GA 104 104 104 104
0
2018-09-26 1 MINI-LINK-Indoor M16A-TN-5.4FP-L 18 18
18 5 5
2018-09-26 1 ERBS 18.Q1-J.1.220 1 0 0 0
0
2018-09-26 1 EPG 16B-R13C 6 6 6 6
0
2018-09-26 1 MINI-LINK-6352 M17B-6352-2.8 15 15 15
15 0
2018-09-26 1 MINI-LINK-Indoor M17.Q4-TN-6.1-L 41 41
41 4 4
2018-09-26 1 MINI-LINK-Indoor M17A-TN-6.0-LH- 9 9
9 9 9
2018-09-26 1 TCU02 17A 56 56 56 54 0
2018-09-26 1 DSC 17B-R1A 10 10 10 10 0
2018-09-26 1 RadioNode 17B-R6A19 550 550 550
550 0
2018-09-26 1 RadioNode 17Q4-R25A12 160 160 160
160 158
[root@server~]# cat /data/tmp/db1/enm_network_element_details.txt
2018-09-26 1 RadioNode 17Q4-R25A12 160 160 160
160 158
2018-09-26 1 RadioNode 17B-R6A19 550 550 550
550 0
2018-09-26 1 RNC 18.Q1-V.12.40 1 1 1 0
0
2018-09-26 1 MGW 17A-C.1.267 15 15 15 15
0
2018-09-26 1 ERBS 17.Q4-J.1.160 480 480 475 480
479
2018-09-26 1 MTAS MTAS-1.5 10 10 10 10
0
2018-09-26 1 SIU02 17A 56 56 56 54 0
2018-09-26 1 Router6672 R17B-GA 1 1 1 0
0
2018-09-26 1 SGSN-MME 16A-CP09 14 14 14
14 0
2018-09-26 1 RNC 17B-V.9.1240 1 1 1 1
0
2018-09-26 1 RBS 18.Q1-U.4.750 350 350 350 350
0
2018-09-26 1 Router6672 R18A-GA 104 104 104 104
0
2018-09-26 1 MINI-LINK-Indoor M16A-TN-5.4FP-L 18 18
18 5 5
2018-09-26 1 EPG 16B-R13C 6 6 6 6
0
2018-09-26 1 ERBS 18.Q1-J.1.220 1 0 0 0
0
2018-09-26 1 MINI-LINK-6352 M17B-6352-2.8 15 15 15
15 0
2018-09-26 1 MINI-LINK-Indoor M17.Q4-TN-6.1-L 41 41
41 4 4
2018-09-26 1 MINI-LINK-Indoor M17A-TN-6.0-LH- 9 9
9 9 9
2018-09-26 1 TCU02 17A 56 56 56 54 0
2018-09-26 1 DSC 17B-R1A 10 10 10 10 0
mysql
edited Nov 19 at 17:43
Andy Lester
66.1k1279135
asked Nov 19 at 15:13
Gill
62
I run mysqldump on a before and after picture of database before tests
mysqldump --no-create-info --no-create-db --tab=/data/tmp/db1 statsdb
and after tests
mysqldump --no-create-info --no-create-db --tab=/data/tmp/db2 statsdb
So then i do a diff
diff --brief /data/tmp/db1 /data/tmp/db2
Files /data/tmp/db1/details.txt and
/data/tmp/db2/details.txt differ
The trouble is that it results in the txt files, despite having same data created in the db1 and db2 directories do not display the lines in the same order.
Is there a way to force them to sort correctly? Here is the differing files for the record
[root@server ~]# cat /data/tmp/db2/enm_network_element_details.txt
2018-09-26 1 RNC 18.Q1-V.12.40 1 1 1 0
0
2018-09-26 1 MGW 17A-C.1.267 15 15 15 15
0
2018-09-26 1 ERBS 17.Q4-J.1.160 480 480 475 480
479
2018-09-26 1 SGSN-MME 16A-CP09 14 14 14
14 0
2018-09-26 1 MTAS MTAS-1.5 10 10 10 10
0
2018-09-26 1 SIU02 17A 56 56 56 54 0
2018-09-26 1 Router6672 R17B-GA 1 1 1 0
0
2018-09-26 1 RBS 18.Q1-U.4.750 350 350 350 350
0
2018-09-26 1 RNC 17B-V.9.1240 1 1 1 1
0
2018-09-26 1 Router6672 R18A-GA 104 104 104 104
0
2018-09-26 1 MINI-LINK-Indoor M16A-TN-5.4FP-L 18 18
18 5 5
2018-09-26 1 ERBS 18.Q1-J.1.220 1 0 0 0
0
2018-09-26 1 EPG 16B-R13C 6 6 6 6
0
2018-09-26 1 MINI-LINK-6352 M17B-6352-2.8 15 15 15
15 0
2018-09-26 1 MINI-LINK-Indoor M17.Q4-TN-6.1-L 41 41
41 4 4
2018-09-26 1 MINI-LINK-Indoor M17A-TN-6.0-LH- 9 9
9 9 9
2018-09-26 1 TCU02 17A 56 56 56 54 0
2018-09-26 1 DSC 17B-R1A 10 10 10 10 0
2018-09-26 1 RadioNode 17B-R6A19 550 550 550
550 0
2018-09-26 1 RadioNode 17Q4-R25A12 160 160 160
160 158
[root@server~]# cat /data/tmp/db1/enm_network_element_details.txt
2018-09-26 1 RadioNode 17Q4-R25A12 160 160 160
160 158
2018-09-26 1 RadioNode 17B-R6A19 550 550 550
550 0
2018-09-26 1 RNC 18.Q1-V.12.40 1 1 1 0
0
2018-09-26 1 MGW 17A-C.1.267 15 15 15 15
0
2018-09-26 1 ERBS 17.Q4-J.1.160 480 480 475 480
479
2018-09-26 1 MTAS MTAS-1.5 10 10 10 10
0
2018-09-26 1 SIU02 17A 56 56 56 54 0
2018-09-26 1 Router6672 R17B-GA 1 1 1 0
0
2018-09-26 1 SGSN-MME 16A-CP09 14 14 14
14 0
2018-09-26 1 RNC 17B-V.9.1240 1 1 1 1
0
2018-09-26 1 RBS 18.Q1-U.4.750 350 350 350 350
0
2018-09-26 1 Router6672 R18A-GA 104 104 104 104
0
2018-09-26 1 MINI-LINK-Indoor M16A-TN-5.4FP-L 18 18
18 5 5
2018-09-26 1 EPG 16B-R13C 6 6 6 6
0
2018-09-26 1 ERBS 18.Q1-J.1.220 1 0 0 0
0
2018-09-26 1 MINI-LINK-6352 M17B-6352-2.8 15 15 15
15 0
2018-09-26 1 MINI-LINK-Indoor M17.Q4-TN-6.1-L 41 41
41 4 4
2018-09-26 1 MINI-LINK-Indoor M17A-TN-6.0-LH- 9 9
9 9 9
2018-09-26 1 TCU02 17A 56 56 56 54 0
2018-09-26 1 DSC 17B-R1A 10 10 10 10 0
mysql
mysql
edited Nov 19 at 17:43
Andy Lester
66.1k1279135
asked Nov 19 at 15:13
Gill
62
edited Nov 19 at 17:43
Andy Lester
66.1k1279135
asked Nov 19 at 15:13
Gill
62
edited Nov 19 at 17:43
Andy Lester
66.1k1279135
edited Nov 19 at 17:43
Andy Lester
66.1k1279135
edited Nov 19 at 17:43
Andy Lester
66.1k1279135
66.1k1279135
asked Nov 19 at 15:13
Gill
62
asked Nov 19 at 15:13
Gill
62
asked Nov 19 at 15:13
Gill
62
62
SQL tables do not have an intrinsic order because you can order the records when you query them. There is no point in comparing unordered sets.
– Shadow
Nov 19 at 15:48
Again, what problem does it cause that the items are in a different order in the two files? After importing these two files into any sql database, you can specify your own ordering of the resultset on the fly.
– Shadow
Nov 19 at 16:44
add a comment |
SQL tables do not have an intrinsic order because you can order the records when you query them. There is no point in comparing unordered sets.
– Shadow
Nov 19 at 15:48
Again, what problem does it cause that the items are in a different order in the two files? After importing these two files into any sql database, you can specify your own ordering of the resultset on the fly.
– Shadow
Nov 19 at 16:44
SQL tables do not have an intrinsic order because you can order the records when you query them. There is no point in comparing unordered sets.
– Shadow
Nov 19 at 15:48
SQL tables do not have an intrinsic order because you can order the records when you query them. There is no point in comparing unordered sets.
– Shadow
Nov 19 at 15:48
Again, what problem does it cause that the items are in a different order in the two files? After importing these two files into any sql database, you can specify your own ordering of the resultset on the fly.
– Shadow
Nov 19 at 16:44
Again, what problem does it cause that the items are in a different order in the two files? After importing these two files into any sql database, you can specify your own ordering of the resultset on the fly.
– Shadow
Nov 19 at 16:44
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
The option "--order-by-primary" might be what you need.
Source: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#mysqldump-option-summary
answered Nov 19 at 15:42
FloT
425
Tried same but still get different orders in both files, edited question to show you what it looks like
– Gill
Nov 19 at 16:20
Though they look exactly the same. What about sorting the txt files with sort command before running the diff? Or what about comparing the tables in SQL?
– FloT
Nov 19 at 16:43
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
The option "--order-by-primary" might be what you need.
Source: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#mysqldump-option-summary
answered Nov 19 at 15:42
FloT
425
Tried same but still get different orders in both files, edited question to show you what it looks like
– Gill
Nov 19 at 16:20
Though they look exactly the same. What about sorting the txt files with sort command before running the diff? Or what about comparing the tables in SQL?
– FloT
Nov 19 at 16:43
add a comment |
up vote
0
down vote
The option "--order-by-primary" might be what you need.
Source: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#mysqldump-option-summary
answered Nov 19 at 15:42
FloT
425
Tried same but still get different orders in both files, edited question to show you what it looks like
– Gill
Nov 19 at 16:20
Though they look exactly the same. What about sorting the txt files with sort command before running the diff? Or what about comparing the tables in SQL?
– FloT
Nov 19 at 16:43
add a comment |
up vote
0
down vote
up vote
0
down vote
The option "--order-by-primary" might be what you need.
Source: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#mysqldump-option-summary
answered Nov 19 at 15:42
FloT
425
The option "--order-by-primary" might be what you need.
Source: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#mysqldump-option-summary
answered Nov 19 at 15:42
FloT
425
answered Nov 19 at 15:42
FloT
425
answered Nov 19 at 15:42
FloT
425
answered Nov 19 at 15:42
FloT
425
425
Tried same but still get different orders in both files, edited question to show you what it looks like
– Gill
Nov 19 at 16:20
Though they look exactly the same. What about sorting the txt files with sort command before running the diff? Or what about comparing the tables in SQL?
– FloT
Nov 19 at 16:43
add a comment |
Tried same but still get different orders in both files, edited question to show you what it looks like
– Gill
Nov 19 at 16:20
Though they look exactly the same. What about sorting the txt files with sort command before running the diff? Or what about comparing the tables in SQL?
– FloT
Nov 19 at 16:43
Tried same but still get different orders in both files, edited question to show you what it looks like
– Gill
Nov 19 at 16:20
Tried same but still get different orders in both files, edited question to show you what it looks like
– Gill
Nov 19 at 16:20
Though they look exactly the same. What about sorting the txt files with sort command before running the diff? Or what about comparing the tables in SQL?
– FloT
Nov 19 at 16:43
Though they look exactly the same. What about sorting the txt files with sort command before running the diff? Or what about comparing the tables in SQL?
– FloT
Nov 19 at 16:43
add a comment |
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53377563%2fmysqldump-how-to-ensure-order-of-tables-content-in-generated-txt-files%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
SQL tables do not have an intrinsic order because you can order the records when you query them. There is no point in comparing unordered sets.
– Shadow
Nov 19 at 15:48
Again, what problem does it cause that the items are in a different order in the two files? After importing these two files into any sql database, you can specify your own ordering of the resultset on the fly.
– Shadow
Nov 19 at 16:44