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.
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:
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
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.
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:
Now lets start up the mysql daemon and skip the grant tables which store the passwords:
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';
Now kill your running mysqld, then restart it normally. You should be good to go. Try not to forget your password again.