Setting up a local database / Import SQL

This goes through how to setup a local database

# 1 > PHPmyAdmin

After you have everything installed and checked out the files you will need to create a database
-> login to PHPmyAdmin


(assumes you have XAMPP / MAMP running)

# 2 > Setup user account

Locally we want to create a basic user account so all our projects between all our developers have the same credentials
-> because this is NOT online, it's ok to use a basic password: However online you must NOT use a basic password

Click 'User Accounts"

Click 'Add user account'

# 3 > Create user

Create a new user

user: local
pass: undologic
hostname: local / localhost

give all permissions (per the picture)

# 4 > Create Database

Create a database 
-> ensure you prefix with test_
-> you can choose a name to match your project



Ensure you set to utf8_general_ci

# 5 > CakePHP create database association

Now that you have created a database in your CakePHP database file (app/Config/database.php)

Ensure you have 

public $local = array(
'datasource' => 'Database/Mysql',
'persistent' => false,
'host' => '',
'login' => 'local',
'password' => 'undologic',
'database' => 'test_database',
'prefix' => '',
'encoding' => 'utf8',

# 6 > Use the local database (simple method)

In order to use the local database

in your AppModel.php (app/Model/AppModel.php)

class AppModel extends Model
var $actsAs = array('Containable');

var $useDbConfig = 'local';

public function __construct($id = false, $table = null, $ds = null)
parent::__construct($id, $table, $ds);

//Can be changed with code later depending on your environment
$this->useDbConfig = 'local';

# 7 > Import SQL via Command Line

Importing SQL files using PHPmyAdmin is not very stable, so for large SQL files we recommend importing your SQL files using the Command line
-> Open a terminal window
-> Our example we will assume the file is in our downloads directory (if it is in another location simple change the path)

First we need to delete the current database 
-> NOTE: this will delete all data and all information will be lost
-> Find the correct database (This will display all the databases on your computer: note the correct one for the next step)
-> Delete that database (change 'test_database' to the correct database from the previous step)
-> Recreate that database (change 'test_database' to the correct database from the previous step)

/Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot -e "show databases;"
/Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot -e "DROP DATABASE test_database;"
/Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot -e "CREATE DATABASE test_database;"

We now have a clean database ready to go
-> Let's import the large SQL file now

/Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot test_database < ~/Downloads/backupfile.sql

If you are importing a very large file allow time to pass
-> The command line will appear to be frozen, but it is working, when done you will see the terminal again and you can type again