Import and Export MySQL and PostgreSQL

There are a number of ways to export and import data from a database. You can use a Mac client like Sequal Pro for MySQL, for example. You can do the same for PostgreSQL, though I have not settled on a Mac client for PostgreSQL.

Mac clients aside, you need can import and export your database SQL from the command line or from a bash script on your server as well. I use the following commands to handle importing and exporting SQL on my servers and development machines.

Export

If you want to export PostgreSQL:

pg_dump -U [username] [database] -f [filepath]

If you want to export MySQL:

mysqldump -u [username] -p[root_password] [database] > [filepath]

Note that there is no space after -p in the MySQL command.

Import

If you want to import PostgreSQL:

psql [database] < [filepath]

If you want to import MySQL:

mysql -u [username] -p[root_password] [database] < [filepath]

Documentation

You can read about the backup process for PostgreSQL on their website for more details.

For MySQL, the documentation is broken up into multiple pages. One for exporting or dumping your database and another for importing your exported database.

 

Raw PHP and MySQL Eager Loading

Working with complicated and joined data sets can be tricky for MySQL database performance. For example, What if you need to load multiple authors and their books from a database. How would you work around the N+1 problem and avoid unnecessary queries to the database without an expensive join statement? The answer is quite simple.

Use eager loading.

Eager Loading

Eager loading is simply fetching records preemptively. For example, when the author records are requested you can aggregate their ID. Then, you can query for only the books used by those authors beforehand.

SELECT * FROM `authors` LIMIT 3;

If this query returns the authors of the IDs 1,2,3 you can save queries on books by running just one select statement,

SELECT * FROM `books` WHERE `author_id` IN ( 1,2,3 );

PHP: Solutions

There are two great ways to work with eager loading in PHP.

  1. Using a handy ORM.
  2. Manually using PHP's PDO and your own code.

ORM

With an ORM such as Eloquent in Laravel eager loading is very simple. Laracasts has a great video explaining the situation and then covers the solution and there is a more recent video if you have an account.

PDO

There are times you do not want the overhead of an ORM. You might also want to build eager loading yourself. In these cases, you will need to use PHP's PDO class.

Let's say we have 10 authors. If you queried their books without eager loading you would run 11 SQL queries.

query("SELECT * FROM `authors` LIMIT 10;");

// Query books 10 times!
foreach($authors as $author) {
var_dump($author);
$id = (int) $author['id'];
$books = $pdo->query("SELECT * FROM `books` WHERE `author_id` = {$id}");
foreach($books as $book) {
var_dump($book);
}
}

The code is nice and brief but at the expense of speed. 11 queries are not ideal. Imagine the speed if 100 authors are returned. Ouch!

If you use eager loading you can have the same net result with only 2 queries.

query("SELECT * FROM `authors` LIMIT 10;");

// Setup eager list of IDs
$ids = [];
foreach($authors as $author) {
$ids[] = (int) $author['id'];
}
$author_ids = implode(',', $ids);

// Get all books and group them
$grouped_books = [];
$all_books = $pdo->query("SELECT * FROM `books` WHERE `author_id` IN ( {$author_ids} )");
foreach($all_books as $book) {
$grouped_books[$book['author_id']][] = $book;
}

// Final loop
foreach ($authors as $author) {
var_dump($author);
var_dump($grouped_books[ $author['id'] ]);
}

Character Encoding: UTF-8, PHP, MySQL and Sequel Pro

I love Sequel Pro. However, the default encoding is latin1 and you should really be using a form of Unicode like utf8mb4. The next time you add a database with Sequel Pro be sure to select UTF-8 Unicode (utf8mb4) if you want to skip character encoding headaches.

sequal-pro-utf8mb4

 

Make proper character encoding a priority and understand how character encoding works. There is a PHP Roundtable on UTF-8 Encoding that will get you started.

When and where to set UTF-8 Encoding

There are a number of place to set the character encoding for a PHP web application.

  • In your HTML forms.
  • HTML meta tag.
  • HTTP Content-Type header
  • In the DB scheme.
  • For DB connection itself.

Enabling Performance Monitoring for MariaDB and MySQL

Enabling performance monitoring for MariaDB and MySQL works the same way.

Performance monitoring for databases is extremely helpful when you have records in the hundreds of thousands using complex joins. It also helps when you want to do basic performance monitoring overall.

In the past you could use general_log, log_slow_queries and SHOW PROFILES. Since MySQL 5.5 you have access to much more detail through performance_schema.

To see if you already have performance monitoring enabled check your performance_schema variables using the your database console. If it is set to OFF you need to enable it.

I’m using MariaDB as my database.

MariaDB [(none)]> SHOW VARIABLES LIKE 'perf%';
+--------------------------------------------------------+-------+
| Variable_name                                          | Value |
+--------------------------------------------------------+-------+
| performance_schema                                     | OFF   |
| performance_schema_accounts_size                       | 10    |
| .......                                                | ...   |
| performance_schema_users_size                          | 5     |
+--------------------------------------------------------+-------+

When you enable performance monitoring do so only on your development environment. You will not see a major speed reduction but on production you can.

Enabling Performance Monitoring

After you install MariaDB using Homebrew create a hidden my.cnf file in your home directory. For MySQL you may need to locate an already existing my.cnf.

> cd ~
> touch .my.cnf

Open your favorite editor and edit the file. Add performance_schema to my.cnf, set performance_schema_events_statements_history_long_size to 10000, be sure you have [mysqld] set at the top, and save the file.

[mysqld]
performance_schema
performance_schema_events_statements_history_long_size=10000

You can also set other configurations in the my.cnf file if you like. Alternatively you can set server system variables in the command line or the mysql client.

Restart MariaDB or MySQL server.

> mysql.server restart

Enter the database console again and see if performance_scheme is enabled.

MariaDB [(none)]> SHOW VARIABLES LIKE 'perf%';
+--------------------------------------------------------+-------+
| Variable_name                                          | Value |
+--------------------------------------------------------+-------+
| performance_schema                                     | ON    |
| performance_schema_accounts_size                       | 10    |
| .......                                                | ...   |
| performance_schema_users_size                          | 5     |
+--------------------------------------------------------+-------+

events_statements_history_long

Now you can start enabling the features you want to use. I personal use the events_statements_history_long table introduced in MySQL 5.6.

events_statements_history_long will give you access to a detailed report on the last 10,000 queries run.

To enable events_statements_history_long monitoring open the MariaDB console and set your setup_consumers to YES. You must do this each time you start MariaDB, even after OS X launch.

MariaDB [(none)]> UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'events_statements_history_long';

From here you can run SELECT queries on the performance_schema.events_statements_history_long table to review and profile your results.

When you make changes to your my.cnf file and restart your Database you will need to re-enable setup_customers.

You can review all the setup_customers using SELECT.

MariaDB [(none)]> SELECT * FROM performance_schema.setup_consumers;
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_stages_current          | NO      |
| events_stages_history          | NO      |
| events_stages_history_long     | NO      |
| events_statements_current      | YES     |
| events_statements_history      | NO      |
| events_statements_history_long | YES     |
| events_waits_current           | NO      |
| events_waits_history           | NO      |
| events_waits_history_long      | NO      |
| global_instrumentation         | YES     |
| thread_instrumentation         | YES     |
| statements_digest              | YES     |
+--------------------------------+---------+

MySQL also has a quick start guide for the performance schema.

Uninstall MySQL from Mac OS X Yosemite 10.10

To uninstall MySQL from your Mac first backup your databases and then stop your MySQL server.

In the terminal run these commands.

sudo /usr/local/mysql/support-files/mysql.server stop
sudo rm /usr/local/mysql
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
sudo rm /Library/LaunchDaemons/com.mysql.mysql.plist
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /private/var/db/receipts/*mysql*
sudo rm -rf /var/db/receipts/com.mysql.*

You may see errors if your copy of MySQL is configured differently but this will cover most or all uninstallation.

For later versions of OSX there is a great answer on superuser.com for uninstalling MySQL. The main difference in older versions of Mac OS X is the use of the /etc/hostconfig file.

Once you are finished uninstalling MySQL restart your Mac.

Install MariaDB on OS X Yosemite 10.10

Use Homebrew to install MariaDB on your Mac. MySQL has a dmg, MariaDB does not. I recently switched to MariaDB for three main reasons:

  1. The influence of big names like Google and Wikipedia making the move as well.
  2. Michael Widenius the creator of MySQL is running the project.
  3. Works with SequalPro, Querious* and Navicat out of the box.

If you already have MySQL installed you will need to backup all your databases and uninstall MySQL. Remove the plist LaunchDaemon, if you added one to Yosemite. Restart your computer.

Article installation notes

Ben Stillman has a great article over on MariaDB.com/blog covering how to install MariaDB, check the link below and my notes to go with it.

Article on how to install MariaDB 10.

Be sure to watch the OS X terminal as you go through the article for information on different levels of configuration.

  • Step 4: You will need to install Homebrew using instructions from http://brew.sh/
  • Step 6: Watch the terminal for your start up plist LaunchDaemon instructions.
  • Step 7: You are probably installing a later version. You will want to cd into the directory with the version you installed.
  • Step 11: If you see MySQL in your version you need to uninstall MySQL. You should’t run MySQL and MariaDB.

Quick and dirty install

For those who have a little more experience these are the brief installation instructions.

I’m installing 10.0.17 your version might be different.

> xcode-select --install
> ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
> brew update
> brew install mariadb
> unset TMPDIR
> cd /usr/local/Cellar/mariadb/10.0.17/
> ln -sfv /usr/local/opt/mariadb/*.plist ~/Library/LaunchAgents
> mysql_install_db
> mysql.server start
> mysql_secure_installation
> cd ~
> touch .my.cnf # your mysql config

Now log into MariaDB and check your version.

>  mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 6
Server version: 10.0.17-MariaDB Homebrew

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> select @@version;
+-----------------+
| @@version       |
+-----------------+
| 10.0.17-MariaDB |
+-----------------+
1 row in set (0.00 sec)