Archive for the ‘MySQL’ Category

How to Set MySQL MariaDB server root user to be able to connect from any host on the Internet / Solution to ‘ ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES) ‘

Tuesday, September 3rd, 2019

How-to-set-up-MariaDB-server-root-admin-user-to-be-able-to-connect-from-any-host-anywhere-mariadb-seal-logo-picture

In this small article, I'll shortly explain on how I setup a Standard default package MariaDB Database server on Debian 10 Buster Linux and how I configured it to be accessible from any hostname on the Internet in order to make connection from remote Developer PC with MySQL GUI SQL administration tools such as MySQL WorkBench / HeidiSQL / Navicat / dbForge   as well as the few set-backs experienced in the process (e.g. what was the reason for ' ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) '  error and its solution.

Setting new or changing old MariaDB (MySQL) root server password

 

I've setup a brand new MariaDB database (The new free OpenSource software fork of MySQL) mariadb-server-10.3 on a Debian 10, right after the OS was installed with the usual apt command:

# apt install mariadb-server

Next tep was to change the root access password which was set to empty pass by default, e.g. connected with mysql CLI locally while logged via SSH on server and run:

MariaDB [(none)]> mysql -u root -p

use mysql;
update user set authentication_string=PASSWORD("MyChosenNewPassword") where User='root';

There was requirement by the customer, that MySQL server is not only accessed locally but be accessed from any IP address from anywhere on the Internet, so next step was to do so.

Allowing access to MySQL server from Anywhere

Allowing access from any host to MariaDB SQL server  is a bad security practice but as the customer is the King I've fulfilled this weird wish too, by changing the listener for MariaDB (MySQL) on Debian 10 codenamed Buster
 
changing the default listener
to be not the default 127.0.0.1 (localhost) but any listener is done by modifying the bind-address directive in conf /etc/mysql/mariadb.conf.d/50-server.cnf:

root@linux:~# vim /etc/mysql/mariadb.conf.d/50-server.cnf

Then comment out

bind-address  = 127.0.0.1

and  add instead 0.0.0.0 (any listener)

 

bind-address  = 0.0.0.0
root@linux:/etc/mysql/mariadb.conf.d# grep -i bind-address 50-server.cnf
##bind-address            = 127.0.0.1
bind-address    = 0.0.0.0


Then to make the new change effective restart MariaDB (luckily still using the old systemV init script even though systemd is working.
 

root@linux:~# /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.


To make sure it is properly listening on MySQL defaults TCP port 3306, then as usual used netcat.

root@pritchi:~# netstat -etna |grep -i 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      109        1479917  

 

By the way the exact mariadb.cnf used on this middle-sized front-backend server is here – the serveris planned to be a Apache Web server + Database host with MySQL DB of a middle range to be able to serve few thousand of simultaneous unique customers.

To make sure no firewall is preventing MariaDB to be accessed, I've checked for any reject rules iptables and ipset definitions, e.g.:
 

root@linux:~# iptables -L |gre -i rej

root@linux:~# ipset list

 

Then to double make sure the MySQL is allowed to access from anywhere, used simple telnet from my Desktop Laptop PC (that also runs Debian Linux) towards the server .

hipo@jeremiah:~$ telnet 52.88.235.45 3306
Trying 52.88.235.45…
Connected to 52.88.235.45.
Escape character is '^]'.
[
5.5.5-10.3.15-MariaDB-1
                       rQ}Cs>v\��-��G1[W%O>+Y^OQmysql_native_password
Connection closed by foreign host.

 

As telnet is not supporting the data encryption after TCP proto connect, in a few seconds time, remote server connection is terminated.

 

Setting MySQL user to be able to connect to local server MySQL from any remote hostname


I've connected locally to MariaDB server with mysql -u root -p and issued following set of SQL commands to make MySQL root user be able to connect from anywhere:

 

CREATE USER 'root'@'%' IDENTIFIED BY 'my-secret-pass';
GRANT ALL ON *.* TO 'root'@'localhost';
GRANT ALL ON *.* TO 'root'@'%';

 

Next step, I've took was to try logging in with root (admin) MariaDB superuser from MySQL CLI (Command Line Interface) on my desktop just to find out, I'm facing a nasty error.
 

hipo@jeremiah:~$ mysql -u root -H remote-server-hostname.com -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)


My first guess was something is wrong with my root user created in MySQL's mysql.user table (In MySQL this is the privileges table that stores, how MySQL user credentials are handled by mysqld local OS running process.

 

Changing the MySQL root (admin) password no longer possible on Debian 10 Buster?

 

The standard way ot change the MySQL root password well known via a simple dpkg-reconfigure (provided by Debian's debconf is no longer working so below command produces empty output instead of triggering the good old Ncurses text based interface well-known over the years …

 

root@linux:~# /usr/sbin/dpkg-reconfigure mariadb-server-10.3

 

 

Viewing MariaDB (MySQL) username / password set-up from the CLI

 

To list how this set-privileges looked like I've used following command:

 

MariaDB [mysql]> select * from mysql.user where User = 'root';
+———–+——+——————————————-+————-+————-+————-+————-+————-+———–+————-+—————+————–+———–+————+—————–+————+————+————–+————+———————–+——————+————–+—————–+——————+——————+—————-+———————+——————–+——————+————+————–+————————+———————+———-+————+————-+————–+—————+————-+—————–+———————-+———————–+———————–+——————+———+————–+——————–+
| Host      | User | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | Delete_history_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string | password_expired | is_role | default_role | max_statement_time |
+———–+——+——————————————-+————-+————-+————-+————-+————-+———–+————-+—————+————–+———–+————+—————–+————+————+————–+————+———————–+——————+————–+—————–+——————+——————+—————-+———————+——————–+——————+————+————–+————————+———————+———-+————+————-+————–+—————+————-+—————–+———————-+———————–+———————–+——————+———+————–+——————–+
| localhost | root | *E6D338325F50177F2F6A15EDZE932D68C88B8C4F | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      | Y                   |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password |                       | N                | N       |              |           0.000000 |
| %         | root | *E6D338325F50177F2F6A15EDZE932D68C88B8C4F | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | N          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      | Y                   |          |            |             |              |             0 |           0 |               0 |                    0 |                       |                       | N                | N       |              |           0.000000 |
+———–+——+——————————————-+————-+————-+————-+————-+————-+———–+————-+—————+————–+———–+————+—————–+————+————+————–+————+———————–+——————+————–+—————–+——————+——————+—————-+———————+——————–+——————+————+————–+————————+———————+———-+————+————-+————–+—————+————-+—————–+———————-+———————–+———————–+——————+———+————–+——————–+

 

The hashed (encrypted) password string is being changed from the one on the server, so please don't try to hack me (decrypt it) 🙂
As it is visible from below output the Host field for root has the '%' string which means, any hostname is authorized to be able to connect and login to the MySQL server, so this was not the problem.

After quite some time on reading on what causes
' ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
',
I've spend some time reading various forum discussions online on the err such as the one on StackOverflow here SuperUser.com's  how to fix access denied for user 'root'@'localhost' and one on askubuntu.com's – ERROR 1045(28000) : Access denied for user 'root@localhost' (using password: no ) and after a while finally got it, thanks to a cool IRC.FREENODE.NET guy nicknamed, hedenface who pointed me I'm that, I'm trying to use the -H flag (Prodice HTML) instead of -h (host_name), it seems somehow I ended up with the wrong memory that the -H stands for hostname, by simply using -h I could again login Hooray!!!

 

root@linux:~$ mysql -u root -h remote-server-host.com -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.3.15-MariaDB-1 Debian 10

 

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


I've further asked the customer to confirm, he can connect also from his Microsoft Windows 10 PC situated on a different LAN network and got his confirmation. Few notes to make here is I've also installed phpmyadmin on the server using phpmyadmin php source code latest version, as in Debian 10 it seems the good old PHP is no longer available (as this crazy developers again made a mess and there is no phpmyadmin .deb package in Debian Buster – but that's a different story I'll perhaps try to document in some small article in future.

Mass substitute WordPress site Old domain URL to new URL in MySQL (MariaDB) database after website migration

Thursday, September 13th, 2018

mass-substitute-old-urls-to-new-urls-when-moving-wordpress-website-migrate-wordpress

Mass substitute WordPress site Old domain URL to new URL in MySQL (MariaDB) database after website migration

If you have just migrated a wordpress blog or site to a new server (domain URL) and you have many articles pointing to the old URL. Out of sudden the new domain will end up with many broken links and that would have a severe negative SEO effect on your website leading to a certain downfall of your number of daily unique visits.

Of course manually changing the URL links is achievable by going through all Published Posts when migrating small websites with 10-20 pages,  however it is an impossible tedious task you would definitely want to avoid when you're migrating large WP based websites with few hundred or thousands of posts / pages,
bacause this would be a few weeks of mindless repeatable job to go through each and every post and substite the broken URLs.

Fortunately with a little bit of SQL magic either through MySQL CLI or PHPMyAdmin (if the website is moved to a shared hosting where you have disabled access to MySQL (MariaDB) default connect tcp port 3306.

Depending on the type of WordPress or Website the old broken URLs might be located in various Database tables.
 

– So when Mass URL substitution is might be required ?


1. You migrate a Website http://what-ever-website.com with (PHP / CSS / HTML / Templates) etc. from Hosting Provider Hostgator.com to UK2.com (because the website target client changed lately to United Kingdom customers) to http://what-ever-website.co.uk and the site is moved to a new domain beacause of Business rebranding
 

2. Other reason for changing internal URLs from one URL to another might be if you're migrating your website from HTTP to HTTPS for security.

3. You are restructuring file storage / image directories on the server or due to migration of files to external CDN (Content Delivery Network).
For example (http://your-server.com/images/ , http://your-server.com/files )  URLs pointing to old website location subdirectory has to be changed to the new one (http://your-server.com/img/ , http://your-server.com/data)

 

– So what is the automated approach to solve the task of Mass URL substitution across WP site ?

 

  •  Create full backup of all your website database and double-check the backup (try to restore on a test (home) server or other hosting account to make sure the backup is consistent and restore would work normally if necessery
     
  • You can Create Backup either with mysqldump command tool manually … with the right command arguments or use some kind of script such as My tiny mysqlbackupper.sh shell script which I shared under my previous article Make daily MySQL on Linux backup with Shell Script  via PhpMyAdmin.


2. Change old website URL to new one directly from Database using MySQL text client
 

To change incorrect URL with the new correct one the general query to run is:

 

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find string’, ‘replace string’);

 

To change old website URL to the new website URL across every table within the wordpress database use below queries:
 

hipo@linux:~$ mysql -u root -p
Enter password:

 

USE blog;

 

UPDATE wp_options SET option_value = replace(option_value, 'Existing (old) URL', 'New URL') WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts SET post_content = replace(post_content, 'Existing (old)URL', 'New URL');

UPDATE wp_postmeta SET meta_value = replace(meta_value,'Existing (old) URL','New URL');

UPDATE wp_usermeta SET meta_value = replace(meta_value, 'Existing (old) URL','New URL');

UPDATE wp_links SET link_url = replace(link_url, 'Existing (old) URL','New URL');

UPDATE wp_comments SET comment_content = replace(comment_content , 'Existing (old) URL','New URL');


3. Replace Old website URL to New one after migration using PHPMyAdmin web interface
 

If you don't have access  ssh shell, you can also run the queries via PhpMyAdmin to do so:

1. Open PHPMyadmin URL Panel in browser and login with your user / pass

2. Choose the wordpress database of the wordpress site / blog

3. Select SQL tab and in the panel type on above given SQL queries
 

web-hosting-phpmyadmin-sql-query-tab-screenshot-how-to-run-sql-queries-via-phpmyadmin

If you're lazy to type there is also a web based SQL queries generator tool for moving websites to a new domain


4. Using Search and Replace WordPress plugin to do the old URL to new URL (strings) transition
 

If you have never used SQL queries and you're totally new to it and don't want to risk breaking up something there is also a bunch of wordpress plugins available that do the URL string substitution throughout each wordpress table in a WP database one such WordPress plugin is Search and Replace I have written earlier an article Change string in all WordPress Posts with Search and Replace plugin.


5. Problems with data-serialization
 

If you do a simple search and replace of Old domain urls to New ones, using above given commands and you still end up with some broken links on WP Pages that might be due to data-serialization issues (for the cause of issues check out what is data serialization).
Data serialization in wordpress terms is an array of PHP encrypted data that contains the actual URL, thus a simple search and replace as explained above if URLs use data-serialization would not work. There are available tools online that does URL search and replace operation through  "serialize-data sensitively" if you stuck with data-serializatoin caused issues.

Besides that for there are written scripts that does URL substitution to a WordPress or Joomla websites so an alternative to above WP plugin to replace the URL after migration is to use one of the scripts available a very famous one that will do pain-free all URL / string substitutions inside your WP, Drupal,  Joomla databses is interconnect/it.
 

Few closure words
 

As a system administrator and webmaster I have migrated wordpress installations many times with the need to change the old URLs to a new ones for both customer websites and my own wordpress based. On many ocassions because of lack of attention and hurry, I've messed up things.
The moral I got out of this is when you're doing a WordPress migration just like everything you have to be very attentive and do everything step by step slowly and have a good idea on what you're doing in advance …

Even as a person who had overall idea on how MySQL Server works and have experience in writting SQL queries, I have to confess I've  made mistakes during URL substitution operations when doing it via the MySQL CLI every now and then.

Thus I would recommend you better use some of the many plugins for wordpress and script tools (few of which I mentioned above), especially if you're not having at least few years with some kind of UNIX variation / Linux / MySQL.

MySQL crashes after upgrade from MySQL to MariaDB and how to fix it

Tuesday, August 21st, 2018

how-to-fix-crashing-mysql-after-upgrade-to-mariadb-database-mariadb-logo.png

If you have recently upgraded your Debian / Ubuntu / CentOS Linux Server to the latest RPM / DEB packages as part of the upgrade you might have noticed the upgrade of MySQL Community Server  (which was bought by Oracle Corporation few years ago) is automatically upgraded to MariaDB (which is a MySQL fork made by the original developers of MySQL and guaranteed to stay open source. Just to name some of the Notable users include Wikipedia, WordPress.com and Google.).

You might have noticed MariaDB's restart script which is still under /etc/init.d/mysql  won't start and a quick check in /var/log/mysql.err | /var/log/mysql.log
shows errors of /usr/bin/mysqld crashing with errors like:

140502 14:13:05 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: Log scan progressed past the checkpoint lsn 108 1057948207
140502 14:13:06  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files…
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer…
InnoDB: Doing recovery: scanned up to log sequence number 108 1058059648
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 15 row operations to undo
InnoDB: Trx id counter is 0 562485504
140502 14:13:06  InnoDB: Starting an apply batch of log records to the database…
InnoDB: Progress in percents: 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Starting in background the rollback of uncommitted transactions
140502 14:13:06  InnoDB: Rolling back trx with id 0 562485192, 15 rows to undo
140502 14:13:06  InnoDB: Started; log sequence number 108 1058059648
140502 14:13:06  InnoDB: Assertion failure in thread 1873206128 in file ../../../storage/innobase/fsp/fsp0fsp.c line 1593
InnoDB: Failing assertion: frag_n_used > 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
140502 14:13:06 – mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=0
max_threads=151
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 345919 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…
stack_bottom = (nil) thread_stack 0x30000
140502 14:13:06 [Note] Event Scheduler: Loaded 0 events
140502 14:13:06 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.41-3ubuntu12.10'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
/usr/sbin/mysqld(my_print_stacktrace+0x2d) [0xb7579cbd]
/usr/sbin/mysqld(handle_segfault+0x494) [0xb7245854]
[0xb6fc0400]
/lib/tls/i686/cmov/libc.so.6(abort+0x182) [0xb6cc5a82]
/usr/sbin/mysqld(+0x4867e9) [0xb74647e9]
/usr/sbin/mysqld(btr_page_free_low+0x122) [0xb74f1622]
/usr/sbin/mysqld(btr_compress+0x684) [0xb74f4ca4]
/usr/sbin/mysqld(btr_cur_compress_if_useful+0xe7) [0xb74284e7]
/usr/sbin/mysqld(btr_cur_pessimistic_delete+0x332) [0xb7429e72]
/usr/sbin/mysqld(btr_node_ptr_delete+0x82) [0xb74f4012]
/usr/sbin/mysqld(btr_discard_page+0x175) [0xb74f41e5]
/usr/sbin/mysqld(btr_cur_pessimistic_delete+0x3e8) [0xb7429f28]
/usr/sbin/mysqld(+0x526197) [0xb7504197]
/usr/sbin/mysqld(row_undo_ins+0x1b1) [0xb7504771]
/usr/sbin/mysqld(row_undo_step+0x25f) [0xb74c210f]
/usr/sbin/mysqld(que_run_threads+0x58a) [0xb74a31da]

/usr/sbin/mysqld(trx_rollback_or_clean_all_without_sess+0x3e3) [0xb74ded43]
/lib/tls/i686/cmov/libpthread.so.0(+0x596e) [0xb6f9f96e]
/lib/tls/i686/cmov/libc.so.6(clone+0x5e) [0xb6d65a4e]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Any recommendations?
mysql

I hoped to solve the /usr/bin/mysqld segfault error with server reboot as I though the problem is caused by the fact libc library was updated, but even a reboot did not solve it.

I've investigated online for a solution and found following MySQL corruption and recovery article.

The solution outlined there is very simple and comes to adding the line:
 

innodb_force_recovery = 1


to /etc/mysql/my.cnf

Assuming the mysql server is not running before restarting mariadb server.

1. Make a backup (Dump) of all MySQL tables

mysql:~# mysqldump -A > dump.sql

2. Drop all databases which need recovery.
You can do that from mysql cli or phpmyadmin

3. Stop mysqld.

mysql:~# /etc/init.d/mysql restart

4.  Remove /var/lib/mysql/ib*

mysql:~# rm -rf /var/lib/mysql/ib*

5. Comment out innodb_force_recovery in /etc/mysql/my.cnf

6. Restart mysqld. Look at mysql error log.
If everything is fine and you have problems with broken or missing databases the best thing next is to stop again mariadb and

7. Restore databases from the dump

mysql:~# mysql < dump.sql

 

 

 

Check is MySQL dropping connections or how to check drop out SQL problems and debug problems with slow queries

Thursday, October 26th, 2017

mysql-dropping-connections-how-to-check-command-drop-out-sql-problems-and-debugging-slow-queries

If you have migrated a website or you recently upgraded MySQL and you face occasional problems with dropped connections to remote Web site or application it is likely that the problem is rooted either in Apache being unable to handle multiple connections, e.g. you will get a lot of 503 errors in log or MySQL dropping connections due to someout or MySQL / PHP bug that occurs after packages upgrade.

So how to check is MySQL dropping connections?

Use below command one liner shell script and check for Aborted conncetions a relatively high number is indication MySQL is dropping connections from PHP script or local / remote application such as SOAP or XSLT connects.
 

# echo 'show global status' | mysql -u root -p |grep -i connect
Enter password:
Aborted_connects    710
Connections    49537
Max_used_connections    76
Ssl_client_connects    0
Ssl_connect_renegotiates    0
Ssl_finished_connects    0
Threads_connected    1

Another indicator for MySQL dropping connections is if you find MySQL to have many slow queries, that would mean the slow queries won't be processed on time and there will be consequential connection drop outs.
 

# echo 'show global status' | mysql -u root -p |grep -i slow
Enter password:
Slow_launch_threads    0
Slow_queries    120237


If you find any you can use some MySQL / MariaDB monitoring script such as (mytop / mtop – for more on monitoring MySQL queries in real time I recommend you check out my previous article Monitoring MySQL queries and debugging performance (slow queries))

Fix MySQL ibdata file size – ibdata1 file growing too large, preventing ibdata1 from eating all your server disk space

Thursday, April 2nd, 2015

fix-solve-mysql-ibdata-file-size-ibdata1-file-growing-too-large-and-preventing-ibdata1-from-eating-all-your-disk-space-innodb-vs-myisam

If you're a webhosting company hosting dozens of various websites that use MySQL with InnoDB  engine as a backend you've probably already experienced the annoying problem of MySQL's ibdata1 growing too large / eating all server's disk space and triggering disk space low alerts. The ibdata1 file, taking up hundreds of gigabytes is likely to be encountered on virtually all Linux distributions which run default MySQL server <= MySQL 5.6 (with default distro shipped my.cnf). The excremental ibdata1 raise appears usually due to a application software bug on how it queries the database. In theory there are no limitation for ibdata1 except maximum file size limitation set for the filesystem (and there is no limitation option set in my.cnf) meaning it is quite possible that under certain conditions ibdata1 grow over time can happily fill up your server LVM (Storage) drive partitions.

Unfortunately there is no way to shrink the ibdata1 file and only known work around (I found) is to set innodb_file_per_table option in my.cnf to force the MySQL server create separate *.ibd files under datadir (my.cnf variable) for each freshly created InnoDB table.
 

1. Checking size of ibdata1 file

On Debian / Ubuntu and other deb based Linux servers datadir is /var/lib/mysql/ibdata1

server:~# du -hsc /var/lib/mysql/ibdata1
45G     /var/lib/mysql/ibdata1
45G     total


2. Checking info about Databases and Innodb storage Engine

server:~# mysql -u root -p
password:

mysql> SHOW DATABASES;
+——————–+
| Database           |
+——————–+
| information_schema |
| bible              |
| blog               |
| blog-sezoni        |
| blogmonastery      |
| daniel             |
| ezmlm              |
| flash-games        |


Next step is to get some understanding about how many existing InnoDB tables are present within Database server:

 

mysql> SELECT COUNT(1) EngineCount,engine FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','performance_schema','mysql') GROUP BY engine;
+————-+——–+
| EngineCount | engine |
+————-+——–+
|         131 | InnoDB |
|           5 | MEMORY |
|         584 | MyISAM |
+————-+——–+
3 rows in set (0.02 sec)

To get some more statistics related to InnoDb variables set on the SQL server:
 

mysqladmin -u root -p'Your-Server-Password' var | grep innodb


Here is also how to find which tables use InnoDb Engine

mysql> SELECT table_schema, table_name
    -> FROM INFORMATION_SCHEMA.TABLES
    -> WHERE engine = 'innodb';

+————–+————————–+
| table_schema | table_name               |
+————–+————————–+
| blog         | wp_blc_filters           |
| blog         | wp_blc_instances         |
| blog         | wp_blc_links             |
| blog         | wp_blc_synch             |
| blog         | wp_likes                 |
| blog         | wp_wpx_logs              |
| blog-sezoni  | wp_likes                 |
| icanga_web   | cronk                    |
| icanga_web   | cronk_category           |
| icanga_web   | cronk_category_cronk     |
| icanga_web   | cronk_principal_category |
| icanga_web   | cronk_principal_cronk    |


3. Check and Stop any Web / Mail / DNS service using MySQL

server:~# ps -efl |grep -E 'apache|nginx|dovecot|bind|radius|postfix'

Below cmd should return empty output, (e.g. Apache / Nginx / Postfix / Radius / Dovecot / DNS etc. services are properly stopped on server).

4. Create Backup dump all MySQL tables with mysqldump

Next step is to create full backup dump of all current MySQL databases (with mysqladmin):

server:~# mysqldump –opt –allow-keywords –add-drop-table –all-databases –events -u root -p > dump.sql
server:~# du -hsc /root/dump.sql
940M    dump.sql
940M    total

 

If you have free space on an external backup server or remotely mounted attached (NFS or SAN Storage) it is a good idea to make a full binary copy of MySQL data (just in case something wents wrong with above binary dump), copy respective directory depending on the Linux distro and install location of SQL binary files set (in my.cnf).
To check where are MySQL binary stored database data (check in my.cnf):

server:~# grep -i datadir /etc/mysql/my.cnf
datadir         = /var/lib/mysql

If server is CentOS / RHEL Fedora RPM based substitute in above grep cmd line /etc/mysql/my.cnf with /etc/my.cnf

if you're on Debian / Ubuntu:

server:~# /etc/init.d/mysql stop
server:~# cp -rpfv /var/lib/mysql /root/mysql-data-backup

Once above copy completes, DROP all all databases except, mysql, information_schema (which store MySQL existing user / passwords and Access Grants and Host Permissions)

5. Drop All databases except mysql and information_schema

server:~# mysql -u root -p
password:

 

mysql> SHOW DATABASES;

DROP DATABASE blog;
DROP DATABASE sessions;
DROP DATABASE wordpress;
DROP DATABASE micropcfreak;
DROP DATABASE statusnet;

          etc. etc.

ACHTUNG !!! DON'T execute!DROP database mysql; DROP database information_schema; !!! – cause this might damage your User permissions to databases

6. Stop MySQL server and add innodb_file_per_table and few more settings to prevent ibdata1 to grow infinitely in future

server:~# /etc/init.d/mysql stop

server:~# vim /etc/mysql/my.cnf
[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Delete files taking up too much space – ibdata1 ib_logfile0 and ib_logfile1

server:~# cd /var/lib/mysql/
server:~#  rm -f ibdata1 ib_logfile0 ib_logfile1
server:~# /etc/init.d/mysql start
server:~# /etc/init.d/mysql stop
server:~# /etc/init.d/mysql start
server:~# ps ax |grep -i mysql

 

You should get no running MySQL instance (processes), so above ps command should return blank.
 

7. Re-Import previously dumped SQL databases with mysql cli client

server:~# cd /root/
server:~# mysql -u root -p < dump.sql

Hopefully import should went fine, and if no errors experienced new data should be in.

Altearnatively if your database is too big and you want to import it in less time to mitigate SQL downtime, instead import the database with:

server:~# mysql -u root -p
password:
mysql>  SET FOREIGN_KEY_CHECKS=0;
mysql> SOURCE /root/dump.sql;
mysql> SET FOREIGN_KEY_CHECKS=1;

 

If something goes wrong with the import for some reason, you can always copy over sql binary files from /root/mysql-data-backup/ to /var/lib/mysql/
 

8. Connect to mysql and check whether databases are listable and re-check ibdata file size

Once imported login with mysql cli and check whther databases are there with:

server:~# mysql -u root -p
SHOW DATABASES;

Next lets see what is currently the size of ibdata1, ib_logfile0 and ib_logfile1
 

server:~# du -hsc /var/lib/mysql/{ibdata1,ib_logfile0,ib_logfile1}
19M     /var/lib/mysql/ibdata1
1,1G    /var/lib/mysql/ib_logfile0
1,1G    /var/lib/mysql/ib_logfile1
2,1G    total

Now ibdata1 will grow, but only contain table metadata. Each InnoDB table will exist outside of ibdata1.
To better understand what I mean, lets say you have InnoDB table named blogdb.mytable.
If you go into /var/lib/mysql/blogdb, you will see two files
representing the table:

  •     mytable.frm (Storage Engine Header)
  •     mytable.ibd (Home of Table Data and Table Indexes for blogdb.mytable)

Now construction will be like that for each of MySQL stored databases instead of everything to go to ibdata1.
MySQL 5.6+ admins could relax as innodb_file_per_table is enabled by default in newer SQL releases.


Now to make sure your websites are working take few of the hosted websites URLs that use any of the imported databases and just browse.
In my case ibdata1 was 45GB after clearing it up I managed to save 43 GB of disk space!!!

Enjoy the disk saving! 🙂

Speed up WordPress / Joomla CMS and MySQL server on Linux with tmpfs ram file system / Decrease Website pageload times with RAM caching

Wednesday, March 4th, 2015

speed-up-accelerate-wordpress-joomla-drupal-cms-and-mysql-server-with-tmpfs_ramfs_decrease-pageload-times-with-ram-caching
As a WordPress blog owner and an sys admin that has to deal with servers running a lot of WordPress / Joomla / Droopal and other custom CMS installed on servers, performoing slow or big enough to put a significant load on servers
and I love efficiency and hardware cost saving is essential for my daily job, I'm constantly trying to find new ways to optimize Customer Website (WordPress) and rest of sites in order to utilize better our servers and improve our clients sites speed (and hence satisfaction). 

There is plenty of little things to do on servers but probably among the most crucial ones which we use nowadays that save us a lot of money is tmpfs, and earlier (ramfs) – previously known as shmfs).
TMPFS is a (Temporary File Storage Facility) Linux kernel technology based on ramfs (used by Linux kernel initrd / initramfs on boot time in order to load and store the Linux kernel in memory, before system hard disk partition file systems are mounted) which is heavily used by virtually all modern popular Linux distributions. 

Using ramfs (cramfs variation – Compressed ROM filesystem) has been used to store different system environment kernel and Desktop components of many Linux environment / applications and used by a lot of the Linux BootCD such as the most famous (Klaus Knopper's) KNOPPIX LiveCD and Trinity Rescue Kit Linux (TRK uses /dev/shm which btw can be seen on most modern Linux distros and is actually just another mounted tmpfs).
If you haven't tried Live Linux yet try it out as me and a lot of sysadmins out there use some kind of LiveLinux at least few times on yearly basis  to Recover Unbootable Linux servers after some applied remote Updates as well as for Rescuing (Save) Data from Linux server failing to properly boot because of hard disk (bad blocks) failures. As I said earlier TMPFS is also used on almost any distribution for the /dev/ filesystem which is kept in memory.

You can see which tmpfs partitions is used on your Linux server with:

 

debian-server:~# mount |grep -i tmpfs
tmpfs on /lib/init/rw type tmpfs (rw,nosuid,mode=0755)
udev on /dev type tmpfs (rw,mode=0755)
tmpfs on /dev/shm type tmpfs (rw,nosuid,nodev)

 

Above is an output from a standard Debian Linux server. On CentOS 7 standard mounted tmpfs are as follows:

 

[root@centos ~]# mount |grep -i tmpfs
devtmpfs on /dev type devtmpfs (rw,nosuid,seclabel,size=1016332k,nr_inodes=254083,mode=755)
tmpfs on /dev/shm type tmpfs (rw,nosuid,nodev,seclabel)
tmpfs on /run type tmpfs (rw,nosuid,nodev,seclabel,mode=755)
tmpfs on /sys/fs/cgroup type tmpfs (rw,nosuid,nodev,noexec,seclabel,mode=755)

 

[root@centos ~]# df -h|grep -i tmpfs
devtmpfs                 993M     0  993M   0% /dev
tmpfs                   1002M   92K 1002M   1% /dev/shm
tmpfs                   1002M  8.8M  993M   1% /run
tmpfs                   1002M     0 1002M   0% /sys/fs/cgroup

The /run tmpfs mounted directory is also to be seen also on latest Ubuntus and Fedoras and is actually the good old /var/run ( where applications keep there pids and some small app related files) stored in tmpfs filesystem stored in memory.

If you're wondering what is /dev/shm and why it appears mounted on every single Linux Server / Desktop you've ever used this is a special filesystem shared memory which various running programs (processes) can use to transfer data quick and efficient between each other to preven the slow disk swapping. People using Linux for the rest 15 years should remember /dev/shm has been a target of a lot of kernel exploits as historically it had a lot of security issues.

While writting this article I've just checked about KNOPPIX developed amd just for info as of time of writting this distro has already 1000+ programs on CD version and 2600+ packages / application on DVD version.
Nowadays Knoppix is mostly used mostly as USB Live Flash drive as a lot of people are dropping CD / DVD use (many servers doesn't have a CD / DVD Drive) and for USB Live Flash Linux distros tmpfs is also key technology used as this gives the end user an amazing fast experience (Desktop applications run much fasten on Live USBs when tmpfs is used than when the slow 7200 RPM HDDs are used).

Loading big parts of the distribution within RAM (with tmpfs from Linux Kernel 2.4+ onwards) is also heavily used by a lot of Cluster vendors in most of Clustered (Cloud) Linux based environemnts, cause TMPFS gives often speeds up improvements to x30 times and decreases greatly I/O HDD. FreeBSD users will be happy to know that TMPFS is already ported and could be used on from FreeBSD 7.0+ onward.

In this small article I will give you example use on how I use tmpfs to speed up our WordPress Websites which use WP Caching plugins such as W3 Total Cache and WP Super Cache
and Hyper Cache / WP Super Cache disk caching and MySQL server as a Database backend.
Below example is wordpress specific but since it can be easily applied to JoomlaDrupal or any other CMS out there that uses mySQL server to make a lot of CPU expensive memory hungry (LEFT JOIN) queries which end up using a slow 7200 RPM hard disk.


 

1. Preparing tmpfs partitions for WordPress File Cache directory
 

If you want to give tmpfs a test drive, I recommend you try to create / mount a 20 Megabyte partition. To create a tmpfs partition you don't need to use a tool like mkfs.ext3 / mkfs.ext4 as TMPFS is in reality a virtual filesystem that is mapped in the server system physical RAM (volatile memory). TMPFS is very nice because if you run out of free RAM system starts a combination of RAM use + some Hard disk SWAP 
The great thing about TMPFS is it never uses all of the available RAM and SWAP, which would not halt your server if TMPFS partition gets filled, but instead you will start getting the usual "Insufficient Disk Space", just like with a physical HDD parititon. RAMFS cares much less about server compared to TMPFS, because if RAMFS is historically older.

ramfs file systems cannot be limited in size like a disk base file system which is limited by it’s capacity, thus ramfs will continue using memory storage until the system runs out of RAM and likely crashes or becomes unresponsive. This is a problem if the application writing to the file system cannot be limited in total size, so in my opinion you better stay away from RAMFS except you have a good idea what you're doing. Another disadvantage of RAMFS compared to TMPFS is you cannot see the size of the file system in df and it can only be estimated by looking at the cached entry in free.

Note that before proceeding to use TMPFS or RAMFS you should know besides having advantages, there are certain serious disadvantage that if the server using tmpfs (in RAM) to store files crashes the customer might loose his data, therefore using RAM filesystems on Production servers is best to be used just for caching folders which are regularly synchronized with (rsync) to some folder to assure no data will be lost on server reboot or crash.

Memory of fast storage areas are ideally suited for applications which need repetitively small data areas for caching or using as temporary space such as Jira (Issue and Proejct Tracking Software) Indexing  As the data is lost when the machine reboots the tmpfs stored data must not be data of high importance as even scheduling backups cannot guarantee that all the data will be replicated in the even of a system crash.

To test mounting a tmpfs virtual (memory stored) filesystem issue:
 

mount -t tmpfs tmpfs -o size=256m /mnt/tmpfs


If you want to test mount a ramfs instead:

 

 mount -t ramfs -o size=256m ramfs /mnt/ramfs

 

debian-server:~#  mount |grep -i -E "ramfs|tmpfs"
tmpfs on /lib/init/rw type tmpfs (rw,nosuid,mode=0755)
udev on /dev type tmpfs (rw,mode=0755)
tmpfs on /dev/shm type tmpfs (rw,nosuid,nodev)
tmpfs on /mnt/tmpfs type tmpfs (rw,size=256m)
ramfs on /mnt/ramfs type tmpfs (rw,size=256m)

 

Once mounted tmpfs can be used in the same way as any ext4 / reiserfs filesystem. In the same way to make mounts permanent, its necessery to add a line to /etc/fstab

To illustrate better a tmpfs use case on my blog running WordPress with W3TotalCache (W3TC) plugin cache folder in /var/www/blog/wp-content/w3tc to get advantage of tmpfs to store w3tc files.

a) Stop Apache

On Debian
 

debian-server:~# /etc/init.d/apache stop


On CentOS 
 

[root@centos ~]# /etc/init.d/httpd stop


b) Move w3tc dir to w3tc-bak

 

debian-server:~# cd /var/www/blog/wp-content/
debian-server:~# mv w3tc w3tc-bak

 

c) Create w3tc directory
 

debian-server:/var/www/blog/wp-content# mkdir w3tc
debian-server:/var/www/blog/wp-content# chown -R www-data:www-data w3tc


d) Add tmpfs record to /etc/fstab

My W3TC Cache didn't grow bigger than 2Gigabytes so I create a 2Giga directory for it by adding following in /etc/fstab 
 

debian-server:~# vim /etc/fstab

 

tmpfs /var/www/blog/wp-content/w3tc tmpfs defaults,size=2g,noexec,nosuid,uid=33,gid=33,mode=1755 0 0


You might also want to add the nr_inodes (option) to tmpfs while mounting. nr_inodes is the maximum inode for instance. Default is half the number of your physical RAM pages, (on a machine with highmem) the number of lowmem RAM page, some common option that should work is nr_inodes=5k, if you're unsure what this option does you can safely skip it 🙂

e) Mount new added tmpfs folder

Then to mount the newly added filesystem issue:
 

mount -a


Or if you're on a CentOS / RHEL server use httpd Apache user instead and whenever you have docroot and wordpress installed.

 

[root@centos ~]# chown -R apache:apache: w3tc


If you're using Apache SuPHP use whatever the UID / GID is proper.

On CentOS you will need to set proper UID and GID (UserID / GroupID), to find out which ones to to use check in /etc/passwd:
 

[root@centos ~]# grep -i apache /etc/passwd
apache:x:48:48:Apache:/var/www:/sbin/nologin


f) Move old w3tc cache from w3tc-bak to w3tc

 

debian-server:/var/www/blog/wp-content# mv w3tc-bak/* w3tc/

 

g) Start again Apache

On Debian:

 

debian-server:~# /etc/init.d/apache2 start

 


On CentOS:
 

[root@centos~]# /etc/init.d/httpd start

h) Keeping w3tc cache site folder synced

As I said earlier the biggest problem with caching (the reason why many hosting providers) and site admins refuse to use it is they might loose some data, to prevent data loss or at least mitigate the data loss to few minutes intervals it is a good idea to synchronize tmpfs kept folders somewhere to disk with rsync.

To achieve that use a cronjob like this:
 

debian-server:~# crontab -u root -e
*/5 * * * * /usr/bin/ionice -c3 -n7 /usr/bin/nice -n 19 /usr/bin/rsync -ah –stats –delete /var/www/blog/wp-content/w3tc/ /backups/tmpfs/cache/ 1>/dev/null


Note that you will need to have the /backups/tmpfs/cache folder existing, create it with:

 

debian-server:~# mkdir -p /backups/tmpfs/cache


You will also need to add a rsync synchronization from backupped folder to tmpfs (in case if the server gets accidently rebooted because it hanged or power outage), place in

/etc/rc.local

 

ionice -c3 -n7 nice -n 19 rsync -ahv –stats –delete /backups/tmpfs/cache/ /var/www/blog/wp-content/w3tc/ 1>/dev/null


(somewhere before exit 0) line
 

0 05 * * * /usr/bin/ionice -c3 -n7 /bin/nice -n 19 /usr/bin/rsync -ah –stats –delete /var/www/blog/wp-content/w3tc/ /backups/tmpfs/cache/ 1>/dev/null

 

 

2. Preparing tmpfs partitions for MySQL server temp File Cache directory


Its common that MySQL servers had to serve a lot of long and heavy SQL JOIN Queries mostly by related posts WP plugins such as (Zemanta Related Posts) and Contextual Related posts though MySQLs are well optimized  to work as much as efficient using mysql tuner (tuning primer) still often SQL servers get a lot of temp tables created to disk (about 25% to 30%) of all SQL queries use somehow HDD to serve queries and as this is very slow and there is file lock created the overall MySQL performance becomes sluggish at times to fix (resolve) that without playing with SQL code to optimize the slow queries the best way I found is by using TMPFS as MySQL temp folder.

To do so I create a TMPFS usually the size of 256 MB because this is usually enough for us, but other hosting companies might want to add bigger virtual temp disk:

a) Add tmpfs new dir to /etc/fstab

In /etc/fstab add below record with vim editor:
 

debian-server:~# vim /etc/fstab

 

tmpfs /var/mysqltmp tmpfs rw,gid=111,uid=108,size=256M,nr_inodes=10k,mode=0700 0 0

 

Note that the uid / and gid 105 and 114 are taken again from /etc/passwd

On Debian

debian-server:~# grep -i mysql /etc/passwd
mysql:x:108:111:MySQL Server,,,:/var/lib/mysql:/bin/false


On CentOS
 

[root@centos ~]# grep -i mysql /etc/passwd
mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash


b) Create folder /var/mysqltmp or whenever you want to place the tmpfs memory kept SQL folder

 

debian-server:~# mkdir /var/mysqltmp
debian-server:~# chown mysql:mysql /var/mysqltmp

 

debian-server:~# mount|grep -i tmpfs
tmpfs on /lib/init/rw type tmpfs (rw,nosuid,mode=0755)
udev on /dev type tmpfs (rw,mode=0755)
tmpfs on /dev/shm type tmpfs (rw,nosuid,nodev)
tmpfs on /var/www/blog/wp-content/w3tc type tmpfs (rw,noexec,nosuid,size=2g,uid=33,gid=33,mode=1755)
tmpfs on /var/mysqltmp type tmpfs (rw,gid=108,uid=111,size=256M,nr_inodes=10k,mode=0700)


c) Add new path to tmpfs created folder in my.cnf 

Then  edit /etc/mysql/my.cnf

 

debian-server:~# vim /etc/mysql/my.cnf

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /var/mysqltmp

 

On CentOS edit and change tmpdir in same way within /etc/my.cnf


d) Finally Restart Apache and MySQL to make mysql start using new set tmpfs memory kept folder

On Debian:
 

debian-server:~# /etc/init.d/apache2 stop; /etc/init.d/mysql restart; /etc/init.d/apache2 start

On CentOS:
 

[root@centos ~]# /etc/init.d/httpd stop; /etc/init.d/mysqld restart; /etc/initd/httpd start


Now monitor your server and check your pagespeed increase for me such an optimization usually improves site performance so site becomes +50% faster, to see the difference you can test your website before applying tmpfs caching for site and after that by using Google PageInsight (PageSpeed) Online Test. Though this example is for MySQL and WordPress you can easily adopt the same for Joomla if you have Joomla Caching enabled to some folder, same goes for any other CMS such as Drupal that can take use of Disk Caching. Actually its a small secret of many Hosting providers that allow clients to create sites via CPanel and Kloxo this tmpfs optimizations are already used for sites and by this the provider is able to offer better website service on lower prices. VPS hosting providers also use heavy caching. A lot of people are using TMPFS also to accelerate Sites that have enabled Google Pagespeed as Cacher and accelerator, as PageSpeed module puts a heavy HDD I/O load that can easily stone the server. Many admins also choose to use TMPFS for  /tmp, /var/run, and /var/lock directories as this leads often to significant overall server services operations improvement.
Once you have tmpfs enabled, It is a good idea to periodically monitor your SWAP used space with (df -h), because if you allocate bigger tmpfs partitions than your physical memory and tmpfs's full size starts to be used your machine will start swapping heavily and this could have a very negative performance affect.
 

debian-server:~# df -h|grep -i tmpfs
tmpfs            3,9G     0   3,9G   0% /lib/init/rw
tmpfs            3,9G     0   3,9G   0% /dev/shm
tmpfs            2,0G  1,4G   712M  66% /var/www/blog/wp-content/w3tc
tmpfs            256M     0   256M   0% /mnt/tmpfs
tmpfs            256M  236K   256M   1% /var/mysqltmp

The applications of tmpfs to accelerate services is up to your imagination, so I will be glad to hear from other admins on any interesting other application or problems faced while using TMPFS.

 Enjoy! 🙂

Create SSH Tunnel to MySQL server to access remote filtered MySQL port 3306 host through localhost port 3308

Friday, February 27th, 2015

create_ssh_tunnel_to-mysql_server-to-access-remote-filtered-mysql-on-port-3306-secure_ssh_traffic
On our Debian / CentOS / Ubuntu Linux and Windows servers we're running multiple MySQL servers and our customers sometimes need to access this servers.
This is usually problem because MySQL Db  servers are running in a DMZ Zone with a strong firewall and besides that for security reasons SQLs are configured to only listen for connections coming from localhost, I mean in config files across our Debian Linux servers and CentOS / RHEL Linux machines the /etc/mysql/my.cnf and /etc/my.cnf the setting for bind-address is 127.0.0.1:
 

[root@centos ~]# grep -i bind-address /etc/my.cnf 
bind-address            = 127.0.0.1
##bind-address  = 0.0.0.0


For source code developers which are accessing development SQL servers only through a VPN secured DMZ Network there are few MySQL servers witha allowed access remotely from all hosts, e.g. on those I have configured:
 

[root@ubuntu-dev ~]# grep -i bind-address /etc/my.cnf 

bind-address  = 0.0.0.0


However though clients insisted to have remote access to their MySQL Databases but since this is pretty unsecure, we decided not to configure MySQLs to listen to all available IP addresses / network interfaces. 
MySQl acess is allowed only through PhpMyAdmin accessible via Cleint's Web interface which on some servers is CPanel  and on other Kloxo (This is open source CPanel like very nice webhosting platform).

For some stubborn clients which wanted to have a mysql CLI and MySQL Desktop clients access to be able to easily analyze their databases with Desktop clients such as MySQL WorkBench there is a "hackers" like work around to create and use a MySQL Tunnel to SQL server from their local Windows PCs using standard OpenSSH Linux Client from Cygwin,  MobaXterm which already comes with the SSH client pre-installed and has easy GUI interface to create SSH tunnels or eventually use Putty's Plink (Command Line Interface) to create the tunnel

Anyways the preferred and recommended (easiest) way to achieve a tunnel between MySQL and local PC (nomatter whether Windows or Linux client system) is to use standard ssh client and below command:
 

ssh -o ServerAliveInterval=10 -M -T -M -N -L 3308:localhost:3306 your-server.your-domain.com


By default SSH tunnel will keep opened for 3 minutes and if not used it will automatically close to get around this issue, you might want to raise it to (lets say 15 minutes). To do so in home directory user has to add in:
 

~/.ssh/config

ServerAliveInterval 15
ServerAliveCountMax 4


Note that sometimes it is possible ven though ssh tunnel timeout value is raised to not take affect if there is some NAT (Network Adress Translation) with low timeout setting on a firewall level. If you face constant SSH Tunnel timeouts you can use below bash few lines code to auto-respawn SSH tunnel connection (for Windows users use MobaXterm or install in advance bash shell cygwin package):
 

while true
do
 
ssh -o ServerAliveInterval=10 -M -T -M -N -L 3308:localhost:3306 your-server.your-domain.com
  sleep 15
done


Below is MySQLBench screenshot connected through server where this blog is located after establishing ssh tunnel to remote mysql server on port 3308 on localhost

mysql-workbench-database-analysis-and-management-gui-tool-convenient-for-data-migratin-and-queries-screenshot-

There is also another alternative way to access remote firewall filtered mysql servers without running complex commands to Run a tunnel which we recommend for clients (sql developers / sql designers) by using HeidiSQL (which is a useful tool for webdevelopers who has to deal with MySQL and MSSQL hosted Dbs).

heidisql-show-host_processlist-screenshot

To connect to remote MySQL server through a Tunnel using Heidi:

mysql_connection_configuration-heidi-mysql-gui-connect-tool

 

In the ‘Settings’ tab

1. In the dropdown list of ‘Network type’, please select SSH tunnel

2. Hostname/IP: localhost (even you are connecting remotely)

3. Username & Password: your mysql user and password

Next, in the tab SSH Tunnel:

1. specify plink.exe or you need to download it and specify where it’s located

2. Host + port: the remote IP of your SSH server(should be MySQL server as well), port 22 if you don’t change anything

3. Username & password: SSH username (not MySQL user)

 

heidi-connection_ssh_tunnel_configuration-heidi-sql-tool-screenshot
 

WordPress Plugins to monitor and debug WP enabled plugins – Find Errors / Warnings and Remove WP problematic plugins slowing down your Website (blog) database

Thursday, February 19th, 2015

plugins-to-monitor-debug-wordpress-enabled-plugins-how-to-track-find-errors-and-warnings-and-remove-problematic-wp-extensions-that-slow-down-your-website

Recent days, I'm spending a lot of time again trying to optimize my wordpress blog. Optimizing WP for better efficiency is becoming harder and harder task day by day as the website file content data is growing along with SQL databases. Moreover situation gets even worse because the number of plugins enabled on my blog is incrementally growing with time because, there is more and more goodies I'd like to add.
Optimizing WordPress to run for Speed on a server is a whole a lot of art and its a small universe in itself, because as of time of writting this post the count (number) of WordPress available PLUGINS is 36,197 ! 

1. Manually Tracking WordPress  Plugins causing Slow SQL Queries (MySQL bottleneck) issues directly using console / SSH

Because of its open source development and its nice modular design wordpress has turned into a standard for building small, middle sized and large websites (some WordPress based blogs and sites have from 50 000 to 100 000 unique pages!). My blog is still a small WordPress site with only 1676 posts, so I still haven't reached the high volume traffic optimization requirements but still even though I have a relatively good server hardware  8GB RAM / (2×2.70 Ghz Intel CPU) / 500 GB (7400 RPM HDD) at times I see Apache Webservers is unable to properly serve coming requests because of MySQL database (LEFT JOIN) requests being slow to serve (taking up to few seconds to complete) and creating a MySQL table lock, putting all the rest SQL queries to stay in a long unserved queues line, I've realized about this performance issue by using a a mysql cli (command) client and few commands and console command (tool) called mytop (also known as mtop). MyTop refreshes every 3 seconds, so the slow query will immediately stay on screen to view moer info about it press "f" and type the  in query ID.

mysql-top-running-on-gnu-linux-server-tracking-sql-queries-in-console-screenshot.png

mysql-top-running-on-gnu-linux-server-tracking-sql-queries-in-console-screenshot2

Finally it is very useful to run  for a while MySQL server logging to /var/log/mysql/slow-query.log:
Slow query is enabled (on my Debian 7 Wheezy host) by adding to /etc/mysql/my.cnf
after conf section

 

vim /etc/mysql/my.cnf
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration

 

Paste:

 

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
log-queries-not-using-indexes

 

And then to make new mysql configuration load restarted mysql server:

 

debian-server:~# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld ..
Checking for tables which need an upgrade, are corrupt or were
not closed cleanly..

 

Leaving mysql-slow.log to be enabled for 30 minutes to an 1 hrs is a good time to track most problematic slow queries and based on this queries, I took parts of  SQL UPDATE / SELECT / INSERT etc. Db queries which was problematic and grepped throughout /var/www/blog/wp-content/plugin files in order to determine which WordPress Plugin is triggering the slow query, causing blog to hang when too many clients try to see it in browser.

My main problematic SQL query having long execution time  (about 2 to 3 seconds!!!) most commonly occuring in slow-query.log was:

 

SELECT DISTINCT post_title, ID, post_type, post_name FROM wp_posts wposts LEFT JOIN wp_postmeta wpostmeta ON wposts.ID = wpostmeta.post_id LEFT JOIN wp_term_relationships ON (wposts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE (post_type='page' OR (wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN(11))) AND post_status = 'publish' AND LENGTH(post_title)>=5 ORDER BY LENGTH(post_title) ASC LIMIT 500

Because above query uses SQL Column names and Tables which are not hard coded in PHP code, to find out which plugins is most probably to launch this complex LEFT JOIN query, I used a quick bash one-liner:

 

# cd /var/www/blog/wp-content/plugins

 

# for i in $(grep -rli 'SELECT DISTINCT' *); do grep -rli 'LEFT JOIN' $i; done 
./seo-automatic-links/seo-links.php
./wp-postviews/wp-postviews.php
./yet-another-related-posts-plugin/classes/YARPP_Cache_Tables.php

 

I wanted to put less load on CPU during grep so looked for string only in .PHP extensioned files with:

 

 # for i in $(find . -iname '*.php' -exec grep -rli 'SELECT DISTINCT' '{}' \;); do grep -rli 'LEFT JOIN' $i; done
./seo-automatic-links/seo-links.php
./wp-postviews/wp-postviews.php
./yet-another-related-posts-plugin/classes/YARPP_Cache_Tables.php


As you can see the complex query is being called from PHP file belonging to one of 3 plugins

  • SEO Automatic Links – this is SEO Smart Links WP plugin (Does internal bliog interlinking in order to boast SEA)
  • WP PostViews – WordPress Post Views plugin (Which allows me to show how many times an article was read in WP Widget menu)
  • Yet Another Related Posts – Which is WP plugin I installed / enabled to show Related posts down on each blog post


2. Basic way to optimize MySQL slow queries (EXPLAIN / SHOW CREATE TABLE)

Now as I have a basic clue on plugins locking my Database, I disabled them one by one while keeping enabled mysql slow query log and viewing queries in mytop and I figure out that actually all of the plugins were causing a short time overheat (lock) on server Database because of LEFT JOINs. Though I really like what this plugins are doing, as they boast SEO and attract prefer to disable them for now and have my blog all the time responsible light fast instead of having a little bit better Search Engine Optimization (Ranking) and loosing many of my visitors because they're annoyed to wait until my articles open

Before disabling I tried to optimize the queries using MySQL EXPLAIN command + SHOW CREATE TABLE (2 commands often used to debug slow SQL queries and find out whether a Column needs to have added INDEX-ing to boast MySQL query).

Just in case if you decide to give them a try here is example on how they're used to debug problematic SQL query:
 

  1. mysql> explain SELECT DISTINCT post_title, ID, post_type, post_name
  2.     -> FROM wp_posts wposts LEFT JOIN wp_postmeta wpostmeta
  3.     -> ON wposts.ID = wpostmeta.post_id LEFT JOIN wp_term_relationships
  4.     -> ON (wposts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_taxonomy
  5.     -> ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
  6.     -> WHERE (post_type='page'
  7.     -> OR (wp_term_taxonomy.taxonomy = 'category'
  8.     -> AND wp_term_taxonomy.term_id IN(11,15,17)))
  9.     -> AND post_status = 'publish'
  10.     -> AND LENGTH(post_title)>=5
  11.     -> ORDER BY LENGTH(post_title) ASC
  12.     -> LIMIT 500;
  13. +—-+————-+———————–+——–+——————+———+———+———————————————+——+———————————————-+
  14. | id | select_type | table                 | type   | possible_keys    | key     | key_len | ref                                         | rows | Extra                                        |
  15. +—-+————-+———————–+——–+——————+———+———+———————————————+——+———————————————-+
  16. |  1 | SIMPLE      | wposts                | ALL    | type_status_date | NULL    | NULL    | NULL                                        | 1715 | Using where; Using temporary; Using filesort |
  17. |  1 | SIMPLE      | wpostmeta             | ref    | post_id          | post_id | 8       | blog.wposts.ID                              |   11 | Using index; Distinct                        |
  18. |  1 | SIMPLE      | wp_term_relationships | ref    | PRIMARY          | PRIMARY | 8       | blog.wposts.ID                              |   19 | Using index; Distinct                        |
  19. |  1 | SIMPLE      | wp_term_taxonomy      | eq_ref | PRIMARY          | PRIMARY | 8       | blog.wp_term_relationships.term_taxonomy_id |    1 | Using where; Distinct                        |
  20. +—-+————-+———————–+——–+——————+———+———+———————————————+——+———————————————-+
  21. 4 rows in set (0.02 sec)
  22.  
  23. mysql>
  24.  

     

     

  1. mysql> show create table wp_posts;
  2. +———-+————————–+
  3. | Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                 |
  4. +———-+————————–+
  5. | wp_posts | CREATE TABLE `wp_posts` (
  6.   `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  7.   `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
  8.   `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  9.   `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  10.   `post_content` longtext NOT NULL,
  11.   `post_title` text NOT NULL,
  12.   `post_excerpt` text NOT NULL,
  13.   `post_status` varchar(20) NOT NULL DEFAULT 'publish',
  14.   `comment_status` varchar(20) NOT NULL DEFAULT 'open',
  15.   `ping_status` varchar(20) NOT NULL DEFAULT 'open',
  16.   `post_password` varchar(20) NOT NULL DEFAULT '',
  17.   `post_name` varchar(200) NOT NULL DEFAULT '',
  18.   `to_ping` text NOT NULL,
  19.   `pinged` text NOT NULL,
  20.   `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  21.   `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  22.   `post_content_filtered` longtext NOT NULL,
  23.   `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  24.   `guid` varchar(255) NOT NULL DEFAULT '',
  25.   `menu_order` int(11) NOT NULL DEFAULT '0',
  26.   `post_type` varchar(20) NOT NULL DEFAULT 'post',
  27.   `post_mime_type` varchar(100) NOT NULL DEFAULT '',
  28.   `comment_count` bigint(20) NOT NULL DEFAULT '0',
  29.   PRIMARY KEY (`ID`),
  30.   KEY `post_name` (`post_name`),
  31.   KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  32.   KEY `post_parent` (`post_parent`),
  33.   KEY `post_author` (`post_author`),
  34.   FULLTEXT KEY `post_related` (`post_title`,`post_content`)
  35. ) ENGINE=MyISAM AUTO_INCREMENT=12033 DEFAULT CHARSET=utf8 |
  36. +———-+———————-+
  37. 1 row in set (0.00 sec)
  38.  
  39. mysql>
  40.  


By the way above output is a paste from the the new PasteBin Open Source (Stikked powered) service I started on www.pc-freak.net – paste.www.pc-freak.net (p.www.pc-freak.net) 🙂

Before I took final decision to disable slow WP plugins, I've experimented a bit trying to add INDEX to Table Column (wposts) in hope that this would speed up SQL queries with:

 

mysql> ALTER TABLE TABLE_NAME ADD INDEX (wposts);

 

But this didn't improve query speed even on the contrary it make execution time worse.

3. Tracking WordPress Plugin PHP Code Execution time and Plugins causing Slow SQL Queries (MySQL bottleneck) issues through WP itself

Well fine, I'm running my own hosted Blog and WordPress sites, but for people who have wordpress sites on shared hosting, there is usually no SSH (Terminal) Access to server, those people will be happy to hear there are 2 Free easy installable WordPress plugins which can be used to Debug Slow WordPress Plugins SQL Queries as well as plugin to Track which plugin takes most time to execute, this are:
 

 

a) P3 Plugin Performance Profiler  

runs a scan over your site to determine what resources your plugins are using, and when, during a standard page request. P3 PPP Can even create reports in a beatiful Excel like Pie chart sheet.

p3-plugin-performance-profiler-godaddy-screenshot-debian-gnu-linux-wordpress-website

Another useful thing to see with P3 PPP is Detailed Timeline it shows when the plugins are being loaded during new page request so you can see if there is a certain sequence in time when a plugin slows down the website.

detailed_timeline-wordpress-p3-performance-plugin-on-website-screenshot

The pictures says it all as P3 PPP is Godaddy's work, congrats to GoDaddy, they've done great job.

 

b) WordPress memory Viewer WP plugins

Is useful to check how much memory each of WordPress plugin is taking on user (visitor) request.
Memory Viewer is allows you to view WordPress’ memory utilization at several hooks during WordPress’ execution. It also shows a summary of MySQL Queries that have ran as well as CPU time.
To use it download it to plugins/ folder as usual enable it from:

Installed Plugins -> (Inactive) -> Memory Viewer (Enable)

To see statistics from Memory Viewer open any post from your blog website and scroll down to the bottom you will notice the statistics, showing up there, like on below screenshot.

wordpress-memory-viewer-plugin-debian-gnu-linux-hosted-website-show-which-plugin-component-eats-most-memory-in-wordprses-blog
 

Though WP Memory Viewer is said to work only up to WP version 3.2.1, I've tested it and it works fine on my latest stable WordPress 4.1 based blog.

c) WordPress Query Monitor

wordpress-query-monitor-plugin-to-monitor-track-and-optimize-problems-with-sql-caused-by-wp-plugins.png
 

Query Monitor is a debugging plugin for anyone developing with WordPress but also very helpful for anyone who want to track issues with plugins who use the database unefficient.
It has some advanced features not available in other debugging plugins, including automatic AJAX debugging and the ability to narrow down things by plugin or theme.
You can view plenty of precious statistics on how enabled plugins query the database server, here is a short overview on its Database Queries capabilities:

  • Shows all database queries performed on the current page
  • Shows affected rows and time for all queries
  • Show notifications for slow queries and queries with errors
  • Filter queries by query type (SELECT, UPDATE, DELETE, etc)
  • Filter queries by component (WordPress core, Plugin X, Plugin Y, theme)
  • Filter queries by calling function
  • View aggregate query information grouped by component, calling function, and type
  • Super advanced: Supports multiple instances of wpdb on one page
  • Once enabled from Plugins you will see it appear as a new menu on bottom Admin raw.

An important note to make here is latest Query Monitor extension fails when loaded on current latest Wordpress 4.1, to use it you will have to download and useolder Query Monitor plugin version 2.6.8 you can download it from here

d) Debug Bar

If you want you want a Memory Viewer like plugin for more complex used components memory debugging, reporting if (WP_DEBUG is set in wp-config.php) also check out Debug Bar .
For me Debug Bar was very useful because it show me depreciated functions some plugins used, so I substituted the obsoleted function with new one.

 

debug-bar-debug-wordpress-plugins-memory-use-screenshot-website


4. Server Hardware hungry (slow) WordPress plugins that you better not use

While spending time to Google for some fixes to WP slow query plugins – I've stumbled upon this post giving a good list with WordPress Plugins better off not to use because they will slow down your site
This is a publicly well known list of WP plugins every WordPress based site adminstrator should avoid, but until today I didn't know so my assumption is you don't know either ..

Below plugins are extremely database intensive mentioned in article that we should better (in all cases!) avoid:

  • Dynamic Related Posts
  • SEO Auto Links & Related Posts
  • Yet Another Related Posts Plugin
  • Similar Posts
  • Contextual Related Posts
  • Broken Link Checker — Overwhelms even our robust caching layer with an inordinate amount of HTTP requests.
  • MyReviewPlugin — Slams the database with a fairly significant amount of writes.
  • LinkMan — Much like the MyReviewPlugin above, LinkMan utilizes an unscalable amount of database writes.
  • Fuzzy SEO Booster — Causes MySQL issues as a site becomes more popular.
  • WP PostViews — Inefficiently writes to the database on every page load. To track traffic in a more scalable manner, both the stats module in Automattic’s Jetpack plugin and Google Analytics work wonderfully.
  • Tweet Blender — Does not play nicely with our caching layer and can cause increased server load.


A good Complete list of known WordPress slow plugins that will hammer down your wordpress performance is here

There are few alternatives to this plugins and when I have some free time I will download and test their alternatives but for now I plan the plugins to stay disabled.
 

For the absolute WP Performance Optimization Freaks, its good to check out the native way to Debug a wordpress installation through using few embedded
variables

 

define('WP_DEBUG', true);
define('WP_DEBUG', false);
define('WP_DEBUG_LOG', true);
define('WP_DEBUG_DISPLAY', false);
define('SAVEQUERIES', true);

 

An article describing how you can use native WP debug variables is here


Happy Optimizing  ! 🙂

How to deb upgrade PHP 5.3.3-7 / MySQL Server 5.1 to PHP 5.4.37 MySQL 5.5 Server on Debian 6.0 / 7.0 Squeeze / Wheezy GNU / Linux

Thursday, February 12th, 2015

how-to-deb-upgrade-mysql-server-5.1-to-mysql-5.5-php-5.3-to-php-5.4-5.5-upgrade-howto-on-old-stable-debian-squeeze-wheezy

I've been still running Debian Squeeze 6.0 GNU / Linux on few of the Linux / Apache / MySQL servers I'm administrating and those servers are running few Wordperss / Joomla websites which lately face severe MySQL performance issues. I tried to optimize using various mysql performance optimization scripts such as mysql-tuner.pl, Tuning-primer.sh and Percona Toolkit – a collection of advanced command-line tools for system administrators and tech / support staff to perform a variety of MySQL and system tasks that are too difficult or complex to perform manually. Though with above tools and some my.cnf tunizations I managed to achieve positive performance improvement results with above optimizations, still I didn't like how MyQSL served queries and since the SQL server is already about 5 years old (running version 5.1) and the PHP on sever is still at 5.3 branch, I was advised by my dear colleague Anatoliy to try version update as a mean to improve SQLserver performance. I took seriously the suggestion to try upgrade as a mean to resolve performance issues in this article I will explain in short what I had to do to make MySQL upgrade a success

Of course to try keep deb installed software versions as fresh as possible possible deb packagse, I'm already using Debian Back Ports (for those who hear it a first time Debian Backports is a special repository for Stable versioned Debian Desktop and Servers  – supporting stable releases of Debian Linux) which allows you to keep install packages versions less outdated (than default installable software which usually are way behind latest stable package versions with 2-5 years).

If you happen to administer Stable Debian servers and you never used BackPorts I warmly recommend it as it often includes security patches of packages part of Debian stable releases that reached End Of Support (EOS) and already too old even for security updates to be issued by respective Debian Long Term Suport (LTS) repositories.

If you're like me and still in situation to manage remotely Debian 6.0 Squeeze and its the first time you hear about BackPorts and Debian LTShttps://wiki.debian.org/LTS/ to start using those two add to your /etc/apt/sources.list below 3 lines

Open with vim editor and press shift+G to go to last line of file and then press I to enter INSERT mode, once you're done to save, press (ESC) then press : and type x! in short key combination for exit and save setting in vim is 
 

Esc + :x! 

 

debian-server:~# vim /etc/apt/sources.list
deb http://http.debian.net/debian squeeze-lts main contrib non-free
deb-src http://http.debian.net/debian squeeze-lts main contrib non-free
deb http://http.debian.net/debian-backports squeeze-backports main

If you haven't been added a security updates line in /etc/apt/sources.list make sure you add also:

 

deb http://security.debian.org/ squeeze/updates main contrib non-free
deb-src http://security.debian.org/ squeeze/updates main contrib non-free


Then to apply latest security updates and packages from LTS / Backports repository run the usual:

 

debian-server:~# apt-get update && apt-get –yes upgrade
….

If you need to search a package or install something from just added backports repository use:

 

debian-server:~# apt-cache -t squeeze-backports search "mysql-server"
auth2db – Powerful and eye-candy IDS logger, log viewer and alert generator
torrentflux – web based, feature-rich BitTorrent download manager
cacti – Frontend to rrdtool for monitoring systems and services
mysql-server-5.1 – MySQL database server binaries and system database setup
mysql-server-core-5.1 – MySQL database server binaries
mysql-server – MySQL database server (metapackage depending on the latest version)

 

To install specific packages only with all their dependencies from Backports while keeping rest of packages from Debian Stable:

 

debian-server:~# apt-get install -t squeeze-backports "package_name"

In same way you can also search or install specific packages from LTS repo:

 

debian-server:~# apt-get search -t squeeze-lts "package_name"

debian-server:~# apt-get install -t squeeze-lts "package_name"

Latest mysql available from Debian BackPorts and LTS is still quite old 5.1.73-1+deb6u1 therefore I made an extensive research online on how can I easily update MySQL 5.1 to MySQL 5.5 / 5.6 on Debian Stable Linux.
 

Luckily there were already DotDeb deb repositories for Debian LAMP (Linux / Apache  / MySQL / PHP / Nginx ) running servers prepared in order to keep the essential Webserver services up2date even long after distro official support is over. I learned about existence of this repo thanks to a Ryan Tate's post who updates his LAMP stack on TurnKey Linux which by the way is based on slightly modified official stable Debian Linux releases packages

To start using DotDeb repos add in /etc/apt/sources.list (depending whereh you're on Squeeze or Wheeze Debian):

 

deb http://packages.dotdeb.org squeeze all
deb-src http://packages.dotdeb.org squeeze all

or for Debian Wheezy add repos:

 

deb http://packages.dotdeb.org wheezy all
deb-src http://packages.dotdeb.org wheezy all

 

I was updating my DebianLatest MySQL / PHP / Apache release to Latest ones on (6.0.4) Squeeze so added above squeeze repos:

Before refreshing list of package repositories, to authenticate repos issue:

 

debian-server:~# wget -q http://www.dotdeb.org/dotdeb.gpg
debian-server:~# apt-key add dotdeb.gpg

Once again to update my packages from newly added DodDeb repository

 

debian-server:~# apt-get update

Before running the SQL upgrade to insure myself, I dumped all databases with:

 

debian-server:~# mysqldump -u root -p -A > /root/dump.sql

Finally I was brave enough to run apt-get dist-upgrade to update with latest LAMP packages

 

debian-server:~# apt-get dist-upgrade
Reading package lists… Done
Building dependency tree
Reading state information… Done
Calculating upgrade… Done
The following packages will be REMOVED:
  mysql-client-5.1 mysql-server mysql-server-5.1
The following NEW packages will be installed:
  libaio1 libmysqlclient18 mysql-client-5.5 mysql-client-core-5.5 python-chardet python-debian
The following packages will be upgraded:
  curl krb5-multidev libapache2-mod-php5 libc-bin libc-dev-bin libc6 libc6-dev libc6-i386 libcurl3 libcurl3-gnutls libcurl4-openssl-dev libevent-1.4-2
  libgssapi-krb5-2 libgssrpc4 libjasper1 libk5crypto3 libkadm5clnt-mit7 libkadm5srv-mit7 libkdb5-4 libkrb5-3 libkrb5-dev libkrb53 libkrb5support0 libmysqlclient-dev
  libxml2 libxml2-dev locales mysql-client mysql-common ntp ntpdate php-pear php5 php5-cgi php5-cli php5-common php5-curl php5-dev php5-gd php5-imagick php5-mcrypt
  php5-mysql php5-odbc php5-recode php5-sybase php5-xmlrpc php5-xsl python-reportbug reportbug unzip

50 upgraded, 6 newly installed, 3 to remove and 0 not upgraded.
Need to get 51.7 MB of archives.
After this operation, 1,926 kB of additional disk space will be used.
Do you want to continue [Y/n]? Y

As you see from above output above command updates Apache webservers / PHP and PHP related modules, however it doesn't update MySQL installed version, to update also MySQL server 5.1 to MySQL server 5.5

 

debian-server:~#  apt-get install –yes mysql-server mysql-server-5.5

You will be prompted with the usual Debian ncurses text blue interface to set a root password to mysql server, just set it the same as it used to be on old upgraded MySQL 5.1 server.

Well now see whether mysql has properly restarted with ps auxwwf

 

debian-server:~#  ps axuwwf|grep -i sql
root     22971  0.0  0.0 112360   884 pts/11   S+   15:50   0:00  |                   \_ grep -i sql
root     19436  0.0  0.0 115464  1556 pts/1    S    12:53   0:00 /bin/sh /usr/bin/mysqld_safe
mysql    19837  4.0  2.3 728192 194552 pts/1   Sl   12:53   7:12  \_ /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –plugin-dir=/usr/lib/mysql/plugin –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –socket=/var/run/mysqld/mysqld.sock –port=3306
root     19838  0.0  0.0 110112   700 pts/1    S    12:53   0:00  \_ logger -t mysqld -p daemon.error

In my case it was running, however if it fails to run try to debug what is going wrong on initialization by manually executing init script /etc/init.d/mysql stop; /etc/init.d/mysql start and look for errors. You can also manually try to run mysqld_safe from console if it is not running run:

 

debian-server:~# /usr/bin/mysqld_safe &

This should give you a good hint on why it is failing to run
 

One more thing left is to check whether php modules load correctly to do so issue:

 

debian-server:~# php -v
Failed loading /usr/lib/php5/20090626/xcache.so:  /usr/lib/php5/20090626/xcache.so: cannot open shared object file: No such file or directory

Failed loading /usr/lib/php5/20090626/xdebug.so:  /usr/lib/php5/20090626/xdebug.so: cannot open shared object file: No such file or directory


You will likely get an exception (error) like above.
To solve the error, reinstall xcache and xcache-debug debs

 

debian-server:~# apt-get purge php5-xcache php5-xdebug

Now PHP + MySQL + Apache environment should be running much smootly.

debian-squeeze-wheeze-update-install-mysql-sevver5.55-620x344-howto

Upgrading the MySQL server / PHP library to MySQL server 5.6 / PHP 5.5 on Wheeze Linux is done in very much analogous ways all you have to do is change the repositories with above wheeze 7.0 ones and to follow the process as described in this article. I haven't tested update on Wheezy yet, so if you happen to try my article with wheezy reports and got a positive upgrade result please drop a comment.

How to get a list and Backup (Save Enabled Plugins) / Restore Enabled (Active) plugins in WordPress site with SQL query

Wednesday, January 14th, 2015

get-list-and-backup-restore-enabled-active-plugins-only-in-wordpress-with-sql-mysql-query

Getting a snapshot of all active plugins and keeping it for future in case if you install some broken plugin and you have to renable all enabled plugins from scratch is precious thing in WordPress.

… It is really annoying when you decide to try to enable few new plugins and out of a sudden your WordPress site / blog starts hanging (when accessed in browser)…

To fix it you have to Disable All Plugins and Re-enable all that used to work. However if you don't keep a copy of the plugins which were previously working and you're like me and have 109 plugins installed of which only 50 are in (Active) state / used. It could take you a day or two until you come up with a similar list to the ones you previously used … Thanksfully there is some prevention you can take by dumping a list of all plugins that are currently active and in later time only enable those in the list.

 

# mysql -u root -p
Enter password:

mysql> USE blog_db;

Here is the output I get in the moment:
 

mysql> DESCRIBE wp_options;
+————–+———————+——+—–+———+—————-+
| Field        | Type                | Null | Key | Default | Extra          |
+————–+———————+——+—–+———+—————-+
| option_id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| option_name  | varchar(64)         | NO   | UNI |         |                |
| option_value | longtext            | NO   |     | NULL    |                |
| autoload     | varchar(20)         | NO   |     | yes     |                |
+————–+———————+——+—–+———+—————-+

 

mysql> SELECT * FROM wp_options WHERE option_name = 'active_plugins';

|        38 | active_plugins | a:50:{i:0;s:45:"add-to-any-subscribe/add-to-any-subscribe.php";i:1;s:19:"akismet/akismet.php";i:2;s:43:"all-in-one-seo-pack/all_in_one_seo_pack.php";i:3;s:66:"ambrosite-nextprevious-post-link-plus/ambrosite-post-link-plus.php";i:4;s:49:"automatic-tag-selector/automatic-tag-selector.php";i:5;s:27:"autoptimize/autoptimize.php";i:6;s:35:"bm-custom-login/bm-custom-login.php";i:7;s:45:"ckeditor-for-wordpress/ckeditor_wordpress.php";i:8;s:47:"comment-info-detector/comment-info-detector.php";i:9;s:27:"comments-statistics/dcs.php";i:10;s:31:"cyr2lat-slugs/cyr2lat-slugs.php";i:11;s:49:"delete-duplicate-posts/delete-duplicate-posts.php";i:12;s:45:"ewww-image-optimizer/ewww-image-optimizer.php";i:13;s:34:"feedburner-plugin/fdfeedburner.php";i:14;s:39:"feedburner-widget/widget-feedburner.php";i:15;s:63:"feedburner_feedsmith_plugin_2.3/FeedBurner_FeedSmith_Plugin.php";i:16;s:21:"feedlist/feedlist.php";i:17;s:39:"force-publish-schedule/forcepublish.php";i:18;s:50:"google-analytics-for-wordpress/googleanalytics.php";i:19;s:81:"google-sitemap-generator-ultimate-tag-warrior-tags-addon/UTWgoogleSitemaps2_1.php";i:20;s:36:"google-sitemap-generator/sitemap.php";i:21;s:24:"headspace2/headspace.php";i:22;s:29:"my-link-order/mylinkorder.php";i:23;s:27:"php-code-widget/execphp.php";i:24;s:43:"post-plugin-library/post-plugin-library.php";i:25;s:35:"post-to-twitter/post-to-twitter.php";i:26;s:28:"profile-pics/profile-pic.php";i:27;s:27:"redirection/redirection.php";i:28;s:42:"scripts-to-footerphp/scripts-to-footer.php";i:29;s:29:"sem-dofollow/sem-dofollow.php";i:30;s:33:"seo-automatic-links/seo-links.php";i:31;s:23:"seo-slugs/seo-slugs.php";i:32;s:41:"seo-super-comments/seo-super-comments.php";i:33;s:31:"similar-posts/similar-posts.php";i:34;s:21:"sociable/sociable.php";i:35;s:44:"strictly-autotags/strictlyautotags.class.php";i:36;s:16:"text-control.php";i:37;s:19:"tidy-up/tidy_up.php";i:38;s:37:"tinymce-advanced/tinymce-advanced.php";i:39;s:33:"tweet-old-post/tweet-old-post.php";i:40;s:33:"w3-total-cache/w3-total-cache.php";i:41;s:44:"widget-settings-importexport/widget-data.php";i:42;s:54:"wordpress-23-related-posts-plugin/wp_related_posts.php";i:43;s:23:"wp-minify/wp-minify.php";i:44;s:27:"wp-optimize/wp-optimize.php";i:45;s:33:"wp-post-to-pdf/wp-post-to-pdf.php";i:46;s:29:"wp-postviews/wp-postviews.php";i:47;s:55:"wp-simple-paypal-donation/wp-simple-paypal-donation.php";i:48;s:46:"wp-social-seo-booster/wpsocial-seo-booster.php";i:49;s:31:"wptouch-pro-3/wptouch-pro-3.php";} | yes      |

Copy and paste this CVS format data to a text file or a Word document for later reference ..

To restore back to normal only active WordPress plugins, first launch following SQL query to disable all enabled wordpress plugins:

UPDATE wp_options SET option_value = 'a:0:{}' WHERE option_name = 'active_plugins';

To restore above "backupped" list of active WP plugins you have to copy paste the saved content and paste it into above UPDATE query substituting option_value=' ' with the backupped string.

P.S. – This query should work on WordPress 3.x on older wordpress ver 2.x use instead:

UPDATE wp_options SET option_value = ' ' WHERE option_name = 'active_plugins';

Because pasting the backupped Active plugins list CSV is a messy and unreadable from command line it is recommended for clarity to use PHPMyAdmin frontend (whenever it is available) on server. This little hint is a real time-saver and saves a lot of headaches. Before proceeding to any Db UPDATE SQL queries always backup your Blog database, with time structure of WordPress data changes!, so in future releases this method might not be working, however if it helped you and works on your version please drop a comment with WordPress version on which this helped you.

Enjoy! 🙂