fseek.me

Have you ever wanted to modify multiple rows and tables inside your MySQL database? We have a simple way to do it.

Well, yesterday I had to modify in one of our databases (a big one), every occurrence of an email address (contact@company.com) to another one (sales@company.com). Doing that via our web interface would take a long time, so I did the simple way:

1- Backup your MySQL database using Mysqldump

$ mysqldump -u dbuser -pdbpass dbname > dump.sql

Now we have our database backed up at dump.sql

2- Search and replace inside your MySQL dump

We did this very easily with sed to replace “contact” for “sales”. Just change for whatever values you want to replace.

$ cat dump.sql | sed ’s/contact@company/sales@company/gi’ > dump-fixed.sql

Note that we did a case insensitve search for contact@company.com and replace that with the sales address. Also, we used the “g” modifier to apply to all cases (not the first one in the line).

3- Restore your database using the modified dump

$ mysql -u dbuser -pdbpass dbname < dump-fixed.sql

Yes, it was that simple. No more running a bunch of INSERTS, DELETES, ALTER tables, etc…

Uh-oh, did you just forgot your MySQL root password? Here’s how to recover it.

1-Stop MySQL:

# /etc/init.d/mysqld stop

2-Start Mysql without the grant table (so it will not ask for a password):

# /usr/bin/mysqld_safe –skip-grant-tables &

3-Log in as root:

# mysql –user=root mysql

4-Set up new password for the user root:

mysql> update user set Password=PASSWORD(‘mynewpwd’) WHERE User=’root’;

*Change mynewpwd for whatever password you want.

5-Restart mysql

# /etc/init.d/mysqld restart

That’s it! Your new password should now work.