Posts Tagged ‘mysql server’
Thursday, March 28th, 2013
I had to run a shell script to run automatically in detached screen during Linux system boot up via /etc/rc.local. This is needed because the server uses the tiny shell script to fetch data from remote host database and fill information into local MySQL server.
My idea was to su from root to www-data (Apache) user – the script has requirements to run with Apache user, then it has to run detached using GNU screen (multi terminal emulator. The tiny one line script I imagined would do the trick is like so:
# tty=$(tty); su www-data -c 'cd /home/user/www/enetpulse; screen -d /home/user/www/enetpulse/while_true.sh'; chmod 0720 $tty
I run this as root user to test whether it will work or not before I put it in /etc/rc.local but for my surprise got an error:
Must run suid root for multiuser support.
After a quick investigation on what is causing it I came across the solution which is to include screen arguments (-m -S shared). The working variant that gets around the error – i.e. successfully changes user privileges to Debian Apache user (www-data) and then detach with screen is:
# tty=$(tty); chmod a+rw $tty; su www-data -c 'cd /home/user/www/enetpulse; screen -d -m -S shared /home/user/www/enetpulse/while_true.sh'; chmod 0720 $tty;
That's all now script works out as planned on next server reboot
If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!
Tags: apache, chmod, gnu screen, host database, line script, Linux, mysql server, Privileges, root user, shell script, surprise, system boot, terminal emulator, tiny shell
Posted in System Administration | No Comments »
Friday, July 20th, 2012
I'm running a two servers for a couple of home hosted websites. One of the servers is serving as Apache host1 and has configured MySQL running on it and the second is used just for database host2 – (has another MySQL configured on it).
The MySQL servers are not configured to run as a MySQL MASTER and MySQL SLAVE (no mysql replication), however periodically (daily), I have a tiny shell script that is actualizing the data from the active SQL host2 server to host1.
Sometimes due to electricity problems or CPU overheats the active MySQL host at host2 gets stoned and stops working causing the 2 WordPress based websites and One joomla site inaccessible.
Until I manually get to the machine and restart host2 the 3 sites are down from the net and as you can imagine this has a very negative impact on the existing website indexing (PageRank) in Google.
When I'm at home, this is not a problem as I have physical access to the servers and if somethings gets messy I fix it quickly. The problem comes, whether I'm travelling or in another city far from home and there is no-one at home to give the hanged host hard reboot ….
Lately the problems with hang-ups of host2 happaned 3 times or so for 2 weeks, as a result the websites were inaccessible for hours and since there is nobody to reboot the server for hours; the websites keep hanging until the DB host is restarted ;;;;
To work-around this I came with the idea to write a tiny shell script to check if host2 is ping-able in order to assure the Database host is not down and then if script determines host2 (mysql) host is down it changes wp-config.php (set to use host2) to a wp-config.php (which I have beforehand configured to use) host1.
Using the script is a temporary solution, since I have to actually find the real hang-up causing troubles, but at least it saves me long downtimes. Here is a download link to the script I called change_blog_db.sh .
I've configured the script to be run on the Apache node (host1) via a crontab calling the script every 10 minutes, here is the crontab:
*/10 * * * * /usr/sbin/change_blog_db.sh > /dev/null 2>&1
The script is written in a way so if it determins host2 is reachable a copy of wp-config.php and Joomla's configuration.php tuned to use host2 is copied over the file config originals. In order to use the script one has to configured the head variables script section, e.g.:
host_to_ping='192.168.0.2';
blog_dir='/var/www/blog';
blog_dir2='/var/www/blog1';blog_dir3='/var/www/joomla';
notify_mail='hipo@pc-freak.net';
wp_config_orig='wp-config.php';
wp_config_localhost='wp-config-localhost.php';
wp_config_other_host='wp-config-192.168.0.2.php';
joomla_config_orig='configuration.php';
joomla_config_other_host='configuration-192.168.0.2.php';
You will have to manually prepare;;;
wp-config-localhost.php, wp-config-192.168.0.2.php ,configuration-192.168.0.2.php, wp-config-localhost.php to be existing files configured to with proper host1 and host2 IP addresses.
Hope the script will be useful to others, experiencing database downtimes with WordPress or Joomla installs.
Tags: apache, change, city, config, copy, cpu overheats, database host, download, electricity, electricity problems, file, google, hang ups, HEAD, host1, impact, indexing, Master, mysql replication, mysql server, mysql servers, negative impact, nobody, one at home, orig, pagerank, php, ping, replication, second, Shell, shell script, SQL, temporary solution, tiny shell, ups, website indexing, Wordpress
Posted in Joomla, MySQL, Various, Web and CMS, Wordpress | 2 Comments »
Sunday, May 20th, 2012
My home run machine MySQL server was suddenly down as I tried to check my blog and other sites today, the error I saw while trying to open, this blog as well as other hosted sites using the MySQL was:
Error establishing a database connection
The topology, where this error occured is simple, I have two hosts:
1. Apache version 2.0.64 compiled support externally PHP scripts interpretation via libphp – the host runs on (FreeBSD)
2. A Debian GNU / Linux squeeze running MySQL server version 5.1.61
The Apache host is assigned a local IP address 192.168.0.1 and the SQL server is running on a host with IP 192.168.0.2
To diagnose the error I've logged in to 192.168.0.2 and weirdly the mysql-server was appearing to run just fine:
debian:~# ps ax |grep -i mysql
31781 pts/0 S 0:00 /bin/sh /usr/bin/mysqld_safe
31940 pts/0 Sl 12:08 /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –socket=/var/run/mysqld/mysqld.sock –port=3306
31941 pts/0 S 0:00 logger -t mysqld -p daemon.error
32292 pts/0 S+ 0:00 grep -i mysql
Moreover I could connect to the localhost SQL server with mysql -u root -p and it seemed to run fine. The error Error establishing a database connection meant that either something is messed up with the database or 192.168.0.2 Mysql port 3306 is not properly accessible.
My first guess was something is wrong due to some firewall rules, so I tried to connect from 192.168.0.1 to 192.168.0.2 with telnet:
freebsd# telnet 192.168.0.2 3306
Trying 192.168.0.2…
Connected to jericho.
Escape character is '^]'.
Host 'webserver' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
Connection closed by foreign host.
Right after the telnet was initiated as I show in the above output the connection was immediately closed with the error:
Host 'webserver' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'Connection closed by foreign host.
In the error 'webserver' is my Apache machine set hostname. The error clearly states the problems with the 'webserver' apache host unable to connect to the SQL database are due to 'many connection errors' and a fix i suggested with mysqladmin flush-hosts
To temporary solve the error and restore my normal connectivity between the Apache and the SQL servers I logged I had to issue on the SQL host:
mysqladmin -u root -p flush-hostsEnter password:
Thogh this temporar fix restored accessibility to the databases and hence the websites errors were resolved, this doesn't guarantee that in the future I wouldn't end up in the same situation and therefore I looked for a permanent fix to the issues once and for all.
The permanent fix consists in changing the default value set for max_connect_error in /etc/mysql/my.cnf, which by default is not too high. Therefore to raise up the variable value, added in my.cnf in conf section [mysqld]:
debian:~# vim /etc/mysql/my.cnf
...
max_connect_errors=4294967295
and afterwards restarted MYSQL:
debian:~# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..
To make sure the assigned max_connect_errors=4294967295 is never reached due to Apache to SQL connection errors, I've also added as a cronjob.
debian:~# crontab -u root -e
00 03 * * * mysqladmin flush-hosts
In the cron I have omitted the mysqladmin -u root -p (user/pass) input options because for convenience I have already stored the mysql root password in /root/.my.cnf
Here is how /root/.my.cnf looks like:
debian:~# cat /root/.my.cnf
[client]
user=root
password=a_secret_sql_password
Now hopefully, this would permanently solve SQL's 'failure to accept connections' due to too many connection errors for future.
Tags: apache version, Auto, basedir, bin, connection, connectionThe, daemon, database connection, debian gnu, default, Draft, due, error error, firewall rules, freebsd, GNU, guess, host, host name, lib, Linux, local ip address, localhost, machine, mysql server, mysqladmin, mysqld, mysqlMoreover, nbsp, occured, password, port 3306, root, server version, sl 12, Socket, something, SQL, sql server, squeeze, support, topology, value
Posted in MySQL, System Administration, Web and CMS | No Comments »
Saturday, May 12th, 2012
After migrating databases data from FreeBSD MySQL 5.0.83 server to a Debian Squeeze Linux MySQL version 5.1.61, below is a mysql –version issued on both the FreeBSD and the Debian servers
freebsd# mysql --version
mysql Ver 14.12 Distrib 5.0.83, for portbld-freebsd7.2 (i386) using 5.2
debian:~# mysql --version
mysql Ver 14.14 Distrib 5.1.61, for debian-linux-gnu (i486) using readline 6.1
The data SQL dump from the FreeBSD server was dumped with following command arguments:
freebsd# mysqldump --opt --allow-keywords --add-drop-table --all-databases -u root -p > complete_db_dump.sql
Then I used sftp to transfer complete_db_dump.sql dump to the a brand new installed latest Debian Squeeze 6.0.2. The Debian server was installed using a "clean Debian install" without graphical environment with CD downloaded from debian.org's site.
On the Debian machine I imported the dump with command:
debian:~# mysq -u root -p < complete_db_dump.sql
Right After the dump was imported I re-started SQL server which was previously installed with:
debian:~# apt-get install mysql-server
The error I got after restarting the mysql server:
debian:~# #/etc/init.d/mysql restart
was:
ERROR 1577 (HY000) at line 1: Cannot proceed because system tables used by Event Scheduler were found damaged at server start
ERROR 1547 (HY000) at line 1: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted
This error cost me a lot of nerves and searching in google to solve. It took me like half an hour of serious googling ,until I finally found the FIX!!!:
debian:~# mysql_upgrade -u root -h localhost -p --verbose --force
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' '--host=localhost'
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' '--host=localhost'
bible.holy_bible OK
bible.holybible OK
bible.quotes_meta OK
Afterwards finally I had to restart the mysql server once again in order to finally get rid of the shitty:
ERROR 1547 (HY000) at line 1: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted error!
debian:~# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..
This solved the insane Column count of mysql.proc is wrong. Expected 20, found 16 once and for all!
Before I came with this fix I tried all kind of forum suggested fixes like:
debian:~# mysql_upgrade -u root -p
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
This installation of MySQL is already upgraded to 5.1.61, use --force if you still need to run mysql_upgrade
debian:~# mysql_upgrade -p
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
This installation of MySQL is already upgraded to 5.1.61, use --force if you still need to run mysql_upgrade
And few more, none of them worked the only one that worked was:
debian:~# #mysql_upgrade -u root -h localhost -p --verbose --force
I have to say big thanks to Mats Lindth wonderful blog post which provided me with the solution.
It seems, since Oracle bought the Community edition of MySQL thinks with this database server are getting more and more messy and backwards incompatible day by day.
Lately, I'm experiencing too much hassles with MySQL version incompitabilities. Maybe I should think for migrating permanently to Postgre …
By the way the ERROR 1547 (HY000) at line 1: Column count of mysql.proc is wrong. is most probably caused of some kind of password hashing incompitability between the password hashing between the BSD and Debian SQL versions, as mysql -u root -p < dump.sql, does override default stored user passwords in the mysql database tables… Such password, hashing issues were common in prior MySQL 4 to MySQL 5 migrations I've done, however since MySQL 5+ is already storing its password strings encrypted with md5 encryption I wonder why on earth this mess happens ….
Tags: Auto, COLUMN, column count, command arguments, connection, cost, count, debian linux, debian server, Draft, event, event scheduler, fix, freebsd server, google, googling, graphical environment, half an hour, host, host localhost, init, installation, line 1, linux gnu, localhost, machine, mysq, mysql server, mysqlcheck, mysqld, nerves, password, port 3306, quot, root, server start, sftp, Socket, SQL, squeeze, system tables, TABLE, verbose
Posted in Everyday Life, MySQL, System Administration, Web and CMS | 2 Comments »
Saturday, April 28th, 2012
When a server hard disk scape gets filled its common that Apache returns empty (no content) pages…
This just happened in one server I administer. To restore the normal server operation I freed some space by deleting old obsolete backups.
Actually the whole reasons for this mess was an enormous backup files, which on the last monthly backup overfilled the disk empty space.
Though, I freed about 400GB of space on the the root filesystem and on a first glimpse the system had plenty of free hard drive space, still restarting the MySQL server refused to start up properly and spit error:
Incorrect key file for table '/tmp/#sql_9315.MYI'; try to repair it" mysql start up error
Besides that there have been corrupted (crashed) tables, which reported next to above error.
Checking in /tmp/#sql_9315.MYI, I couldn't see any MYI – (MyISAM) format file. A quick google look up revealed that this error is caused by not enough disk space. This was puzzling as I can see both /var and / partitions had plenty of space so this shouldn't be a problem. Also manally creating the file /tmp/#sql_9315.MYI with:
server:~# touch /tmp/#sql_9315.MYI
Didn't help it, though the file created fine. Anyways a bit of a closer examination I've noticed a /tmp filesystem mounted besides with the other file system mounts ????
You can guess my great amazement to find this 1 Megabyte only /tmp filesystem hanging on the server mounted on the server.
I didn't mounted this 1 Megabyte filesystem, so it was either an intruder or some kind of "weird" bug…
I digged in Googling to see, if I can find more on the error and found actually the whole mess with this 1 mb mounted /tmp partition is caused by, just recently introduced Debian init script /etc/init.d/mountoverflowtmp.
It seems this script was introduced in Debian newer releases. mountoverflowtmp is some kind of emergency script, which is triggered in case if the root filesystem/ space gets filled.
The script has only two options:
# /etc/init.d/mountoverflowtmp
Usage: mountoverflowtmp [start|stop]
Once started what it does it remounts the /tmp to be 1 megabyte in size and stops its execution like it never run. Well maybe, the developers had something in mind with introducing this script I will not argue. What I should complain though is the script design is completely broken. Once the script gets "activated" and does its job. This 1MB mount stays like this, even if hard disk space is freed on the root partition – / ….
Hence to cope with this unhandy situation, once I had freed disk space on the root partition for some reason mountoverflowtmp stop option was not working,
So I had to initiate "hard" unmount:
server:~# mount -l /tmp
Also as I had a bunch of crashed tables and to fix them, also issued on each of the broken tables reported on /etc/init.d/mysql start start-up.
server:~# mysql -u root -p
mysql> use Database_Name;
mysql> repair table Table_Name extended;
....
Then to finally solve the stupid Incorrect key file for table '/tmp/#sql_XXYYZZ33444.MYI'; try to repair it error, I had to restart once again the SQL server:
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..
root@server:/etc/init.d#
Tadadadadam!, SQL now loads and works back as before!
Tags: amazement, apache, Auto, backup files, backups, Disk, disk space, Draft, drive, emergency, empty space, file, file tmp, first glimpse, format, free hard drive, free hard drive space, google, googling, hard disk, help, Incorrect, init, intruder, key file, mess, MYI, MyISAM, mysql server, mysqld, operation, partition, quot, root, root filesystem, script, server operation, shouldn, size, something, TABLE, tmp, weird bug
Posted in MySQL, System Administration, Web and CMS | 1 Comment »
Thursday, April 19th, 2012
I'm doing replication for one server. Its not the first time I do configure replication between two MySQL database nodes, however since I haven't done it for a few years, my "know how" has mostly vanished so I had some troubles in setting it up. Once I followed some steps to configure replication I had to check if the two MASTER / Slave MySQL db nodes communicate properly. Hence I decided to drop a short post on that just in case if someone has to do the same or if I myself forget how I did it so I can check later on:
1. Check if MASTER MySQL server node is configured properly
The standard way to check a MySQL master node status info is with:
mysql> show master status;
+——————+———-+———————————————————+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+———————————————————+——————+
| mysql-bin.000007 | 106 | database1,database2,database3 | |
+——————+———-+———————————————————+——————+
1 row in set (0.00 sec)
By putting \G some extra status info is provided:
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000007
Position: 106
Binlog_Do_DB: database1,database2,database3
Binlog_Ignore_DB:
1 row in set (0.00 sec)
ERROR:
No query specified
2. Check if Slave MySQL node is configured properly
To check status of the slave the cmd is:
mysql> show slave status;
The command returns an output like:
mysql> show slave status;+———————————-+————-+————-+————-+—————+——————+———————+————————-+—————+———————–+——————+——————-+——————————————————-+———————+——————–+————————+————————-+—————————–+————+————+————–+———————+—————–+—————–+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+——————————-+—————+—————+—————-+—————-+| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error |+———————————-+————-+————-+————-+—————+——————+———————+————————-+—————+———————–+——————+——————-+——————————————————-+———————+——————–+————————+————————-+—————————–+————+————+————–+———————+—————–+—————–+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+——————————-+—————+—————+—————-+—————-+| Waiting for master to send event | HOST_NAME.COM | slave_user | 3306 | 10 | mysql-bin.000007 | 106 | mysqld-relay-bin.000002 | 251 | mysql-bin.000007 | Yes | Yes | database1,database2,database3 | | | | | | 0 | | 0 | 106 | 407 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | |+———————————-+————-+————-+————-+—————+——————+———————+————————-+—————+———————–+——————+——————-+——————————————————-+———————+——————–+————————+————————-+—————————–+————+————+————–+———————+—————–+—————–+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+——————————-+—————+—————+—————-+—————-+
As you can see the output is not too readable, as there are too many columns and data to be displayed and this doesn't fit neither a text console nor a graphical terminal emulator.
To get more readable (more verbose) status for the SQL SLAVE, its better to use command:
mysql> show slave status\G;
Here is a sample returned output:
mysql> show slave status\G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: HOST_NAME.COM Master_User: slave_user Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 106 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000007 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: database1,database2,database3 Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 106 Relay_Log_Space: 407 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec)ERROR: No query specified
If show master status or shwo slave status commands didn't reveal replication issue, one needs to stare at the mysql log for more info.
Tags: Auto, Cipher, cmd, condition, configure, Connect, Draft, event, exec, host, info, key, Master, master node, master slave, mysql server, name, Path, quot, Replicate, replication, Retry, server node, show, slave status, someone, Space, SQL, time, User
Posted in MySQL, System Administration, Web and CMS | 4 Comments »
Friday, March 16th, 2012
Are you a Debian System Administrator and you recently run apt-get upgrade && apt-get upgrade finding out there are plenty of new packagesfor upgrade? Do you need only a pre-selected number of packages to upgrade with apt?
I run apt-get update && apt-get upgrade on one of our company Debian servers, just to see there are a number of packages to be upgraded among which there was some I didn't wanted to upgrade. Here is a little paste output from apt-get upgrade:
debian:~# apt-get update && apt-get upgrade
Hit http://security.debian.org squeeze/updates Release.gpg
...
Hit http://security.debian.org squeeze/updates/main amd64 Packages
Fetched 128 kB in 0s (441 kB/s)
Reading package lists... Done
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages will be upgraded:
at imagemagick libdbd-pg-perl libfreetype6 libmagickcore3 libmagickcore3-extra libmagickwand3 libmysqlclient16 mysql-client
mysql-client-5.1 mysql-common mysql-server mysql-server-5.1 mysql-server-core-5.1
Do you want to continue [Y/n]
14 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.
From first sight it seems logical to issue apt-get upgrade packagename to upgrade only single package with its package dependencies, instead of the whole group the above packs. However doing:
apt-get upgrade imagemagick will still try to upgrade all the packages instead of just imagemagick and its dependency package deb libmagickcore3
debian:~# apt-get upgrade imagemagick
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages will be upgraded:
at imagemagick libdbd-pg-perl libfreetype6 libmagickcore3 libmagickcore3-extra libmagickwand3 libmysqlclient16 mysql-client
mysql-client-5.1 mysql-common mysql-server mysql-server-5.1 mysql-server-core-5.1
14 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.
Do you want to continue [Y/n]
Doing all package,upgrade is not a good idea in my case, since upgrading mysql-server will require a MySQL server restart (something which we cannot afford to do right now) on this production server.
MySQL server restart during upgrade is never a good idea especially on productive busy (heavy loaded) SQL servers.
A restart of the MySQL server serving thousands of requests per second could lead often to crashed tables and hence temporary server downtime etc.
Still it is a good idea to upgrade the rest of packages with their newer versions. For exmpl. to upgrade; imagemagick, at , libfreetype6 and so on.
In order to upgrade only this 3 ones and their respective package dependencies, issue:
debian:~# apt-get --yes install imagemagick at libfreetype6
Repeat the apt-get install command with passing all the single package name you want to be upgraded and voila you're done 
.
Be sure the apt-get install packagename upgrade doesn't require also upgrade of myssql-server, mysql-client, mysql-common or mysql-server-core-5.1 or any of the package name you want to preserve from upgrading.
Tags: administrator, amp, Auto, building, deb, debian system, dependencies, dependency, dependency tree, doesn, DoneBuilding, Draft, Fetched, information, libfreetype, libmysqlclient, Linux, mysql server, number, package, packagesfor, pg, production, reading package, Repeat, second, server mysql, servers, something, SQL, squeeze, state information, system administrator, tree, Ubuntu, ubuntu linux, upgradeHit, voila
Posted in Linux, System Administration | No Comments »
Tuesday, March 6th, 2012
Nowdays WordPress is ran by million of blogs and websites all around the net. I myself run wordpress for this blog in general wordpress behaves quite well in terms of performance. However as with time the visitors tend to increase, on frequently updated websites or blogs. As a consequence, the blog / website performance slowly starts to decrease as result of the MySQL server read / write operations creating I/O and CPU load overheads. Buying a new hardware and migrating the wordpress database is a possible solution, however for many small or middle size wordpress blogs en sites like mine this is not easy task. Getting a dedicated server or simply upgrading your home server hardware is expensive and time consuming process… In my efforts to maximize my hardware utilization and increase my blog decaying performance I've stumbled on the article Optimize WordPress performance with wp-config.php
According to the article there are 4 simple wp-config.php config directvies useful in decreasing a lot of queries to the MySQL server issued with each blog visitor.
define('WP_HOME','http://www.yourblog-or-siteurl.com');
define('WP_SITEURL','http://www.yourblog-or-siteurl.com');
define('TEMPLATEPATH', '/var/www/blog/wp-content/themes/default');
define('STYLESHEETPATH', '/var/www/blog/wp-content/themes/default');
1. WP_HOME and WP_SITEURL wp-config.php directvies
The WP_HOME and WP_SITEURL variables are used to hard-code the address of the wordpress blog or site url, so wordpress doesn't have to check everytime in the database on every user request to know it is own URL address.
2. TEMPLATEPATH and TEMPLATEPATH wp variables
This variables will surely improve performance to Wodpress blogs which doesn't implement caching. On wp install with enabled caching plugins like WordPress Super Cache, Hyper Cache or WordPress Db Cache is used, I don't know if this variables will have performance impact …
So far I have tested the vars on a couple of wordpress based installs with caching enabled and even on them it seems the pages load faster than before, but I cannot say this for sure as I did not check the site loading time in advance before hardcoding the vars.
Anyways even if the suggested variables couldn't make positive impact on performance, having the four variables in wp-config.php is a good practice for blogs or websites which are looking for extra clarity.
For multiple wordpress installations living on the same server, having defined the 4 vars in different wordpress seems like a good idea too.
Tags: Auto, blogs, Cache, config, config variables, consequence, consuming, content themes, cpu load, dedicated server, default, doesn, Draft, everytime, home server, Hyper, impact, Load, loading, lot, mysql server, new hardware, Optimize, performance impact, php, possible solution, queries, request, server hardware, site url, siteurl, size, time, url, Wordpress, wordpress blog, wp, yourblog
Posted in SEO, Various, Web and CMS | 2 Comments »
Monday, July 11th, 2011
If you are stuffed with errors in /var/log/mysqld.log similar to:
110711 11:00:48 [ERROR] /usr/libexec/mysqld: Incorrect information in file: './anyboots_moncler_spaccio/zen_seo_cache.frm'
110711 11:00:48 [ERROR] /usr/libexec/mysqld: Incorrect information in file: './anyboots_moncler_spaccio/zen_sessions.frm'
This is a sure sign something terrible has happened with your mysql database tables that lead to corruption.
Having corrupt table in mysql installation can severely lead to data loss as well as significantly reduce the speed and performance of a MySQL server in this awful times mysqlcheck is the best friend of the administrator, here is how you can check and repair broken tables in MySQL server:
mysql-server:~# mysqlcheck --all-databases -u root -p
chillor_hjbgl.vn_users OK
chillor_lul.mybb_adminlog OK
chillor_lul.mybb_adminoptions OK
chillor_lul.mybb_adminsessions OK
chillor_lul.mybb_adminviews OK
chillor_lul.mybb_announcements OK
...
You will notice the corrupt sql tables will be reported as corrupt by the tool and mysqlcheck will try it's best to recover the corrupt tables.
In most cases this should be enough to recover corrupt tables.
Tags: adminlog, best friend, corruption, database tables, databases, ERROR, file, hjbgl, installation, libexec, loss, lul, moncler, mybb, Mysql, mysql database, mysql server, mysqld, OKchillor, pchillor, performance, repair, root, SEO, server mysql, sessions, something, spaccio, speed and performance, sql tables, tool, usr, var, zen
Posted in Linux, MySQL, System Administration | No Comments »
Tuesday, May 3rd, 2011
Every now and then my FreeBSD router dmesg ( /var/log/dmesg.today ) logs, gets filled with error messages like:
pid 86369 (httpd), uid 80, was killed: out of swap space
swap_pager_getswapspace(14): failed
swap_pager_getswapspace(16): failed
swap_pager_getswapspace(11): failed
swap_pager_getswapspace(12): failed
swap_pager_getswapspace(16): failed
swap_pager_getswapspace(16): failed
swap_pager_getswapspace(16): failed
swap_pager_getswapspace(16): failed
swap_pager_getswapspace(14): failed
swap_pager_getswapspace(16): failed
swap_pager_getswapspace(8): failed
Using swapinfo during the swap_pager_getswapspace(16): failed messages were logged in, I figured out that definitely the swap memory over-use is the bottleneck for the troubles, to find this I used the command:
freebsd# swapinfo
Device 1K-blocks Used Avail Capacity Type
/dev/ad0s1b 49712 45920 3792 92% Interleaved
After some investigation, I've figured out that the MySQL server is causing the kernel exceeded swap troubles.
My current MySQL server version is installed from the ports tree, whether I'm using the bsd port /usr/ports/databases/mysql51-server/ and it appears to work just fine.
However I have noticed that the mysql-server is missing a my.cnf file!, which means the mysql server is running under a mode with some kind of default configurations.
Strangely in the system process list it appeared it is using a default my.cnf file located in /var/db/mysql/my.cnf
Below you see the paste from the ps command:
ps axuww freebsd# ps axuww | grep -i my.cnf | grep -v grep
mysql 7557 0.0 0.1 3464 1268 p1 I 12:03PM 0:00.01 /bin/sh /usr/local/bin/mysqld_safe --defaults-extra-file=/var/db/mysql/my.cnf --user=mysql --datadir=/var/db/mysql --pid-file=/var/db/mysql/pcfreak.pidmysql 7589 0.0 5.1 93284 52852 p1 I 12:03PM 0:59.01 /usr/local/libexec/mysqld --defaults-extra-file=/var/db/mysql/my.cnf --basedir=/usr/local --datadir=/var/db/mysql --user=mysql --pid-file=/var/db/mysql/pcfreak.pid --port=3306 --socket=/tmp/mysql.sock
Nevertheless it appeared the sql server is running the file /var/db/mysql/my.cnf conf was not existing! This was really weird for me as I'm used to have the default my.cnf from my previous experience with Linux servers!
Thus the next logical thing I did was to create my.cnf conf file in order to be able to have a proper limiting configuration for the sql server.
The FreeBSD my.cnf skele files are found in /usr/local/share/mysql/, here are the 4 files one can use as a starting basis for further configuration of the mysql-server.
freebsd# ls -al /usr/local/share/mysql/my-*.cnf
-r--r--r-- 1 root wheel 4948 Aug 12 2009 /usr/local/share/mysql/my-huge.cnf
-r--r--r-- 1 root wheel 20949 Aug 12 2009 /usr/local/share/mysql/my-innodb-heavy-4G.cnf
-r--r--r-- 1 root wheel 4924 Aug 12 2009 /usr/local/share/mysql/my-large.cnf
-r--r--r-- 1 root wheel 4931 Aug 12 2009 /usr/local/share/mysql/my-medium.cnf
-r--r--r-- 1 root wheel 2502 Aug 12 2009 /usr/local/share/mysql/my-small.cnf
I have chosen to use the my-medium.cnf as a skele to tune up, as my server is not high iron one e.g. the host I run the mysql is a (simple dual core 1.2Ghz system).
Further on I copied the /usr/local/share/mysql/my-medium.cnf to /var/db/mysql/my.cnf e.g.:
freebsd# cp -rpf /usr/local/share/mysql/my-medium.cnf /var/db/mysql/my.cnf
As a next step to properly tune up the default values of the newly copied my.cnf to my specific server I used the Tuning-Primer MySQL tuning script
Using tuning-primer.sh is really easy as all I did is download, launch it and follow the script suggestions to correct some of the values already in my.cnf
I have finally ended up with the following my.cnf after using tuning-primer.sh to optimize mysql server to work with my bsd host
Now I really hope the shitty swap_pager_getswapspace: failed errors would not haunt me once again by crashing my server and causing mem overheads.
Still I wonder why the port developer Alex Dupre – ale@FreeBSD.org choose not to provide the default mysql51-server conf with some kind of my.cnf file? I hope he had a good reason.
Tags: Aug, Avail, avail capacity, bottleneck, cause and solution, cnf, command ps, databases, default, default configurations, dmesg, error messages, failedswap, file, freebsd router, getswapspace, host, Interleaved, investigation, kernel, log, logs, memory, mysql server, mysqld, pcfreak, ports, ps command, root, script, server version, Strangely, swapinfo, system, tree, uid
Posted in FreeBSD, MySQL, System Administration | 2 Comments »
