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 with a MySQL database, the native MySQL binary log functionalithy must be enabled on the source database. You will need to connect to the Source Environment Database server using either Option A. Systems Manager (easiest) or Option B ssh/putty.

Option A. Create a Systems Manager connection:

  1. In the Mangagment Console switch to the US East (N. Virginia) region where the source database server has already been registered with Systems Manager under the name gameday-db

change-region

  1. Go to AWS Console > Services > Systems Manager > Node Management > Session Manager and click the Start Session button.

sessions-manager

  1. You should see a list of two instances for which Systems Managers sessions are available. Select the gameday-db instance and click the Start Session button.

target-instances

  1. A logged-in session window on the source database server running a Unix command line will automatically open where you can configure the database server.

session-window

More information on connecting to on-premise servers with Systems Manager can be found in the Systems Manager documentation.

  1. Now skip Option B. below and scroll down to Enable binary log on the source database.

Option B. Create a ssh/putty connection

  1. ssh 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

  1. Mac OS users and Windows 10 users with command line ssh enabled can follow the command line ssh client instructons in this article.

  2. All other Windows users can use the “putty” tool by following instructions in this article.

End of Options

Enable binary log on the source database

  1. 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

  2. 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.

  1. Update the MySQL configuration in the /etc/mysql/my.cnf file.

    Run the following commands to create the file:

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

    Then use the following command to append the new configuration information under the [mysqld] section of the configuration file.

    sudo sed -i '/\[mysqld\]/a \
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' /etc/mysql/my.cnf

  2. Restart MySQL service to apply changes.

    sudo service mysql restart

    This restart will interrupt the source database, but only for a few seconds. If binary logging had already been enabled no disruption would be necessary.

  3. 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 should show the BINARY LOGGING STATUS set to ON, and the BINARY LOG FORMAT set to ROW as on the screenshot below: expected-results

In case of problems - check the /var/log/mysql/error.log file for errors e.g. with the command less /var/log/mysql/error.log

  1. You can now click the Terminate button and confirm to end the Systems Manager session. Then make sure to switch back to the Oregon region. (Or if you choose Option B. earlier then exit from the SSH session).

change-region-back

  1. You are ready to setup replication on the next step.