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@g.o mailing list