4

I've googled around for this with no luck. I have a master database with one slave. The perms for the slave are set and it runs fine. I'd like to add another slave using a clone of the original server. So far, everything is fine except my slave user is only granted access from one host: 10.10.10.10 . The second slave I'd like to bring up has the IP address 10.10.10.20 and I can't allow access across the entire subnet ( I also can't change the internal IP addresses).

So, I'd like to update the hosts the slaves can access the master to both IP addresses and can seem to figure out how.

Any help is greatly appreciated.

5

Look at the mysql:user table The user is likely enabled only from one host (by name or IP). You can do one of two things:

  • Add a second occurrence of the user with access from the second host. (Most secure.)
  • Change the host criteria to be less restrictive. (Less secure.)

See the MySQL Access Restrictions documentation for possible less restrictive host specifications.

1
  • Thanks @BillThor. That access restrictions doc was exactly what I needed. – brycemcd Mar 29 '11 at 17:31
1

Normally MySQL refer host name instead IP address.

Making a new user called "chaminda" at mysql:user using the MySQL shell:

CREATE USER 'chaminda'@'%' IDENTIFIED BY 'password';

The first thing to do is to provide the user with necessary permission and here I have given all permission to the particular user.

GRANT ALL PRIVILEGES ON * . * TO 'chaminda'@'%';

Reload all the privileges.

FLUSH PRIVILEGES;

Note: Here host Name = % and that means you can access this database server from any host. Giving all privileges to the user is a big risk and that's not a best practice. Further If you already create a user set the necessary permission

If you need retrieve more details use following command

select * from  mysql.user \G;

Result

          User: chaminda
          Password: *4E018IOIPOP89896GFYUIUI25B585CF9F
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
          Reload_priv: Y
        Shutdown_priv: Y
         Process_priv: Y
            File_priv: Y
           Grant_priv: Y
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
         Show_db_priv: Y
           Super_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
         Execute_priv: Y
      Repl_slave_priv: Y
     Repl_client_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
     Create_user_priv: Y
           Event_priv: Y
         Trigger_priv: Y
             ssl_type:
           ssl_cipher:
          x509_issuer:
         x509_subject:
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0

8 rows in set (0.00 sec)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.