Home / server / Questions / 320125
Accepted
Cem
Asked: 2011-10-11 04:30:26 +0800 CST

Moving MySQL users from one db server to another? [duplicate]

  • 772

Possible Duplicate:
How can I copy MySQL users table from one server to another?

I have a MySQL server with appr. 6000 MySQL users inside. I am now setting up a better MySQL server on a different physical server and need to transfer these 6000 MySQL users from old server to the new one. How can I do this?

The important thing is, permissions and passwords shouldn't change.

mysql

3 Answers

  1. Best Answer

    Dump the mysql.user table (and optionally the mysql.db table) then replace any specific references to the name of the old host with the name of the new host. Import into the new host database and flush the privileges.

    That's not hard is it?

    • 4

  2. Please keep in mind that mysql.user is different between major releases.

    select count(1) from information_schema.columns
where table_schema='mysql' and table_name='user';

    This query brings back

    • 37 for MySQL 5.0
    • 39 for MySQL 5.1
    • 42 for MySQL 5.5.
    • You cannot run this query in MySQL 4.x, but run desc mysql.user and you will see 31 columns.

    There are actually two ways to safely port users from one database to another regardless of the version of MySQL.

    OPTION 1 : Use mk-show-grants (Soon to be called pt-show-grants from Percona Toolkit)

    This dumps out all the MySQL Grants as SQL statements, which is completely portable to any MySQL 5.x instance.

    OPTION 2 : Run these commands (My personal emulation of what mk-show-grants does)

    mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

    The resulting MySQLUserGrants.sql from either option can simply be executed and the grant tables are properly populated.

    • 3

  3. You can do what symcbean explain by phpMyAdmin if you want. Go in the export Tab and chose to export the mysql database (with user table AND db table if you want to keep permissions).

    • 0