Show running queries if MySQl is stuck

Sometimes we want to know which query causes the DB to be so slow. Here is the magic MySQL queries which can do it:

show processlist;

Look into "info" column listed in the output table to see what queries are working now.

Repair ALL tables in a MySQL DB schema

Here is how to do it with the help of 'information_schema' of MySQL:

select concat('repair table ', table_name, ';') from information_schema.tables where table_schema='my-db-schema-name';

Installing MYSQL on RedHat Enterprise Linux RHEL 6.1

Get MySQL here: http://www.mysql.com/downloads/mysql/#downloads


Select TAR installation if available, get the link of TAR and run
wget [link]

Uncompress
 tar -xvf MySQL-5.5.13-1.rhel5.x86_64.tar


Install server:
rpm -Uvh MySQL-server-5.5.13-1.rhel5.x86_64.rpm

Install client:
rpm -Uvh MySQL-client-5.5.13-1.rhel5.x86_64.rpm


Note this warning:
----------------------
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h ip-10-34-70-147 password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

Please report any problems with the /usr/bin/mysqlbug script!

-----------------------------------

Start MySQL:
service mysql start

mySQL FULLTEXT search returns no results: ft_min_word_len

MySQL FULLTEXT search query worked well until I tried to find "boy" in my DB.
No results, and I can see some records with this word directly on my phpMyAdmin screen.
The same repeated with the word "red". It can find, "blue"< "green" and "yellow", but not "red".

Well, after some searching I found that FULLTEXT in mySQL has two server variables (well, it have more of course, but these two are relevant for this issue):
ft_min_word_len = 4
ft_max_word_len = 84

Words less than 4 chars will not be indexed until you change ft_min_word_len to something less than 4 chars.

So, on Redhat/Fedora and many other Linuxes you have to edit /etc/my.cnf
The addition might look:

[mysqld]
ft_min_word_len=1
ft_max_word_len=32

Note [mysqld] header - if it is already in that file, just put two lines woth ft_ under it. It's important! If you put it under another header mySQL will not recognize it. Cost me about 2 hours two figure this stupid thing out since typically "[...]" headers are ignored by the parsing software (used as comments).
But obviously, not in my.cnf and mySQL.

On Windows with XAMPP there is a file called "my" (just "my", now extension whatsoever) in /mysql/bin folder.
It's the same my.cnf, so just add the lines into [mysqld] section there.

Verify that the change worked in phpMyAdmin ->localhost -> Varaibles

The last thing to do to get the things working is rebuilding all FULLTEXT indexes, if you got some data in the DB yet.
With phpMyAdmin you can just click "edit" link next to the FULLTEXT index name, and when the edit screen opens, click "Save" button. You will see that mySQL runs actually 'drop the old index and create the new index" query.

Adding a new user to mySQL DB with phpMyAdmin

In addition to the graphical UI provided by phpMyAdmin, you can just issue the following SQL:
GRANT ALL ON DB-NAME.* TO IDENTIFIED BY '';

replace by teh user name you want, e.g. fernandotorres and replace by the password for this user. Note that there are ' signs around the password - keep them.

http://www.trap17.com/index.php/adding-users-databases-using-phpmyadmin_t31803.html

mySQL 2026 error (SSL connection) on Fedora Core4

In some cases we have got the following error trying to access mySQL DB:

[root@localhost]#mysql
ERROR 2026 (HY000): SSL connection error

This was caused by SeLinux which run on that machine. The simpliest receipt is to switch SeLinux off.

To do that, go to /etc/selinux/config and change the line as follow:
SELINUX=disabled
Than save the file and reboot the system.

Installing Bugzilla on a Windows XP computer already running Apache and MySQL

The complete expalnation I followed is here.

The first task was to verify that I can run another instance of Apache on a different port and can configure this instance to run as a Windows service.
This process is described here.
I copied httpd.conf file with the name "httpd_for_bugzill.conf" and have made the changes recommended in the article above.
Then I open a cmd window and typed:
apache -k install -n "Apache for Bugzilla" -f "C:/Program Files/Apache Group/Apache2/conf/httpd_for_bugzilla.conf"
This installed the new windows serivice "Apache for Bugzilla" which uses "httpd_for_bugzilla.conf" file.
Then I went ot "Control Panel/Administartive Tools/Services" and selected "Apache for Bugzilla" -> Start to start the new Win service.
I downloaded latest stable Bugzilla release from http://www.bugzilla.org/download and I have uncompressed the .tar.gz archive.
The rest of the installation was quite straight forward, probably excluding the fact that some Perl modules described in the installation guide above where not found by the Perl packages management utility (e.g. GD package), so I did not install them
The tricky thing was an email configuration, but I finished that quite fast since fortunately we have a configured SMTP server.