Import Large SQL Files

I've been in the process of server migration over the past few days. It's been easy and I've experienced no down time thanks to Cloudflare and the fact that DNS propagation takes a while.

One of the hardest parts of migration wasn't the site files, it was the databases! I had exported all of the databases into 1 SQL file via phpMyAdmin, but it was too large for phpMyAdmin to import so I had to find a way to get everything back where it belonged! Thankfully, MySQL makes this simpler than I'd imagined.

To import a SQL file:

  1. Login to the server via SSH
  2. Login to mysql with: mysql -u USER -p'PASSWORD'where USER is your MySQL user and PASSWORD is your MySQL password.
  3. Type: source FILENAME.SQL where FILENAME.SQL is the path to the SQL file you wish to import
  4. Hit enter, and watch it import!

Optimizing the Database

Optimizing your database is an increasingly popular way to speed up your blog. The concept is really simple: over time, your

database becomes overburdened with requests and thus creates overhead to compensate. The more a table is accessed, the more likely it is to have overhead. It's the same way a hard drag fragments itself.

The optimize process is the equivalent to de-fragging your hard drive.

Here's how you can check for overhead, and get rid of it!
1. First you need to login to PHPMyAdmin (this is the simplest way to do this).

  1. After logging in, open up your blog's database.

[singlepic id=3 w=320 h=240 float=]

  1. Under the list of your tables, click on "Check tables with overhead"...if you look at my database, you can see that the Options table has some overhead. That's because I've been working a lot on different aspects of this blog that all utilize the Options table. For you, it may be the Posts table, or the user table if you have a lot of people signing up. It really depends on your setup as to which tables are going to have the most overhead.

  2. Click the dropdown, and select Optimize table.

*Note: This is usually a non-invasive task, but you should always have database backups ready to go. I have mine in 3 places!

[singlepic id=6 w=320 h=240  float=]

  1. Click go!

You just optimized your Wordpress database!

It's a really simple process that can greatly effect your blog's overall performance. I have this blog set to Optimize daily. Any increment is good, but the busier the blog, the more often you should optimize.

You learn more here.