LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   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/)

Skaperen 09-23-2011 11:12 AM

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)?

tronayne 09-23-2011 01:18 PM

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.

Skaperen 09-23-2011 01:28 PM

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.

Skaperen 09-23-2011 03:04 PM

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).

tronayne 09-23-2011 04:34 PM

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.

Skaperen 09-23-2011 05:01 PM

Quote:

Originally Posted by tronayne (Post 4480498)
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.

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.

frankbell 09-23-2011 08:44 PM

What I have done when I've moved a database from one instance of MySQL to another (my blog database) is this:
  1. 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.)
  2. Use mysqldump (or phpMyAdmin's export feature if you have phpMyAdmin installed) to export a *.sql file of the database.
  3. 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.

tronayne 09-24-2011 09:02 AM

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.

Skaperen 09-29-2011 11:01 AM

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.


All times are GMT -5. The time now is 09:13 PM.