Admins eHow SysAdmin Tips & Tricks

April 29, 2011

Backup all MySQL DBs and Compress and Email the backup

Filed under: CentOS,Debian,General,linux,MySQL — Tags: , , , , , , — admin @ 1:28 pm

Make sure mutt & bzip2 are installed on your server.
Change USERNAME & PASSWORD to your MySQL login credentials.
Change email@domain.com to your email which can accept large attachments (gmail is recommended, currently it accepts attachments up to 25MBs)
Put the following line in your crontab. you can access crontab by this command : crontab -e

0 0 * * * mysqldump --user=USERNAME --password=PASSWORD -A | bzip2 > ~/AllDBsBackup.bz2 && echo | mutt -a ~/AllDBsBackup.bz2 -s "All DBs Daily Backup" -- email@domain.com

April 9, 2011

How to optimize MySQL server configuration ?

Filed under: MySQL — Tags: , , , — admin @ 7:23 pm

There are already many guides on the Internet on how to optimize MySQL server, MySQL is a very popular opensource database engine.
but most of such guides are too technical or too general or too specific which will not fit for any configuration.
What I have found and seems very promising, is a perl script which analyses your MySQL server and makes some recommendations regarding how to change your configuration to optimize the performance.
I tried it on a production hosting server and results were satisfactory, I am sure such scripts still can not beat database engineers, but if you dont have enough money to hire a a database engineer. they can be handful 😉

Here is the explanation from its website :

MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief format along with some basic performance suggestions.

You can find the script and guide on this link : http://mysqltuner.com/

March 18, 2010

Prevent MySQL Injection in PHP

Filed under: General,MySQL,PHP,Security — Tags: , , , , — admin @ 5:21 pm

As it is explained everywhere on the web , the general solution to prevent mysql injection attacks in php is to use mysql_real_escape_string function , there are many guides on how to use it in conjunction with sprintf to escape dangerous characters before executing a mysql query, but I found a very innovative solution in a comment on php.net website , it recommends to escape all variables sent by user by get or post method in the beginning of every page which executes mysql queries :
first make sure magic_quotes_gpc is turned off on your php configuration , it can be checked on php info page.
this step makes sure your variables are not escaped twice.
This article explains how to disable magic quotes : Disabling Magic Quotes Guide
put the following code in the beginning of every page executing mysql queries :

	include ('mysql_connect.php');
	
	//This stops SQL Injection in POST vars
	foreach ($_POST as $key => $value) {
		$_POST[$key] = mysql_real_escape_string($value);
	}

	//This stops SQL Injection in GET vars
	foreach ($_GET as $key => $value) {
		$_GET[$key] = mysql_real_escape_string($value);
	}

mysql_real_escape_string needs you to be connected to your mysql database when you call it , so I have placed include (‘mysql_connect.php’); in the beginning of script , you can replace it with your own mysql connect commands.

June 17, 2009

MySQL Backup/Restore from command line

Filed under: CentOS,Debian,General,MySQL — Tags: , , , — admin @ 2:48 pm

Backup

Dump ALL MySQL Databases

mysqldump --user=XXXXXXXX --password=XXXXXXX -A > /PATH/TO/DUMPFILE.SQL

Dump Individual or Multiple MySQL Databases

mysqldump --user=XXXXXXXX --password=XXXXXXX DB_NAME1 DB_NAME2 DB_NAME3 > /PATH/TO/DUMPFILE.SQL

Dump only certain tables from a MySQL Database

mysqldump --user=XXXXXXXX --password=XXXXXXXX DB_NAME --tables TABLE_NAME > /PATH/TO/DUMPFILE.SQL

Restore

mysql --user=XXXXXXXX --password=XXXXXXXX DB_NAME < /PATH/TO/DUMPFILE.SQL

June 2, 2009

MySQL Change root password

Filed under: General,MySQL — Tags: , , , — admin @ 8:58 am

If you have never set a root password for MySQL, the server does not require a password at all for connecting as root. To setup root password for first time, use mysqladmin command at shell prompt as follows:

mysqladmin -u root password NEWPASSWORD

However, if you want to change (or update) a root password, then you need to use following command

mysqladmin -u root -p'oldpassword' password newpass

For example, If old password is abc, and set new password to 123456, enter:

mysqladmin -u root -p'abc' password '123456'

May 31, 2009

How to configure nginx + php5 + mysql on debian 5 lenny

Filed under: Debian,General,MySQL,Nginx,PHP — Tags: , , , , — admin @ 3:56 pm

1.Install PHP5
We will use dotdeb repo for installing the latest version of PHP5 and MySQL Server so first we need to configure apt to use dotdeb repo.
Edit /etc/apt/sources.list :

nano /etc/apt/sources.list

Add The following lines to end of it :

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

Press CTRL+X Choose Yes to save the file and Exit.
update apt cache :

apt-get update

Now install PHP5 :

apt-get install php5-cgi php5-mysql

Now edit /etc/php5/cgi/php.ini :

nano /etc/php5/cgi/php.ini

and change cgi.fix_pathinfo to 1 :

cgi.fix_pathinfo = 1

Save File and Exit.
2. Install MySQL Server

apt-get install mysql-server

3. Install Lighttpd
We need to install lighttpd because Nginx does not come with a FastCGI package, and there isn’t a standalone package yet. So we are going to install Lighttpd, disable it, and use spawn-fcgi from the Lighttpd package.

apt-get install lighttpd
/etc/init.d/lighttpd stop
update-rc.d -f lighttpd remove

Also remove lighttpd executable file :

rm /usr/sbin/lighttpd

4. Setup spawn-fcgi
Since we are going to use spawn-fcgi to handle PHP, we need to set it up to start when our server starts and make an init script so that we can control the processes. For starters, create the init script:

nano /etc/init.d/php-fastcgi

and add the following code to it and save it:

#! /bin/sh
### BEGIN INIT INFO
# Provides:          php-fastcgi
# Required-Start:    $all
# Required-Stop:     $all
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: Start and stop php-cgi in external FASTCGI mode
# Description:       Start and stop php-cgi in external FASTCGI mode
### END INIT INFO

# Author: Kurt Zankl <[EMAIL PROTECTED]>

# Do NOT "set -e"

PATH=/sbin:/usr/sbin:/bin:/usr/bin
DESC="php-cgi in external FASTCGI mode"
NAME=php-fastcgi
DAEMON=/usr/bin/php-cgi
PIDFILE=/var/run/$NAME.pid
SCRIPTNAME=/etc/init.d/$NAME
PHP_CONFIG_FILE=/etc/php5/cgi/php.ini

# Exit if the package is not installed
[ -x "$DAEMON" ] || exit 0

# Read configuration variable file if it is present
[ -r /etc/default/$NAME ] && . /etc/default/$NAME

# Load the VERBOSE setting and other rcS variables
. /lib/init/vars.sh

# Define LSB log_* functions.
# Depend on lsb-base (>= 3.0-6) to ensure that this file is present.
. /lib/lsb/init-functions

# If the daemon is not enabled, give the user a warning and then exit,
# unless we are stopping the daemon
if [ "$START" != "yes" -a "$1" != "stop" ]; then
log_warning_msg "To enable $NAME, edit /etc/default/$NAME and set START=yes"
exit 0
fi

# Process configuration
export PHP_FCGI_CHILDREN PHP_FCGI_MAX_REQUESTS
DAEMON_ARGS="-q -b $FCGI_HOST:$FCGI_PORT -c $PHP_CONFIG_FILE"

do_start()
{
# Return
#   0 if daemon has been started
#   1 if daemon was already running
#   2 if daemon could not be started
start-stop-daemon --start --quiet --pidfile $PIDFILE --exec $DAEMON --test > /dev/null \
|| return 1
start-stop-daemon --start --quiet --pidfile $PIDFILE --exec $DAEMON \
--background --make-pidfile --chuid $EXEC_AS_USER --startas $DAEMON -- \
$DAEMON_ARGS \
|| return 2
}

do_stop()
{
# Return
#   0 if daemon has been stopped
#   1 if daemon was already stopped
#   2 if daemon could not be stopped
#   other if a failure occurred
start-stop-daemon --stop --quiet --retry=TERM/30/KILL/5 --pidfile $PIDFILE > /dev/null # --name $DAEMON
RETVAL="$?"
[ "$RETVAL" = 2 ] && return 2
# Wait for children to finish too if this is a daemon that forks
# and if the daemon is only ever run from this initscript.
# If the above conditions are not satisfied then add some other code
# that waits for the process to drop all resources that could be
# needed by services started subsequently.  A last resort is to
# sleep for some time.
start-stop-daemon --stop --quiet --oknodo --retry=0/30/KILL/5 --exec $DAEMON
[ "$?" = 2 ] && return 2
# Many daemons don't delete their pidfiles when they exit.
rm -f $PIDFILE
return "$RETVAL"
}
case "$1" in
start)
[ "$VERBOSE" != no ] && log_daemon_msg "Starting $DESC" "$NAME"
do_start
case "$?" in
0|1) [ "$VERBOSE" != no ] && log_end_msg 0 ;;
2) [ "$VERBOSE" != no ] && log_end_msg 1 ;;
esac
;;
stop)
[ "$VERBOSE" != no ] && log_daemon_msg "Stopping $DESC" "$NAME"
do_stop
case "$?" in
0|1) [ "$VERBOSE" != no ] && log_end_msg 0 ;;
2) [ "$VERBOSE" != no ] && log_end_msg 1 ;;
esac
;;
restart|force-reload)
log_daemon_msg "Restarting $DESC" "$NAME"
do_stop
case "$?" in
0|1)
do_start
case "$?" in
0) log_end_msg 0 ;;
1) log_end_msg 1 ;; # Old process is still running
*) log_end_msg 1 ;; # Failed to start
esac
;;
*)
# Failed to stop
log_end_msg 1
;;
esac
;;
*)
echo "Usage: $SCRIPTNAME {start|stop|restart|force-reload}" >&2
exit 3
;;
esac

There is no editing needed for the code above. Now we need to make it executable.

chmod +x /etc/init.d/php-fastcgi

Now we will create the configuration file for spawn-fcgi:

nano /etc/default/php-fastcgi

and add:

START=yes

# Which user runs PHP? (default: www-data)

EXEC_AS_USER=www-data

# Host and TCP port for FASTCGI-Listener (default: localhost:9000)

FCGI_HOST=localhost
FCGI_PORT=9000

# Environment variables, which are processed by PHP

PHP_FCGI_CHILDREN=4
PHP_FCGI_MAX_REQUESTS=1000

In this file, you can change the FCGI_PORT, PHP_FCGI_CHILDREN, and PHP_FCGI_MAX_REQUESTS. If you change the port, make sure to note what you change it to because we will need it for later. Now, we want to make sure that spawn-fcgi starts when the server starts:

update-rc.d php-fastcgi defaults
/etc/init.d/php-fastcgi restart

5. Install Nginx

apt-get install nginx

6. Configure Nginx and your default vhost
For help with configuring Nginx beyond just the default configuration, please refer to the Nginx Wiki. We are now going to configure the default vhost so that we can verify that PHP is working with Nginx.

nano /etc/nginx/sites-available/default

In the default vhost, change the following lines to look like this:

server_name _;
location ~ \.php$ {
fastcgi_pass 127.0.0.1:9000;
fastcgi_index index.php;
fastcgi_param SCRIPT_FILENAME /var/www/nginx-default$fastcgi_script_name;
include fastcgi_params;
}

What we did here was changed the server_name to accept all incoming requests, uncommented the location stanza and defined where Nginx needs to look for spawn-fcgi. If you changed the port above in the /etc/default/php-fastcgi file, then you need to change the port on the fastcgi_pass line as well. On the line fastcgi_param SCRIPT_FILENAME /var/www/nginx-default$fastcgi_script_name;, you need to change /var/ww/nginx-default to match your web root. On the line include fastcgi_params; you need to add a space between the include and the fastcgi_params as there is a bug in the default configuration and that space was omitted. Once you save the default-vhost, you need to restart Nginx.

/etc/init.d/nginx restart

Now we will create an info.php file in your web root:

nano /var/www/nginx-default/info.php

Add the following code and save the file:

<? phpinfo(); ?>

Now in your web browser, pull up the newly created file (http://your-ip/info.php). You should see information about the version of PHP that you are running.

Note: When I installed Nginx, it did not start automatically, however it was setup to start when the server booted. I have heard stories of users having issues with Nginx starting automatically when the server boots. If Nginx does not start when you boot your server, run the following command which will make it start on boot:

update-rc.d nginx defaults

Enjoy!

Reference : ChrisJohnston.org

May 18, 2009

Convert and Change MySQL Collation/Encoding to utf8

Filed under: General,MySQL — Tags: , , , , , , — admin @ 12:33 pm

This is simple , use the following command on mysql :

alter table TABLE_NAME convert to character set utf8 collate utf8_unicode_ci;

Powered by WordPress