MySQL queries

To select records containing a particular string chain:

SELECT * FROM tablename WHERE instr(fieldname, "find this string");

To update text strings in records:

update films set fieldname = replace(fieldname, 'find this string', 'replace found string with this string');

import and export MySql database via SSH

Problem to import mysql databse with phpmyadmin because of escape character, maybe time to try import via SSH then! To import your database using SSH:

mysql -u username -ppassword dbname < dbname.sql

To export your database using SSH:

mysql -u username -ppassword dbname > dbname.sql

SQL Find and Replace part of text

Want to update (find and replace) part of a text using SQl? Check out this query: UPDATE TABLE my_table REPLACE(my_column,'string_toreplace','new_string');

Start or stop MySql Server

To start or stop the MySQL server:

service mysqld start

or

service mysqld stop

to restart:

service mysqld restart

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

>>disabling password authentication
service mysql stop

wait until MySQL shuts down. Then run

mysqld_safe --skip-grant-tables &

then you will be able to login as root with no password.

mysql -uroot mysql

In MySQL command line prompt issue the following command:
use databasename;

UPDATE user SET password=PASSWORD("abcd") WHERE user="root";
FLUSH PRIVILEGES;
EXIT

/etc/init.d/mysqld restart

At this time your root password is reset to "abcd" and MySQL will now
know the privileges and you'll be able to login with your new password:

mysql -uroot -p mysql

(Source: LinuxDeal)

MySQL max connections

To cthe mahange dximum number of connections to your mysql database, you need to update the file my.cnf and set the new value to max_connections.

More details --> Increasing Max MySQL Connections