From Technical Knowledge to IT Service Management

Archive for the ‘MySQL’ Category

Just lost your root password on MySQL ?

Loosing your root password on a MySQLserver could become tricky.

If you face this situation, you will have to stop your MySQL server

#/etc/init.d/mysql stop

One your database is stopped, you will start it again with 2 options to avoir authentication process but also avoid networtking connection to your unsecure database:

#/usr/libexec/mysqld --skip-grant-tables --skip-networking &

you will now be able to connect without password:

#mysql mysql -u root

One you are connected, you can reset your password and flush the privileges to apply changes on database:

MYSQL> UPDATE user SET password=PASSWORD('newpassword') WHERE user='root';

MYSQL>FLUSH PRIVILEGES;

MYSQL>quit;

Then restart mysql:

#/etc/init.d/mysql restart

And start using KeePass software to store your password !

Transfer easily a Mysql Dump between two servers

#Dump of the master database
$ mysqldump -u user -p db_test > /var/dump/db_test.dat

#Copy the dump file db_test.dat with scp to the slave mysql database
$ scp /var/dump/db_test.dat user@nuxora.com:/var/backup/db_test.dat

#Restore database on the slave server from the master server
$ ssh user@nuxora.com ‘mysql -u user -p db_test < /var/backup/db_test.dat’

it is quite simple and efficient.

But if you are a real geek you’d prefer this one:

#Dump from master database to slave one with ssh
$ mysqldump db_test | ssh user@nuxora.com mysql db_test