Silicon Technix

MySQL replication is quite straight forward. Here are the setup steps: Read the rest of this entry »

MySQL database has a handy and simple string function REPLACE() that allows table data with the matching string (from_string) to be replaced by new string (to_string). This is useful if there is need to search and replace a text string which affects many records or rows, such as change of company name, postcode, URL or spelling mistake.

The syntax of REPLACE is REPLACE(text_string, from_string, to_string)

MySQL reference describes REPLACE as function that returns the string text_string with all occurrences of the string from_string replaced by the string to_string, where matching is case-sensitive when searching for from_string. text_string can be retrieved from the a field in the database table too. Most SQL command can be REPLACE() function, especially SELECT and UPDATE manipulation statement.

For example:

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, 'find this string', 'replace found string with this string');

update jos_docman set dmurl = replace(dmurl, 'click.net', 'm4u.com.pk')

The above statement will replace all instances of ‘click.net’ to ‘m4u.com.pk’ in the field of dmurl of jos_docman table.

Enjoy!

Lost MySQL root password Recovery

/etc/init.d/mysql stop
mysqld_safe --skip-grant-tables &

This stops MySQL and reloads it without the authentication (grant) tables, so we can connect to MySQL without a password. Beware this locks out all of your applications until the password reset process is completed. Now we need to go in an reset the password

su -
mysql -u root -p

It will prompt you for a password, just hit enter. You should now be inside the MySQL terminal and ready to change the root password:

update user set password=PASSWORD(”NEW-PASSWORD”) where User=’root’;

Of course, replace NEW-PASSWORD with your chosen root password. Now all that remains is to restart MySQL in the normal manner in order for it to pick up the authentication tables correctly and let your customers and applications back in

# /etc/init.d/mysql stop
# /etc/init.d/mysql start