Back Up MySQL Databases With mylvmbackup On Ubuntu

mylvmbackup is a Perl script for quickly creating MySQL backups. It uses LVM's snapshot feature to do so. To perform a backup, mylvmbackup obtains a read lock on all tables and flushes all server caches to disk, creates a snapshot of the volume containing the MySQL data directory, and unlocks the tables again. This article shows how to use it on an Ubuntu 8.10 server.
I do not issue any guarantee that this will work for you!

1 Preliminary Note

I'm assuming that MySQL is already set up and running on your system. The system must use LVM, and the MySQL data directory (/var/lib/mysql) should have an LVM partition of its own (althouth that is optional).

If you have read Back Up (And Restore) LVM Partitions With LVM Snapshots you know that LVM snapshots require some unused LVM partition for the snapshot. My test system has a second, currently unused hard drive /dev/sdb that will be used by mylvmbackup to create a temporary logical volume for the backup.

Creating MySQL Backups With AutoMySQLBackup On Ubuntu 9.10

AutoMySQLBackup is a shell script that lets you take daily, weekly and monthly backups of your MySQL databases using mysqldump. It can back up multiple databases, compress the backups, back up remote databases, and email the logs. This tutorial explains how to install and use it on an Ubuntu 9.10 server.
 do not issue any guarantee that this will work for you!

1 Preliminary Note
AutoMySQLBackup uses mysqldump to create SQL dumps of your databases. Please note that mysqldump will lock your databases while the backup is being created, and this can take from less than a second up to a few minutes, depending on the size of your database. If you're running a high-traffic web site with a large database, then AutoMySQLBackup is not for you!
This script will not help in the event of a hard drive crash. You should copy your backups offline regularly for best protection.
I'm running all the steps in this tutorial with root privileges, so make sure you're logged in as root:

sudo su

phpmyadmin - Operate MySQL with Web Browser

Install phpmyadmin to operate MySQL from Web browser. Web server is required.

[1] Install and configure phpmyadmin

[root@www ~]# yum -y install phpmyadmin php-mysql php-mcrypt

[root@www ~]# vi /usr/share/phpmyadmin/config.inc.php

# line 16: set password
$cfg['blowfish_secret'] = 'password';

[root@www ~]# vi /etc/httpd/conf.d/phpmyadmin.conf

# line 8: add IP address you allow
Allow from 127.0.0.1 192.168.0.0/24

# line 11: add alias
Alias /mysql /usr/share/phpmyadmin

[root@www ~]# /etc/rc.d/init.d/httpd reload
Reloading httpd: [ OK ]

[2] Access to 'http://(your hostname or IP address)/(alias name you set)/' with web browser,then following screen is shown. Login with a user in MySQL.

[3] Just logined. You can operate MySQL on here.

Install Mysql

Install MySQL for database server.
[root@www ~]# yum -y install mysql-server

[root@www ~]# /etc/rc.d/init.d/mysqld start
Initializing MySQL database: Installing all prepared tables
Fill help tables

To start mysqld at boot time you have to copy support-files/mysql.server
to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h www.server-linux.info password 'new-password'
See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory:
cd sql-bench ; perl run-all-tests

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
Starting MySQL: [ OK ]

[root@www ~]# chkconfig mysqld on
[root@www ~]# mysql -u root# login to MySQL
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2 to server version: 5.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

# show user info
mysql> select user,host,password from mysql.user;

4 rows in set (0.00 sec)

# set root password
mysql> set password for root@localhost=password('password');
Query OK, 0 rows affected (0.00 sec)

# set root password
mysql> set password for root@'www.server-linux.info'=password('password');
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from mysql.user;

4 rows in set (0.00 sec)

# delete anonymous users
mysql> delete from mysql.user where user='';
Query OK, 2 rows affected (0.00 sec)

mysql> select user,host,password from mysql.user;

2 rows in set (0.00 sec)

mysql> exit# logout
Bye
[root@www ~]# mysql -u root -p# login as root
Enter password:# root password
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4 to server version: 5.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> exit
Bye

How To Install memcached with memcache PHP Extension on CentOS

In this how to i will describe how to install memcached on your server with PHP Extension. memcached is a high-performance, distributed memory object caching system, generic in nature, but intended for use in speeding up dynamic web applications by alleviating database load. My server is running CentOS 5.3 with CPanel.

1) Install memcached.

Enable rpmforge respository to install latest memcached rpm using yum.
For i386 / i686
rpm -Uhv http://apt.sw.be/redhat/el5/en/i386/...l5.rf.i386.rpm
For x86_64
rpm -Uhv http://apt.sw.be/redhat/el5/en/x86_6....rf.x86_64.rpm

Use yum to install memcached
yum -y install memcached
As soon as memcached installation completed, edit options for memcached in /etc/sysconfig/memcached to meet your need.
vi /etc/sysconfig/memcached

PORT=”11211″ #define on which port to urn
USER=”nobody” #same as apache user
MAXCONN=”1024″ #maximum number of connections allowed
CACHESIZE=”64″ #memory used for caching
OPTIONS=”" #use for any custom options

Save the file. All options can be seen by using following command

memcached -h

Start memcached

/etc/init.d/memcached start
Starting Distributed memory caching (memcached): [ OK ]

to check the running status of memcached

/etc/init.d/memcached status
memcached (pid 6475) is running…

and
netstat -anp | grep 11211
tcp 0 0 :::11211 :::* LISTEN 6475/memcached
udp 0 0 0.0.0.0:11211 0.0.0.0:* 6475/memcached


2) Install PHP Extension.
Download and install latest stable memcache version from PECL.

cd /usr/src
wget http://pecl.php.net/get/memcache-2.2.5.tgz
tar zxvf memcache-2.2.5.tgz
cd memcache-2.2.5
phpize
./configure
make
make install


memcache.so will be install in php modules directory, now enable memcache.so extension in php.ini
To find out your php.ini location, execute following command
php -i | grep php.ini
Configuration File (php.ini) Path => /usr/local/lib
Loaded Configuration File => /usr/local/lib/php.ini

vi /usr/local/lib/php.ini

extension = “memcache.so”


save the file and restart httpd server.

/etc/init.d/httpd restart

To check is memcache extension loaded in php, execute following command.


php -i | grep memcache
memcache
memcache support => enabled
memcache.allow_failover => 1 => 1
memcache.chunk_size => 8192 => 8192
memcache.default_port => 11211 => 11211
memcache.default_timeout_ms => 1000 => 1000
memcache.hash_function => crc32 => crc32
memcache.hash_strategy => standard => standard
memcache.max_failover_attempts => 20 => 20
Registered save handlers => files user sqlite memcache
PWD => /usr/src/memcache-2.2.5
_SERVER["PWD"] => /usr/src/memcache-2.2.5
_ENV["PWD"] => /usr/src/memcache-2.2.5

Install Lighttpd with PHP5 and MySQL on CentOS 5.4

[1] Install MySQL 5.0

- Use command line:
yum install mysql mysql-server

- Start MySQL with OS
chkconfig --levels 235 mysqld on
/etc/init.d/mysqld start

- Take password for root on MySQL
mysqladmin -u root password yourrootsqlpassword
mysqladmin -h server1.example.com -u root password yourrootsqlpassword

[2] Install Lighttpd

1. Download packet rpmforge-release if system your is x86_64 and install
wget http://packages.sw.be/rpmforge-release/rpmforge-release-0.3.6-1.el5.rf.x86_64.rpm
rpm -Uvh rpmforge-release-0.3.6-1.el5.rf.x86_64.rpm

- If system your is i386 and install
wget http://packages.sw.be/rpmforge-release/rpmforge-release-0.3.6-1.el5.rf.i386.rpm
rpm -Uvh rpmforge-release-0.3.6-1.el5.rf.i386.rpm

2. Install Lighttpd
yum install lighttpd

3. Start Lighttpd with OS
chkconfig --levels 235 lighttpd on
/etc/init.d/lighttpd start

[3] Install PHP5

1. Install Lighttpd-fastcgi and php-cli
yum install lighttpd-fastcgi php-cli

2. Configuring Lighttpd And PHP5
- Open /etc/php.ini add "cgi.fix_pathinfo = 1"
[...]
cgi.fix_pathinfo = 1

3. Open /etc/lighttpd/lighttpd.conf and uncomment "mod_fastcgi"
[...]
server.modules = (
# "mod_rewrite",
# "mod_redirect",
# "mod_alias",
"mod_access",
# "mod_cml",
# "mod_trigger_b4_dl",
# "mod_auth",
# "mod_status",
# "mod_setenv",
"mod_fastcgi",
# "mod_proxy",
# "mod_simple_vhost",
# "mod_evhost",
# "mod_userdir",
# "mod_cgi",
# "mod_compress",
# "mod_ssi",
# "mod_usertrack",
# "mod_expire",
# "mod_secdownload",
# "mod_rrdtool",
"mod_accesslog" )
[...]

4. In file lighttpd.conf, after uncomment "fastcgi.server"
[...]
#### fastcgi module
## read fastcgi.txt for more info
## for PHP don't forget to set cgi.fix_pathinfo = 1 in the php.ini
fastcgi.server = ( ".php" =>
( "localhost" =>
(
"socket" => "/tmp/php-fastcgi.socket",
"bin-path" => "/usr/bin/php-cgi"
)
)
)
[...]

5. Restart service Lighttpd
/etc/init.d/lighttpd restart

6. Take file /srv/www/lighttpd/info.php
phpinfo();
?>

7. Open brows add address http://ip_your/info.php

[4] Configuring PHP5 support MySQL

1. Install php-mysql
yum install php-mysql php-gd php-imap php-ldap php-odbc php-pear php-xml php-xmlrpc

2. Restart Lighttpd
/etc/init.d/lighttpd restart

Install and Configure PHP on Centos

Install PHP

1. Run the yum install command
yum install php

Configure PHP

1. Increase PHP script memory limit
In the /etc/php.ini file replace memory_limit = 16M with memory_limit = 128M
2. Increase PHP script max execution time
In the /etc/php.ini file replace max_execution_time = 30 with max_execution_time = 120
3. Increase PHP script max upload size
In the /etc/php.ini file replace max_upload_size = 2M with max_upload_size = 50M
In the /etc/php.ini file replace post_max_size = 8M with post_max_size = 50M
4. Create the /usr/share/phpinfo folder
mkdir /usr/share/phpinfo
5. Create the /usr/share/phpinfo/index.php file with the following text:
phpinfo();
?>
6. Change permissions on the index.php file
chmod 0755 /usr/share/phpinfo/index.php
7. Create the /etc/httpd/conf.d/phpinfo.conf file with the following text:
# phpinfo - PHP utility function for displaying php configuration
#
# Allows only localhost by default

Alias /phpinfo /usr/share/phpinfo

order deny,allow
deny from all
allow from 127.0.0.1

8. Restart Apache Server
service httpd restart

Backup databases by scripts on CentOS

-B1: Create backup_mysql.sh content:

*************************

# -------------------------------------------------------------------------

MyUSER="root" # USERNAME
MyPASS="Dj4uLgQd" # PASSWORD
MyHOST="localhost" # Hostname

# Linux bin paths, change this if it can't be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"

# Backup Dest directory, change this if you have someother location
DEST="/backup_mysql"

# Main directory where backup will be stored
MBD="$DEST/mysql"

# Get hostname
HOST="$(hostname)"

# Get data in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y")"

# File to store current backup file
FILE="mysql_db_backup"
# Store list of databases
DBS="sodepvn_mlmobi sodepvn_sim" # danh sach database can backup

# DO NOT BACKUP these databases
# IGGY="test"

[ ! -d $MBD ] && mkdir -p $MBD || :

# Only root can access it!
$CHOWN 0.0 -R $DEST
$CHMOD 0600 $DEST

# Get all database list first
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"

for db in $DBS
do
skipdb=-1
if [ "$IGGY" != "" ];
then
for i in $IGGY
do
[ "$db" == "$i" ] && skipdb=1 || :
done
fi

if [ "$skipdb" == "-1" ] ; then
FILE="$MBD/$db.$HOST.$NOW.gz"
# do all inone job in pipe,
# connect to mysql using mysqldump for select mysql database
# and pipe it out to gz file in backup dir :)
$MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE
fi
done

*********************************

-B2: Restart crontab
service crond restart
- B3: Create Crontab content:
#crontab -e
0 15 * * 1,4 sh /mnt/data/linux/code/backupbookmarks.sh
- B4: Restart crontab