Configure Source Database

Run DMS Replication Task with Change Data Capture (CDC)

To ensure minimal downtime for the database migration, we’re going to use continuous replication of changes (also known as Change Data Capture (CDC)) from the source database to the target database. For more information about CDC and native CDC support of AWS DMS see this article.

Enable binary log on the source database

For AWS DMS continuous replication from MySQL database, you’ll need to enable the binary log and make configuration changes on the source database.

  1. Login to the Source Environment Database server

    For self-paced lab - information needed to access the Database environment is described in the Output section of the ApplicationMigrationWorkshop CloudFormation Template.

    Database Server login information

    For AWS Event - information needed to access the Database environment is described at Database IP, Database Username and Database SSH Key on the Team Dashboard.

    Database Server login information

    If you’re not sure how to use SSH to access servers, check the following:

  2. Grant additional privileges to the wordpress-user database user

    Run the following commands on the database server:

    sudo mysql -u root -pAWSRocksSince2006
    
    GRANT REPLICATION CLIENT ON *.* to 'wordpress-user';
    GRANT REPLICATION SLAVE ON *.* to 'wordpress-user';
    GRANT SUPER ON *.* to 'wordpress-user';
    exit
    
  3. Create a folder for the bin logs

    Run the following commands on the database server:

    sudo su - 
    mkdir /var/lib/mysql/binlogs
    chown -R mysql:mysql /var/lib/mysql/binlogs
    exit
    

    More information on the binary log can be found in the MySQL documentation.

  4. Create and modify /etc/mysql/my.cnf file

    Run the following to edit the file:

    sudo su -
    cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/my.cnf
    chown -R mysql:mysql /etc/mysql/my.cnf
    nano /etc/mysql/my.cnf
    

    Then add the following information under the [mysqld] section, save the file and exit nano:

    server_id=1
    log-bin=/var/lib/mysql/binlogs/log
    binlog_format=ROW
    expire_logs_days=1
    binlog_checksum=NONE
    binlog_row_image=FULL
    log_slave_updates=TRUE
    performance_schema=ON
    
  5. Restart MySQL service to apply changes

    Back in the console, run the following command to apply the changes:

    sudo service mysql restart
    

    Applying those changes requires mysql service restart. This will interrupt the source database for a few seconds.

  6. Test the changes

    Make sure the update in /etc/mysql/my.cnf took effect, by running the following commands:

    sudo mysql -u root -pAWSRocksSince2006
    
    select variable_value as "BINARY LOGGING STATUS (log-bin) :: "
     from performance_schema.global_variables where variable_name='log_bin';
    
    select variable_value as "BINARY LOG FORMAT (binlog_format) :: "
     from performance_schema.global_variables where variable_name='binlog_format';
    
    exit
    

    The output must show the BINARY LOGGIN STATUS set to ON, as on the screenshot below: expected-results

    If that’s the case - you can exit from the SSH, in case of problems - check the /var/log/mysqld.log file for errors.