Reset password on MySQL

I’d imagine that would happen after trying lots of passwords for testing purposes… Somewhere I got it wrong and the root password doesn’t work anymore. But there’s another quick solution to this: stop the running MySQL instance and start it again by running:

mysqld_safe --skip-grant-tables

Then login as root without password and reset it. Once changed, log out and restart MySQL as normal.

Advertisement

/var/mysql/mysql.sock on OS X

Looks like that by default the mysql.sock file on OS X is stored under /tmp/. That causes problems wheh applications try to connect to the MySQL instance via /var/mysql/mysql.sock.

Quick solution: create symlink of /tmp/mysql.sock to /var/mysql/mysql.socn

Remote MySQL database backup

Scenario: A database named db1 is hosted on host1. It needs to be backed up on a remote machine named host2 and be restored on the local MySQL instance. An overnight cronjob running the following script (or more accurate the command in sequence) gets the job done. The script runs mysqldump for backing up the database on host1. The generated .sql file is copied over ssh on the remote host host2. On host2, the /root/script.sql file is invoked to drop the existing db1 database and re-creates an empty db1 database to be ready to store the data:

#!/bin/bash
mysqldump -u root -ppassword db1 > /root/db1.mysql
scp /root/db1.mysql host1:/root/db1.mysql
ssh host2 "mysql -u root -ppassword < /root/script.sql"
ssh host2 "mysql -u root -ppassword db1 < /root/db1.mysql"

It may not be the most appropriate way, but it works. For any other ways of doing it please comment (apart from rsync’ing the /var/lib/mysql/db1 directories between the two hosts ;)).

SQL replace

Very handy SQL statement to replace part of a string that exists in multiple entries within a field of a table:

UPDATE table_name set field_name=replace(field_name, 'old_string', 'new_string')
where field_name LIKE '%old_string%'