Category Archives: mysql

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

mysql cheatsheet

@freebsd

make install WITH_CHARSET=utf8 WITH_COLLATION=utf8_general_ci
cp /usr/local/share/mysql/my-large.cnf /var/db/mysql/my.cnf

/usr/local/etc/rc.d/mysql-server start

Создаем базу

mysql -p -u root
mysql> create database samplebase1;
mysql> show databases;

создаем юзера с полными правами на созданную таблицу
mysql> grant all on samplebase1.* to ‘user1’@’localhost’ identified by ‘password’;

дампим базу

mysqldump -u username -p --opt --default-character-set=utf8 db_name | gzip > db_name.sql.gz

для переноса информации из базы данных на другой MySQL-сервер:

mysqldump --opt database | mysql --host=remote-host -C database

восстанавливаем из дампа

mysql -u username -p db_name < db_name.sql.sql

проверка и восстановление поврежденной БД

mysqlcheck -u username -p -r database

mysqlcheck --repair --analyze --optimize --all-databases --auto-repair

mysql transfer priveledges

for db in `ls -1 /home/mysql | grep -v mysql`; do echo "show grants for ${db};" | mysql | grep ^GRANT | sed "s|$|;|" >> grant.sql; done