Dùng mysqldiff xác định khác biệt giữa CSDL trên các server khác nhau
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)
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
- 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)
- 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