Ever got the following error message after installing MySQL on a Windows Server:

ERROR 1045 (28000): Access denied for user ‘root’@'localhost’ (using password: YES)

Sounds like this is quite a common problem if you look check in google.

This issue could have several roots:

• The MySQL Service is not running
• Service is not configured to accept network connections
• Wrong password/username
• Firewall setup
• Wrong port (standard is 3306)

The mysql help actually has some good hints for these issues:

http://dev.mysql.com/doc/refman/5.0/en/access-denied.html

(From my own experience, port 3306 actually does not need to be open in order to use the MySql command line tool or the MySQL Workbench.)

My service was running and configured for tcp/ip, the firewall was disabled and the port was correct. So it had to be something with the password.

You can reset the root password like this:

1. Stop the MySQL service:
   sc stop mysql
2. Start MySQL with the –skip-grant-tables option, this disables the password checks. You might wanna start it in a different command prompt window.
   mysqld.exe –skip-grant-tables
3. Run the following commands:
   mysql -u root mysql
   mysql> UPDATE user SET Password=PASSWORD(‘your_new_password’) where USER=’root’;mysql> FLUSH PRIVILEGES;
4. Stop the Server:
   mysqladmin -uroot -pyour_new_password shutdown
5. You should be able to login now
   mysql -uroot -pyour_new_password

If this does not help, there is a long discussion in the MySQL forum about this issue:

http://forums.mysql.com/read.php?11,34014,34014#msg-34014

Good luck!

After not having updated Wordpress for a while (we were still on 2.3), the new Wordpress iPhone Application convinced us that it was about time.

Unfortunately, all my categories were missing after the upgrade. Seems like the wp_categories table got removed an replaced by wp_terms and wp_term_taxonomy. The entries are still there, just without name and descriptions.

Fortunately there are ways to fix it (if you have a backup, harhar).

David Cumps wrote a nice post about how to fix it manually, but if you have lots of Categories, you can do it automatically too (if you prepare of it).

First step (before the upgrade) is to save a version of the wp_categories table:

mysqldump -uUsername -pPassword wordpress_db
wp_categories > wp_categories.sql

After the upgrade you have to play it back and run the following two queries:

mysql -uUsername -pPassword
wordpress_db < wp_categories.sql

UPDATE wp_terms, wp_categories
SET wp_terms.name = wp_categories.cat_name,
wp_terms.slug = wp_categories.category_nicename
WHERE wp_terms.term_id = wp_categories.cat_ID

UPDATE wp_term_taxonomy, wp_categories
SET wp_term_taxonomy.description =
wp_categories.category_description
WHERE wp_term_taxonomy.term_id = wp_categories.cat_ID

If you didn’t make a backup of the wp_categories table, you can just load the whole backup again and give it a different name. When you run the 2 queries, just prefix the table names with the name of the backup DB.

Update: If you have subcategories, you can try the query I posted in my comment below. I didn’t actually try it myself, so be careful.

There are already tons of Pages dedicated to the tuning and benchmarking of MySQL. But one thing that took me a long time to figure out is who to easy figure out what makes a query fast and what not. The problem is that all queries are cached, so every time you run it, you get different results. The solution is

Select SQL_NO_CACHE * FROM mytable

The official documentation from MySQL is here. If you need some other good things, I can only recommend the MySQL Performance Blog.

You can also disable cache on a DB level with the Admin Interface under Health > System Variables > Memory > Cache or via query_cache_type in the config file. This way you can run your app normally and track down slow queries in the Slow Query Log.

http://www.petefreitag.com/item/390.cfm

There are tons of pretty enhanced scripts out there to make backup and also to copy them to FTP sites, but what I needed was just something really simple to have my daily backup saved somewhere.

The script below will dump your DB into the backup.sql file and copy it to an FTP server of your choice into the root directory of that user. Can’t be any simpler.

Here it is:

The BackupAndFTP.cmd file:

mysqldump -uuserName -ppassword DBName > backup.sql 

ftp -s:ftpcommands.txt ftp.hostname.com

The ftpcommands.txt file:

username
password
binary
put backup.sql
bye

Just put both code blocks into an individual file, replace the usernames and passwords (first for MySQL and then for the FTP access), replace DBName with the name of the database you wanna save and that’s it.

Not sure if I’m the only left that uses Vista with the UAC (User Account Control), but it definitely got me into trouble yesterday.

I tried to change the my.ini (Configuration File for the MySQL Database), but due to some Vista security feature it created a Shadow Copy of the file and MySQL never actually got to see my changes.

You can check this via the MySQL command prompt. In my case I had to adjust the max_allowed_packet setting, so if you run:

mysql> show variables like 'max%' ; 

+-----------------------+------------+
| Variable_name         | Value      |
+-----------------------+------------+
| max_allowed_packet    | 1048576    |

So, this is a good way to check if your changes actually have an effect.

Now, to change the my.ini file, it helps to know where and how MySQL reads it.

This Section in the MySQL Online help explains it nicely. As a summary, this is where it reads from:

• WINDIR\my.ini Global options
• C:\my.cnf Global options
• INSTALLDIR\my.ini Global Options
• defaults-extra-file The file specified with --defaults-extra-file=path, if any

If you start MySQL as a service you can also use the defaults-file option. More info here.

What we can do to have our my.ini file read, is to make a copy, store it somewhere outside of the Program Files path and point defaults-file to it. It’s a registry setting:

KEY:
HKU\SYSTEM\CurrentControlSet\Services\MySQL\ImagePath

Value:
“C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt” –defaults-file=”C:\MySQL\my.ini” MySQL

Good luck!