Tag Archives: mysql

Can’t access MySQL because there are no records in mysql.user? This is how you fix that.

Posted on by
Reply

I don’t know if it is a problem with the OSX distro of MySQL 5.1.54, but when I installed it recently I found I couldn’t login at all. Nor could I set or reset the root password. The underlying problem was that there were no records in the users table…yeah, exactly, WTF?

I found this out because I eventually got to the end of the MySQL reference for resetting the root password where it tells you to perform a lobotomy on your mysqld and start it up using the –skip-grant-tables option. Nothing else had worked, and when I finally did then get in and SELECT * FROM mysql.user, I got no results, nadda.

The reference manual for resetting permissions starts by telling you to update the mysql.user table and set the password for the root user – but that doesn’t work if you’ve got no users in there. What to do? Well, you need to get all down and dirty and bypass CREATE USER and GRANT PRIVILEGES and simply INSERT a new user and their privileges into the mysql.user table. Simple really, and also described in the reference for adding users.

Migrating WordPress to a new URL

Posted on by
34 Replies

WordPress is a nonsense, and stores it’s URL in multiple places in the database. If you want to move your WordPress install to a different host, exec the following MySQL commands to change the URL in all instances:

UPDATE wp_options 
   SET option_value = REPLACE(option_value, "[Old site URL]", "[New site URL]")
 WHERE option_value LIKE "%[Old site URL]%";
 
 UPDATE wp_posts 
   SET post_content = REPLACE(post_content, "[Old site URL]", "[New site URL]")
 WHERE post_content LIKE "%[Old site URL]%";
 
  UPDATE wp_posts 
   SET guid = REPLACE(guid, "[Old site URL]", "[New site URL]")
 WHERE guid LIKE "%[Old site URL]%";

   UPDATE wp_postmeta
   SET meta_value = REPLACE(meta_value, "[Old site URL]", "[New site URL]")
 WHERE meta_value LIKE "%[Old site URL]%";

Replace [Old site URL] and [New site URL] appropriately

Use this form to generate the SQL for YOUR database