Compare two MySQL databases [closed]

by

If you’re working with small databases I’ve found running mysqldump on both databases with the --skip-comments and --skip-extended-insert options to generate SQL scripts, then running diff on the SQL scripts works pretty well.

By skipping comments you avoid meaningless differences such as the time you ran the mysqldump command. By using the --skip-extended-insert command you ensure each row is inserted with its own insert statement. This eliminates the situation where a single new or modified record can cause a chain reaction in all future insert statements. Running with these options produces larger dumps with no comments so this is probably not something you want to do in production use but for development it should be fine. I’ve put examples of the commands I use below:

mysqldump --skip-comments --skip-extended-insert -u root -p dbName1>file1.sql
mysqldump --skip-comments --skip-extended-insert -u root -p dbName2>file2.sql
diff file1.sql file2.sql

More Related Contents:

  1. How do I store large no.of small entries in Mysql?
  2. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  3. 1052: Column ‘id’ in field list is ambiguous
  4. Location of my.cnf file on macOS
  5. How to do Inheritance Modeling in Relational Databases?
  6. What’s the difference between ” = null” and ” IS NULL”?
  7. Maximum number of records in a MySQL database table
  8. Swapping column values in MySQL
  9. What’s the difference between MyISAM and InnoDB? [duplicate]
  10. MySQL stored procedures use them or not to use them
  11. Is there an ANSI SQL alternative to the MYSQL LIMIT keyword?
  12. Is there a Profiler equivalent for MySql? [closed]
  13. Cast int to varchar
  14. Import SQL file into mysql
  15. How to skip certain database tables with mysqldump?
  16. “where 1=1” statement [duplicate]
  17. How do you debug MySQL stored procedures?
  18. Meaning of “n:m” and “1:n” in database design
  19. How to set a MySQL row to READ-ONLY?
  20. MySQL: LAST_INSERT_ID() returns 0
  21. mySQL – Create a New Table Using Data and Columns from Three Tables
  22. Calculating a Moving Average MySQL?
  23. Case Sensitive collation in MySQL
  24. Can’t import database through phpmyadmin file size too large
  25. What is the best way to do incremental backups in MySQL?
  26. #1130 – Host ‘localhost’ is not allowed to connect to this MySQL server
  27. WooCommerce: Finding the products in database
  28. Are there any functions in MySQL like dense_rank() and row_number() like Oracle?
  29. Optimistic locking in MySQL
  30. mySQL SELECT upcoming birthdays

Leave a Comment