Category: MySQL

WordPress SQLite to MySQL Migration Complete

Just finished migrating my website from SQLite to MySQL. What a rush. (lol)

It was actually not as bad as I thought. A lot of sed, grep and other sorcery involved; especially in transforming of SQLite statements to MySQL.

Some quick commands I used:

sqlite techish.db .dump > production_2018-08-23.dump.sql

I found that it used quotes for tables and column names, so I had to remove those first and foremost.

sed -i '/INSERT INTO/,/VALUES (/s/"//g' production_2018-08-23.dump.sql

Next I found that there was an error using mysql -ufoo -p mynewdatabase < production_2018-08-23.dump.sql because the table creations were failing still. So I did a quick fresh install of a vanilla WordPress install, did a dump of the database and just grabbed the table creation parts out:

Dump fresh database:

mysqldump -ufoo -p wordpres > wordpress.sql

Next, I just want table creations…

awk '/CREATE TABLE/, /) ENGINE/' wordpress.sql > create_tables.sql

Next, run create_tables.sql on my new database and then import data.

mysql -ufoo -p mynewdatabase < create_tables.sql

Sweet, that worked and I have a baseline of tables now.

Now importing the data…

Change WordPress Password using MySQL

If the Forgot password option doesn’t work for you (which it really should), then you can change the password of your self-hosted WordPress login.

Check the contents of wp-config.php for the database login. Use that login information for MySQL login.

define('DB_NAME', 'wpdatabase');
define('DB_USER', 'wpdbuser');
define('DB_PASSWORD', 'wpdbpassword');

Replacing DB_USER and DB_NAME from that command with the values from wp-config.php, enter the following command to connect to the database on your server.

mysql -uDB_USER -p DB_NAME

Get a list of users in the WordPress database.

select user_login from wp_users;

This might return multiple entries, but it was just one in my case: admin
To change the password, issue the following. Be sure to change ‘admin’ to the value of your user_login.

update wp_users set user_pass=md5('NEW_PASSWORD_HERE') where user_login='admin';

All done. Now you can log into your WordPress administration panel with the new password you specified.