MySQL - Reset root password and extract table from dump

Just a couple MySQL notes.

Debian 6 Squeeze - Reset MySQL root user password

You’ll need to stop MySQL server for resetting it this way.

service mysqld stop

Start MySQL with --skip-grant-tables and send to background. Press enter to get back to command line.

mysqld_safe --skip-grant-tables &

Connect to to MySQL.

mysql -u root

Use mysql database and run the command to update the root user password and flush privileges.

USE mysql;
UPDATE user SET password=PASSWORD("newrootpassword") WHERE user='root';

FLUSH PRIVILEGES;

service mysqld stop

Start MySQL and verify you can now login with the root user.

service mysqld start

mysql -u root -p

Extract single table from mysqldump file

There are a number of ways to do this with perl, sed, awk etc etc…, but I like scripts and I’ve successfully used this script before.

wget http://www.tsheets.com/downloads/oss/extract_sql.pl

Make it executable or run with perl and you can run ./extract_sql.pl or perl extract_sql.pl to see the options.

chmod u+x extract_sql.pl
./extract_sql.pl  or perl extract_sql.pl

I ran this to extract the wp_comments table from my Wordpress dump file and send it to a file.

./extract_sql.pl -t wp_comments -r wordpress.sql > wp_comments_table.sql

Now to import the table.

mysql -u root -p wordpress < wp_comments_table.sql

The article with the script and other methods of doing this is here.