Friday, March 5, 2010

Apache Module mod_access

SkyHi @ Friday, March 05, 2010

Summary

The directives provided by mod_access are used in <Directory>, <Files>, and <Location> sections as well as .htaccess files to control access to particular parts of the server. Access can be controlled based on the client hostname, IP address, or other characteristics of the client request, as captured in environment variables. The Allow and Deny directives are used to specify which clients are or are not allowed access to the server, while the Order directive sets the default access state, and configures how the Allow and Deny directives interact with each other.

Both host-based access restrictions and password-based authentication may be implemented simultaneously. In that case, the Satisfy directive is used to determine how the two sets of restrictions interact.

In general, access restriction directives apply to all access methods (GET, PUT, POST, etc). This is the desired behavior in most cases. However, it is possible to restrict some methods, while leaving other methods unrestricted, by enclosing the directives in a <Limit> section.

Directives

See also

top

Allow Directive

Description:Controls which hosts can access an area of the server
Syntax: Allow from all|host|env=env-variable [host|env=env-variable] ...
Context:directory, .htaccess
Override:Limit
Status:Base
Module:mod_access

The Allow directive affects which hosts can access an area of the server. Access can be controlled by hostname, IP Address, IP Address range, or by other characteristics of the client request captured in environment variables.

The first argument to this directive is always from. The subsequent arguments can take three different forms. If Allow from all is specified, then all hosts are allowed access, subject to the configuration of the Deny and Order directives as discussed below. To allow only particular hosts or groups of hosts to access the server, the host can be specified in any of the following formats:

A (partial) domain-name

Example:

Allow from apache.org

Hosts whose names match, or end in, this string are allowed access. Only complete components are matched, so the above example will match foo.apache.org but it will not match fooapache.org. This configuration will cause the server to perform a reverse DNS lookup on the client IP address, regardless of the setting of the HostnameLookups directive.

A full IP address

Example:

Allow from 10.1.2.3

An IP address of a host allowed access

A partial IP address

Example:

Allow from 10.1

The first 1 to 3 bytes of an IP address, for subnet restriction.

A network/netmask pair

Example:

Allow from 10.1.0.0/255.255.0.0

A network a.b.c.d, and a netmask w.x.y.z. For more fine-grained subnet restriction.

A network/nnn CIDR specification

Example:

Allow from 10.1.0.0/16

Similar to the previous case, except the netmask consists of nnn high-order 1 bits.

Note that the last three examples above match exactly the same set of hosts.

IPv6 addresses and IPv6 subnets can be specified as shown below:

Allow from fe80::a00:20ff:fea7:ccea
Allow from fe80::a00:20ff:fea7:ccea/10

The third format of the arguments to the Allow directive allows access to the server to be controlled based on the existence of an environment variable. When Allow from env=env-variable is specified, then the request is allowed access if the environment variable env-variable exists. The server provides the ability to set environment variables in a flexible way based on characteristics of the client request using the directives provided by mod_setenvif. Therefore, this directive can be used to allow access based on such factors as the clients User-Agent (browser type), Referer, or other HTTP request header fields.

Example:

SetEnvIf User-Agent ^KnockKnock/2.0 let_me_in
<Directory /docroot>
Order Deny,Allow
Deny from all
Allow from env=let_me_in
</Directory>

In this case, browsers with a user-agent string beginning with KnockKnock/2.0 will be allowed access, and all others will be denied.

top

Deny Directive

Description:Controls which hosts are denied access to the server
Syntax: Deny from all|host|env=env-variable [host|env=env-variable] ...
Context:directory, .htaccess
Override:Limit
Status:Base
Module:mod_access

This directive allows access to the server to be restricted based on hostname, IP address, or environment variables. The arguments for the Deny directive are identical to the arguments for the Allow directive.

top

Order Directive

Description:Controls the default access state and the order in which Allow and Deny are evaluated.
Syntax: Order ordering
Default:Order Deny,Allow
Context:directory, .htaccess
Override:Limit
Status:Base
Module:mod_access

The Order directive controls the default access state and the order in which Allow and Deny directives are evaluated. Ordering is one of

Deny,Allow
The Deny directives are evaluated before the Allow directives. Access is allowed by default. Any client which does not match a Deny directive or does match an Allow directive will be allowed access to the server.
Allow,Deny
The Allow directives are evaluated before the Deny directives. Access is denied by default. Any client which does not match an Allow directive or does match a Deny directive will be denied access to the server.
Mutual-failure
Only those hosts which appear on the Allow list and do not appear on the Deny list are granted access. This ordering has the same effect as Order Allow,Deny and is deprecated in favor of that configuration.

Keywords may only be separated by a comma; no whitespace is allowed between them. Note that in all cases every Allow and Deny statement is evaluated.

In the following example, all hosts in the apache.org domain are allowed access; all other hosts are denied access.

Order Deny,Allow
Deny from all
Allow from apache.org

=================================================================================

In the next example, all hosts in the apache.org domain are allowed access, except for the hosts which are in the foo.apache.org subdomain, who are denied access. All hosts not in the apache.org domain are denied access because the default state is to deny access to the server.

Order Allow,Deny
Allow from apache.org
Deny from foo.apache.org

On the other hand, if the Order in the last example is changed to Deny,Allow, all hosts will be allowed access. This happens because, regardless of the actual ordering of the directives in the configuration file, the Allow from apache.org will be evaluated last and will override the Deny from foo.apache.org. All hosts not in the apache.org domain will also be allowed access because the default state will change to allow.

The presence of an Order directive can affect access to a part of the server even in the absence of accompanying Allow and Deny directives because of its effect on the default access state. For example,

<Directory /www>
Order Allow,Deny
</Directory>

will deny all access to the /www directory because the default access state will be set to deny.

The Order directive controls the order of access directive processing only within each phase of the server's configuration processing. This implies, for example, that an Allow or Deny directive occurring in a <Location> section will always be evaluated after an Allow or Deny directive occurring in a <Directory> section or .htaccess file, regardless of the setting of the Order directive. For details on the merging of configuration sections, see the documentation on How Directory, Location and Files sections work.


REFERENCE

http://213.11.80.10/manual/mod/mod_access.html#allow



Thursday, March 4, 2010

HowTo mount cdrom in linux

SkyHi @ Thursday, March 04, 2010
CD's and DVDs are using iso9660 filesytem. As any other operating system, Linux is also able to read data from standard iso9660 filesystem. Let's see how we can mount CD-ROM or DVD-ROM with use of mount command.
1. Detecting CD-ROM/DVD-ROM drive

If we are not sure which block device is our DVD-ROM or CD-ROM we can find it out by running a following wodim command:

# wodim --devices

If the wodim command is not available on your system make sure that package cdrecord is installed. After running wodim command you should get output similar to one below:

# wodim -devices
wodim: Overview of accessible drives (1 found) :
----------------------------------------------------------
0 dev='/dev/sdc' rwrw-- : 'TSSTcorp' 'CD/DVDW SH-S183L'
----------------------------------------------------------

Well, now it's clear that my DVD-ROM drive is know by system as a block device /dev/hdc. Note different systems may behave differently. For example on Debian Linux a user runing wodim command must belong to cdrom group otherwise the wodim command will fail with a following error message:

wodim: No such file or directory.
Cannot open SCSI driver!
For possible targets try 'wodim --devices'
or 'wodim -scanbus'.
For possible transport specifiers try 'wodim
dev=help'.
For IDE/ATAPI devices configuration,
see the file README.ATAPI.setup from
the wodim documentation.

2. Testing for iso9660 compatibility

Lets see if my current kernel supports iso9660 filesystem.

# cat /proc/filesystems

This command should report available iso9660 filesystem.
Testing for iso9660 compatibility
3. Creating mount point

Probably there is already mount point on you system. Usually it is /cdrom, /media/cdrom, /media/cdrom0 or /mnt/cdrom . If this not your case create a new mount point now.

# mkdir /media/cdrom0

4. Mounting CD

As a root user issue a command:

# mount -t iso9660 /dev/hdc /media/cdrom0/

Mounting CD using linux
your CD is now mounted as /media/cdrom0/. Navigate to this directory to see its content.
5. Mounting Audio CD's

Sometimes you would like to listen to your favorite music and when you try mount music cd with a command above you can get problems like:

linuxconfig.org# mount -t iso9660 /dev/hdc /media/cdrom0/
mount: block device /dev/hdc is write-protected, mounting read-only
mount: wrong fs type, bad option, bad superblock on /dev/hdc,
missing codepage or other error
In some cases useful info is found in syslog - try
dmesg | tail or so

linuxconfig.org#

This is because music CD's does not contain standard iso9660 filesystem. In fact the story with music cd's is easier as a story with data CD's with iso9660 filesystem. In order to listen to music CD just insert it into CD-ROM/DVD-ROM drive and fire up you favorite music CD player. Only thing you need to be concern about is whether you have "kdemultimedia-kio-plugins" for KDE or in case of gnome "gnome-media" packages installed which also helps you to browse CD content. In case you would like to see content of music CD just start "KONQUEROR" and enter location:

audiocd:/

6. Altering /etc/fstab

To allow users on the system mount CD's or DVD's alter your /etc/fstab and add line similar to the one below:

/dev/hdc /media/cdrom0 iso9660 ro,user,noauto 0 0

7. Unmount and eject CD

There are couple ways how to remove CD/DVD from a drive.

# umout /dev/hdc
# eject

or you can simply run

# eject

If you are unable to unmount, make sure, that you are not in the directory where your DVD-ROM is mounted or that some other application is not using it. If you still have a problem to unmount you DVD-ROM medium you can use fuser command to kill all related processes using your device.

# fuser -mk /dev/hdc
# eject

8. Video


REFERENCES
http://www.linuxconfig.org/HowTo_mount_cdrom_in_linux

Wednesday, March 3, 2010

sudo: sorry, you must have a tty to run sudo

SkyHi @ Wednesday, March 03, 2010

Per customer request, we recently wrote a script to restart their application server around 3AM via crontab every night. We were alerted that their server did not come back up this morning. After investigating, we found the following output in the log file:

sudo: sorry, you must have a tty to run sudo

If you ever encounter this error, you need to use the ‘visudo’ command and locate the following line:

Defaults    requiretty<br />

Just comment out the line, and you will resolve the issue.

If this option is set, sudo will only run when the user is logged in to a real tty. When this flag is set, sudo can only be run from a login session and not via other means such as cron or cgi-bin scripts.


REFERENCE

http://www.adminmyserver.com/articles/sorry-you-must-have-a-tty-to-run-sudo



HOWTO CentOS 5.x Virtual Web Hosting With Apache MYSQL and ProFTPD

SkyHi @ Wednesday, March 03, 2010

Introduction

There are many ways to do virtual websites under linux. The technique I am going to use is multiple domains on one ip address. I also didn't want to use system users for the virtual hosts. So I decided to use a mysql database to store the virtual user information.

Setting Up The Base Server

For a dedicated server start with the base server setup for CentOS 5.x.

Install Softwre

We need to install is proftpd with mysql support. Type:
> yum -y remove vsftpd
> yum -y install proftpd proftpd-mysql

Now lets download and install proftpd admin.
> wget http://downloads.sourceforge.net/proftpd-adm/proftpd_admin_v1.2.tar.gz
> tar -xzvf proftpd_admin_v1.2.tar.gz
> mv proftpd_admin_v1.2 /usr/share/proftpd_admin

Configuring Apache

  1. First we need to create a user called virtwww. We will also make the directory world writable.
    > adduser -m virtwww
    > passwd virtwww
    > chmod a+rwx /home/virtwww
  2. Create the virtual host config file. We will use the name the brouser passes to us to determin the website to load. Type
    > echo "UseCanonicalName Off" > /etc/httpd/conf.d/virtual.conf
    > echo "VirtualDocumentRoot /home/virtwww/%0" >> /etc/httpd/conf.d/virtual.conf
  3. restart apache
    > service httpd restart
  4. Make a directory with your server's ipaddress as its name. Example
    > mkdir /home/virtwww/192.168.1.100
  5. create a test index.html page in your new directory.

Configuring ProFTPD

  1. Create the following proftpd config file
    > mv proftpd.conf proftpd.conf.old
    > nano -w /etc/proftpd.conf
 
# This is the ProFTPD configuration file

# Load modules for sql support
LoadModule mod_sql.c
LoadModule mod_sql_mysql.c

ServerName "Servername"
ServerType standalone
ServerIdent on "Servers identifying string"
DeferWelcome off
DefaultServer on
DefaultRoot ~ !adm
AllowOverwrite on
UseReverseDNS off
IdentLookups off

Port 21
Umask 022
MaxInstances 15
MaxClientsPerHost 3 "Only %m connections per host allowed"
MaxClients 10 "Only %m total simultanious logins allowed"
MaxHostsPerUser 1

User ftp
Group ftp

ScoreboardFile /var/run/proftpd.score

# Some logging formats
LogFormat default "%h %l %u %t \"%r\" %s %b"
LogFormat auth "%v [%P] %h %t \"%r\" %s"
LogFormat write "%h %l %u %t \"%r\" %s %b"

# Define log-files to use
TransferLog /var/log/proftpd/xferlog
ExtendedLog /var/log/proftpd/access_log WRITE,READ write
ExtendedLog /var/log/proftpd/auth_log AUTH auth
ExtendedLog /var/log/proftpd/paranoid_log ALL default
SQLLogFile /var/log/proftpd/mysql

# Set up authentication via SQL
# ===========
AuthOrder mod_sql.c
SQLAuthTypes Backend
SQLConnectInfo proftpd_admin@localhost proftpd password
SQLUserInfo usertable userid passwd uid gid homedir shell
SQLGroupInfo grouptable groupname gid members
SQLUserWhereClause "disabled=0 and (NOW()<=expiration or expiration=-1 or expiration=0)"
SQLHomedirOnDemand on

# Log the user logging in
SQLLog PASS counter
SQLNamedQuery counter UPDATE "lastlogin=now(), count=count+1 WHERE userid='%u'" usertable

# logout log
SQLLog EXIT time_logout
SQLNamedQuery time_logout UPDATE "lastlogout=now() WHERE userid='%u'" usertable

# display last login time when PASS command is given
SQLNamedQuery login_time SELECT "lastlogin from usertable where userid='%u'"
SQLShowInfo PASS "230" "Last login was: %{login_time}"

# xfer Log in mysql
SQLLog RETR,STOR transfer1
SQLNamedQuery transfer1 INSERT "'%u', '%f', '%b', '%h', '%a', '%m', '%T', now(), 'c', NULL" xfer_stat
SQLLOG ERR_RETR,ERR_STOR transfer2
SQLNamedQuery transfer2 INSERT "'%u', '%f', '%b', '%h', '%a', '%m', '%T', now(), 'i', NULL" xfer_stat

AllowStoreRestart on
AllowRetrieveRestart on
RequireValidShell off
#RootLogin off

# Normally, we want users to do a few things.
<Global>
AllowOverwrite yes
<Limit ALL SITE_CHMOD>
AllowAll
</Limit>
</Global>

Configuring ProFTPD Administrator

First lets create the apache config file and restart apache. The file has been set to only allow access from the local host. Change the access to meet your needs.

/etc/httpd/conf.d/proftpd_admin.conf

 <br />alias /ftpadmin /usr/share/proftpd_admin<br /><br /><Location /ftpadmin><br />    Order deny,allow<br />    Deny from all<br />    Allow from 127.0.0.1<br />    Allow from ::1<br />    # Allow from .example.com<br /></Location><br /><br />

> service httpd restart

Edit the file /usr/share/proftpd_admin/misc/database_structure_mysql/db_structure.sql
Skip to the last three lines. Enter the desired password in these three lines.

Lets create the database and tables. Type the following.
> mysql -u root -p < /usr/share/proftpd_admin/misc/database_structure_mysql/db_structure.sql

Now type the following to do a little housekeeping for php5.
> chmod o+w /usr/share/proftpd_admin/configuration.xml

Now start proftpd. Be sure to also have it start at boot time.
> service proftpd start

Now go to the web interface configuration screen http://yourserver.tld/ftpadmin/configure.php
You will need to configure database access and some other settings.

Conclusion

That's the complete setup. Proftpd Admin dose almost everything we need. You will need to create links for multiple host names pointing to the same directory. For example if your directory is '/home/virtwww/yourdomain.com' and you want www.yourdomain.com to work then you'll have to create a link called '/home/virtwww/www.yourdomain.com/' which points to '/home/virtwww/yourdomain.com'.


REFERENCE

http://www.campworld.net/thewiki/pmwiki.php/LinuxServersCentOS/Cent5VirtWebServer



Setting Up ProFtpd with MySQL

SkyHi @ Wednesday, March 03, 2010
Prerequisites : MySQL 5 & PHP 5
Download proftpd source
wget ftp://ftp-stud.fht-esslingen.de/pub/Mirrors/ftp.proftpd.net/distrib/source/proftpd-1.3.1.tar.gz
tar xzvf proftpd-1.3.1.tar.gz
cd proftpd-1.3.1

Configure using:
install_user=root install_group=wheel ./configure --with-modules=mod_sql:mod_sql_mysql:mod_quotatab:mod_quotatab_sql --with-includes=/usr/include/mysql/ --with-libraries=/usr/lib/mysql/ --enable-timeout-linger --enable-timeout-stalled --sysconfdir=/etc --localstatedir=/var --prefix=/usr

make & make install Create the /etc/init.d/proftpd as
#!/bin/sh
# $Id: proftpd.init,v 1.1 2004/02/26 17:54:30 thias Exp $
#
# proftpd This shell script takes care of starting and stopping
# proftpd.
#
# chkconfig: - 80 30
# description: ProFTPD is an enhanced FTP server with a focus towards \
# simplicity, security, and ease of configuration. \
# It features a very Apache-like configuration syntax, \
# and a highly customizable server infrastructure, \
# including support for multiple 'virtual' FTP servers, \
# anonymous FTP, and permission-based directory visibility.
# processname: proftpd
# config: /etc/proftp.conf
# pidfile: /var/run/proftpd.pid

# Source function library.
. /etc/rc.d/init.d/functions

# Source networking configuration.
. /etc/sysconfig/network

# Check that networking is up.
[ ${NETWORKING} = "no" ] && exit 0

[ -x /usr/sbin/proftpd ] || exit 0

RETVAL=0

prog="proftpd"

start() {
echo -n $"Starting $prog: "
daemon proftpd
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && touch /var/lock/subsys/proftpd
}

stop() {
echo -n $"Shutting down $prog: "
killproc proftpd
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/proftpd
}

# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
status)
status proftpd
RETVAL=$?
;;
restart)
stop
start
;;
condrestart)
if [ -f /var/lock/subsys/proftpd ]; then
stop
start
fi
;;
reload)
echo -n $"Re-reading $prog configuration: "
killproc proftpd -HUP
RETVAL=$?
echo
;;
*)
echo "Usage: $prog {start|stop|restart|reload|condrestart|status}"
exit 1
esac

exit $RETVAL

chmod 755 /etc/init.d/proftpd

Create /etc/proftpd.conf file as

ServerName "FTP Server"
ServerType standalone
ServerIdent on "Servers identifying string"
DeferWelcome on
DefaultServer on

DisplayLogin .welcome # Textfile to display on login
DisplayConnect .connect # Textfile to display on connection
DisplayChdir .firstchdir # Textfile to display on first changedir

UseReverseDNS off
IdentLookups off

Port 21
Umask 002
MaxInstances 15
MaxClientsPerHost 3 "Only %m connections per host allowed"
MaxClients 30 "Only %m total simultanious logins allowed"
MaxHostsPerUser 1

User nobody
Group nobody

ScoreboardFile /var/proftpd/proftpd.scoreboard

AllowStoreRestart on
AllowRetrieveRestart on
RequireValidShell off
PathDenyFilter "\\.ftp)|\\.ht)[a-z]+$"
DefaultRoot ~
DenyFilter \*.*/

# Some logging formats
LogFormat default "%h %l %u %t \"%r\" %s %b"
LogFormat auth "%v [%P] %h %t \"%r\" %s"
LogFormat write "%h %l %u %t \"%r\" %s %b"

# Define log-files to use
TransferLog /var/log/proftpd/proftpd.xferlog
ExtendedLog /var/log/proftpd/proftpd.access_log WRITE,READ write
ExtendedLog /var/log/proftpd/proftpd.auth_log AUTH auth
ExtendedLog /var/log/proftpd/proftpd.paranoid_log ALL default
SQLLogFile /var/log/proftpd/proftpd.mysql

# Set up authentication via SQL
# ===========
AuthOrder mod_sql.c
SQLAuthTypes Backend
#===========================
# CHANGE THE FOLLOWING LINE TO REFLECT YOUR INSTALLATION
#===========================
SQLConnectInfo proftpd_admin@localhost proftpd proftpd
#===========================
SQLUserInfo usertable userid passwd uid gid homedir shell
SQLGroupInfo grouptable groupname gid members
SQLUserWhereClause "disabled=0 and (NOW()<=expiration or expiration=-1 or expiration=0)"

# Log the user logging in
SQLLog PASS counter
SQLNamedQuery counter UPDATE "lastlogin=now(), count=count+1 WHERE userid='%u'" usertable

# logout log
SQLLog EXIT time_logout
SQLNamedQuery time_logout UPDATE "lastlogout=now() WHERE userid='%u'" usertable

# display last login time when PASS command is given
SQLNamedQuery login_time SELECT "lastlogin from usertable where userid='%u'"
SQLShowInfo PASS "230" "Last login was: %{login_time}"

# xfer Log in mysql
SQLLog RETR,STOR transfer1
SQLNamedQuery transfer1 INSERT "'%u', '%f', '%b', '%h', '%a', '%m', '%T', now(), 'c', NULL" xfer_stat
SQLLOG ERR_RETR,ERR_STOR transfer2
SQLNamedQuery transfer2 INSERT "'%u', '%f', '%b', '%h', '%a', '%m', '%T', now(), 'i', NULL" xfer_stat

# Normally, we want users to do a few things.
<Global>
AllowOverwrite yes
<Limit ALL SITE_CHMOD>
AllowAll
</Limit>
</Global>

mkdir /var/log/proftpd/
Create /etc/logrotate.d/proftpd as
/var/log/proftpd/*log {
compress
missingok
postrotate
test -f /var/lock/subsys/proftpd && /usr/bin/killall -HUP proftpd
endscript
}
/var/log/proftpd/proftpd.mysql {
compress
missingok
postrotate
test -f /var/lock/subsys/proftpd && /usr/bin/killall -HUP proftpd
endscript
}

Dowload proFTPd Administrator (http://proftpd-adm.sourceforge.net/)

cd /var/www/html
tar xzvf proftpd_admin_v1.2.tar.gz
rm proftpd_admin_v1.2.tar.gz
mv proftpd_admin_v1.2/ proftpd_admin/

cd proftpd_admin

edit misc/database_structure_mysql
and change the last 3 lines according to the credentials you have set in /etc/proftpd.conf and then :
mysql -u root -p < misc/database_structure_mysql/db_structure.sql
chmod o+w /var/www/html/configuration.xml
Edit misc/user_script/create_user.sh to
#!/bin/bash
USER=$1
USER_ID=$2
GROUP_ID=$3

mkdir -p --mode=775 /ftp/$USER
chown $USER_ID.$GROUP_ID /ftp/$USER

Then :

chown -R root.root /var/www/html/proftpd_admin/misc/user_script
chmod 700 -R /var/www/html/proftpd_admin/misc/user_script

edit /etc/sudoers files and add the following 2 lines at the end

# Cmnd alias specification
Cmnd_Alias CREATE_USER = /var/www/html/proftpd_admin/misc/user_script/create_user.sh

# User privilege specification
apache ALL=(ALL) NOPASSWD: CREATE_USER

Also make sure that Defaults requiretty is commented out

Securing Installation of Proftpd Admin

vi /etc/httpd/conf.d/dirSecurity.conf

# Secure proftpd_directory both by ip and password
<Directory "/var/www/html/proftpd_admin">
deny from all
Order deny,allow
Allow from <any safe IP>
AuthType Basic
AuthUserFile /etc/httpd/conf/proftpd.htpasswd
AuthName Administration
require valid-user
satisfy All

</Directory>

Then execute

htpasswd -b -c /etc/httpd/conf/proftpd.htpasswd proftp <password>

/etc/init.d/httpd restart
/etc/init.d/mysqld restart
/etc/init.d/proftpd start

And point your browser to http://<server-ip>/proftpd-admin and supply the username & password you created.
Go to the Configure Tab and then the ProFTPd tab and set

Create user command: sudo /var/www/html/proftpd_admin/misc/user_script/create_user.sh


REFERENCE

http://www.thanosk.net/node/5



Monday, March 1, 2010

Fast restore one database from a dump of ALL databases

SkyHi @ Monday, March 01, 2010
There have been times that I needed to restore one database from a dump file that contains all of the databases from the server. There are a few ways to accomplish this goal. One way would be to grep through the entire file for the table schema and insert statements. There are some problems with this method in some environments. For example, what if a table name in the targeted databases had the same name in another database on the same instance? The logic for grep now gets increasingly more complex.

It’s a good thing that the developers at MySQL already thought of this and implemented it, --one-database. In the example below I have created 4 databases, test, test[1-3] and filled each of them with 10K rows. I run mysqldump for all databases and put them in a file called test.all.sql then truncate test2.tbl1. Using mysql with the --one-database option I restore the data to its original state.

Example:

mysql> select min(anum), max(anum) from test.tbl1;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| 0 | 10000 |
+-----------+-----------+
1 row in set (0.01 sec)

mysql> select min(anum), max(anum) from test1.tbl1;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| 10001 | 20000 |
+-----------+-----------+
1 row in set (0.01 sec)

mysql> select min(anum), max(anum) from test2.tbl1;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| 20001 | 30000 |
+-----------+-----------+
1 row in set (0.01 sec)

mysql> select min(anum), max(anum) from test3.tbl1;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| 30001 | 40000 |
+-----------+-----------+
1 row in set (0.01 sec)

shell> mysqldump -uroot -S mysql.sock --opt --all-databases > test.all.sql

mysql> use test2;

mysql> select count(*) from tbl1;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.01 sec)

mysql> truncate table tbl1;
Query OK, 0 rows affected (0.05 sec)

mysql> select count(*) from tbl1;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

shell> mysql -uroot -S mysql.sock --one-database test2 < test.all.sql

mysql> select min(anum), max(anum) from test.tbl1;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| 0 | 10000 |
+-----------+-----------+
1 row in set (0.01 sec)

mysql> select min(anum), max(anum) from test1.tbl1;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| 10001 | 20000 |
+-----------+-----------+
1 row in set (0.01 sec)

mysql> select min(anum), max(anum) from test2.tbl1;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| 20001 | 30000 |
+-----------+-----------+
1 row in set (0.01 sec)

mysql> select min(anum), max(anum) from test3.tbl1;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| 30001 | 40000 |
+-----------+-----------+
1 row in set (0.01 sec)

As you can see test2.tbl1 has the original data back in and we did not have to write our own script to accomplish this.

REFERENCE
https://www.joomla-webmasters.com/index/articles/open-sourcenews/49-mysql-news/3781-fast-restore-one-database-from-a-dump-of-all-databases.html

mysql command line: specify password

SkyHi @ Monday, March 01, 2010
Error:
password first letter contain # $ @ *

[root@home ]# mysql -u root -h ford -p#$49asdfasd
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)


solution:
After some more testing i come to notice a few things. I reloaded the system and this time i did not do any updates. Also i noticed that the password for the the mysql does not like symbols like # $ @ *.