Working with XAMPP and and phpMyAdmin makes dealing with databases more visual, but importing large db dumb files can fail/take a long time.

Importing from the command line is faster and more stable.

Use the XAMPP mysql, if there is not a global mysql

cd /Applications/XAMPP/xamppfiles/bin 

Import

./mysql -u root -p db_name < ~/path/to/db/file.sql

• Importing a ca. 300MB file takes ca. 5 seconds.
• Make sure the DB "db_name" already exists.

I am surprised that there is not a simple import function in mysql or phpMyAdmin, I did the following:

• In SQLite3, export tables as CSV
• In phpMyAdmin, create DB, and import table as CSV

Make sure you check the box that converst the first line into the table structure.

Table Size/Execution Time Limit

When importing a larger table (in my case ~300M), the phpMyAdmin was complaining about memory (which I fixed), but then the script time out.

Solution? Import directly from mysql

Open the XAMPP mysql binary:

cd /Applications/XAMPP/xamppfiles/bin 
./mysql -u root -p

LOAD DATA LOCAL INFILE '/path/to/table.csv' 
INTO TABLE mydb.mytable FIELDS TERMINATED 
BY ',' ENCLOSED BY '"' LINES TERMINATED 
BY '\n' IGNORE 1 LINES;

(Line breaks are added for legibility.)

• mydb.mytable are mydb name and mytable table
• IGNORE 1 LINES ignores the first line with the headers

I encountered a situation where my dev MySQL was 5.6 and running with a utf8mb4_unicode_ci Server Connection Collation, and the remote MySQL with 5.5 and a utf8mb4_general_ci collation. Needless to say, the remote DB did not like the dumps from my dev DB. Quick and Dirty Solution: replace('utf8mb4_unicode_520_ci', 'utf8mb4_general_ci') replace('utf8mb4_unicode_ci', 'utf8mb4_general_ci')

If your are using WordPress you might have run into the following situation. Let’s say the main usage of this particular WordPress installation is not so much blogging (articles), but as light-weight CMS (pages). Wordpress’ Page and Subpage system works quite well in this respect, but I am no happy with one thing: Numbers as Page Slugs. While it’s possible to create a new page, name it with a number “2012”, the page slug automatically turns to 2012-2. Manually editing the page slug back to “2012” and publishing it should solve that. Unfortunately not. Back to “2012-2”. In most cases, when the page slug is not a number, the presence of a “-2” and the end of the slug suggests, that the page already exists in the database. Make sure it’s not in the Trash, empty the trash, if it is. trembl.org/2012/ It does not seem to be a huge problem, because even when the page slug is “2012-2” and you ask for a page named trembl.org/2012/, WordPress will happily forward (redirect?) you to trembl.org/2012-2/. So… Working: Yes? Nice solution: No!. No other choice than to have a look at the MySQL itself. If you are not comfortable doing it on the CL, have a look at phpMyAdmin. Actually, if you are not comfortable editing MySQL, don’t do it at all. Messing up the MySQL will mess up you WordPress installation! And I assume you have a recent backup. So, with that out of the way, open your WordPress MySQL DB in phpMyAdmin. The wp_posts table holds all your posts, pages, as well as all the revisions to them. Look for the one you want to edit, in this case “2012-2”. Open the entry for editing (the small crayon on the left). You’ll want to edit “post_name”. Change it to whatever you live (“2012”), save (it’s called “Go”) and you’re done. Wordpress Version: 3.1.3