Simple backup script for remotely backing up databases

This backup script is actually a small modification of another backup script available in Google. I am not able to get that URL to mark/link reference. Only modification I added is a four liner to remove a week old backup automatically. You may also see http://www.supportsages.com/blog/2011/05/applying-wildcards-in-grant-option-of-mysql/ as well. Indenting in python is lost, will fix tomorrow

#!/usr/bin/env python
import ConfigParser
import os
import time

# Variable Definition
username = 'cpdbbackups'
password = 'p@ssw0Rd'
hostname = 're.mo.te.ip
backupfolder = '/home/dbbackups/servername'

filestamp = time.strftime('%Y-%m-%d-%H')
deletetime = time.time() - 7 * 86400

# Delete old files

for backup_file in os.listdir (backupfolder):
 full_file_path = os.path.join(backupfolder, backup_file)
 if os.path.getmtime(full_file_path) < deletetime:
 os.unlink(full_file_path)

# Get a list of databases with :
database_list_command="mysql -u%s -p%s -h %s --silent -N -e 'show databases'" % (username, password, hostname)
for database in os.popen(database_list_command).readlines():
 database = database.strip()

 if database == 'information_schema':
 continue
 filename = "%s/%s-%s.sql" % (backupfolder, database, filestamp)
 os.popen("mysqldump -u%s -p%s -h %s -e --opt -c %s | gzip -c > %s.gz" % (username, password, hostname, database, filename))

Applying wildcards in GRANT option of MySQL

We had this particular requirement of creating a database user to be used to backup only the cPanel databases. Only recently the database mapping has been introduced by cPanel which allows the clients to create databases without the _ . However on all our client servers, we insist to have the old style with every database has Prefixing On.

Creating the database user to backup only the cPanel databases, means matching the databases with an underscore (_) in its name and that resulted in this particular SQL command to be executed as root user.

GRANT ALL PRIVILEGES ON `%\_%`.* TO `cpdbbackups`@`re.mo.te.ip` IDENTIFIED BY 'p@ssw0Rd' WITH GRANT OPTION;

Read more about the cPanel’s DB mapping at http://www.cpanel.net/blog/integration/2010/05/more-details-about-db-mapping.html

MySQL for Beginners

In this post, I’ll explain some basic MySQL tips that would be very useful for beginners

Reset MySQL root password

Log in as Root and Stop the MySQL daemon. Then Start the MySQL daemon and skip the grant tables which store the passwords.

# /etc/init.d/mysql stop

# mysqld_safe --skip-grant-tables

# mysql -u root

mysql> use mysql;

Now you should be able to connect to mysql without a password. If not, it might be some other issues. Execute the command to reset the password

mysql> update user set Password=PASSWORD('new-password') where user='root';
mysql> flush privileges;
mysql> exit

After this you need to kill the running mysqld and restart it normally.

Creation of MySQL database, user and access rights

It should be very easy to create a database through cpanel or some other panel. But if you are on your own with only Shell access, try this :

# mysql -u root -p
 mysql> use mysql;
 mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES  ('%','databasename','username','Y','Y','Y','Y','Y','N');
 mysql> flush privileges;

Or you can use

mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

MySQLdump and Restore

The mysqldump client is a database backup.  It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both.

Creating a Mysqldump is quite easy. If you know the database name, say database :

# mysqldump database > database.sql

When restoring a Database, make sure you login as the user. In other words, do not restore a database with root privileges. It may cause real damage. Assuming that you are logged in as the user

<strong> </strong>
mysql> use dbname; //Which is the database which the backup has to be restored to
mysql > source olddb.sql; //Backup

Repair a corrupted Database

Switch to the database directory which is having issues with, Like :

# cd /var/lib/mysql/database

Stop the MySQL server

# /etc/init.d/mysql stop

Check the tables

# myisamchk *.MYI

Repair the tables

# myisamchk -r *.MYI

Start the MySQL server

# /etc/init.d/mysql start

phpMyadmin

phpMyAdmin is an open source tool written in PHP intended to handle the administration of MySQL over the World Wide Web. It can perform various tasks such as creating, modifying or deleting databases, tables, fields or rows; executing SQL statements; or managing users and permissions.

If you are having a cPanel or WHM interface, its quite easy to manage a database. In cPanel interface, go to Databases section

This is the main phpMyadmin page.

In WHM, at SQL Services you can perform a variety of options. As mentioned earlier, the database repair option (for corrupted databases) can be performed here also. Access the option ‘Repair a Database’

Select the Database name and Click Repair Database.

These are quite simple tasks associated with MySQL operations.

How to list out all the mysql database tables having the storage engine of INNODB

Simple Issue the mysql command

SELECT table_schema, table_name  FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';

MySQL Storage Engines – an overview, their limitations and an attempt for comparison

Most of us are using Mysql database and majority don’t know how to choose the data base engines, what are the different types of storage engines available in mysql and how they differ from each other. In this article let me give you a brief idea about the Storage Engines and what are the limitations and where to use these various storage engines.

One of the greatest things about MySQL, other than being free, widely supported and fast, is the flexibility of choosing different storage engines for different tables. These storage engines act as handlers for different table types. Thus MySQL storage engines include both those that handle transaction-safe tables and those that handle non-transaction-safe tables along with many others. MySQL does this through their Pluggable Storage Engine Architecture.

MySQL Storage Engines Overview

To determine which storage engines your server supports, we use the SHOW ENGINES statement. The value in the Support column indicates whether an engine can be used. A value of YES, NO, or DEFAULT indicates that an engine is available, not available, or available and currently set as the default storage engine. (Read the rest of this entry…)