Trong quá trình phát triển phần mềm, ta thường cài đặt môi trường dev và môi trường production trên các server khác nhau. Và trong quá trình phát triển, có đôi lúc CSDL (databases) giữa các môi trường có sự khác biệt. Vậy làm thế nào để nhanh chóng kiểm tra sự khác biệt đó và sửa chúng?

mysqldiff là một công cụ hỗ trợ ta việc đó. Hãy cùng dùng thử mysqldiff qua ví dụ đơn giản sau.

Chuẩn bị

Ta dùng vagrant và ansible để tạo 3 server

  • db1: Cài đặt MySQL với CSDL test1 (IP 192.168.44.11)
  • db2: Cài đặt MySQL với CSDL test1 (IP 192.168.44.12)
  • proxy: Có thể kết nối đến cả db1 và db2. Cài đặt mysqldiff (IP 192.168.44.10)

uc?id=1CFrTu3LY3RfJWjkEayt9OHcz7J5Z1XVW&export=download

Bài toán đặt ra

Ta sẽ dùng mysqldiff ở proxy server để kiểm tra sự khác biệt giữa table definition của database test1 và test2

Thiết lập

Ở server db1 ta thiết lập quyền cho user vagrant

CREATE DATABASE test1 CHARACTER SET utf8;

GRANT ALL PRIVILEGES ON test1.* TO vagrant@"192.168.44.10" IDENTIFIED BY '{PASSWORD}' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Tương tự với server db2

CREATE DATABASE test2 CHARACTER SET utf8;

GRANT ALL PRIVILEGES ON test2.* TO vagrant@"192.168.44.10" IDENTIFIED BY '{PASSWORD}' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Ngoài ra ở mỗi server ta cần sửa setting ở file /etc/my.cnf như sau

  1. Comment out dòng bind-address = 127.0.0.1 nếu có khi gặp lỗi sau
# server1 on db1: ... ERROR: Cannot connect to the server1 server.
Error Can't connect to MySQL server on 'db1:3306' (111 Connection refused)
  1. Thêm sql-mode="" khi gặp lỗi sau
ERROR: Query failed. 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'information_schema.TABLES.TABLE_SCHEMA' which is not in SELECT list; this is in
compatible with DISTINCT

So sánh

Trường hợp table definition giống nhau

Đầu tiên ta sẽ tạo 2 table giống hệt nhau trên database test1 và test2

CREATE TABLE table_test_1(
  col1                               int auto_increment NOT NULL
 ,col2                               varchar(30) NOT NULL
 ,col3                               int NOT NULL
 ,col4                               datetime NOT NULL
 ,PRIMARY KEY(col1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

CREATE TABLE table_test_2(
  col21                               int auto_increment NOT NULL
 ,col22                               varchar(30) NOT NULL
 ,col23                               text NOT NULL
 ,PRIMARY KEY(col21)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

So sánh bằng mysqldiff

mysqldiff --server1=vagrant:user[:passwd]@host[:port][:socket] --server2=user[:passwd]@host[:port][:socket] test1:test2 --force

[vagrant@proxy ~]$ mysqldiff --server1=vagrant:{PASSWORD}@db1:3306 --server2=vagrant:{PASSWORD}@db2:3306 test1:test2 --force
# server1 on db1: ... connected.
# server2 on db2: ... connected.
# Comparing `test1` to `test2`                                     [FAIL]
# Object definitions differ. (--changes-for=server1)
#

--- `test1`
+++ `test2`
@@ -1,1 +1,1 @@
-CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */
+CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET utf8 */
# Comparing `test1`.`table_test_1` to `test2`.`table_test_1`       [PASS]
# Comparing `test1`.`table_test_2` to `test2`.`table_test_2`       [PASS]
Success. All objects are the same.

Trong đó

  • --server1 -server2 là thông tin để kết nối đến server db1 và db2
  • --force: mysqldiff sẽ không dừng lại khi tìm thấy khác biệt đầu tiên, mà sẽ tìm hết tất cả sự khác biệt

Theo kết quả trả về thì bảng table_test_1 và table_test_2 trên 2 database test1 và test2 là giống nhau

Trường hợp table definition khác nhau

Ta sẽ xóa table_test_2, và xóa 1 column của bảng table_test_1 trên database test2

DROP TABLE table_test_2;

ALTER TABLE table_test_1
DROP COLUMN col3;

Và kết quả khi so sánh bằng mysqldiff như sau

[vagrant@proxy ~]$ mysqldiff --server1=vagrant:_Gz:YUEX#1@db1:3306 --server2=vagrant:_Gz:YUEX#2@db2:3306 test1:test2 --force
# server1 on db1: ... connected.
# server2 on db2: ... connected.
# WARNING: Objects in server1.test1 but not in server2.test2:
#        TABLE: table_test_2
# Comparing `test1` to `test2`                                     [FAIL]
# Object definitions differ. (--changes-for=server1)
#

--- `test1`
+++ `test2`
@@ -1,1 +1,1 @@
-CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */
+CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET utf8 */
# Comparing `test1`.`table_test_1` to `test2`.`table_test_1`       [FAIL]
# Object definitions differ. (--changes-for=server1)
#

--- `test1`.`table_test_1`
+++ `test2`.`table_test_1`
@@ -1,7 +1,6 @@
 CREATE TABLE `table_test_1` (
   `col1` int(11) NOT NULL AUTO_INCREMENT,
   `col2` varchar(30) NOT NULL,
-  `col3` int(11) NOT NULL,
   `col4` datetime NOT NULL,
   PRIMARY KEY (`col1`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
Compare failed. One or more differences found.

Và lúc muốn tạo câu lệnh sql để sửa database test1 giống test2

[vagrant@proxy ~]$ mysqldiff --server1=vagrant:_Gz:YUEX#1@db1:3306 --server2=vagrant:_Gz:YUEX#2@db2:3306 test1:test2 --force --difftype=sql --changes-for=server1
# server1 on db1: ... connected.
# server2 on db2: ... connected.
# WARNING: Objects in server1.test1 but not in server2.test2:
#        TABLE: table_test_2
# Comparing `test1` to `test2`                                     [FAIL]

# WARNING: Cannot generate SQL statements for these objects.
# Check the difference output for other discrepencies.
--- `test1`
+++ `test2`
@@ -1,1 +1,1 @@
-CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */
+CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET utf8 */
# WARNING: Could not generate changes for {0}. No changes required or not supported difference.
# Comparing `test1`.`table_test_1` to `test2`.`table_test_1`       [FAIL]
# Transformation for --changes-for=server1:
#

ALTER TABLE `test1`.`table_test_1`
  DROP COLUMN col3,
  CHANGE COLUMN col4 col4 datetime NOT NULL;

Compare failed. One or more differences found.

Và lúc muốn tạo câu lệnh sql để sửa database test2 giống test1

[vagrant@proxy ~]$ mysqldiff --server1=vagrant:_Gz:YUEX#1@db1:3306 --server2=vagrant:_Gz:YUEX#2@db2:3306 test1:test2 --force --difftype=sql --changes-for=server2
# server1 on db1: ... connected.
# server2 on db2: ... connected.
# WARNING: Objects in server1.test1 but not in server2.test2:
#        TABLE: table_test_2
# Comparing `test1` to `test2`                                     [FAIL]

# WARNING: Cannot generate SQL statements for these objects.
# Check the difference output for other discrepencies.
--- `test2`
+++ `test1`
@@ -1,1 +1,1 @@
-CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET utf8 */
+CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */
# WARNING: Could not generate changes for {0}. No changes required or not supported difference.
# Comparing `test1`.`table_test_1` to `test2`.`table_test_1`       [FAIL]
# Transformation for --changes-for=server2:
#

ALTER TABLE `test2`.`table_test_1`
  ADD COLUMN col3 int(11) NOT NULL AFTER col2,
  CHANGE COLUMN col4 col4 datetime NOT NULL;

Compare failed. One or more differences found.

Trong đó

  • --difftype: Định dạng hiển thị, có các giá trị là unified (default), context, differ, and sql
  • --changes-for: Nhằm chỉ định server để tạo câu lệnh query chuyển đổi từ server này sang server khác. Ví dụ, cần chuyển định nghĩa của server db2 sang server db1 ta chọn --changes-for=server1

Kết luận

  • mysqldiff là một công cụ tiện lợi giúp ta nhanh chóng kiểm tra sự khác biệt database giữa các môi trường trên các server khác nhau và dễ dàng sửa chúng
  • mysqldiff còn có nhiều option khác có thể tham khảo ở mysqldiff — Identify Differences Among Database Objects

Tham khảo

  1. mysqldiff — Identify Differences Among Database Objects
  2. mysqldiffでテーブル定義の違いをチェックする
  3. mysqldiff を使って継続的に MySQL のデータベーススキーマの差分をチェックする