Edventures in Normalcy

Granting permissions in MySQL, localhost and external hosts must be separately run, the % sign means any external host and will not account for local connections:

But say you wanted to then remove remote access, but leave local access, easy (with this being the actual part I learned.  I wanted to write both granting and denying down.):

I ran into this error on a RHEL machine that recently patched.  To correct the issue, I recreated the symlink in /tmp :

I found the solve and associated troubleshooting steps here: http://forums.cpanel.net/f354/cant-connect-local-mysql-server-through-socket-var-lib-mysql-mysql-sock-111-a-78444.html

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
The root cause was that: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.

CF can not open a TCP socket to the database server. Check that the database server is actually running, listening on port 3306 and not locked by a firewall.

I want a ONE liner to migrating MySQL users between servers. We know that migrating databases is easy enough using mysqldump, but migrating users from one MySQL instance or server to separate one can be annoying. Based on well known export commands, here is a neat little piped set of commands that you can run on the source DB server. The command will generate all the GRANT statements that you can simply run on the destination DB server.

If you are logged in as root user and do NOT need to supply user and password:

Hey, lets go two better, lets run the output, and add semicolons at the end of the lines to make the output appropriate to run on the destination server.

The beauty of this is that you need not know the passwords for any of the mysql user accounts and are then given one block of output, instead of the commands to run to get the grants.

*Migrating all databases except mysql and information_schema:

Originaly found on http://jasonbos.co/migrating-mysql-users-from-one-server-to-anot

Sometimes you want to move a database between hosts, this is an easy way to cut down on the clutter created in the process, is to output the backup directly to the new host.

If you need to change the URL for a WordPress blog, and only have access via MySQL, you can use the following command, once you have selected the database.  Keep in mind I am assuming the wp_ table prefix.

Some times I see strange characters when viewing the Shipping Options in a Miva Merchant store.  This is because the characters are dependent on a character set.  The way to correct this is to run the following command:

It is semi safe to copy and paste the above command, ensure you change the s02 as needed. On a side note, this runs best from phpMyAdmin as pasting this in putty can cause problems with it matching the bad characters.