MySQL 101 on OS X - Part II

The database is up and running and you have a user account. Now you need to create a database and load it with data. Since I am working on the Professional CodeIgniter book, I will use the database for that book as an example. You can download the claudias_kids.sql file from here. This file will be used to create a claudias_kids database.

Creating the Database

Creating the database is really straight forward. Just use the SQL create database command.

mysql -u monty -psomePass -v -e "create database claudias_kids;"

That is it. Your database has been created.

Loading Data

The next step is to load the database with tables and data. Often when working with a book example, the code to create the test tables and data is provided for you. The data is generally stored in a "dump" file, named after the utility used to export data to a file. A dump file is used in this example. The syntax for loading the data for the claudias_kids database is:

mysql -u student -psomePass claudias_kids < claudias_kids.sql

This command executes all the SQL code in the claudias_kids.sql file. Your database should now be full of tables and data.

To get list of tables, type:

"mysql -u student -psomePass -e "use claudias_kids; show tables;"

To list the contents of the products table, try this:

mysql -u student -psomePass -e "use claudias_kids; select * from products;"

Backing Up the Database

Now you have installed your database. What if you want to back it up after making changes to it? A backup can be created using the mysqldump utility. For example, to backup the claudias_kids database you would use this command.

mysqldump -u root -psomePass --databases claudias_kids > claudias_kids_bk.sql

The command creates a dump file just like the one you used to install the database. The command can be run at any time to save any changes you make to the database.

Well that concludes the posting on MySQL for now. Hopefully that gets MySQL up and going so you can get focused on coding.