Posts Tagged ‘replication’

Script to Automatically change current MySQL server in wp-config.php to another MySQL host to minimize WordPress and Joomla downtimes

Friday, July 20th, 2012

I'm running a two servers for a couple of home hosted websites. One of the servers is serving as Apache host1 and has configured MySQL running on it and the second is used just for database host2 – (has another MySQL configured on it).
The MySQL servers are not configured to run as a MySQL MASTER and MySQL SLAVE (no mysql replication), however periodically (daily), I have a tiny shell script that is actualizing the data from the active SQL host2 server to host1.

Sometimes due to electricity problems or CPU overheats the active MySQL host at host2 gets stoned and stops working causing the 2 WordPress based websites and One joomla site inaccessible.
Until I manually get to the machine and restart host2 the 3 sites are down from the net and as you can imagine this has a very negative impact on the existing website indexing (PageRank) in Google.

When I'm at home, this is not a problem as I have physical access to the servers and if somethings gets messy I fix it quickly. The problem comes, whether I'm travelling or in another city far from home and there is no-one at home to give the hanged host hard reboot ….

Lately the problems with hang-ups of host2 happaned 3 times or so for 2 weeks, as a result the websites were inaccessible for hours and since there is nobody to reboot the server for hours; the websites keep hanging until the DB host is restarted ;;;;

To work-around this I came with the idea to write a tiny shell script to check if host2 is ping-able in order to assure the Database host is not down and then if script determines host2 (mysql) host is down it changes wp-config.php (set to use host2) to a wp-config.php (which I have beforehand configured to use) host1.

Using the script is a temporary solution, since I have to actually find the real hang-up causing troubles, but at least it saves me long downtimes. Here is a download link to the script I called change_blog_db.sh .
I've configured the script to be run on the Apache node (host1) via a crontab calling the script every 10 minutes, here is the crontab:
 

*/10 * * * * /usr/sbin/change_blog_db.sh > /dev/null 2>&1

The script is written in a way so if it determins host2 is reachable a copy of wp-config.php and Joomla's configuration.php tuned to use host2 is copied over the file config originals. In order to use the script one has to configured the head variables script section, e.g.:

host_to_ping='192.168.0.2';
blog_dir='/var/www/blog';
blog_dir2='/var/www/blog1';blog_dir3='/var/www/joomla';
notify_mail='hipo@pc-freak.net';
wp_config_orig='wp-config.php';
wp_config_localhost='wp-config-localhost.php';
wp_config_other_host='wp-config-192.168.0.2.php';
joomla_config_orig='configuration.php';
joomla_config_other_host='configuration-192.168.0.2.php';

You will have to manually prepare;;;

wp-config-localhost.php, wp-config-192.168.0.2.php ,configuration-192.168.0.2.php, wp-config-localhost.php to be existing files configured to with proper host1 and host2 IP addresses.
Hope the script will be useful to others, experiencing database downtimes with WordPress or Joomla installs.
 

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 😉

The brief story of information according to James Gleich – and how we possibly come to be at where we are

Saturday, June 23rd, 2012

We're living at a times, where information has become more valuable than money and gold. Though we're living in a such a wild changing times, still it is not easy to answer in onefold clear way what is information.
The more the world and humanity developed the more meanings information word accepted.
While checking some interesting videos on the net, I've come across a video representing in a very brief form the history of information.

The Story of Information and how we come to be at where we are now

We're living at a times, where information has become more valuable than money and gold. Lest that living in a such a wild changing times, still it is not easy to answer in onefold clear way what is information.
As you can see from the video, the more the world and humanity developed the more meanings information word took up.

At the end end of the short presentational video there are few questions asked:
 

  • Who coined the term MEME
  • How many zeros are in yottabyte?
  • What is a one thing that can escape a black hole?

I did a quick research on the newly heard words and came to know meme concept. A meme is an "an idea, behavior or style that spreads from person to person within a culture.". The theory lists some interesting information like replication of personality (propagation) from one person to another etc. etc. However the general content of meme's theory puts Christian faith, religions and all kind of people beliefs in the graph of being just a continuous meme's patterns which are transferred from a person to person (generation to generation). Therefore according to memetics (as the meme's science is called), Christian faith and even my Orthodox Christian faith is just an informtion transmitted similarly to genetics information and the laws against:; sexual morality, bestiality, adultery, castration etc. are just a taboo and makes not sense and is just they claim is a information transferred from family generation to next generation until today.

Obviously the whole "theory" of memetics as a science is not true and is just a world view of Mr. Richard Dawkins in a book called The Selfish Gene (1976). It is well to be said Dawkins a follower of the fairy tales of Charle's Darwin's Darwinism.
Modern science however is seriously questioning Darwin's materialist world view. There are plenty of scientific researches which has prooved the existence of spiritual realm and hence some of the claims memetics are standing on a shaky grounds.

Concerning the second question in the video how many zeros are in an yottabyte the answer according to FreeFactFinder.com is:

2^80 = 1,208,925,819,614,629,174,706,176 bytes = 1024 zettabytes.

So having in mind the yottabyte equals 1,208,925,819,614,629,174,706,176 bytes if in bytes it would have 2 zeros 🙂

But if by zero is meant the number of values which could become zeroes then the answer would be 24 zeroes. This of course is in bytes in zettabytes the zeroes are only 0 or 3 i.e. (1024 zettabytes) 🙂

Moving back to the 3rd video question What is a one thing that can escape a black hole?. The answer is "WHO KNOWS ??".
The BlackHoles are a matter of science based on a huge theory base, there are plenty of discussions by some scientists still doubting if black holes really exist ,,, even if we assume blackholes exist noone can tell if the blackhole would suck up all matter (including light) or some specific kind of still un-researched matter or energy can get out… In short the question imposed is completely ridiculously funny 😉

How to check MASTER / SLAVE MySQL nodes status – Check MySQL Replication Status

Thursday, April 19th, 2012

I'm doing replication for one server. Its not the first time I do configure replication between two MySQL database nodes, however since I haven't done it for a few years, my "know how" has mostly vanished so I had some troubles in setting it up. Once I followed some steps to configure replication I had to check if the two MASTER / Slave MySQL db nodes communicate properly. Hence I decided to drop a short post on that just in case if someone has to do the same or if I myself forget how I did it so I can check later on:

1. Check if MASTER MySQL server node is configured properly

The standard way to check a MySQL master node status info is with:
 

mysql> show master status;
+——————+———-+———————————————————+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+———————————————————+——————+
| mysql-bin.000007 | 106 | database1,database2,database3 | |
+——————+———-+———————————————————+——————+
1 row in set (0.00 sec)

By putting \G some extra status info is provided:
 

mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000007
Position: 106
Binlog_Do_DB: database1,database2,database3
Binlog_Ignore_DB:
1 row in set (0.00 sec)

ERROR:
No query specified

2. Check if Slave MySQL node is configured properly

To check status of the slave the cmd is:
 

mysql> show slave status;

The command returns an output like:
 

mysql> show slave status;+———————————-+————-+————-+————-+—————+——————+———————+————————-+—————+———————–+——————+——————-+——————————————————-+———————+——————–+————————+————————-+—————————–+————+————+————–+———————+—————–+—————–+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+——————————-+—————+—————+—————-+—————-+| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error |+———————————-+————-+————-+————-+—————+——————+———————+————————-+—————+———————–+——————+——————-+——————————————————-+———————+——————–+————————+————————-+—————————–+————+————+————–+———————+—————–+—————–+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+——————————-+—————+—————+—————-+—————-+| Waiting for master to send event | HOST_NAME.COM | slave_user | 3306 | 10 | mysql-bin.000007 | 106 | mysqld-relay-bin.000002 | 251 | mysql-bin.000007 | Yes | Yes | database1,database2,database3 | | | | | | 0 | | 0 | 106 | 407 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | |+———————————-+————-+————-+————-+—————+——————+———————+————————-+—————+———————–+——————+——————-+——————————————————-+———————+——————–+————————+————————-+—————————–+————+————+————–+———————+—————–+—————–+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+——————————-+—————+—————+—————-+—————-+

As you can see the output is not too readable, as there are too many columns and data to be displayed and this doesn't fit neither a text console nor a graphical terminal emulator.

To get more readable (more verbose) status for the SQL SLAVE, its better to use command:
 

mysql> show slave status\G;

Here is a sample returned output:
 

mysql> show slave status\G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: HOST_NAME.COM Master_User: slave_user Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 106 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000007 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: database1,database2,database3 Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 106 Relay_Log_Space: 407 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec)ERROR: No query specified

If show master status or shwo slave status commands didn't reveal replication issue, one needs to stare at the mysql log for more info.