Category Archives: MySQL

Debian LAMP stack in one command

Start out with the basics – Apache, MySQL and PHP + extensions

apt-get install mysql-server mysql-client apache2 php5 php5-cli libapache2-mod-php5 php5-mysql php5-curl php5-gd php-pear php5-imagick php5-mcrypt php5-memcache php5-mhash php5-sqlite php5-xmlrpc php5-xsl php5-json php5-dev libpcre3-dev

I recommend using this on Debian Jessie. That way you get a bleeding edge version of PHP (5.5.8 as of right now)

OpCode cache and APCu (Optional)

pecl install ZendOpcache-beta
pecl install apcu-beta

Add the following to /etc/php5/apache2/php.ini

zend_extension=opcache.so
extension=apcu.so

Configure Opcache by adding this to /etc/php5/apache2/php.ini

opcache.max_accelerated_files=30000
opcache.memory_consumption=160
opcache.revalidate_freq=0

Restart apache. The extensions should be available and configured. (You can verify this via phpinfo(); )

Useful tools (Optional)

apt-get install htop vim

Check if column in MySQL table has duplicate values

So you have a column that is not UNIQUE, but you’d still like to check if there is duplicate values in it.

If your column name is my_column in table my_table, the query is:

SELECT my_column, COUNT(*) as count
FROM my_table
GROUP BY my_column
HAVING COUNT(*) > 1

This will return all records that have duplicate my_column content, as well as how many times this content occurs in the database.

Source

Checklist for moving a PHP-based site from one server to another

Research

  • Make sure server configuration is the same. Install missing php extensions and modules. (A good way of comparing is by diffing the output of phpinfo() between the two servers.)
  • Check the mail configuration on the new server. (Is it Sendmail, Postfix or something else? Is any configuration required to get the mail() function working?)

Move

  • Move files (Best ways are either to pack them in an archive (.tar.gz) or moving the files via sshfs.
  • Set file permissions (files should typically be owned by the web server unless you run some sort of suexec config.)
  • Move databases and create database users on the new server.
  • Move CRON jobs (Crontab, etc)
  • Update DNS records (Don’t forget the MX records for mail, and any SPD records you may have.)

Application-specific: WordPress

  • Disable caching plugins (If you figure this out too late, just comment out define(‘WP_CACHE’, true); from wp-config.php and then Disable -> Enable -> Activate your caching plugin through wp-admin.

Post-move

  • Run CacheCheck to see if your DNS has propagated
  • Monitor site via Pingdom or similar service for errors after the move. (And not only timeout, make sure you check for non-200 HTTP code errors too!)

Anything missing? Post a comment!

MySQL query cache overview – Enabling and checking status

Enabling query cache

Edit your my.cnf

Below you will find some example values to get you started. (Paste them after the [mysqld] declaration)

query_cache_size    = 96M
tmp_table_size      = 16M
max_heap_table_size = 16M
table_open_cache    = 256
thread_cache_size   = 32
query_cache_limit   = 8M

Now log onto MySQL from CLI:

mysql -u root -p

Check cache settings:

SHOW VARIABLES LIKE 'query%';

Result example:

+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_alloc_block_size       | 8192      |
| query_cache_limit            | 8388608   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 100663296 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
| query_prealloc_size          | 8192      |
+------------------------------+-----------+

The important part here is that query_cache_type is ON and that query_cache_size is not 0.

Check cache performance

To see how the cache is performing (hits/misses and free space), run:

SHOW STATUS LIKE 'Qc%';

Result example:

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 105      |
| Qcache_free_memory      | 98688872 |
| Qcache_hits             | 2946     |
| Qcache_inserts          | 1581     |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 13       |
| Qcache_queries_in_cache | 1357     |
| Qcache_total_blocks     | 2850     |
+-------------------------+----------+

More reading

Speed Up Your Web Site With MySQL Query Caching
MySQL.com Query Cache Configuration