After Vagrant Crash How To Recover Database

It becomes more common for businesses to use vagrant as single virtual machine solution in house that can be shared among developers that way ensuring every developer has the same version of services, frameworks and libraries running. It also saves developer time for upgrades and maintaining their development sandbox.

In short, vagrant manages the configurations defining what ports forwarded, memory size and others configuration including which virtual machine to load. The actual virtual machine is provided by Virtual Box technology and is available as separate entity.

Sometime it happens that vagrant crashes and hangs at the load process. For me, i got the following error message:

[default] Booting VM...
[default] Waiting for VM to boot. This can take a few minutes.

The logs are available in your Virtual Box folder or by default these images are in your home directory ‘VirtualBox VMs’ folder

Which Virtual Box image corresponds which Vagrant config?

In case you have more than one vagrant configurations or multiple virtual box images on your machine. To find out the particular Vagrant configuration pointing to which virtual image, go to the vagrant configuration folder and look at ‘.vagrant’ file. You will see something like:

{"active":{"default":"79a80fbd-2804-4dde-abd8-c23bb18a9b9a"}}% 

That is the UUID of the Virtual Box image to load from this vagrant configuration.
Next, how do you know what is the UUID for particular Virtual Box image. Go to the folder of your VirtualBox images and run:

VBoxManage list vms

This will display each VirtualBox name with its UUID

If your Vagrant crashes, perhaps, instead of spending time troubleshooting you would just load new instance. if so, you may still need the data from database of the old virtual image.

Step 1: Access VirtualBox image directly
With ‘VirtualBox’ software, you can run any Virtual box image you like. Once you start, the default login:

username:vagrant
password:vagrant

Step 2: Add Shared folder to access the mysql dump file
To add shared folder, go to ‘Settings’->Shared Folder and select folder(i.e.share) on your machine itself
Next mount that folder as following

sudo mount -t vboxsf share ~/host

Where the ‘share’ is the folder you specified in share folder settings above and the ‘~/host’ is the folder on the virtual image
Note: make sure your permissions set on virtual machine and your original machine accordingly
Step 3: Take mysql dump file
To take the back up, got the new mounted folder and run mysqldump command as follows:

mysqldump -u root -p nameOfDatabase>backupFile.sql

This will create the back up file and save into folder outside the virtual image that you have direct access. Afterwards, you can share this folder with another virtual image to import the database.

Useful links

Installing MySQL on Mac

Despite the popular virtualization solutions like Vagrant, Micro Cloud Foundry and others all of which are perfect solutions for your development environment that would include mysql, there may be reason you need to install Mysql server locally on your machine. This covers steps how to install mysql via macports packaging distribution.

Environment: MySQL 5.5, Mac 10.7

1- Install MacPorts

http://www.macports.org/install.php

2- Update MacPorts

sudo port selfupdate

3- Install MySQL server and client binaries

sudo port install mysql55

4- Install MySQL server startup scripts

sudo port install mysql55-server

5- Initialize the Database:

sudo /opt/local/lib/mysql55/bin/mysql_install_db --user=mysql

6A- Trick to get root user work

sudo chown -R mysql:admin /opt/local/var/db/mysql55
sudo chmod u+rwx,go= /opt/local/var/db/mysql55

6B- Starting MySQL

sudo /opt/local/share/mysql55/support-files/mysql.server start

7- Setting Root password

/opt/local/lib/mysql55/bin/mysqladmin -u root password 'changepasswordhere'

8- Enable the startup script so mysql starts on boot

sudo launchctl load -w /Library/LaunchDaemons/org.macports.mysql55-server.plist

– or —

sudo port load mysql55-server

9-Creating user that can access DB from any IP address

GRANT ALL PRIVILEGES ON *.* TO db_user@'%' IDENTIFIED BY 'db_passwd';
FLUSH PRIVILEGES;

10 – Verify all working

shell> mysqladmin version
shell> mysqladmin variables
shell> mysqladmin -h `hostname` version variables
shell> mysqladmin -h `hostname` --port=3306 version
shell> mysqladmin -h host_ip version
shell> mysqladmin --protocol=SOCKET --socket=/tmp/mysql.sock version

11-Reinstalling

If you need to uninstall/reinstall for whatever reason, remove the database directory when you do the uninstall

sudo port uninstall mysql55-server
cd /opt/local/var/db/
sudo rm -rf mysql55/

Troubleshooting

1. ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (61)
I wasn’t able to connect via client such as MySQL Workbench. Part of troubleshooting was running:

mysql -u makapacs -p -h localhost
mysql -u makapacs -p -h 127.0.0.1

The last, displayed the error ‘ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (61)’. Next:

○  telnet localhost 3306
Trying ::1...
telnet: connect to address ::1: Connection refused
Trying 127.0.0.1...
telnet: connect to address 127.0.0.1: Connection refused
Trying fe80::1...
telnet: connect to address fe80::1: Connection refused
telnet: Unable to connect to remote host

Clearly, something doesn’t let connect via TCP connection
Solution:Make sure ‘skip-networking’ is commented out in configuration file(our case /opt/local/etc/mysql55/my.conf) or when starting mysql server, it does’t start with ‘–skip-networking’

In skip-networking mode, the mysql instance doesn’t “listen for TCP/IP connections at all. All interaction with mysqld must be made using named pipes or shared memory (on Windows) or Unix socket files (on Unix)” from MySql Docs

2. ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)
This error was because, mysql server was binded on particular ip as follows (/etc/mysql/my.cnf):

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 173.255.xx.xx

So, when mysql binded on specific ip then it only listens remote connection from localhost or the ip specified. Solutions was to comment out ‘bind-address’ and restart mysql server. Afterwards, any remote connection worked

3. Warning: PDO::__construct() [pdo.–construct]: [2002] No such file or directory (trying to connect via unix:///var/mysql/mysql.sock)
This error came out suddenly one day. It cannot find the mysql socket file. To locate

mysqladmin variables | grep 'socket'

This will display where the mysql.socket is created and placed every time mysql starts. Next, create symbolic link so php can find it

sudo ln -s /opt/local/var/run/mysql55/mysqld.sock mysql.sock

Important to note, to find out where Apache looks for mysql.sock, run in browser and look under ‘pdo_mysql’ section

4. Warning: mysql_connect(): No such file or directory
Solved by changing mysql host from ‘localhost’ to ‘127.0.0.1’. The reason for this error is because the “localhost” is a special name for the mysql driver making it use the unix socket to connect to mysql instead of the a tcp socket.

Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[HY000]: General error: 2006 MySQL server has gone away’ /includes/database/database.inc

The solution was to add following line in mysql55/my.conf file:

[mysqld]
max_allowed_packet=100M

This gave the necessary extra space for the communication between db and the app

Useful Links:

 

  • http://jackal.livejournal.com/2160464.html
  • http://stackoverflow.com/questions/4798464/installing-mysql-on-snow-leopard-using-macports
  • http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting-permissions-unix
  • http://dba.stackexchange.com/questions/15004/how-to-initiate-mysql-on-mac-os-x-10-6-8
  • http://dev.mysql.com/doc/refman/5.0/en/flush.html
  • http://www.razorsql.com/docs/mysql_connection_troubleshooting.html

 

Cannot delete or update a parent row: a foreign key constraint fails

There may be situation where you are unable to drop tables or whole database in MySQL due to existing foreign keys. In another words, you run ‘Drop table tableName’ or ‘Drop database dbName’ and you receive the following error:

Cannot delete or update a parent row: a foreign key constraint fails

The trick to delete the table or database is to first disable foreign key constrain check as following:

SET foreign_key_checks = 0;
-- Drop tables
drop table ...
-- Drop views
drop view ...
SET foreign_key_checks = 1;

This way, by setting ‘foreign_key_checks’ to zero turns off foreign key constrains check. After dropping tables or database, make sure to turn back foreign key check

Configure MySQL with Grails

Oh Grails, you are the Holy Grail!…It makes developers life so easy and so much more pleasant. To configure MySql db with Grails application contains two simple steps:

Pre-Condition: MySql Installed, Database(Schema) created

Step 1 – Configure Connection

In the /grails-app/conf/DataSource.groovy, update you appropriated environment as following example demonstrated for development env:

development {
        dataSource {
            dbCreate = "create-drop"
            driverClassName = "com.mysql.jdbc.Driver"
            url = "jdbc:mysql://localhost/database-name"
            username = "user"
            password = "password"
        }
    } 
...

Step 2 – Instruct load MySql driver

In the /grails-app/conf/BuildConfig.groovy update ‘dependencies’ code block as following:

dependencies {
        // specify dependencies here under either 'build', 'compile', 'runtime', 'test' or 'provided' scopes eg.
         runtime 'mysql:mysql-connector-java:5.1.16'
    }
...

Possible Issues

Cannot load JDBC driver class ‘com.mysql.jdbc.Driver’ – If you try to run application and receive the following issue, then you have missed step 2 or specified the incorrect dependency in step 2.

Importing MySQL on WordPress Cloud Instance

Prerequisites: MySql, VMC, AppFog installed

In prevouse post, i deployed my first PHP app on Cloud. The reasoning for doing was, so i don’t have to configure and maintain my local development infrastructure(Apache, PhpMyAdmin,etc). So, I make a change following with push to cload and the resulting instance on cloud is my development solution ready for testing, however. In order to do, we need import data into MySql on the cloud instance to keep it in sync with production environment. This post is exactly how we accomplish it with ColdeCott  – an extension of CloudFoundry that lets run commands on MySql instance residing on cloud feeding our PHP App

Step 1 – Install DevKit and ColdeCott

DevKit is needed, so we are able to install ColdeCott. To install DevKit, go to rubbyinstaller page and download DevKit. Afterwards, extract DevKit and in ‘Command Line with Ruby’ go to the folder where you extracted DevKit and run the following:

In the above screen shot, the last command was run to install ColdeCotte – ‘get install CaldeCotte’

Step 1 For Mac – Install ColdeCott via XCode

For Mac environments, the Coldecott is installed via XCode as follows:

sudo xcode-select -switch /Applications/Xcode.app/Contents/Developer   
sudo gem install caldecott

Step 2 – Connect to MySql instance

To connect with the particular MySql instance feeding your app, we use a command ‘tunnel’ from Command Line with Ruby  that establishes the connection. Here is the screen shot:

!!! Before tunneling make sure you are logged in by running ‘af login -u appFogUsername’

The Shell on the left you tunnel(connect to) MySQL instance and afterwards, you are able to run any inquiries you like via mysql command. As you can also see while connecting MySQL, it displayed the current instance username, password and database name that you will need next step when importing data.

Step 3 – Import Data

Once you tunneled in your MySql instance (see shell on the left in the above screen shot), you can use another shell from which you can run mysql command to import data as it is displayed in the shell on the left of the screen shot above

Alternative is to use MySql Workbench with the same credentials given to you in Step 2

!!! Once you close the tunnel(shell), the connection is lost with the MySql instance

!!! The credentials seems to stay the same next time you open connection(tunnel) into the MySql instance

After data imported, we have  our development WordPress app working in sync with production and available for testing without much developers configuring and setting up Apache, PhpMyAdmin,MySql,PHP,etc. Next would be nice to have  Gradle script to do all this for us, so that once me make change, it auto updates our cloud instance but that will be another post….till then, happy coding!

Sources:

‘Manage you AppFog data from workstation’ by Jeremy Voorihs

Documentation Installing WebKit by the RubyInstaller Google Group