Category Archives: mysql

Update a Mysql password field with an MD5 string

One of our servers had a database with quite a lot of ftp users using there username as there password. To get rid of that problem i did the following. Make sure to backup you databases first. Now find out how many users there are with userid=passwd.

This select will show you which users have a userid field equal to the passwd field not a good idea.

select * from ftpuser where userid=passwd;

This update statement will update all of these users and set a random MD5 string as there password.

update ftpuser set passwd=MD5(RAND()) where userid=passwd;

This will only work with clear text passwords.

Extracting a Database From a mysqldump File

Leave a Reply

Restoring a single database from a full dump is pretty easy, using the mysql command line client’s –one-database option:

mysql -u root -p --one-database db_to_restore < fulldump.sql

But what if you don’t want to restore the database, you just want to extract it out of the dump file? Well, that happens to be easy as well, thanks to the magic of sed:

sed -n '/^-- Current Database: `test`/,/^-- Current Database: `/p' fulldump.sql > test.sql

You just need to change “test” to be the name of the database you want extracted. Or you can use this shell script:

Download mysqldumpsplitter

Usage:

$>sh MyDumpSplitter.sh
Usage: sh MyDumpSplitter.sh DUMP-FILE-NAME — Extract all tables as a separate file from dump.
sh MyDumpSplitter.sh DUMP-FILE-NAME TABLE-NAME — Extract single table from dump.
sh MyDumpSplitter.sh DUMP-FILE-NAME -S TABLE-NAME-REGEXP – Extract tables from dump for specified regular expression.

Further instructions for using this script can be found here:

Mysql dump-shell script

MySQL Dump the table structure only no data

Leave a Reply

The only option that is different than creating an entire backup is the -d switch, which tells mysqldump not to output the data.

mysqldump -u root -pmypassword -d proftpd > /root/proftp.sql

This should leave you with the table structure without the data. To restore the DB do the following:

mysql -u root -pmypassword proftpd < proftpd.sql

For this to work the database proftpd must exist as an empty database.

Reset Forgotten MySQL Root Password

Leave a Reply

Okay so you have forgotten your mysql root password and need to access you mysql server. This howto requires root access to the shell or via sudo:

First stop your mysql server via the init script:

/etc/init.d/mysql stop

Now lets start up the mysql daemon and skip the grant tables which store the passwords:

mysqld_safe --skip-grant-tables

You should be able to see mysql starting. Logon to mysql with the fowling command:

mysql --user=root mysql

Now change the password with:

update user set Password=PASSWORD('new-password') where user='root'; flush privileges; exit;

Now kill your running mysqld, then restart it normally. You should be good to go. Try not to forget your password again.