5

I need to create a user with replication slave permission for each database.

When I use the command

GRANT replication slave ON `mydb`.* TO 'user';

the error

Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

is shown.

Is there a way to GRANT replication privilege to only a database?

buddemat
  • 4,552
  • 14
  • 29
  • 49
Joel Ollé Ripoll
  • 451
  • 1
  • 4
  • 15
  • this might be a duplicate question. please see answer [here] :http://stackoverflow.com/questions/13552206/grant-file-on-just-one-database – oozmac Jan 31 '17 at 15:54
  • 1
    I think it's not a duplicate question. In your answer there is no solution for my question – Joel Ollé Ripoll Feb 01 '17 at 19:25

2 Answers2

5

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.

user10089632
  • 5,216
  • 1
  • 26
  • 34
  • an otherwise less viable option is to exclude some databases on the slave by using `replicate-ignore-db=dbname` in the [mysqld] option group , it can take multiple database names, but anyone who can access the configuration file on the slave can change it. – user10089632 Jan 18 '18 at 10:04
-2

You can try this instead.

$ mysql -h 10.20.30.1 -u user -p
mysql> CREATE USER 'repl_slave'@'10.20.30.%' IDENTIFIED BY 'Passw0rdf0rRepl';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_slave'@'10.20.30.1';
oozmac
  • 151
  • 1
  • 2
  • 16