Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 4 down vote accepted

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.

share|improve this answer
    
Thanks @BillThor. That access restrictions doc was exactly what I needed. – Bryce Mar 29 '11 at 17:31

Normally MySQL refer host name instead IP address

According to your requirement,

Let's start by making a new user called "chaminda" within 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)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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