Thanks Boros, this helps a lot.
--- On Tue, 3/15/11, petya <petya@stripped> wrote:
> From: petya <petya@stripped>
> Subject: Re: Certain replication
> To: replication@stripped
> Date: Tuesday, March 15, 2011, 1:07 PM
> Hi,
>
> In the end, they are. Grants are only special by handling
> the privilege cache. In case of binlog do and ignore db,
> this is dependent only on the current schema.
> Let me demonstrate:
> mysql> show master status;
> +------------------+----------+--------------+------------------+
> | File
> | Position | Binlog_Do_DB |
> Binlog_Ignore_DB |
> +------------------+----------+--------------+------------------+
> | mysql-bin.000018 | 106 | mysql
> |
> |
> +------------------+----------+--------------+------------------+
> 1 row in set (0.00 sec)
>
> mysql> use mysql
> Database changed
> mysql> grant all on binlogtest.* to
> 'withuse'@'localhost' identified by 'aaa';
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> use binlogtest;
> Database changed
> mysql> grant all on binlogtest.* to
> 'withoutuse'@'localhost' identified by 'aaa';
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> Bye
> # ./current/bin/mysqlbinlog ./binlogs/mysql-bin.000018
> /*!40019 SET @@session.max_insert_delayed_threads=0*/;
> /*!50003 SET
> @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
> DELIMITER /*!*/;
> # at 4
> #110315 19:00:50 server id 1 end_log_pos 106
> Start: binlog v 4, server v
> 5.1.54-rel12.5-log created 110315 19:00:50 at startup
> # Warning: this binlog is either in use or was not closed
> properly.
> ROLLBACK/*!*/;
> BINLOG '
> Uql/TQ8BAAAAZgAAAGoAAAABAAQANS4xLjU0LXJlbDEyLjUtbG9nAAAAAAAAAAAAAAAAAAAAAAAA
> AAAAAAAAAAAAAAAAAABSqX9NEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
> '/*!*/;
> # at 106
> #110315 19:02:01 server id 1 end_log_pos 256
> Query thread_id=27
> exec_time=0 error_code=0
> use mysql/*!*/;
> SET TIMESTAMP=1300212121/*!*/;
> SET @@session.pseudo_thread_id=27/*!*/;
> SET @@session.foreign_key_checks=1,
> @@session.sql_auto_is_null=1, @@session.unique_checks=1,
> @@session.autocommit=1/*!*/;
> SET @@session.sql_mode=0/*!*/;
> SET @@session.auto_increment_increment=1,
> @@session.auto_increment_offset=1/*!*/;
> /*!\C latin1 *//*!*/;
> SET
>
> @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33/*!*/;
> SET @@session.lc_time_names=0/*!*/;
> SET @@session.collation_database=DEFAULT/*!*/;
> grant all on binlogtest.* to 'withuse'@'localhost'
> identified by 'aaa'
> /*!*/;
> DELIMITER ;
> # End of log file
> ROLLBACK /* added by mysqlbinlog */;
> /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
>
> In the binary log, you only see the withuse user. Because
> with binlog-do-db, if your current schema isn't mysql (you
> can check that with select schema()), your statements won't
> be written to binlog -> they won't be replicated. It is a
> good practice to avoid binlog-do* and binlog-ignore*, unless
> you have a very well behaving application, and you know what
> are you doing.
>
> Hope it's all clear no, I see people having problems with
> binlog-do* and binlog-ignore* options quite often.
>
> Peter Boros
>
> On 03/15/2011 06:24 PM, Johan De Meersman wrote:
> > ----- Original Message -----
> >> From: "Database System"<database100@stripped>
> >>
> >> Does GRANT command should be replicated?
> >> In my master my.ini file, I have
> binlog-do-db=mysql, But I granted a
> >> user privilege, it was added on the master server,
> it was not
> >> replicated. It is not in the binlog file. I think
> any change to the
> >> mysql database should be replicated by my setting.
> What's wrong?
> >
> > Mmm... GRANT/REVOKE aren't, technically, statements
> against a database, they're statements against the
> authentication system. I suspect the binlog-do-db is why
> they don't replicate.
> >
> > Row-based replication may replicate them, as that
> replicates actual database changes instead of statements;
> but that option comes with it's own caveats.
> >
> >
>
> -- MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe: http://lists.mysql.com/replication?unsub=1
>
>
