Skip to main content

MySQL Data Transfer

Data transfer is an essential skill for database administrators and developers working with MySQL. Whether you're migrating to a new server, creating backups, or sharing data between applications, understanding how to efficiently move data between MySQL databases is crucial.

Introduction to MySQL Data Transfer​

MySQL data transfer refers to the process of moving data from one MySQL database to another. This could be between:

  • Different databases on the same server
  • Different servers in the same network
  • Different servers across different networks or environments

Effective data transfer ensures data integrity, minimizes downtime, and maintains relationships between tables and records.

Common MySQL Data Transfer Scenarios​

Before diving into specific methods, let's consider when you might need to transfer MySQL data:

  • Server migration: Moving from an old server to a new one
  • Environment transitions: Copying data from development to testing or production
  • Backup and recovery: Creating backups for disaster recovery
  • Data sharing: Providing database copies to teammates or other departments
  • Database scaling: Distributing data across multiple servers

Method 1: Using mysqldump for Data Transfer​

The mysqldump utility is one of the most common and reliable methods for transferring MySQL data.

Basic mysqldump Export​

bash
mysqldump -u username -p database_name > backup.sql

This command creates a SQL file containing all the table structures and data from the specified database.

Importing the Dump File​

After generating the dump file, you can import it into a different MySQL server:

bash
mysql -u username -p database_name < backup.sql

Example: Transferring a Specific Table​

If you only need to transfer certain tables, specify them after the database name:

bash
mysqldump -u username -p database_name table1 table2 > specific_tables.sql

One-Line Transfer Between Servers​

For direct server-to-server transfer without creating an intermediate file:

bash
mysqldump -u source_user -p database_name | mysql -h destination_server -u dest_user -p database_name

This pipes the output of mysqldump directly into the mysql client connected to the destination server.

Method 2: Using MySQL Workbench for Data Transfer​

MySQL Workbench provides a graphical interface for data migration that can be simpler for beginners.

Steps for Data Migration with Workbench:​

  1. Open MySQL Workbench
  2. Navigate to Server > Data Migration
  3. Configure source and target connections
  4. Select schemas to migrate
  5. Configure migration options
  6. Review the migration plan
  7. Execute the migration

This approach is user-friendly but may be slower for very large databases compared to command-line methods.

Method 3: Using SELECT INTO OUTFILE and LOAD DATA INFILE​

For transferring specific data, you can use SQL commands directly.

Exporting Data to a File​

sql
SELECT * 
FROM employees 
INTO OUTFILE '/tmp/employees.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Importing Data from a File​

sql
LOAD DATA INFILE '/tmp/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Note: This method requires file system access on both the source and destination servers. You'll also need to ensure the MySQL user has FILE privileges.

Method 4: Replication for Continuous Data Transfer​

MySQL replication sets up a master-slave relationship between databases, allowing continuous data transfer.

Setting Up Basic Replication​

On the master server, edit the my.cnf file:

ini
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = database_name

On the slave server:

ini
[mysqld]
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log

Then on the master, create a replication user:

sql
CREATE USER 'repl_user'@'slave_ip' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'slave_ip';

Get the master's binary log position:

sql
SHOW MASTER STATUS;

Finally, on the slave server:

sql
CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=123;

START SLAVE;

Replication is ideal for minimal-downtime migrations and keeping a hot standby server.

Performance Considerations​

When transferring large amounts of data, consider these optimization techniques:

  1. Split large tables: Break large tables into smaller chunks for transfer
  2. Disable constraints: Temporarily disable foreign key constraints during import
  3. Optimize network: Use compression when transferring over networks
  4. Schedule transfers: Perform transfers during off-peak hours
  5. Increase buffer sizes: Adjust MySQL configuration parameters like max_allowed_packet

Example of importing with optimizations:

sql
SET foreign_key_checks = 0;
SET unique_checks = 0;
SET autocommit = 0;

-- Import data here

COMMIT;
SET autocommit = 1;
SET unique_checks = 1;
SET foreign_key_checks = 1;

Real-World Example: Migrating Production Database​

Let's walk through a complete example of migrating a production database to a new server with minimal downtime:

Phase 1: Initial Transfer​

bash
# Create a compressed dump file excluding system tables
mysqldump --routines --triggers --events --single-transaction \
  --set-gtid-purged=OFF --compress --databases myapp_db \
  -u admin -p > myapp_db_backup.sql.gz

# Copy the file to the new server
scp myapp_db_backup.sql.gz user@new-server:/tmp/

# Import on the new server
gunzip -c /tmp/myapp_db_backup.sql.gz | mysql -u admin -p

Phase 2: Configure Replication to Catch Up Changes​

bash
# On the source server, get binary log position
SHOW MASTER STATUS;

# On the destination server, set up replication
CHANGE MASTER TO
  MASTER_HOST='old-server',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000123',
  MASTER_LOG_POS=456;

START SLAVE;

Phase 3: Switchover​

bash
# On the old server, prevent further writes
FLUSH TABLES WITH READ LOCK;

# Check that the slave is caught up
SHOW SLAVE STATUS\G

# If slave is caught up, update application connection settings
# Then release the lock on the old server
UNLOCK TABLES;

This approach minimizes downtime while ensuring all data is transferred correctly.

Troubleshooting Common Issues​

Connection Timeouts​

If your export or import times out:

bash
# Use the --net_buffer_length parameter
mysqldump --net_buffer_length=16384 --max_allowed_packet=32M \
  -u username -p database_name > backup.sql

Access Denied Errors​

If you encounter "Access Denied" errors, check:

  1. MySQL user permissions on both servers
  2. File system permissions if using LOAD DATA INFILE or SELECT INTO OUTFILE
  3. Firewall settings for cross-server transfers

Import Failures Due to Constraints​

If your import fails due to constraint violations:

sql
-- Before importing
SET foreign_key_checks = 0;

-- After importing
SET foreign_key_checks = 1;

Summary​

MySQL data transfer is a fundamental skill that involves multiple techniques depending on your specific needs:

  • mysqldump is versatile and reliable for most transfers
  • MySQL Workbench offers a user-friendly interface for migrations
  • SELECT INTO OUTFILE/LOAD DATA INFILE works well for specific data transfers
  • Replication provides continuous data transfer with minimal downtime

The best approach depends on factors like database size, downtime tolerance, and your specific requirements. For critical production systems, testing your transfer process in a staging environment first is always recommended.

Practice Exercises​

  1. Transfer a single table from one database to another using mysqldump
  2. Set up a replication between two MySQL servers on your local machine
  3. Export data from a table to CSV using SELECT INTO OUTFILE and import it into a different table
  4. Use MySQL Workbench to migrate a database between two servers
  5. Write a shell script that automates the backup and transfer of a MySQL database

Additional Resources​

By mastering these data transfer techniques, you'll be well-equipped to handle database migrations, backups, and other data movement tasks efficiently and reliably.


If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)