MySQL

The most popular open-source relational database, perfect for web applications and data validation workflows.

Overview

MySQL is a reliable, open-source SQL database widely used for web applications, e-commerce platforms, and data-driven systems. The Data Testing connector provides seamless integration for validating MySQL data across environments.

Ideal for:

• ✅ Web application databases

• ✅ Cross-environment validation

• ✅ Migration verification

• ✅ Data synchronization checks

---

⚙️ Configuration Parameters

Parameter	Description	Required	Example
Host URL	Server address or hostname	✅ Yes	mysql-prod.example.com
Port	MySQL connection port	✅ Yes	3306
Database Name	Target database	✅ Yes	production_db
Username	Database user	✅ Yes	validator_user
Password	Authentication credential	✅ Yes	••••••••••
Query	SQL SELECT statement	✅ Yes	See examples below

Configuration Screenshot

MySQL Configuration

---

📖 Getting Started

Step 1: Gather Connection Details

Obtain these details from your database administrator:

• ✅ MySQL server hostname/IP

• ✅ Connection port (default: 3306)

• ✅ Database name

• ✅ Username (preferably read-only)

• ✅ User password

• ✅ Network access (firewall rules)

Step 2: Create Read-Only User

Security Best Practice: Create a dedicated read-only user for validations.

-- Create validation user with SELECT-only permissions
CREATE USER 'data_validator'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT ON production_db.* TO 'data_validator'@'%';
FLUSH PRIVILEGES;

Step 3: Configure the Connector

1. Navigate to your job configuration

2. Select MySQL as the data source

3. Fill in all connection parameters

4. Click Test Connection

5. Write your data query

6. Click Verify Query

Step 4: Write Your Query

See SQL Query Examples section below.

---

📝 SQL Query Examples

Basic Select Query

SELECT 
  id,
  name,
  email,
  status,
  created_at
FROM customers
WHERE created_at >= '2024-01-01'
ORDER BY id ASC
LIMIT 10000;

Filtered Query with Aggregation

SELECT 
  category,
  COUNT(*) as total_items,
  SUM(price) as total_value,
  AVG(price) as avg_price
FROM products
WHERE status = 'active'
GROUP BY category
ORDER BY total_value DESC;

Join Multiple Tables

SELECT 
  o.order_id,
  o.order_date,
  c.customer_name,
  c.email,
  SUM(oi.quantity) as total_items
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2024-01-01'
GROUP BY o.order_id
ORDER BY o.order_date DESC;

Date-Based Partitioning

SELECT *
FROM transactions
WHERE transaction_date = CURDATE()
  AND status = 'completed'
ORDER BY transaction_id ASC
LIMIT 50000;

---

🚀 Features & Capabilities

Pagination Support

Enable pagination controls for large datasets:

-- Use LIMIT for pagination
SELECT * FROM large_table
LIMIT 10000 OFFSET 0;  -- First 10k rows

SELECT * FROM large_table
LIMIT 10000 OFFSET 10000;  -- Next 10k rows

Alignment Keys for Comparisons

Specify keys for matching rows during Compare jobs:

Choose columns that uniquely identify records:

• Single key: id, email, reference_id

• Composite key: customer_id, order_id

• Natural key: email (if unique)

Query Optimization Tips

Optimization	Benefit	Example
Add WHERE clause	Reduce data volume	WHERE status = 'active'
Use indexes	Faster execution	Ensure columns have indexes
Projection	Less data transfer	SELECT id, name not SELECT *
Limit results	Reduce memory	LIMIT 100000
Avoid functions	Better index usage	Compare raw values

---

🔐 Security Best Practices

Essential Security Measures:

1. ✅ Create dedicated read-only users

2. ✅ Use strong passwords

3. ✅ Restrict user access by host/network

4. ✅ Enable SSL/TLS connections

5. ✅ Never hardcode credentials

6. ✅ Use environment variables or secrets management

7. ✅ Enable query audit logging

8. ✅ Rotate credentials regularly

SSL/TLS Connection

-- Verify SSL is enabled
SHOW VARIABLES LIKE 'have_ssl';

-- Force SSL for connections
CREATE USER 'validator_user'@'localhost' IDENTIFIED BY 'password' REQUIRE SSL;

---

🐛 Troubleshooting

Issue	Solution
Connection refused	Check host, port, firewall rules
Authentication failed	Verify username/password, check user permissions
Query timeout	Add WHERE clause, use LIMIT, check indexes
No data returned	Verify table/column names, check WHERE conditions
Memory errors	Reduce result set size, use pagination
Permission denied	Ensure user has SELECT grant on database

---

📊 Performance Considerations

Large Dataset Handling

For datasets exceeding 1 million rows:

-- Use pagination and batching
SELECT * FROM large_table
WHERE id BETWEEN 0 AND 1000000
ORDER BY id
LIMIT 100000;

-- For subsequent batches:
SELECT * FROM large_table
WHERE id BETWEEN 1000001 AND 2000000
ORDER BY id
LIMIT 100000;

Index Strategy

-- Verify indexes exist for WHERE clause columns
SHOW INDEX FROM table_name;

-- Create index if needed
CREATE INDEX idx_status ON customers(status);
CREATE INDEX idx_date ON orders(created_at);

-- Composite index for multiple conditions
CREATE INDEX idx_composite ON transactions(customer_id, status, created_at);

---

💾 Maintenance Tips

Task	Frequency	Benefit
Update statistics	Weekly	Improves query planning
Rebuild indexes	Monthly	Optimizes performance
Check table integrity	Weekly	Detects corruption early
Monitor slow queries	Ongoing	Identifies bottlenecks
Review permissions	Quarterly	Maintains security

---

Related Documentation

• SQL Connectors Guide

• Data Source Overview

• Create Compare Job

• Create Evaluate Job

• DB to DB Demo

---

FAQ

Q: What port does MySQL use? A: Default is 3306, but verify with your administrator.

Q: Can I use MySQL over the internet? A: Yes, but ensure network security and use encrypted connections.

Q: What's the query result size limit? A: No hard limit, but large results may cause timeouts.

Q: Can I validate encrypted columns? A: Validation happens at the database level post-decryption.

Q: Do I need superuser access? A: No, read-only SELECT permissions are sufficient.