Posts Tagged by MySQL

Backup multiple MySQL databases into separate files – Windows

06-Mar-2012 Posted by Sonia Hamilton under ,
5 Comments

Years ago I wrote a post on how to Backup multiple MySQL databases into separate files (Linux). I get a lot of questions about how to do the same on Windows; here’s a script submitted by one of my readers:

:: MySQl DB user
set dbuser=xxxx

:: MySQl DB users password
set dbpass=xxxx

:: Switch to the MySQL data directory and collect the folder names
pushd "C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data"

:: Loop through the folders and use the fnames for the sql filenames, collects all databases automatically this way

echo "hello"

echo "Pass each name to mysqldump.exe and output an individual .sql file for each"

FOR /D %%F IN (*) DO (
"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe" --user=%dbuser% --password=%dbpass% --databases %%F > "C:\SQLBackup\%%F.%backupdate%.sql"
)

Ruby on Rails – Foreign Key Constraints in MySQL

01-Oct-2008 Posted by Sonia Hamilton under , ,
5 Comments

As an ex-DBA, one of the things that annoys me about Rails is that migrations don’t have a way of setting up referential integrity (I’m still using Rails 1.2.3, so this may have changed). But apart from that, I lurv Rails :-)

Here’s some code I wrote to for adding and removing foreign key constraints on MySQL (using InnoDB, of course). There’s other code out there to do the same thing, but they didn’t do what I wanted, or required installing a plugin. (more…)

mysql, localhost, and ssh work-around

05-Jun-2008 Posted by Sonia Hamilton under ,
2 Comments

I use ssh to tunnel all the time, especially to get around brain-dead firewalls. But this doesn’t work easily for mysql – if you tunnel a port from localhost (eg localhost:3306) to the target db, mysql client tools will see ‘localhost’ and try to connect via the local socket. Very broken – there’s bugs lodged here and here about this (and lots of chest beating and wailing, too).

Solution: tunnel ssh via an intermediate machine. For example:

intermediate$ ssh -o GatewayPorts=yes -L 3306:targetdb:3306 targetdb
localhost$ mysql -h intermediate -P 3306

GatewayPorts – dangerous yes – but so is life…

Update

The problem seems to have been fixed; either that or I was having a very blond moment last time I tried using - – protocol.

localhost$ mysql --version
localhost$ mysql  Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2
localhost$ ssh -fN -L3306:targetdb:3306 targetdb
localhost$ mysql --protocol=tcp -hlocalhost -uroot
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 14788104
Server version: 4.0.21-Max-log
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql>

Update 2

However graphical tools like OpenOffice Base, GMySqlCC and MySQL Query Browser either don’t obey the – - protocol=tcp flag or don’t have a place to set it, and therefore try to connect via the local socket file when connecting to localhost. Aarghhhhh….

Update 3

Using 127.0.0.1 instead of localhost works for OpenOffice – yay.

Connect to mysql from OpenOffice using ODBC

05-Jun-2008 Posted by Sonia Hamilton under
No Comments

There’s an article here that explains how to connect to a mysql database from OpenOffice.org Base using odbc, but the author makes it sound like rocket science. It’s easy – summary:

  • sudo aptitude install unixodbc libmyodbc unixodbc-bin
  • sudo ODBCConfig
    • driver is /usr/lib/odbc/libmyodbc.so
    • setup is /usr/lib/odbc/libodbcmyS.so
  • run the wizard, connect using the saved odbc configuration

MySQL Backups

21-Dec-2007 Posted by Sonia Hamilton under
No Comments

See Also:

Next Page »