MySql Export / Import Large Database

Hello,

In this blog I am going to explain a trick which I recently used for Export / Import large database. 

Let me first explain the issue I faced. The database was over 250 MB. I was using MySql WorkBench to export in single self contained file. But it was breaking in between as some tables were really huge. 

So here is what I did. First in MySql WorkBench, in stead of exporting it to self contained single file, export it to Dump project folder. Here it will create sql file for each table in the folder. So in my case it created approximately 180 sql files. 

Now open terminal and go to that folder. First step here is we will concat all those files and make a single file.

Run the following command in terminal from folder where you have all the sql files.

cat *.sql  > .single_file.sql

Above command will concat all the files into one file and create that file in same folder. Now we will use this file to import into our database.

/Applications/MAMP/Library/bin/mysql -u DB_USER -p DB < .single_file.sql

That's it. With this trick you can Import / Export large database. Please note that, for this you should have terminal access to your MySql server.

OSX MAMP, Update PHP Version

Hello,

So recently I was working on Laravel Lumen framework where we need PHP 7.1.3 version. In my OSX XAMPP was using php version 7.1.1

So here in this blog I am going to explain step by step procedure.

Step 1: Go to MAMP website and download the new version of PHP. Go to following link

MAMP Downloads

On left side you will see column with title "Additional PHP versions"

There you will find list of PHP versions for OSX, download the one which you want and extract it. Copy it to

MAMP\bin\php folder.

For example if you download php version 7.1.3 then you will get folder with name

php7.1.3

Now you have to make two changes.

First edit path in your bash profile and update the existing path of PHP to newer version.

export PATH=/Applications/MAMP/bin/php/php7.1.3/bin:$PATH

After this quit the MAMP and restart it. It should work. No wait still it may not work for you.  As you go to MAMP preference screen, you will see that still it's using old version and new version is not displayed there.

That's because, by default free version of MAMP displays only last two folder here. So trick is to delete old folder or rename it and now your version of PHP will be displayed. Select it click on OK, restart MAMP and that's it now you get newer version of PHP. 

Hope this helps you.

MAMP - Install MSSQL Extension

Recently in one of my project I was trying to connect to MS SQL server from PHP and it was showing error  "Call to undefined function mssql_connect()". That means you are missing MSSQL extension and you have to install it. In this blog I am going to explain how to do this.

First of all you will need FreeTds. FreeTds is the library for linux and unix to communicate with MS SQL server. You can download latest stable release from this link

ftp://ftp.freetds.org/pub/freetds/stable/freetds-patched.tar.gz

After downloading it, extract it and go to that directory in terminal and run following command.

$ sudo ./configure --prefix=/private/etc/freetds --with-tdsver=8.0 --sysconfdir=/private/etc/freetds/conf
$ sudo make
$ sudo make install

This will install FreeTds on your system. Now we have to compile MSSQL extension for your PHP version. For that first check which PHP version are you using in MAMP. That you can check in Server -> PHP tab

Check your version and download source code of that PHP version.

http://php.net/releases/

Extract it and go to this directory in terminal.

Now run following commands in terminal

$ ./configure --with-php-config=/usr/bin/php-config

$ make

Now go to MSSQL extension directory in PHP source code.

$ cd ext/mssql/

$ /usr/bin/phpize

$ ./configure --with-php-config=/usr/bin/php-config --with-mssql=/private/etc/freetds

$ make

Above command will generate MSSQL module so now go to module directory.

$ cd modules/

Now we have to copy this generated module to MAMP's current PHP extension directory. For that open the ini file of your PHP version. In my case I was using PHP 5.5.3 so my ini file was located at.

/Applications/MAMP/bin/php/php5.5.3/conf/php.ini

Open this file and search for "extension_dir" and you will find following line.

extension_dir = "/Applications/MAMP/bin/php/php5.5.3/lib/php/extensions/no-debug-non-zts-20121212/"

so this is your extension directory. We have to copy module here. So now go to terminal and run following command.

$ cp mssql.so /Applications/MAMP/bin/php/php5.5.3/lib/php/extensions/no-debug-non-zts-20121212/

This will copy mssql.so in your extensions directory. Now we have to add this extension in php.ini file.

Now in php.ini file find for the word "; Extensions" below that there are list of extensions. Add following line to the end of the list.

extension=mssql.so

Save the file. Restart your MAMP and run the phpinfo(). You must see following section there.

But wait it won't be there. That means there is something wrong. After wasting almost an hour I figure out that MAMP is actually using different ini file.  So we have to update that. For that go to 

/Applications/

Right click on your MAMP or MAMP Pro icon (if you are using MAMP Pro) and select "Show Package Contents" Now go to Contents/Resources  and open ini file of your PHP version. Now in that file find for the word "; Extensions" below that there are list of extensions. Add following line to the end of the list.

extension=mssql.so

Save the file. Restart your MAMP and run the phpinfo(). You must see following section there.

That's it and now you can connect to MSSQL server with following code.

$con = mssql_connect('MYSQLSERVER', 'username', 'password')

or die('could not connect to server.");

I hope this helps you and saves your time.