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
--
[EMAIL PROTECTED] mailing list