User privileges

Before you migrate data between databases by using OceanBase Migration Service (OMS) Community Edition, make sure that you have created a database user for each data source as the migration or synchronization user. These users must have the required privileges on the source and destination data sources.

User privileges required when a MySQL database serves as the source

• The database user must have the read privilege on the database from which data is migrated.

  GRANT SELECT ON <database_name>.* TO '<user_name>';
  

• The database user must have the REPLICATION CLIENT and REPLICATION SLAVE privileges to perform incremental synchronization on a MySQL database.

  GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO <user_name> WITH GRANT OPTION;
  

  If you select Allow OMS to automatically write incremental heartbeat data to the instance during incremental synchronization when you add a MySQL data source, OMS needs to create the drc.heartbeat table in the MySQL database and keep the table up to date. In that case, the MySQL database user must have the privileges to create and write the table. For more information about how to add a data source, see Add a MySQL data source.

  • Grant a database user the privilege to create the drc.heartbeat table:

    GRANT CREATE ON drc.heartbeat TO '<user_name>';
    

  • Grant a database user the privilege to write the drc.heartbeat table:

    GRANT INSERT, UPDATE, DELETE ON drc.heartbeat TO '<user_name>';
    

• The database user must have the SELECT *.* privilege to synchronously pull incremental logs.

  GRANT SELECT ON *.* TO '<user_name>';
  

• You must enable binlogs for the MySQL database.

  When OMS is performing a full data migration project, it reads the binlogs of the MySQL database to synchronize incremental data to the destination database in real time. The binlog configuration of the MySQL database is checked in the precheck step of a data migration project. Before you migrate incremental data from a MySQL database by using OMS, you must enable binlogs for the MySQL database.

  1. Log on to the host where the self-managed MySQL database is deployed.

  2. Edit the my.cnf file of the MySQL database.

     The my.cnf file is stored in the /etc/ directory by default.

     log_bin=mysql_bin
     binlog_format=row
     binlog_row_image=full // This setting is required if the source database version is MySQL 5.6 or later.
     

  3. Restart the MySQL database.

     $Mysql_Dir must be replaced with the installation path of the MySQL database.

     [$Mysql_Dir]/bin/mysqladmin -u root -p ******
     [$Mysql_Dir]/bin/safe_mysqld &
     

  4. Log on to the self-managed MySQL database and execute the following SQL statement to confirm that the binlog feature is enabled:

     show variables like '%log_bin%';
     

     In the command output, both log bin and sql log bin must be ON to indicate that the binlog feature is enabled.

User privileges required when a MySQL database serves as the destination

Run the following command to grant privileges to the user in the MySQL database:

GRANT <privilege_type> ON <database_name>.<table_name> TO '<user_name>'@'<host_name>' [WITH GRANT OPTION];

User privileges required when OceanBase Community Edition serves as the source

To synchronize data from OceanBase Community Edition to a Kafka or RocketMQ database, the user must have the following privileges on the source:

• SELECT privilege on the source business database.

• SELECT privilege on the OceanBase, information_schema, and MySQL databases of the source tenant.

• To synchronize incremental data, you need to create a user under the sys tenant of OceanBase Community Edition and grant the SELECT ON *.* privilege to the user.

  The username and password must be the same as those in the config.yaml file.

User privileges required when OceanBase Community Edition serves as the destination

To migrate data from a MySQL database to OceanBase Community Edition, the user must have the following privileges on the destination:

• SELECT, INSERT, UPDATE, and DELETE privileges on the business database.

• SELECT privilege on the OceanBase, information_schema, and MySQL databases.

User privileges required when a Kafka database serves as the destination

If the Kafka database requires authentication, see Add a Kafka data source.

To synchronize data to a Kafka database, the user must have privileges to perform the following operations:

• Create and view topics.

• View topic partition information.

• Write records.

• Read records.

User privileges required when a RocketMQ database serves as the destination

To synchronize data to a RocketMQ database, the user must have privileges to perform the following operations:

• Create and view topics.

• View the information of the topic message queue.

• Write records.

• Read records.