[gentoo-user] OT - Migrating databases to a new box

[gentoo-user] OT - Migrating databases to a new box

[Michael Sullivan]

I'm currently in the process of migrating databases to a new box.  One
of my users has two databases that he needs access to.  Is there a way
(through the mysql terminal interface) to find out what passwords he
uses to access these two databases?  This will save me the trouble of
finding him.  (Most of my users access remotely).  I have root
priveleges.  Thank you for your help.
-Michael Sullivan-

-- 
gentoo-user@gentoo.org mailing list

Re: [gentoo-user] OT - Migrating databases to a new box

[kashani]

Michael Sullivan wrote:
> I'm currently in the process of migrating databases to a new box.  One
> of my users has two databases that he needs access to.  Is there a way
> (through the mysql terminal interface) to find out what passwords he
> uses to access these two databases?  This will save me the trouble of
> finding him.  (Most of my users access remotely).  I have root
> privileges.  Thank you for your help.

Why bother? User accounts are stored in the db. Just move the whole thing.

/etc/inti.d/mysql stop
rsync -av --delete /var/lib/mysql/ newbox01:/var/lib/mysql/

ssh newbox01
/etc/init.d/mysql start

	This is can be slightly more complicated if you are changing db 
versions and/or have vastly different options in the respective my.conf 
files.

Or you can dump the mysql table and just copy the sql lines that you 
care about if you're moving databases individually.

mysqldump -u root -p --skip-opt --databases mysql > mysql-db-20070436.sql

Then cut and paste any access lines from db and user into the new db.

	Additionally you can get slick and replicate from the old box to the 
new box. Then as you migrate individual database users over to the new 
server you can stop replication on a per db basis. I can go through this 
option if you're interested. I've done a number of Mysql 
upgrade/migrations this way and it's easier than it might appear.

kashani
-- 
gentoo-user@gentoo.org mailing list

Re: [gentoo-user] OT - Migrating databases to a new box

[Michael Sullivan]

On Thu, 2007-04-26 at 15:03 -0700, kashani wrote:
> Michael Sullivan wrote:
> > I'm currently in the process of migrating databases to a new box.  One
> > of my users has two databases that he needs access to.  Is there a way
> > (through the mysql terminal interface) to find out what passwords he
> > uses to access these two databases?  This will save me the trouble of
> > finding him.  (Most of my users access remotely).  I have root
> > privileges.  Thank you for your help.
> 
> Why bother? User accounts are stored in the db. Just move the whole thing.
> 
> /etc/inti.d/mysql stop
> rsync -av --delete /var/lib/mysql/ newbox01:/var/lib/mysql/
> 
And this method will preserve all granted permissions with their
passwords?  That would be great!
> ssh newbox01
> /etc/init.d/mysql start
> 
> 	This is can be slightly more complicated if you are changing db 
> versions and/or have vastly different options in the respective my.conf 
> files.
> 
> Or you can dump the mysql table and just copy the sql lines that you 
> care about if you're moving databases individually.
> 
> mysqldump -u root -p --skip-opt --databases mysql > mysql-db-20070436.sql
> 
> Then cut and paste any access lines from db and user into the new db.

How do I get those?
> 
> 	Additionally you can get slick and replicate from the old box to the 
> new box. Then as you migrate individual database users over to the new 
> server you can stop replication on a per db basis. I can go through this 
> option if you're interested. I've done a number of Mysql 
> upgrade/migrations this way and it's easier than it might appear.
> 
> kashani

-- 
gentoo-user@gentoo.org mailing list

Re: [gentoo-user] OT - Migrating databases to a new box

[kashani]

Michael Sullivan wrote:

>> Why bother? User accounts are stored in the db. Just move the whole thing.
>>
>> /etc/inti.d/mysql stop
>> rsync -av --delete /var/lib/mysql/ newbox01:/var/lib/mysql/
>>
> And this method will preserve all granted permissions with their
> passwords?  That would be great!

	Moving the entire Mysql database store is very simple. Mysql puts all 
data in /var/lib/mysql by default under Gentoo. You can even go in there 
and poke around to see all the databases in individual directories. 
Assuming you're using Mysql 5.0.26 on both databases with similar 
my.conf files you should have no problems just rsync-ing the directory 
and then starting Mysql on the new server. You can run into issues with 
Innodb if data files are different sizes on each server. That's the 
usual gotcha since Gentoo has changed it a few times between releases.

>> Or you can dump the mysql table and just copy the sql lines that you 
>> care about if you're moving databases individually.
>>
>> mysqldump -u root -p --skip-opt --databases mysql > mysql-db-20070436.sql
>>
>> Then cut and paste any access lines from db and user into the new db.
> 
> How do I get those?

All passwords, account privileges, etc are stored in the mysql db within 
Mysql. Notice the distinction there. All Mysql databases store user, 
access, etc in a db called mysql. On disk that would be in 
/var/lib/mysql/mysql/ and through command line you can see the data by 
doing:

mysql -u root -p
use mysql;
select * from db;
select * from user;

So if you wanted to pull things out without thinking too much you can 
just dump the mysql database with all the user accounts out into a text 
file and then import only the parts you care about. Here some sample 
data from one of my servers.

--
-- Dumping data for table `db`
--

INSERT INTO `db` VALUES 
('localhost','sqlgrey','sqlgrey','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
INSERT INTO `db` VALUES 
('localhost','postfix','postfixadmin','Y','Y','Y','Y','Y','N','N','N','N','N','N','N','N','N','N','N','N'
);


--
-- Dumping data for table `user`
--

INSERT INTO `user` VALUES 
('localhost','sqlgrey','12231231313131231231312313123123131231C','N','N','N','N','N','N','N','N','N
','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0);
INSERT INTO `user` VALUES 
('localhost','postfixadmin','1312312312312333432423131231312313123131','N','N','N','N','N','N','N','N','N
','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0);

So in these examples mysql.user defines the user accounts and mysql.db 
defines the accounts and what access they are allowed per database. The 
big hash is the MD5 password. You can log into your new Mysql instance, 
use mysql, and then paste the above lines in to add the some access.

	Additionally you can dump just the whole mysql db and import it from 
the old db to the new db.

mysqldump -u root -p --databases mysql > mysqldb-20070426.sql

on the new server
mysql -u root -p
drop database mysql;
exit; (do not restart Mysql at this point or you'll be locked out)
mysql -u root -p < mysqldb-20070426.sql
mysql -u root -p
flush privileges; (this will load the new mysql you imported)
exit;

kashani
-- 
gentoo-user@gentoo.org mailing list

Re: [gentoo-user] OT - Migrating databases to a new box

[Daniel da Veiga]

On 4/26/07, Michael Sullivan <michael@espersunited.com> wrote:
> I'm currently in the process of migrating databases to a new box.  One
> of my users has two databases that he needs access to.  Is there a way
> (through the mysql terminal interface) to find out what passwords he
> uses to access these two databases?  This will save me the trouble of
> finding him.  (Most of my users access remotely).  I have root
> priveleges.  Thank you for your help.
> -Michael Sullivan-
>

First, there's no way to know the passwords, because MySQL uses
one-way encription. Second, if you have SUPER privileges , you can
just dumb the whole server and import this into the new server
(assuming same version and same .conf file). This method causes less
errors (in my tests) but its a lot slower than disk copies...

-- 
Daniel da Veiga
Computer Operator - RS - Brazil
-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
------END GEEK CODE BLOCK------
-- 
gentoo-user@gentoo.org mailing list