MySQL: Replicating the MySQL database

Posted by Lee on Server Fault See other posts from Server Fault or by Lee
Published on 2011-03-02T11:41:46Z Indexed on 2011/03/02 15:27 UTC
Read the original article Hit count: 522

Filed under:
|
|
|

Hi guys,

I have a primary write server (server1) which replications to two servers (server2 and server3) which are query servers. I am replicating all databases to these servers including the MySQL database.

When i execute a GRANT as follows replication works perfectly..

GRANT execute,select ON database1.* TO `user1`@`host` IDENTIFIED BY 'password';

However if i did the same GRANT to alter permissions on an existing user without IDENTIFIED clause replication breaks..

Error 'Can't find any matching row in the user table' on query. Default database: 'mysql'. Query: 'GRANT execute,select ON database1.* TO `user`@`host`'

If I try and run the query manually i get the same error..

Server 1:

mysql> SHOW VARIABLES LIKE "%version%";                                                                           
+-------------------------+------------------------------------------------------------+
| Variable_name           | Value                                                      |
+-------------------------+------------------------------------------------------------+
| protocol_version        | 10                                                         | 
| version                 | 5.0.77-log                                                 | 

**my.cnf**
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    old_passwords=1
    symbolic-links=0
    max_allowed_packet = 100M

    log-bin = /var/lib/mysql/logs/borg-binlog.log
    max_binlog_size=50M
    expire_logs_days=7

    [mysql.server]
    user=mysql
    basedir=/var/lib

    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

Server 2:

mysql>  SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------------------------------------+
| Variable_name           | Value                                                      |
+-------------------------+------------------------------------------------------------+
| protocol_version        | 10                                                         | 
| version                 | 5.0.77-log                                                 | 

my.cnf

server-id=12
master-host=x
master-user=x
master-password=x
master-connect-retry=60
relay-log=/var/lib/mysql/borg-relay.log
relay-log-index=/var/lib/mysql/borg-relay-log.index

Thanks for taking a look


Edit: Currently its running fine, until you do the grant which breaks it...

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.128.0.5
                Master_User: repli-ragnarok
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: borg-binlog.002730
        Read_Master_Log_Pos: 4375760
             Relay_Log_File: borg-relay.005489
              Relay_Log_Pos: 4375899
      Relay_Master_Log_File: borg-binlog.002730
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 4375760
            Relay_Log_Space: 4375899
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

Edit: Broken show slave status from history

+----------------------------------+-------------+----------------+-------------+---------------+--------------------+---------------------+-------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State                   | Master_Host | Master_User    | Master_Port | Connect_Retry | Master_Log_File    | Read_Master_Log_Pos | Relay_Log_File    | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error                                                                                                                                                                                                     | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+-------------+----------------+-------------+---------------+--------------------+---------------------+-------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | 10.128.0.5  | repli-valhalla |        3306 |            60 | borg-binlog.002729 |            40429793 | borg-relay.005486 |      40311514 | borg-binlog.002729    | Yes              | No                |                 |                     |                    |                        |                         |                             |       1133 | Error 'Can't find any matching row in the user table' on query. Default database: 'mysql'. Query: 'GRANT execute,select ON auth_tracker.* TO `mail-sin1`@`%.sin1.netline.net.uk` IDENTIFIED BY 'mail-sin1666'' |            0 |            40311375 |        40429932 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL |
+----------------------------------+-------------+----------------+-------------+---------------+--------------------+---------------------+-------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.06 sec)

© Server Fault or respective owner

Related posts about mysql

Related posts about replication