Posts Tagged ‘sql server’

How to completely disable Replication in MySQL server 5.1.61 on Debian GNU / Linux

Monday, July 16th, 2012

Replication_mysql_disable

Some time ago on one of the Database MySQL servers, I've configured replication as it was required to test somethings. Eventually it turned out replication will be not used (for some reason) it was too slow and not fitting our company needs hence we needed to disable it.

It seemed logical to me that, simply removing any replication related directives from my.cnf and a restart of the SQL server will be enough to turn replication off on the Debian Linux host. Therefore I proceeded removed all replication configs from /etc/my/my.cnf and issued MySQL restart i. e.:

sql-server:~# /etc/init.d/mysql restart
....

This however didn't turned off replication,as I thought and in phpmyadminweb frontend interface, replication was still appearing to be active in the replication tab.

Something was still making the SQL server still act as an Replication Slave Host, so after a bit of pondering and trying to remember, the exact steps I took to make the replication work on the host I remembered that actually I issued:

mysql> START SLAVE;

Onwards I run:

mysql> SHOW SLAVE STATUS;
....

and found in the database the server was still running in Slave Replication mode

Hence to turn off the db host run as a Slave, I had to issue in mysql cli:

mysql> STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> RESET SLAVE;
Query OK, 0 rows affected, 1 warning (0.01 sec)

Then after a reload of SQL server in memory, the host finally stopped working as a Slave Replication host, e.g.

sql-server:~# /etc/init.d/mysql restart
....

After the restart, to re-assure myself the SQL server is no more set to run as MySQL replication Slave host:

mysql> SHOW SLAVE STATUS;
Empty set (0.00 sec)

Cheers 😉

‘host-name’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’

Sunday, May 20th, 2012

mysql-logo-host-name-blocked-because-of-many-connection-errors
My home run machine MySQL server was suddenly down as I tried to check my blog and other sites today, the error I saw while trying to open, this blog as well as other hosted sites using the MySQL was:

Error establishing a database connection

The topology, where this error occured is simple, I have two hosts:

1. Apache version 2.0.64 compiled support externally PHP scripts interpretation via libphp – the host runs on (FreeBSD)

2. A Debian GNU / Linux squeeze running MySQL server version 5.1.61

The Apache host is assigned a local IP address 192.168.0.1 and the SQL server is running on a host with IP 192.168.0.2

To diagnose the error I've logged in to 192.168.0.2 and weirdly the mysql-server was appearing to run just fine:
 

debian:~# ps ax |grep -i mysql
31781 pts/0 S 0:00 /bin/sh /usr/bin/mysqld_safe
31940 pts/0 Sl 12:08 /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –socket=/var/run/mysqld/mysqld.sock –port=3306
31941 pts/0 S 0:00 logger -t mysqld -p daemon.error
32292 pts/0 S+ 0:00 grep -i mysql

Moreover I could connect to the localhost SQL server with mysql -u root -p and it seemed to run fine. The error Error establishing a database connection meant that either something is messed up with the database or 192.168.0.2 Mysql port 3306 is not properly accessible.

My first guess was something is wrong due to some firewall rules, so I tried to connect from 192.168.0.1 to 192.168.0.2 with telnet:
 

freebsd# telnet 192.168.0.2 3306
Trying 192.168.0.2…
Connected to jericho.
Escape character is '^]'.
Host 'webserver' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
Connection closed by foreign host.

Right after the telnet was initiated as I show in the above output the connection was immediately closed with the error:

Host 'webserver' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'Connection closed by foreign host.

In the error 'webserver' is my Apache machine set hostname. The error clearly states the problems with the 'webserver' apache host unable to connect to the SQL database are due to 'many connection errors' and a fix i suggested with mysqladmin flush-hosts

To temporary solve the error and restore my normal connectivity between the Apache and the SQL servers I logged I had to issue on the SQL host:

mysqladmin -u root -p flush-hostsEnter password:

Thogh this temporar fix restored accessibility to the databases and hence the websites errors were resolved, this doesn't guarantee that in the future I wouldn't end up in the same situation and therefore I looked for a permanent fix to the issues once and for all.

The permanent fix consists in changing the default value set for max_connect_error in /etc/mysql/my.cnf, which by default is not too high. Therefore to raise up the variable value, added in my.cnf in conf section [mysqld]:

debian:~# vim /etc/mysql/my.cnf
...
max_connect_errors=4294967295

and afterwards restarted MYSQL:

debian:~# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..

To make sure the assigned max_connect_errors=4294967295 is never reached due to Apache to SQL connection errors, I've also added as a cronjob.

debian:~# crontab -u root -e
00 03 * * * mysqladmin flush-hosts

In the cron I have omitted the mysqladmin -u root -p (user/pass) input options because for convenience I have already stored the mysql root password in /root/.my.cnf

Here is how /root/.my.cnf looks like:

debian:~# cat /root/.my.cnf
[client]
user=root
password=a_secret_sql_password

Now hopefully, this would permanently solve SQL's 'failure to accept connections' due to too many connection errors for future.

Optimize, check and repair tables in MySQL, howto improve work with tables in MySQL

Monday, April 12th, 2010

There are few quick tips that helps if some unexpected downtime of your SQL server occurs. Even though nowdays this won’t happen too often with servers running with a good ups, sometimes even administrator errors can cause problems with your mysql tables. If your MySQL server refuses to start, it’s quite probable that you’re experiencing a problem with a broken table or tables in MySQL. Therefore you need to go through all your mysql databases and check the consistency of your MyISAM or Innodb tables, ofcourse accordingly to your MySQL database types. To check a certain table for consistency with MySQL after you select the database, you have to execute: mysql$ CHECK TABLE your_table_name; If the above command after presumably executed with all your databases and there consequent tables reports, everytime OK then your MySQL crashes are not caused by table incosistencies. However if instead of OK the CHECK TABLE reports Corruptthen you have a broken table and you have to fix it as soon as possible, in order to be able to bring up to life the MySQL server once again. Here is an example of a broken table after a CHECK REPAIR searchindex; : +------------------+-------+----------+------------------------------------+ | Table | Op | Msg_type | Msg_text | +------------------+-------+----------+------------------------------------+ | test.searchindex | check | error | Key in wrong position at page 4096 | | test.searchindex | check | error | Corrupt | +------------------+-------+----------+------------------------------------+ To fix the CORRUPTED or BROKEN table as also known you have to issue the command: mysql$ REPAIR TABLE yourtable_name; Depending on your table size after a while, if everything is going fine you should see something like: +------------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------+--------+----------+----------+ | test.searchindex | repair | status | OK | +------------------+--------+----------+----------+ 1 row in set (0.08 sec) Be aware that sometimes in order to fix a broken table you have to use the MySQL repair extended function. Expect The EXTENDED REPAIR function option to take a much more time, even sometimes with large databases with million of records it could take hours, especially if the MySQL server is serving other client requests as well. This terrible siutation sometimes occurs because of mysql locks, though I believe locks are probably a topic of another post. Hopefully after issuing that the table in MySQL would properly repair and your MySQL will begin starting up with the rc script once again. Apart from crashes and table repairs there are few nice things concerning MySQL that are doing me good every now and then. I’m talking about the MySQL functions: ANALYZE TABLE and OPTIMIZE TABLE ANALYZE TABLE does synchronization of the information concerning the variables within tables that has a INDEX key settled according to the database to which they belong. In other simply words, executing ANALYZE TABLE to your database tables every now and then and that would probably help in speeding up the code executed in the SQL that has JOINS involved. The second one OPTIMIZE TABLE is natively supported with MyISAM SQL database types, and secondary supported with Innodb, where the Optimize with Innodb is done in a non-traditional way. When invoked to process an Innodb table OPTIMIZE TABLE does use ALTER TABLE to achieve an Innodb table optimization. In practice what the optimize table does is defragmentation of the table unto which it’s executed. A quick example of the optimize table is for instance: OPTIMIZE TABLE your_table_name; In order to find out which tables need to be defragmented or in other words needs optimize table you have to issue the cmd: show table status where Data_free!=0; Note that you have to issue this command on each of your databases; Just because this is so boring you can of course use my script check_optimize_sql.sh which will quickly loop through all the databases and show you which tables need to be optimized. I’ve written also a second shell script that loops through all MySQL databases and lists all databases and sub tables that requires optimize and further on proceeds optimizing to download the script check_and_optimize_sql_tables.sh click here Happy optimizing 🙂

WordPress blog MySQL data maintainance valuable plugin WP-OPTIMIZE

Tuesday, April 12th, 2011

The more my blog is growing the slower it becomes, this is a tendency I’ve noticed since a couple of weeks.

Very fortunately while reading some random articles online I’ve came across a super valuable wordpress plugin called WP-OPTIMIZE

I think it’s best if I present instead of taking the time to explain what the WP-optimize does for a wordpress powered blog:

WP-Optimize is a WordPress 2.9++ database cleanup and optimization tool. It doesn’t require PhpMyAdmin to optimize your database tables. It allows you to remove post revisions, comments in the spam queue, un-approved comments within few clicks.

Additionally you can rename any username to another username too.

For example: If you want to rename default ‘admin’ username to ‘someothername’; just put ‘admin’ (without quotes) to the Old username field and the new username to the New username field, then click “PROCESS”)

Now in short to rephrase above text, during MySQL database requests a lot of database starts needing the so called MySQL optimization , this operation becomes necessery especially for databases which are very actively used and is related to the basic design of the mysql server.

Very often many tables starts having a lot of garbage (repetitive) data and therefore read and writes from the SQL server gets slower and slower day by day.

Thus the MySQL server has it’s famous OPTIMIZE TABLE command which does wipe out all the garbage unnecessery data stored in a tables/s and hence seriously impacts the later read/write table operations.

Now to go back to wordpress the same optimization issues, very often are a cause for a performance bottleneck and some smart guy has came with the great idea to create a specific wordpress plugin to fix such an optimize table issues

The WP-Optimize plugin has 5 major options which somehow could have a positive impact on blog’s performance, these are:
 

  • Remove all Post revisions
  • Remove all auto draft posts
  • Clean marked Spam comments
  • lean Unapproved comments
  • Optimize database tables

Apart from the nice performance boost applicaiton the wp-optimize plugin has one super valuable function, it could change the default wordpress blog administrator user admin to some other desired username, for example rename the default blog administrator username “admin” user to “john”.

From a security perspective this is another must have feature in every wordpress blog as it can kill many of the possible brute force attacks with some web brute force softwares like Hydra

Installing and using wp-optimize is a real piece of cake, here is a very quick few command lines to install it:

host:~# cd /var/www/blog/wp-content/plugins/
host:/var/www/blog/wp-content/plugins:# wget https://www.pc-freak.net/files/wp-optimize.0.9.2.zip
host:/var/www/blog/wp-content/plugins:# unzip wp-optimize.0.9.2.zip
...

To launch WP-OPTIMIZE and fix many speed bottlenecks caused by unoptimized tables, or just hanging around in database old spam comments, go and login with your adminsitrator to wordpress.

In the left pane menu starting up with Dashboard somewhere near the end of the vertical menu you will notice a button reading WP-Optimize .
Click over the Wp-Optimize button and a screen like the one in below’s screenshot will appear in your browser screen:

wp optimize plugin database optimization options screen

I have personally decided to use just 2 of all the 5 available primary plugin options, I decided to select only:
 

  • Clean marked Spam comments
  • Optimize database tables

Scrolling below the PROCEED button I could also notice a number of tables which were missing optimization and hence required an optimize table to be executed on each one of them.
Further on I pressed the PROCESS button and after a couple of minutes (2, 3 minutes) of waiting the wp-optimize plugin was done it’s selected tasks:

In the screenshot below, you see all my blog tables which were optimized by the plugin:
WP-Optimize optimized blog tables screenshot

It’s interesting to say few words about the Remove All Posts revisions plugin option, as many might be wondering what this plugin option really means.

When writting posts, wordpress has an option to restore to a certain point of the write out and makes a sort of different versions in time of each of it’s written posts.

Therefore later restoration if something gots deleted by mistake is facilitated, this is actually the all meaning of Remove All Posts revisions

With time and the increase wp posts the amount of Posts Revisions is growing on and on and just taking space and possibly making the access to the database entries slower, thus it might be a good idea to also remove them as, it’s absolutely rare to use the wp post restoration function.
However I’ve personally decided to not wipe out the old posts revisions as someday i might need it and I’m a bit scared that it might cause me some database issues.

I’ll be glad if somebody has tried the Posts Revisions wp-Optimize funciton and is happy with the results.

How to change MySQL server root password

Friday, July 29th, 2011

MySQL pass dialog Debian

I had to change my mysql root password for one of the servers since during the install I mispasted the password in the MySQL password prompt I needed the pwd to be changed.

Here is how I changed it to my desired one:

linux:~# /usr/bin/mysqladmin -u root -p'OLD_PASSWORD_STRING' password NEW_PASSWORD_STRING
linux:~#

The password gets changed immediately 😉

If a new password has to be set to a passwordless mysql server, the command to be issued is:

linux:~# /usr/bin/mysqladmin -u root password PASSWORD_STRING

Changing the MySQL password is also possible with mysql cli, after connecting to the sql server, though this method is a bit more time consuming. Here is how to do it from mysql console:

linux:~# mysql -u root -p
Server version: 5.1.49-3 (Debian)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.
mysql> use mysql;
mysql> update user set password=PASSWORD(“NEW_PASSWORD”) where User=’root’;mysql> flush privileges;

Of course it’s possible to do change the root pass via phpmyadmin
Cheers 😉