Tuesday, November 22, 2011

Flush memcached

nc "flushall\n" localhost 11211 

Sunday, November 20, 2011

Backup and Restore Database

Back up From the Command Line (using mysqldump)

If you have shell or telnet access to your web server, you can backup your MySQL data by using the mysqldump command. This command connects to the MySQL server and creates an SQL dump file. The dump file contains the SQL statements necessary to re-create the database. Here is the proper syntax:

$ mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]
  • [uname] Your database username
  • [pass] The password for your database (note there is no space between -p and the password)
  • [dbname] The name of your database
  • [backupfile.sql] The filename for your database backup
  • [--opt] The mysqldump option

For example, to backup a database named 'Tutorials' with the username 'root' and with no password to a file tut_backup.sql, you should accomplish this command:

$ mysqldump -u root -p Tutorials > tut_backup.sql

This command will backup the 'Tutorials' database into a file called tut_backup.sql which will contain all the SQL statements needed to re-create the database.

With mysqldump command you can specify certain tables of your database you want to backup. For example, to back up only php_tutorials and asp_tutorials tables from the 'Tutorials' database accomplish the command below. Each table name has to be separated by space.

$ mysqldump -u root -p Tutorials php_tutorials asp_tutorials > tut_backup.sql

Sometimes it is necessary to back up more that one database at once. In this case you can use the --database option followed by the list of databases you would like to backup. Each database name has to be separated by space.

$ mysqldump -u root -p --databases Tutorials Articles Comments > content_backup.sql

If you want to back up all the databases in the server at one time you should use the --all-databases option. It tells MySQL to dump all the databases it has in storage.

$ mysqldump -u root -p --all-databases > alldb_backup.sql

The mysqldump command has also some other useful options:

--add-drop-table: Tells MySQL to add a DROP TABLE statement before each CREATE TABLE in the dump.

--no-data: Dumps only the database structure, not the contents.

--add-locks: Adds the LOCK TABLES and UNLOCK TABLES statements you can see in the dump file.

The mysqldump command has advantages and disadvantages. The advantages of using mysqldump are that it is simple to use and it takes care of table locking issues for you. The disadvantage is that the command locks tables. If the size of your tables is very big mysqldump can lock out users for a long period of time.

Back up your MySQL Database with Compress

If your mysql database is very big, you might want to compress the output of mysqldump. Just use the mysql backup command below and pipe the output to gzip, then you will get the output as gzip file.

$ mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

If you want to extract the .gz file, use the command below:

$ gunzip [backupfile.sql.gz]

Restoring your MySQL Database

Above we backup the Tutorials database into tut_backup.sql file. To re-create the Tutorials database you should follow two steps:

  • Create an appropriately named database on the target machine
  • Load the file using the mysql command:
$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

Have a look how you can restore your tut_backup.sql file to the Tutorials database.

$ mysql -u root -p Tutorials < tut_backup.sql

To restore compressed backup files you can do the following:

gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

If you need to restore a database that already exists, you'll need to use mysqlimport command. The syntax for mysqlimport is as follows:

mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]

Creating MySQL database on Linux system


Creating MySQL database on Linux system

  1. I assume that you are working from your account and not the root. Start a terminal session and become the superuser (Type su at the prompt and then enter the root password).
  2. Now we'll access the MySQL server. Type:
     mysql -u root -p 
    The system prompts for the MySQL root password that you set up in Installing MySQL on Linux. (Note: This is not the Linux root password but the MySQL root password). Enter the password, which is not displayed for security reasons.
    Once you are successfully logged in, the system prints a welcome message and displays the mysql prompt ... something like
     Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 1 to server version: 3.22.32  Type 'help' for help.  mysql>  
  3. Now we are ready for creating the employees database. Issue the command:
     create database employees; 
    (Note: The command ends with a semi-colon)
  4. An important point to note is that this database is created by the root and so will not be accessible to any other user unless permitted by the root. Thus, in order to use this database from my account (called manish), I have to set the permissions by issuing the following command:
     GRANT ALL ON employees.* TO manish@localhost IDENTIFIED BY "eagle" 
    The above command grants my account (manish@localhost) all the permissions on employees database and sets my password to eagle. You should replace manishwith your user name and choose an appropriate password.
  5. Close the mysql session by typing quit at the prompt. Exit from superuser and come back to your account. (Type exit).
  6. To connect to MySQL from your account, type:
     mysql -u user_name -p 
    Type in the password when prompted. (This password was set by the GRANTS ALL... command above) . The system displays the welcome message once you have successfully logged on to MySQL. Here is how your session should look like:
     [manish@localhost manish]$ mysql -u manish -p Enter password:  Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 3 to server version: 3.22.32  Type 'help' for help.  mysql>  
  7. Typing the command SHOW DATABASES; will list all the databases available on the system. You should get a display similar to:
     mysql> SHOW DATABASES; +----------------+ | Database       | +----------------+ | employees      | | mysql          | | test           | +----------------+ 3 rows in set (0.00 sec) 
  8. Enter quit at the mysql> prompt to come out of the mysql client program.

Friday, November 18, 2011

quick fix of mysqldump error 1044

mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES

To quickly fix this problem use the following switch when running mysqldump for MySQL database backups.

mysqldump -u root -p –all-databases –single-transaction > all.sql

Tuesday, November 15, 2011

Ready to use script for website backup


HowTo: Backup MySQL Databases, Web server Files to a FTP Server Automatically

by Vivek Gite on August 10, 2006 · 104 comments

This is a simple backup solution for people who run their own web server and MySQL database server on a dedicated or VPS server. Most dedicated hosting provider provides backup service using NAS or FTP servers. These service providers will hook you to their redundant centralized storage array over private VLAN. Since, I manage couple of boxes, here is my own automated solution. If you just want a shell script, go here (you just need to provided appropriate input and it will generate FTP backup script for you on fly, you can also grab my php script generator code).

Making Incremental Backups With tar

You can make tape backups. However, sometime tape is not an option. GNU tar allows you to make incremental backups with -g option. In this example, tar command will make incremental backup of /var/www/html, /home, and /etc directories, run:
# tar -g /var/log/tar-incremental.log -zcvf /backup/today.tar.gz /var/www/html /home /etc


  • -g: Create/list/extract new GNU-format incremental backup and store information to /var/log/tar-incremental.log file.

Making MySQL Databases Backup

mysqldump is a client program for dumping or backing up mysql databases, tables and data. For example, the following command displays the list of databases:
$ mysql -u root -h localhost -p -Bse 'show databases'


Enter password: brutelog cake faqs mysql phpads snews test tmp van wp

Next, you can backup each database with the mysqldump command:
$ mysqldump -u root -h localhost -pmypassword faqs | gzip -9 > faqs-db.sql.gz

Creating A Simple Backup System For Your Installation

The main advantage of using FTP or NAS backup is a protection from data loss. You can use various protocols to backup data:

  1. FTP
  2. SSH
  3. RSYNC
  4. Other Commercial solutions

However, I am going to write about FTP backup solution here. The idea is as follows:

  • Make a full backup every Sunday night i.e. backup everything every Sunday
  • Next backup only those files that has been modified since the full backup (incremental backup).
  • This is a seven-day backup cycle.

Our Sample Setup

   Your-server     ===>       ftp/nas server IP:   ===> 

Let us assume that your ftp login details are as follows:

  • FTP server IP:
  • FTP Username: nixcraft
  • FTP Password: somepassword
  • FTP Directory: /home/nixcraft (or /)

You store all data as follows:
=> /home/nixcraft/full/mm-dd-yy/files - Full backup
=> /home/nixcraft/incremental/mm-dd-yy/files - Incremental backup

Automating Backup With tar

Now, you know how to backup files and mysql databases using the tar and mysqldump commands. It is time to write a shell script that will automate entire procedure:

  1. First, our script will collect all data from both MySQL database server and file system into a temporary directory called /backup using a tar command.
  2. Next, script will login to your ftp server and create a directory structure as discussed above.
  3. Script will dump all files from /backup to the ftp server.
  4. Script will remove temporary backup from /backup directory.
  5. Script will send you an email notification if ftp backups failed due to any reason.

You must have the following commands installed (use yum or apt-get package manager to install ftp client called ncftp):

  • ncftp ftp client
  • mysqldump command
  • GNU tar command

Here is the sample script:

#!/bin/sh # System + MySQL backup script # Full backup day - Sun (rest of the day do incremental backup) # Copyright (c) 2005-2006 nixCraft <http://www.cyberciti.biz/fb/> # This script is licensed under GNU GPL version 2.0 or above # Automatically generated by http://bash.cyberciti.biz/backup/wizard-ftp-script.php # --------------------------------------------------------------------- ### System Setup ### DIRS="/home /etc /var/www" BACKUP=/tmp/backup.$$ NOW=$(date +"%d-%m-%Y") INCFILE="/root/tar-inc-backup.dat" DAY=$(date +"%a") FULLBACKUP="Sun" ### MySQL Setup ### MUSER="admin" MPASS="mysqladminpassword" MHOST="localhost" MYSQL="$(which mysql)" MYSQLDUMP="$(which mysqldump)" GZIP="$(which gzip)" ### FTP server Setup ### FTPD="/home/vivek/incremental" FTPU="vivek" FTPP="ftppassword" FTPS="" NCFTP="$(which ncftpput)" ### Other stuff ### EMAILID="admin@theos.in" ### Start Backup for file system ### [ ! -d $BACKUP ] && mkdir -p $BACKUP || : ### See if we want to make a full backup ### if [ "$DAY" == "$FULLBACKUP" ]; then   FTPD="/home/vivek/full"   FILE="fs-full-$NOW.tar.gz"   tar -zcvf $BACKUP/$FILE $DIRS else   i=$(date +"%Hh%Mm%Ss")   FILE="fs-i-$NOW-$i.tar.gz"   tar -g $INCFILE -zcvf $BACKUP/$FILE $DIRS fi ### Start MySQL Backup ### # Get all databases name DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')" for db in $DBS do  FILE=$BACKUP/mysql-$db.$NOW-$(date +"%T").gz  $MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE done ### Dump backup using FTP ### #Start FTP backup using ncftp ncftp -u"$FTPU" -p"$FTPP" $FTPS<<EOF mkdir $FTPD mkdir $FTPD/$NOW cd $FTPD/$NOW lcd $BACKUP mput * quit EOF ### Find out if ftp backup failed or not ### if [ "$?" == "0" ]; then  rm -f $BACKUP/* else  T=/tmp/backup.fail  echo "Date: $(date)">$T  echo "Hostname: $(hostname)" >>$T  echo "Backup failed" >>$T  mail  -s "BACKUP FAILED" "$EMAILID" <$T  rm -f $T fi  

How Do I Setup a Cron Job To Backup Data Automatically?

Just add cron job as per your requirements:
13 0 * * * /home/admin/bin/ftpbackup.sh >/dev/null 2>&1

Generate FTP backup script

Since I setup many Linux boxes, here is my own FTP backup script generator. You just need to provided appropriate input and it will generate FTP backup script for you on fly.