LinuxQuestions.org
Visit Jeremy's Blog.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 09-23-2011, 11:12 AM   #1
Skaperen
Senior Member
 
Registered: May 2009
Location: WV, USA
Distribution: Xubuntu, Ubuntu, Slackware, Amazon Linux, OpenBSD, LFS (on Sparc_32 and i386)
Posts: 2,185
Blog Entries: 21

Rep: Reputation: 151Reputation: 151
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)?
 
Old 09-23-2011, 01:18 PM   #2
tronayne
Senior Member
 
Registered: Oct 2003
Location: Northeastern Michigan, where Carhartt is a Designer Label
Distribution: Slackware 32- & 64-bit Stable
Posts: 3,541

Rep: Reputation: 1062Reputation: 1062Reputation: 1062Reputation: 1062Reputation: 1062Reputation: 1062Reputation: 1062Reputation: 1062
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.
 
Old 09-23-2011, 01:28 PM   #3
Skaperen
Senior Member
 
Registered: May 2009
Location: WV, USA
Distribution: Xubuntu, Ubuntu, Slackware, Amazon Linux, OpenBSD, LFS (on Sparc_32 and i386)
Posts: 2,185

Original Poster
Blog Entries: 21

Rep: Reputation: 151Reputation: 151
Quote:
Originally Posted by tronayne View Post
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.
 
Old 09-23-2011, 03:04 PM   #4
Skaperen
Senior Member
 
Registered: May 2009
Location: WV, USA
Distribution: Xubuntu, Ubuntu, Slackware, Amazon Linux, OpenBSD, LFS (on Sparc_32 and i386)
Posts: 2,185

Original Poster
Blog Entries: 21

Rep: Reputation: 151Reputation: 151
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).
 
Old 09-23-2011, 04:34 PM   #5
tronayne
Senior Member
 
Registered: Oct 2003
Location: Northeastern Michigan, where Carhartt is a Designer Label
Distribution: Slackware 32- & 64-bit Stable
Posts: 3,541

Rep: Reputation: 1062Reputation: 1062Reputation: 1062Reputation: 1062Reputation: 1062Reputation: 1062Reputation: 1062Reputation: 1062
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.
 
Old 09-23-2011, 05:01 PM   #6
Skaperen
Senior Member
 
Registered: May 2009
Location: WV, USA
Distribution: Xubuntu, Ubuntu, Slackware, Amazon Linux, OpenBSD, LFS (on Sparc_32 and i386)
Posts: 2,185

Original Poster
Blog Entries: 21

Rep: Reputation: 151Reputation: 151
Quote:
Originally Posted by tronayne View Post
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.
 
Old 09-23-2011, 08:44 PM   #7
frankbell
LQ Guru
 
Registered: Jan 2006
Location: Virginia, USA
Distribution: Slackware, Ubuntu MATE, Mageia, and whatever VMs I happen to be playing with
Posts: 16,774
Blog Entries: 27

Rep: Reputation: 5089Reputation: 5089Reputation: 5089Reputation: 5089Reputation: 5089Reputation: 5089Reputation: 5089Reputation: 5089Reputation: 5089Reputation: 5089Reputation: 5089
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.

Last edited by frankbell; 09-23-2011 at 08:46 PM.
 
Old 09-24-2011, 09:02 AM   #8
tronayne
Senior Member
 
Registered: Oct 2003
Location: Northeastern Michigan, where Carhartt is a Designer Label
Distribution: Slackware 32- & 64-bit Stable
Posts: 3,541

Rep: Reputation: 1062Reputation: 1062Reputation: 1062Reputation: 1062Reputation: 1062Reputation: 1062Reputation: 1062Reputation: 1062
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.
 
Old 09-29-2011, 11:01 AM   #9
Skaperen
Senior Member
 
Registered: May 2009
Location: WV, USA
Distribution: Xubuntu, Ubuntu, Slackware, Amazon Linux, OpenBSD, LFS (on Sparc_32 and i386)
Posts: 2,185

Original Poster
Blog Entries: 21

Rep: Reputation: 151Reputation: 151
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.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Mysql -- Grant tables not installed & Innodb configuration ttumelty Linux - Software 0 06-22-2006 10:41 AM
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option s Braynid Linux - Newbie 1 06-08-2006 09:14 PM
lowercase windows mysql tables became uppercase import to linux Niceman2005 Linux - Software 2 06-06-2006 03:18 AM
Updating MySQL grant tables... sigep739 Linux - Newbie 2 01-04-2004 12:57 AM
MYSQL GRANT etc etc markopolox Programming 8 09-23-2003 03:58 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 01:49 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration