Category Archives: MySQL

Tunneling MySQL over SSH

ssh -fNg -L 3307:127.0.0.1:3306 user@host

		

Add a Remote User in MySQL Server

GRANT ALL PRIVILEGES ON *.* TO user@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION;

Duplicate MySQL database

mysqldump -u [USER] --password=[PASSWORD] -h [HOST] [DATABASE_TO_COPY] | mysql -u [USER] --password=[PASSWORD] -h [HOST] [COPY_TO_DATABASE]

Backup / Export users from MySql

put this on your ~/.bashrc

mygrants()
{
  mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR ''', user, '''@''', host, ''';'
    ) AS query FROM mysql.user" | \
  mysql $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}

usage

mygrants --host=[HOST] --user=[USER] --password=[PASSWORD]

I found this solution on: http://bit.ly/JMFCOd

Install MySQL Workbench on Ubuntu 12.04

download MySQL Workbench on http://www.mysql.com/downloads/mirror.php?id=406525#mirrors

wget https://launchpad.net/ubuntu/+archive/primary/+files/libzip1_0.9.3-1_i386.deb
sudo dpkg -i libzip1_0.9.3-1_i386.deb

64bits => https://launchpad.net/ubuntu/+source/libzip/0.9.3-1/+build/1728114/+files/libzip1_0.9.3-1_amd64.deb

This package (libmysqlclient16) was removed from 12.04 repos, so you need to grab the old one.

wget http://launchpadlibrarian.net/94563300/libmysqlclient16_5.1.58-1ubuntu5_i386.deb
sudo dpkg -i libmysqlclient16_5.1.58-1ubuntu5_i386.deb

64bits => http://launchpadlibrarian.net/94808408/libmysqlclient16_5.1.58-1ubuntu5_amd64.deb

sudo apt-get install python-paramiko python-pysqlite2 mysql-client mysql-common libctemplate0 libgtkmm-2.4-1c2a

now install MySQL Workbench

sudo dpkg -i mysql-workbench-gpl-5.2.38-1ubu1104-i386.deb

routine to backup / restore mysql database

mysqldump -u root --password=[PASSWORD] [DATABASE] --compact -R --skip-lock-tables --default-character-set=utf8 | gzip -c > /path/to/your/$(date +%Y-%m-%d_%H-%M-%S)backup.gz
gunzip -c /path/to/your/backup.gz | mysql -u [USER] --password=[PASSWORD] [-h HOST] [DATABASE] --default-character-set=utf8

Grant Acess to user for any host (MySQL)

GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY 'topsecret';

Execute sql query on terminal

mysql -u [USER] -e 'SQL';

mysqldump only schema

mysqldump -u [USER] -p [-h HOST] -d > /path/to/dump.sql

Execute sql files from terminal

on terminal

mysql -u [user] -p [-h host] [database] < /path/to/file.sql

on client

source /path/to/file.sql

or

\. /path/to/file.sql
Page 1 of 212