MySQL - RDS, Aurora and self-managed databases running on Amazon EC2 - Amazon Data Firehose
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

MySQL - RDS, Aurora and self-managed databases running on Amazon EC2

Note

Firehose supports database as a source in all Amazon Web Services Regions except China Regions, Amazon GovCloud (US) Regions, and Asia Pacific (Malaysia). This feature is in preview and is subject to change. Do not use it for your production workloads.

Create the following SQL procedure in your database and then call the procedure to create watermark table, database user for Firehose access to database, and provide required permissions for the Firehose database user. You can use this procedure for self-hosted MySQL, RDS and Aurora MySQL databases.

Note

Some older database versions may not support the string IF NOT EXISTS in the CREATE PROCEDURE line. In such cases, remove IF NOT EXISTS from the CREATE PROCEDURE and use the rest of the procedure.

DELIMITER //
CREATE PROCEDURE IF NOT EXISTS setupFirehose(IN databaseName TEXT, IN watermarkTableName TEXT, IN firehoseUserName TEXT, IN firehosePassword TEXT)
BEGIN

     -- Create watermark table
    SET @create_watermark_text := CONCAT('CREATE TABLE IF NOT EXISTS ', databaseName, '.', watermarkTableName, '(id varchar(64) PRIMARY KEY, type varchar(32), data varchar(2048))');
    PREPARE createWatermarkTable from @create_watermark_text;
    EXECUTE createWatermarkTable;
    DEALLOCATE PREPARE createWatermarkTable;

    SELECT CONCAT('Created watermark table with name ', databaseName, '.', watermarkTableName) as log;

     -- Create firehose user
    SET @create_user_text := CONCAT('CREATE USER IF NOT EXISTS ''', firehoseUserName, ''' IDENTIFIED BY ''', firehosePassword, '''');
    PREPARE createUser from @create_user_text;
    EXECUTE createUser;
    DEALLOCATE PREPARE createUser;

    SELECT CONCAT('Created user with name ', firehoseUserName) as log;

     -- Grant privileges to firehose user
    -- Edit *.* to database/tables you want to grant Firehose access to
    SET @grant_user_text := CONCAT('GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT, LOCK TABLES 
ON *.* TO ''', firehoseUserName, '''');
    PREPARE grantUser from @grant_user_text;
    EXECUTE grantUser;
    DEALLOCATE PREPARE grantUser;

    SET @grant_user_watermark_text := CONCAT('GRANT CREATE, INSERT, DELETE ON ', watermarkTableName, ' to ', firehoseUserName);
    PREPARE grantUserWatermark from @grant_user_watermark_text;
    EXECUTE grantUserWatermark;
    DEALLOCATE PREPARE grantUserWatermark;

    SELECT CONCAT('Granted necessary permissions to user ', firehoseUserName) AS log;

    FLUSH PRIVILEGES;

     -- Show if binlog enabled/disabled
    SELECT variable_value as "BINARY LOGGING STATUS (log-bin) ::" FROM performance_schema.global_variables WHERE variable_name='log_bin';

END //
DELIMITER ;

Usage

Call this procedure using a, SQL Client.

CALL setupFirehose('database', 'watermark_test', 'new_user', 'Test123');