LinuxQuestions.org - [SOLVED] MySQL grant tables - how can I import them from another DB?

- Linux - Software (https://www.linuxquestions.org/questions/linux-software-2/)

- - MySQL grant tables - how can I import them from another DB? (https://www.linuxquestions.org/questions/linux-software-2/mysql-grant-tables-how-can-i-import-them-from-another-db-904622/)

MySQL grant tables - how can I import them from another DB?

I'm setting up a new MySQL instance intended to replace a previous one. Both are version 5.1 running on Ubuntu. Nothing is to be changed except which host they are running on.

I believe, in theory, I could simply shut down the old one (briefly), copy all the files to the new one, and start both, and this would include the grant tables, and everything else. And if I don't see an alternative, this is what I will do.

I do make a full backups using "mysqldump --all-databases" 3 times a day and send them over to backup servers. So I have the data. But I don't see any grant tables in this. So if I create new grant tables on the new server, they won't be a replica of what the old server has ... and importing the backup data won't make the grant tables be that way, either, since the grant tables aren't (apparently) in this data.

FYI, I'm also now concerned I don't have a backup of the grant tables.

Is there a "proper" way to replicate/import the grant tables from one server to another? Are they in a separate file I could just copy (during a database shutdown to be sure all is in sync)?

Are you talking about the db table in the mysql data base? That plus the user table in mysql pretty much has what you need, methinks.

Hope this helps some.

Quote:

Originally Posted by tronayne (Post 4480339)

Are you talking about the db table in the mysql data base? That plus the user table in mysql pretty much has what you need, methinks.

Hope this helps some.

I'm talking about what mysql_install_db would do if I were setting up a new blank database. Since I'm setting up one that needs to be exactly identical to another, and don't know what needs to go into the grant table (other people have changed that), I want to merely replicate. I did not find any means in mysql_install_db to replicate this data. Also, I need to replicate the same passwords and everything. I do not see these steps, at least as identified like this, in the reference manual for 5.1.

I went ahead and shutdown MySQL and rsync'd everything to the new server. It was down for only about 20 seconds to let rsync copy all the data out. Once I got apparmor updated (forgot to do that for the first couple of tries to start MySQL on the new server) on the new server it worked. The apparmor change was because everything in /var/lib/mysql had been moved to /home/mysql (on both old and new). The new site seems to be running fine (just not yet live to the world).

Sounds like you got there.

The mysql data base has the login, password and data base permissions in the table user; you can unload that table and load it into a new data base to set up your users (watch for the first column, host, though). If you do that in a text file, you can edit the host column manually with, oh, vi or whatever ails ya.

The other table, db, is where permission go when you grant. Similarly, you can unload that to a text file, edit the host column if necessary, then load it into the mysql data base.

You may want to take a look-see at the host column in both of those tables and change the name (or address) if needed from the old box to the new box (assuming that your site is accessing MySQL via a network connection -- localhost should work just fine without any fiddling around). You can do the change with a simple SQL "update table" statement.

Hope this helps some.

Quote:

Originally Posted by tronayne (Post 4480498)

It might help. How would I load this FROM the other server (e.g. from its "mysqldump --all-databases" out) when I first install MySQL? I might need to do this, again.

What I have done when I've moved a database from one instance of MySQL to another (my blog database) is this:

Configure a user who has rights to just that database in the new MySQL, create a database with the same name as the one in the old MySQL. Give that user all privileges over that database. (Since I have one user and database, that's easier than importing the user table.)
Use mysqldump (or phpMyAdmin's export feature if you have phpMyAdmin installed) to export a *.sql file of the database.
Import that *.sql file into the new database in the new instance of MySQL. The import will create all the tables. Again, if you are using phpMyAdmin, you can use its "import" function.

If you are doing this in a situation in which the database will be used in conjunction with php, you may have to increase the upload and import limits in php.ini. By default they are set to 8MB.

There is, near the bottom of the mysqldump manual page a section (this looks like what you want to do):

Code:

mysqldump is also very useful for populating databases by copying data from one

MySQL server to another:



    shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name



It is possible to dump several databases with one command:



    shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql



To dump all databases, use the --all-databases option:



    shell> mysqldump --all-databases > all_databases.sql



For InnoDB tables, mysqldump provides a way of making an online backup:



    shell> mysqldump --all-databases --single-transaction > all_databases.sql



This backup acquires a global read lock on all tables (using FLUSH TABLES WITH READ

LOCK) at the beginning of the dump. As soon as this lock has been acquired, the

binary log coordinates are read and the lock is released. If long updating

statements are running when the FLUSH statement is issued, the MySQL server may get

stalled until those statements finish. After that, the dump becomes lock free and

does not disturb reads and writes on the tables. If the update statements that the

MySQL server receives are short (in terms of execution time), the initial lock

period should not be noticeable, even with many updates.



For point-in-time recovery (also known as �..roll-forward,�.. when you need to restore

an old backup and replay the changes that happened since that backup), it is often

useful to rotate the binary log (see Section 5.2.4, �..The Binary Log�..) or at least

know the binary log coordinates to which the dump corresponds:



    shell> mysqldump --all-databases --master-data=2 > all_databases.sql



Or:



    shell> mysqldump --all-databases --flush-logs --master-data=2

                  > all_databases.sql



The --master-data and --single-transaction options can be used simultaneously,

which provides a convenient way to make an online backup suitable for use prior to

point-in-time recovery if tables are stored using the InnoDB storage engine.



For more information on making backups, see Section 6.2, �..Database Backup Methods�..,

and Section 6.3, �..Example Backup and Recovery Strategy�...



If you encounter problems backing up views, please read the section that covers

restrictions on views which describes a workaround for backing up views when this

fails due to insufficient privileges. See Section E.4, �..Restrictions on Views�...

I have what some may consider a strange way of doing this but I have found it useful to keep a file, grant.sql that contains all the instructions to create every data base, add users and grant permissions. I use this when copying data bases from one machine to another (or if, shudder, there's a disk crash or some other catastrophe to deal with). grant.sql does not contain schema or table data, just the create and grant instructions. A small sample of grant.sql looks like this (names have been changed to protect the guilty):

Code:

create  database if not exists bugs;



grant  select, insert,

        update, delete, index, alter, create, lock tables,

        create temporary tables, drop, references on bugs.*

to      bugs@localhost identified by 'admpasswd';



grant  select, insert,

        update, delete, index, alter, create, lock tables,

        create temporary tables, drop, references on bugs.*

to      bugs@'%' identified by 'admpasswd';



flush  privileges;



create  database if not exists phpgedview;



grant  select, insert,

        update, delete, index, alter, create, lock tables,

        create temporary tables, drop, references on phpgedview.*

to      ged@localhost identified by 'admpasswd';



grant  select, insert,

        update, delete, index, alter, create, lock tables,

        create temporary tables, drop, references on bugs.*

to      ged@'%' identified by 'admpasswd';



flush  privileges;



drop    database if exists ipplan;

create  database if not exists ipplan;



grant  select, insert,

        update, delete, index, alter, create, lock tables,

        create temporary tables, drop, references on ipplan.*

to      ipplan@localhost identified by 'admpasswd';



grant  select, insert,

        update, delete, index, alter, create, lock tables,

        create temporary tables, drop, references on bugs.*

to      ipplan@'%' identified by 'admpasswd';



flush  privileges;



#      this one is for the super-user/administrator



grant  all privileges on *.*

to      'username'@'localhost' identified by 'userpasswd' with grant option;



grant  all privileges on *.*

to      'username'@'%' identified by 'userpasswd' with grant option;



flush  privileges;

I have also found it useful to keep an up-to-date schema for each individual data base -- over the years I've learned (the hard way, usually) that a complete schema for each data database is worth its weight in gold. When something bad happens (and it will) you can quickly build your data bases then populate them with dumps from backups.

Doing this grant.sql thing turns out to be the easiest way I've found to deal with the (slight) goofiness of MySQL (because the users and permission don't go with the individual data bases but are kept in the mysql data base). It's not that difficult to keep it up-to-date; when I add a new user I simply add a couple of lines to that file then run it

Code:

mysql --user=mysql --password='mysqlpassword' mysql < grant.sql

and that works just fine.

Anyway, glad to know you got going.

Hope this helps some.

I went ahead and marked this thread SOLVED so others could find these suggestions. I ended up shutting the servers down (to get clean states) and replicating the entire file tree for MySQL to the new server. So I didn't get a chance to actually test these.