How can I transfer MySQL USERS & PASSWORD from one databse to another? I have users on both databases.?

Question

I just want to transfer the users I don't have on the second database if possible. I'm using MySQL 5 and my Host is 1and1.com. If you need any additional info. to help answer my question let me know. Thanks in advance.

Answer 1

Hello,



I don't know what access of hosting 1and1.com has but you need admin rights to backup the users and password.



mysqldump --all-databases -u root -p > databases.sql



The above command will dump all the databases including the users associated with that. That is the only way you can grab the user info table.



mysql> use mysql

Database changed

mysql> show tables;

+---------------------------+

| Tables_in_mysql |

+---------------------------+

| columns_priv |

| db |

| event |

| func |

| general_log |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| host |

| plugin |

| proc |

| procs_priv |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

| user_info |

+---------------------------+

22 rows in set (0.08 sec)





You could try and backup the "mysql" database but not recommended. It is best to backup --all-databases and let MySQL deal with the user info associated to each database.



If you don't care about the other database, just drop them and keep the 2 default databases. You could try this tool:

http://dev.mysql.com/downloads/gui-tools/5.0.html



Which is an administration tool for MySQL



Good Luck.



BTW: I believe you wont have root on your shared server, so the best way to do this is to contact tech support for your host and let them back up the user/pass for you.

Answer 2

It really depends on if you have admin rights or not. If you do have admin rights, then you should be able to export the user tables and import them into the other databases. MySQL is simple in that it lets you do things like that.

Answer 3

Both answers! You can dump the mysql DB and import it into your other DB............Careful.....To import in to the other DB,



insert into TableName (select * from YourExportedData where user not in TableName.user)

^NOT TESTED SQL, ONLY FOR EXAMPLE^



What you want to do is insert those rows where the user doesn't already exist or you may end up with duplicate user names.