Search code examples
sqlmysqldatabasedatabase-replicationprivileges

How to grant replication privilege to a database in MySQL?

I need to create a user with REPLICATION SLAVE privilege for each database.

When I use the command:

GRANT REPLICATION SLAVE ON `mydb`.* TO 'user';

The error is shown:

Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

Is there a way to grant REPLICATION SLAVE privilege to only a database?

Solution

  • You can't restrict GRANT REPLICATION SLAVE to only one database, so you must use

    GRANT REPLICATION SLAVE ON *.* TO 'user'@'host'

    But you can restrict the replication itself :

    • put the following command in your configuration file (my.ini) on the master slave:

    --replicate-do-db=db_name

    EDIT :

    As the latter is rather for the slave server, it can easily be messed up with. So you'll want to do it on the master server, using either :
    [mysqld]

    binlog-do-db=replicated_db_name

    for the databases that you want to replicate

    OR

    binlog-ignore-db=ignored_db_name

    for the databases that you don't want to be replicated

    • For both commands : to specify more than one database, use this option multiple times.

    Warning:

    Excluding databases on the server using the last two commands, has the effect that no statement about them will be included in the binary log file which will compromise the backup procedure.