dino

MySQL Cheat Sheet

by

SSH repair and optimize all MySQL databases:

# /usr/bin/mysqlcheck –repair –all-databases –password=xxxxxx

# /usr/bin/mysqlcheck –optimize –all-databases –password=xxxxxx

Upgrade/Downgrade to MySQL 4.x:

backup database:

# cd /root

# /usr/bin/mysqldump –create-options –compatible=mysql40 –all-databases –force –user=root –password=xxxx > backup.sql

MySQL Optimization (my.cnf)

by 
# vi /etc/my.cnf
#opteron 852 4GB RAM
[mysqld]
safe-show-database
max_connections = 500
key_buffer = 150M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 1M
table_cache = 1024
thread_cache_size = 100
wait_timeout = 300
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb
thread_concurrency=4

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
#skip-networking
safe-show-database
query_cache_limit=1M
query_cache_size=128M ## 32MB for every 1GB of RAM
query_cache_type=1
max_user_connections=200
max_connections=500
interactive_timeout=10
wait_timeout=20
connect_timeout=20
thread_cache_size=128
key_buffer=256M ## 64MB for every 1GB of RAM
join_buffer=1M
max_connect_errors=20
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=4M ## 1MB for every 1GB of RAM
read_buffer_size=4M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=4M  ## 1MB for every 1GB of RAM
thread_concurrency=4 ## Number of CPUs x 2
myisam_sort_buffer_size=64M
server-id=1
log_slow_queries=/var/log/mysql-slow-queries.log
long_query_time=2
collation-server=latin1_general_ci
old-passwords

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

Optimize sysctl.conf

by 
# vi /etc/sysctl.conf
# Kernel sysctl configuration file for Red Hat Linux
#
# For binary values, 0 is disabled, 1 is enabled. See sysctl(8) and
# sysctl.conf(5) for more details.

# Disables packet forwarding
net.ipv4.ip_forward=0

# Disables IP source routing
net.ipv4.conf.all.accept_source_route = 0
net.ipv4.conf.lo.accept_source_route = 0
net.ipv4.conf.eth0.accept_source_route = 0
net.ipv4.conf.default.accept_source_route = 0

# Enable IP spoofing protection, turn on source route verification
net.ipv4.conf.all.rp_filter = 1
net.ipv4.conf.lo.rp_filter = 1
net.ipv4.conf.eth0.rp_filter = 1
net.ipv4.conf.default.rp_filter = 1

# Disable ICMP Redirect Acceptance
net.ipv4.conf.all.accept_redirects = 0
net.ipv4.conf.lo.accept_redirects = 0
net.ipv4.conf.eth0.accept_redirects = 0
net.ipv4.conf.default.accept_redirects = 0

# Enable Log Spoofed Packets, Source Routed Packets, Redirect Packets
net.ipv4.conf.all.log_martians = 0
net.ipv4.conf.lo.log_martians = 0
net.ipv4.conf.eth0.log_martians = 0

# Disables IP source routing
net.ipv4.conf.all.accept_source_route = 0
net.ipv4.conf.lo.accept_source_route = 0
net.ipv4.conf.eth0.accept_source_route = 0
net.ipv4.conf.default.accept_source_route = 0

# Enable IP spoofing protection, turn on source route verification
net.ipv4.conf.all.rp_filter = 1
net.ipv4.conf.lo.rp_filter = 1
net.ipv4.conf.eth0.rp_filter = 1
net.ipv4.conf.default.rp_filter = 1

# Disable ICMP Redirect Acceptance
net.ipv4.conf.all.accept_redirects = 0
net.ipv4.conf.lo.accept_redirects = 0
net.ipv4.conf.eth0.accept_redirects = 0
net.ipv4.conf.default.accept_redirects = 0

# Disables the magic-sysrq key
kernel.sysrq = 0

# Decrease the time default value for tcp_fin_timeout connection
net.ipv4.tcp_fin_timeout = 15

# Decrease the time default value for tcp_keepalive_time connection
net.ipv4.tcp_keepalive_time = 1800

# Turn off the tcp_window_scaling
net.ipv4.tcp_window_scaling = 0

# Turn off the tcp_sack
net.ipv4.tcp_sack = 0

# Turn off the tcp_timestamps
net.ipv4.tcp_timestamps = 0

# Enable TCP SYN Cookie Protection
net.ipv4.tcp_syncookies = 1

# Enable ignoring broadcasts request
net.ipv4.icmp_echo_ignore_broadcasts = 1

# Enable bad error message Protection
net.ipv4.icmp_ignore_bogus_error_responses = 1

# Log Spoofed Packets, Source Routed Packets, Redirect Packets
net.ipv4.conf.all.log_martians = 1

# Increases the size of the socket queue (effectively, q0).
net.ipv4.tcp_max_syn_backlog = 1024

# Increase the tcp-time-wait buckets pool size
net.ipv4.tcp_max_tw_buckets = 1440000

# Allowed local port range
net.ipv4.ip_local_port_range = 16384 65536

For changes to take affect right away

# /sbin/sysctl -p

Easy apache error on a VPS – !! Warning (VZ): You are only only guaranteed xx Megabytes of ram! xxx Megabytes is recommended. !!

by


Easy::Apache v3.2.0 Build 4310

!! Warning (VZ): You are only only guaranteed 224 Megabytes of ram! 512 Megabytes is recommended. !!

If you get above memory error on VPS while running easy apache,  You can fix by using following steps. Login in to main VPS and edit the variable OOMGUARPAGES accordingly.

OR

OpenVZ Users :
You can also modify the OOMGUARPAGES values from shell as required.  :

vzctl set 101 –OOMGUARPAGES 561120:2147483647 –save

Need Assistance with nominal fee contact [email protected]

How to install Zend Optimizer with the (IonCube) Loader? (RHEL, Plesk)

by

How to install Zend Optimizer with the (IonCube) Loader?

  1. wget http://downloads.zend.com/optimizer/3.3.3/ZendOptimizer-3.3.3-linux-glibc23-i386.tar.gz
  2. tar zxvf ZendOptimizer-3.3.3-linux-glibc23-i386.tar.gz
  3. cd ZendOptimizer-3.3.3-linux-glibc23-i386
  4. php -v (Check php version note it down)
  5. php -i | grep php.ini (note down the php.ini path)
  6. httpd -v (Check version)
  7. ./install
  8. After the above it will prompt for various path of apache, php
  9. php -v (if it gives error) “PHP Fatal error: [ionCube Loader] The Loader must appear as the first entry in the php.ini file in Unknown on line 0”
  10. Comment the zend in php.ini
  11. nano -w /etc/php.ini
  12. #[Zend]
    #zend_extension=/usr/local/Zend/lib/ZendExtensionManager.so
    #zend_extension_ts=/usr/local/Zend/lib/ZendExtensionManager_TS.so
    #zend_extension_manager.optimizer=/usr/local/Zend/lib/Optimizer-3.3.3
    #zend_extension_manager.optimizer_ts=/usr/local/Zend/lib/Optimizer_TS-3.3.3 
    #zend_optimizer.version=3.3.3
  13. Make a zend.ini in /etc/php.d/ directory
  14. copy the zend so to it
  15. [Zend]
    zend_extension_manager.optimizer=/usr/local/Zend/lib/Optimizer-3.3.3
    zend_extension_manager.optimizer_ts=/usr/local/Zend/lib/Optimizer_TS-3.3.3
    zend_optimizer.version=3.3.3
    zend_extension=/usr/local/Zend/lib/ZendExtensionManager.so
    zend_extension_ts=/usr/local/Zend/lib/ZendExtensionManager_TS.so
  16. restart httpd | /etc/init.d/httpd restart
  17. check the if it is loaded or not php -v
  18. [root@rshagrawal php.d]# php -v
    PHP 4.3.9 (cgi) (built: Sep 12 2007 11:09:31)
    Copyright (c) 1997-2004 The PHP Group
    Zend Engine v1.3.0, Copyright (c) 1998-2004 Zend Technologies
    with the ionCube PHP Loader v3.1.16, Copyright (c) 2002-2006, by ionCube Ltd., and
    with Zend Extension Manager v1.2.2, Copyright (c) 2003-2007, by Zend Technologies
    with Zend Optimizer v3.3.3, Copyright (c) 1998-2007, by Zend Technologies

Thats all you are done.

=======================
Reference ::

http://kb.swsoft.com/en/631

 

Need Assistance with nominal fee contact [email protected]

Any operation on a VE gives me “Cannot lock VE”.

by

Any operation on a VE gives me “Cannot lock VE“.

VE is locked when some operation (backup, migration, start / stop, etc.) with this VE is in progress. You can determine which process is holding VE #111 using the following command on the hardware node:

# cat /vz/lock/111.lck

16824
backing-up

You can kill that process if needed. Make sure that the process is really killed. If there is no process with that PID on the node, just remove the lockfile.

# kill 16824

# vzctl start 111

Service Unavailable error message on a server with Plesk Windows

by

Plesk Error : Service Unavailable

Error under Event Viewer :

The identity of application pool ‘PleskControlPanel’ is invalid, so the World Wide Web Publishing Service can not create a worker process to serve the application pool. Therefore, the application pool has been disabled.

Try Plesk Reconfigurator > Repair Plesk Installation with checked option Plesk Services this fixed the issue which I faced.

Easy apache error on a VPS – You are only only allowed to use xx Megabytes of ram! xxx Megabytes is required

by

Sometimes, you can get the following error on VPS while running easy apache. You can fix by simple following steps.

Login in to main VPS and edit the variable PRIVVMPAGES accordingly

/scripts/easyapache
Use of uninitialized value in pattern match (m//) at /var/cpanel/perl/easy/Cpanel/Easy/Utils.pm line 436.
!! Critical Error (VZ): You are only only allowed to use 90 Megabytes of ram! 512 Megabytes is required. !!

You can also modify the privvmpages values from shell as required. (For OpenVZ users) :

vzctl set 101 --privvmpages 850M:900M --save

DirectAdmin License Shows To Be Expired (Early License)

by

DirectAdmin is fully installed but we are unable to login and access the host.

Steps to try and resolve this problem.

Verify IP information bound to the host matches the data in /usr/local/directadmin/scripts/setup.txt

Confirm the date is correct.
# date
Sun Jan 4 03:14:22 CST 2004

If not then set the date with ‘rdate’ or ‘ntp’
# rdate -s time.nist.gov

Verify date is now correct.
# date
Thu Feb 3 17:44:23 CST 2005
Check and see if DA is now accessable.

If that does not work try the following.

cd /usr/local/directadmin/scripts
./getLicense.sh 123 1234
service directadmin restart

Where 123 and 1234 are your Client ID and License ID, respectively. If there are errrors extracting the update.tar.gz file, then run:
head -n 1 /usr/local/directadmin/conf/license.key

to search for an error. If there is an readable error inside the file, double check the IP you are using matches the IP in the license on our system.

Need to change IP address in FreeBSD

by

Issue: A change of IP address is needed in FreeBSD.

Solution: If you do not have the root password boot into Single User mode [option 4]. Procced to step 1.

If you have the root password procced to step 2.

1. mount -o (if you get a read-only error, you will have to run fschk -y)

2. vi /etc/rc.conf (If vi is unavailable use ee)

at this point simply edit the IP Address lines that will need to be corrected.