This is the process to completely erase a database and import it again via an SQL file. This is useful for instance to sync up a dev installation of a website with the production site. Download a production database SQL backup, do a quick find/replace to change all production URLs to dev URLs and then do the following:

Warning: this completely deletes the existing database!

NOTE: The create database command character set and collation are simply set to match whatever was used on production - this is shown inside of the .sql file.

mysql -u root -p
show databases;
drop database somewebsitedb;
create database somewebsitedb character set UTF8mb4 collate utf8mb4_unicode_520_ci;

mysql -u somewebsitedbuser -p somewebsitedb < ~/somewebsitedb.sql

Done! If you experience the error Unknown collation: 'utf8mb4_unicode_520_ci' and you are running CentOS 7 with MariaDB 5.5, the solution is here.

Next Post Previous Post