Monthly Archives: April 2011

mysql db and privileges transfer;

Simple solution  for transfering privileges between mysql servers.

Maatkit – a toolkit for users, developers, and administrators of open-source databases.

Package maatkit (http://www.maatkit.org),  contains the useful script mk-show-grants.

mk-show-grants – Canonicalize and print MySQL grants so you can effectively replicate, compare and version-control them.
mk-show-grants | egrep -v "(debian-sys-maint'@'localhost|'root'@)" | ssh root@dst mysql
apt-get install maatkit rsync
mk-show-grants --flush | egrep -v "('debian-sys-maint'|'root'|'backup')" | ssh root@dst "cat > ~/mysql-grants.sql"
—–
transfer data:
1a. (it would require a ssh key)
# for i in `ls -1 /home/mysql | egrep -v "(^mysql|^ib)"`; do rsync -avz --compress-level=1 -e ssh --delete /home/mysql/${i}/ root@mysql_dst:/home/mysql/${i}/; done
1b. (it would not remove deleted tables)
rsync -avz --compress-level=1 -e ssh --exclude=/mysql* --exclude=/ibdata* --exclude=/ib_logfile* /home/mysql/ root@mysql_dst:/home/mysql/
2. r/o
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;
echo "FLUSH TABLES; FLUSH TABLES WITH READ LOCK; SET GLOBAL read_only = ON;" | mysql
to set it back to r/w mode:
#echo "SET GLOBAL read_only = OFF; UNLOCK TABLES;" | mysql
3. sync1: repeat step1
4. sync2:
mk-show-grants | egrep -v "(debian-sys-maint'@'localhost|'root'@)" | ssh root@dst mysql

new rig wishlist

Total: about 1500USD