CentOS 6.6 and MySQL 5.6
By default, CentOS 6.6 will come with MySQL 5.1 packages. After MySQL upgrade to version 5.6, some old good working SQL inserts start to cause errors. The error message was “data too long” which was a bit strange.
MySQL
MySQL master full disk and replication problem
After /var partition on master MySQL server was full, replication has stopped working. Master server wasn’t able to write new changes to the binlog file. Recovery procedure seems simple, clean /var partition and restart slave servers but it was not so easy.
Fixing MySQL replication after slaves’s relay log was corrupted
MySQL replication on slave (version 5.1.61) has stopped. Slave_IO_Running was marked as Yes, but Slave_SQL_Running as No. Simple stop/start slave didn’t help so further problem analysis was needed. It seemed that current slave’s relay log was corrupted because testing with “mysqlbinlog” has printed out an error. Therefore, the solution was to discard current relay binlogs and to point slave to the last master binlog position.
MySQL replication – master migration
This cookbook explains how to move MySQL master server to another host. Scenario in short goes: dump out master databases, import data on the new master server and point slave to the new master server. If you follow this simple steps, master migration should proceed without problems.
MySQL query logging
Small tip of how to log all queries done by the MySQL server. It is very useful if you want to preview SQL requests from WordPress, Joomla or any other system or client. After logging is enabled, server will write information to the log file when clients connect or disconnect, and it will log each SQL statement.
How to add a new MySQL slave
This post explains how to add a new slave to the existing MySQL replication. New slave server can be added to the MySQL replication without stopping master or shutting down existing slave server. You will have to temporarily stop replication on the slave server in order to take the current data snapshot.
MySQL replication recovery
MySQL replication can stop if slave fails to execute SQL statement from the binary log. From that moment, slave prints last error and waits for replication recovery. If master has consistent snapshot, then is only necessary to re-point slave to the new master position. It can be done with “change master to” or “sql_slave_skip_counter”.
From MySQL to HTML with PHP and XML
In my post “From MySQL to XML with PHP”, I described how to generate XML from the MySQL database. Instead of printing out XML, generated XML can be buffered and transformed with XSL to the HTML. Maybe it sounds complicated for a simple process of displaying MySQL data on the WEB page. But if you set a WEB architecture this way, you will have a separated presentation layer from the database and business logic.
From MySQL to XML with PHP
PHP XML generator can be written in less then 100 lines. In this example, data from the MySQL database are converted to the XML. PHP function sql2xml has optional second parameter to describe the XML hierarchy, while first parameter is SQL query. Second parameter is string in form of integers separated by commas.
Make WordPress fly
New WordPress installation with few modules can give only 3.44 requests per second. After turning on MySQL cache and PHP cache, site becomes 150% quicker. This can be done on MySQL and PHP level. On the other hand if your site is installed on shared host, you probably have restricted access and you can’t edit my.cnf nor install APC. You can only install cache module like WP-Cache is, to improve speed.
LAMP setup: Make it faster
Popularity means more traffic or we can say more requests per second. Did you ever measure how many requests per second your Web server can handle and how to improve it? This article should give the answer and show how to increase quickness of Web applications like WordPress, phpBB, Joomla …