Creating a SSH Tunnel

Posted by | Posted in Command-Line, Guides, Linux, OS X, Server, SSH, Tunneling, Ubuntu | Posted on 01-02-2011

To traverse firewalls that block incoming SSH connections or to access a computer with a non routable IP address, you can create an SSH tunnel. When creating a SSH tunnel, you’ll generate the tunnel on the host system and connect it to another system.

With the SSH command, you’ll use the “-R” flag, that allows reversible communications or a reverse tunnel.

This command that I normally use:

It states, create a reversible SSH tunnel from this localhost port 22 to USER@SERVER on port 10000.

 
/usr/bin/ssh -R 10000:localhost:22 USER@SERVER

You’ll need to leave this connection open otherwise the tunnel will collapse. Normally I execute a program at the other end to keep the connection alive. Some SSHd configurations will drop connections due to inactivity.

To connect to the tunnel from USER@SERVER:

use ssh to connect to your USER account on the host server via port 10000.

 
/usr/bin/ssh USER@localhost -p 10000

this will give you the login prompt at the host server. User your login information or you can also setup SSH Keys.

PHP and $argv

Posted by | Posted in Code, Command-Line, Functions, Guides, Linux, OS X, PHP, Server, Ubuntu | Posted on 31-01-2011

I’ve been adding $argv functionality to all of my PHP maintenance scripts. I am doing this, so I don’t have to edit a variable each time I want to execute it.

A PHP $argv example.

 
#!/usr/bin/php
< ? PHP
 
#	test.php
#	demonstration of $argv
#	Dan McCoy
#	January 31 2011
 
$command = $argv[1];
 
$GLOBALS['script'] = basename($argv[0]);
$GLOBALS['ver'] = "1.0";
$GLOBALS['file'] = $argv[2];
 
function help() {
	echo "Help for ". $GLOBALS['script'] ."\n";
 
	echo "-h \t Help prompt \n";
	echo "-v \t Version ". $GLOBALS['ver'] ."\n";
 
	echo "-g \t Grab file from X \n";
	echo "-f \t Reformat data from X \n";
	echo "-l \t Load data from X \n";
 
}// end help
 
function grab(){
	@exec('/opt/local/bin/wget http://webserver.com/page.php -O '. $GLOBALS['file'],$retval);
	return "Grab completed ". $retval[0] ." \n";
}// end grab
 
function reformat() {
	$fp = $GLOBALS['file'];
	$file = fopen($fp,'r');
	$data = fread($file,filesize($fp));
	fclose($file);
 
	$data = str_replace("<br /><br />","\n\n",$data);
	$data = str_replace("<br />","\n",$data);
	$data = strip_tags($data);
	$data = str_replace("\t","",$data);
 
	$fp = $GLOBALS['file'];
	$file = fopen($fp,'w');
	fwrite($file,$data);
	fclose($file);
 
	return "Reformating completed \n";
}// end reformat
 
function load() {
        $fp = $GLOBALS['file'];
        $file = fopen($fp,'r');
        $data = fread($file,filesize($fp));
        fclose($file);
	return $data;
}
 
function delete() {
	$ret = @unlink($GLOBALS['file']);
 
	if ($ret==TRUE){
		return "File has been deleted \n";
	}else{
		return "There was an ERROR deleting ". $GLOBALS['file']."\n";        
	}
}// end delete
 
function switch_default() {
	echo "Command not found \n";
	help();
}
 
switch($command) {
	case "-h":
	        echo help();
	break;
 
	case "-g":
        	echo grab();
	break;
 
	case "-f":
		echo reformat();
	break;
 
	case "-l":
		echo load();
	break;
 
	case "-d":
		echo delete();
	break;
 
	default:
		switch_default();
	break;	
}// end switch
 
? >

Change the default crontab editor – Bash

Posted by | Posted in Bash, Code, Command-Line, Cron, Guides, Linux, OS X | Posted on 30-01-2011

You can change your default crontab (crontab -e) by setting the EDITOR environment variable in BASH:

hostname# which vi
/usr/bin/vi
hostname# export EDITOR=/usr/bin/vi
hostname# set | grep EDITOR
EDITOR=/usr/bin/vi
_=EDITOR
hostname#

Activating Mod_Userdir.c in Apache2 – Ubuntu

Posted by | Posted in Command-Line, Guides, Linux, Ubuntu | Posted on 16-09-2009

This morning, I was working on allowing users of my web server to create web pages with in their home directory. Normally this is indicated by a URL of http://server.com/~useraccount and is activated by default. But during the Apache install process the module for modifying the user directory wasn’t activated.

List of things you’ll need to do.

1) Add the module to your virtual host.
2) Enable the module in apache2
3) Restart apache2
4) Create a web directory folder in your home folder
5) make sure your permissions are correct

Step 1, adding the module to your virtual host.

Normally in Ubuntu, your enabled virtual hosts (or virtual hosts that are current running) are located in /etc/apache2/sites-enabled/your_domain_naming_scheme. For this example, I’ll always name my virtual hosts after their qualified domain name (qdn).

sudo nano /etc/apache2/sites-enabled/server.com

With in your < virtualhost > < / virtualhost > tags, you need to add and If Module statement. An If Module states, that if the module exists in the apache lib directory, to go ahead and load it into ram. The common use of the UserDir statement is command and then directory. So in this case UserDir public_html. public_html is the directory that will need to be created in each user’s home directory in order for apache to map the URL to file systems correctly. In this case the URL of http://server.com/~dmccoy will be mapped to the file directory path of /home/dmccoy/public_html/.

        < IfModule mod_userdir.c >
                UserDir html
        < /IfModule >

a complete virtual host might look something like this.

NameVirtualHost *:80
< VirtualHost server.com:80 >
        ServerAdmin dmccoy@server.com
 
        DocumentRoot /var/www/server.com
        ServerName server.com
        ServerAlias server
 
        < Directory />
                Options FollowSymLinks
                AllowOverride None
        < /Directory>
        < Directory /var/www/server.com/ >
                Options Indexes FollowSymLinks MultiViews
                AllowOverride None
                Order allow,deny
                allow from all
                # This directive allows us to have apache2's default start page
               # in /apache2-default/, but still have / go to the right place
              #RedirectMatch ^/$ /apache2-default/
        < /Directory >
 
        < IfModule mod_userdir.c >
                UserDir html
        < /IfModule >
 
        ScriptAlias /cgi-bin/ /usr/lib/cgi-bin/
        < Directory "/usr/lib/cgi-bin" >
                AllowOverride None
                Options ExecCGI -MultiViews +SymLinksIfOwnerMatch
                Order allow,deny
                Allow from all
        < /Directory >
 
        ErrorLog /var/log/apache2/server.com-error.log
# Possible values include: debug, info, notice, warn, error, crit,
        # alert, emerg.
        LogLevel warn
 
        CustomLog /var/log/apache2/server.com-access.log combined
        ServerSignature On
 
   Alias /doc/ "/usr/share/doc/"
    < Directory "/usr/share/doc/" >
        Options Indexes MultiViews FollowSymLinks
        AllowOverride None
        Order deny,allow
        Deny from all
        Allow from 127.0.0.0/255.0.0.0 ::1/128
    < /Directory >
< /VirtualHost >

Step 2, Once you’ve told the virtual host to load the UserDir module, you’ll need to make sure it is enabled in apache2. You can find this out by looking in the /etc/apache2/mods-enabled/ directory.

Both of these files will need to be in the mods-enabled directory.

userdir.conf
userdir.load

If they are not, make sure they are in the mods-available directory located at /etc/apache2/mods-available/. If they are in the mods-available directory, all you need to do is symbolically link them into the mods-enabled directory.

sudo ln -s /etc/apache2/mods-available/userdir.conf /etc/apache2/mods-enabled/userdir.conf
sudo ln -s /etc/apache2/mods-available/userdir.load /etc/apache2/mods-enabled/userdir.load

Step 3, You’ll need to restart apache2 in order to activate the userdir module.

sudo /etc/init.d/apache2 restart

Step 4, Now that the userdir module is active you can now add the “public_html” directory to any user that wants a website.

Normal command line steps might look something like this.

For yourself

 
cd ~
mkdir public_html
echo "test" > public_html/index.html

Or for another account

 
sudo mkdir /home/USER/public_html
sudo echo "test" > home/USER/public_html/index.html

Step 5, You should now be able to type in your browser http://server.com/~dmccoy and see “test” if you get a Forbidden error, you’ll need to change the permissions on your home directory folder and your public_html folder.

 
chmod 701 /home/dmccoy
chmod 705 /home/dmccoy/public_html

Repeat steps 4 and 5 for every account that would like web access.

Apache 2 Module mod_userdir
Apache 1.3 Module mod_userdir
Per-user web directories

Reset root password in Ubuntu (or Debian)

Posted by | Posted in Command-Line, Guides, Linux, Ubuntu | Posted on 28-07-2009

When you lose/misplace/forget your root password on a RedHat-based linux distro, resetting the root password isn’t too difficult. I’ve added those instructions at the bottom in case you are interested.

In Debian distros (which includes Ubuntu) you are required to have the root password in order to change the root password (even when you boot into single user mode, as you would with Redhat versions). While this adds a level of security, it also adds a level of annoyance when you have lost your password and are in a pinch.

Not to worry though, there is a way around (albeit, not as easy).

The first steps are the same for either Redhat distros or Ubuntu. Reboot the server, and get into the boot menu by pressing ESC to enter the menu:

ESC to enter the menu

Next you’ll need to choose which kernel you’re going to edit (it doesn’t really matter, but I usually choose the first in the list that is *not* recovery mode):

Select kernel to edit

Type ‘e’ to edit that line, and it will take you to another menu, which brings you to the line you’ll edit:

Edit kernel line

Scroll down to the ‘kernel’ like, and hit ‘e’ again. This will insert the kernel line into the grub> prompt, where you can add/edit the boot instructions. Yours will not be identical to the example below, but should have the same components:

 kernel /boot/vmlinuz-2.6.17-11-386 root=UUID=7d2d4dd5-f7fd-45ad-8bc9-51b1b284fcaa ro quiet splash

You will want to edit that to :

 grub> kernel /boot/vmlinuz-2.6.17-11-386 root=UUID=7d2d4dd5-f7fd-45ad-8bc9-51b1b284fcaa init=/bin/bash

When finished, hit enter to go back to the previous screen, and hit ‘b’ to boot that newly edited line.

After it boots, you’ll be at a shell prompt, but it isn’t writable! So any changes you make (like changing the root password) won’t hold through the reboot. So you’ll need to mount your root directory (which is simply labeled: / ) by typing the following command:

mount -o remount,rw /

Be sure to note the spacing in that command

Next you’ll need to change the root password. Since you are in the root prompt, it will change the password for user ‘root’ by default. I’ve added the root user below so you can see how to change other user passwords as well (simply replace ‘root’ with another user):

 passwd root

Enter the new password (one that you’ll remember, but is sufficiently secure).

It should reboot as it is now, but if you want to be safe you can reset the drive to read only:

mount -o remount,ro /

Then type:

 shutdown -r now

When your server reboots, you should now be able to log in as root with your newly set password. Granted, this will only work if you are using the cli (or have already set to allow root login through the settings in gnome/kde).

To perform this same task on a RedHat linux distro (RedHat, Fedora, CentOS, etc.) you follow the same instructions as above, except you add the word ‘single’ instead of ‘init=/bin/bash’ from above. Then continue to boot.

When you boot that kernel, it will drop you into a root prompt, at which point you can type passwd to change your password (with passwd, just like above). Next time you reboot your server, you will have root access with your newly formed password.

Backing up and Restoring your MySQL database – Ubuntu

Posted by | Posted in Bash, Code, Command-Line, Guides, Linux, MySQL, Ubuntu | Posted on 23-07-2009

If you manage any type of databases, you know the importance of backing up your data. Whether you have file corruption from a failing hard drive, a run away script or from a hacking attempt; loosing your databases can and will break your business. If you’ve never backed up your database, now is a good time to start.

The easiest way to backup your MySQL database is with “/usr/bin/mysqldump”. You’ll need to access this application via command. Whether it is from the a console or an SSH connection.

mysqldump – a database backup program
mysqldump [options] [db_name [tbl_name ...]]

There are three general ways to invoke mysqldump:

mysqldump [options] db_name [tables]
mysqldump [options] --databases db_name1 [db_name2 db_name3...]
mysqldump [options] --all-databases

By default, the mysqldump command will dump the contents of your database directly into your terminal and not into a file. This output can be redirected anyway and to anywhere you like. But it is commonly used in this manner:

mysqldump -u [username] -p [password] [databasename] > [backup.sql]
mysqldump -u root -p PASSWORD DATABASE > DATABASE-BACKUP-DATE.sql

[username] – your database username
[password] – the password for your database
[databasename] – the name of your database
[backup.sql] – the file to which the database contents needs to be directed to

If you don’t want to use your password in the command line, you can always prompt for your password by excluding it from your command.

mysqldump -u root -p DATABASE > DATABASE-BACKUP-DATE.sql
Enter password:

If you forgot your password, you’ll get an error like this.

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

Or if you didn’t enter a password when one is needed, you’ll get this error.

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

If you’d like the convenience of having the database automatically delete the old database before re-writing the content you can use the “–add-drop-table” feature.

 mysqldump --add-drop-table -u root -p PASSWORD DATABASE > DATABASE-BACKUP-DATE.sql

If you need to only backup specific tables of the database you can use this command.

mysqldump --add-drop-table -u root -p PASSWORD DATABASE TABLE> DATABASE-TABLE-BACKUP-DATE.sql

The syntax for the command is:

mysqldump -u [username] -p [password] [databasename] [table1 table2 ....]

[tables] – This is a list of tables to backup. Each table is separated by a space.

If you are a server administrator and need to backup multiple databases at the same time, you can use the next command. You just have to be sure that the “-u” user account has permission to read those databases.

mysqldump -u root -p PASSWORD --databases DATABASE1 DATABASE2 DATABASE3 > DATABASE-1-2-3-BACKUP-DATE.sql

Or if you just want to back up ALL the databases at one, use the “–all-databases” parameter to backup all the databases in the server in one easy step.

mysqldump --all-databases> ALL-DATABASES-DATE.sql

How to back up only the data structure.

While in development, most programmers only need to back up their database structure, not their test data. To do this, use the “–no-data” parameter.

mysqldump --no-data --databases DATABASE1 DATABASE2 DATABASE3 > DATABASE-1-2-3-Structure-Bakcup-DATE.sql

If you have large databases and not enough hard drive space to store them in the regular SQL, text format, you can compressing the backup file on the fly.

mysqldump --all-databases | bzip2 -c >all-databases-backup-date.sql.bz2
 
mysqldump --all-databases | gzip >all-databases-backup-date.sql.gz

If you are away from your servers, or have multiple servers that you manage, you can still backup your databases while you are away. Use the bash script below in conjunction with a cron-tab to automate your backups.

#!/bin/sh
date=`date -I`
mysqldump --all-databases | gzip > /var/mysql_backup/mysql-backup-all-$date.sql.gz

How to restore your databases.

If the unforeseen happens and you need to totally rebuild your databases, you can do so easily from the mysqldump SQL files.

mysql -u root -p PASSWORD DATABASE < DATABASE-BACKUP-DATE.sql

Mysql Synopsis:

mysql -u [username] -p [password] [database_to_restore] < [backup_file.sql]

Restoring from the compressed files.

gunzip < DATABASE-BACKUP.sql.gz | mysql -u root -p PASSWORD DATABASE

Or do 2 at a time.

cat DATABASE-BACKUP-1.sql DATABASE-BACKUP-2.sql | mysql -u root -p PASSWORD

Installing and setting up Apache2 with Vhosts – Ubuntu

Posted by | Posted in Command-Line, Guides, Linux, Ubuntu | Posted on 22-07-2009

Apache is the most widely used website hosting applications. At the time of this article, Apache is reported to have about 70% of the market. While Microsoft IIS has dropped dramatically to 30%. I’ve personally been using Apache for the last 15 years had have never ever had any problems with the application. All the problems I’ve had were related to typos in my configuration files.

To get Apache up and running, you need to do 2 things.

1) install apache
2) start apache

That is basically all you need to know, to get apache up and running. Fairly simple. But of course human nature creeps in and we want to start customizing and personalizing.

To Installing apache2 via apt-get:

sudo apt-get install apache2

Starting apache2 for the first time

sudo /etc/init.d/apache2 start

If you get this error:

apache2: Could not determine the server’s fully qualified domain name, using 127.0.0.1 for ServerName

You need to add “ServerName localhost” to the “/etc/apache2/conf.d/fqdn” file. This can all be done in a single command:

echo "ServerName localhost" | sudo tee /etc/apache2/conf.d/fqdn

If you need to Start|Stop|Reload|etc Apache’s httpd daemon on Ubuntu, you’d want to use the “/etc/init.d/apache2″ commands:

Usage: /etc/init.d/apache2 {
start
stop
restart
reload
force-reload
start-htcacheclean
stop-htcacheclean
status
}

If you are using any other operating system, you’d want to use the apachectl command:

apachectl – Apache HTTP Server Control Interface

/usr/sbin/apachectl

apachectl commands

start : Loads the org.apache.httpd launchd job.
stop, graceful-stop : Unloads the org.apache.httpd launchd job.
restart, graceful : Unloads, then loads the org.apache.httpd launchd job.
fullstatus : Displays  a  full status report from mod_status. For this to work, 
you need to have mod_status enabled on your server and a text-based 
browser such as lynx available on your system. The URL used to access the 
status report can be set by editing the STATUSURL variable in the script.
 
status : Displays a brief status report. Similar to the fullstatus option, except 
that the list of requests currently being served is omitted.
 
configtest : Run a configuration file syntax test. It parses the configuration files 
and either reports Syntax Ok or detailed information about  the  particular 
syntax error. This is equivalent to apachectl -t.
 
The following option was available in earlier versions but has been removed. 
(It is now built into the httpd daemon).
 
startssl : To  start  httpd  with  SSL  support,  you should edit your configuration 
file to include the relevant directives and then use the normal apachectl start.

The main files/directories you will be working with first are:
apache2.conf : the configuration file
sites-available : a directory to let Apache know what sites are available to be accessed by httpd
sites-enables : a directory to let Apache know which websites need to be loading on start

A list of the default apache2 configuration file structure in “/etc/apache2/”:

dmccoy@server:/etc/apache2# ls -l /etc/apache2/
total 48
-rw-r--r-- 1 root root 10104 2009-04-01 11:01 apache2.conf
drwxr-xr-x 2 root root  4096 2009-07-17 05:16 conf.d
-rw-r--r-- 1 root root   378 2009-04-01 11:01 envvars
-rw-r--r-- 1 root root     0 2009-07-13 10:57 httpd.conf
drwxr-xr-x 2 root root 12288 2009-07-17 05:16 mods-available
drwxr-xr-x 2 root root  4096 2009-07-13 10:57 mods-enabled
-rw-r--r-- 1 root root   513 2009-04-01 11:01 ports.conf
drwxr-xr-x 2 root root  4096 2009-07-22 00:54 sites-available
drwxr-xr-x 2 root root  4096 2009-07-13 10:57 sites-enabled

The default web site is located at:

/var/www/index.html

if you launch the domain in a web browser “http://localhost” you should see a message “It Works!”. If you are using the server version with out a gui, you can use “lynx http://localhost”. If lynx isn’t installed, you can use apt-get to install it.

If you need to maintain more then (1) website, you can use a Virtual Host to manage its location and access. Below is the default Virtual Host sample, located in “/etc/apache2/sites-available/default/”

There are (4) lines that you need to change.
1) You’ll need to change the ServerAdmin from webmaster@localhost to your_admin_account@domain.name email address.
2) If you are changing your default location of DocumentRoot /var/www, you’ll need to edit the path here. An instance where you’d need to change this directory, is if it was located on a different hard drive or hard drive partition.
3) If you change your DocumentRoot, then you’ll need to change the Directory from /var/www/ to your new path.
4) You’ll also need to change your log names to allow easier management of multiple domains. ErrorLog /var/log/apache2/error.log and CustomLog /var/log/apache2/access.log combined could be changed to domain.name-error.log and domain.name-access.log.

< VirtualHost *:80 >
        ServerAdmin webmaster@localhost
 
        DocumentRoot /var/www
        < Directory />
                Options FollowSymLinks
                AllowOverride None
        < /Directory >
        < Directory /var/www/ >
                Options Indexes FollowSymLinks MultiViews
                AllowOverride None
                Order allow,deny
                allow from all
        < /Directory >
 
        ScriptAlias /cgi-bin/ /usr/lib/cgi-bin/
        < Directory "/usr/lib/cgi-bin" >
                AllowOverride None
                Options +ExecCGI -MultiViews +SymLinksIfOwnerMatch
                Order allow,deny
                Allow from all
        < /Directory >
 
        ErrorLog /var/log/apache2/error.log
 
        # Possible values include: debug, info, notice, warn, error, crit,
        # alert, emerg.
        LogLevel warn
 
        CustomLog /var/log/apache2/access.log combined
 
    Alias /doc/ "/usr/share/doc/"
    < Directory "/usr/share/doc/" >
        Options Indexes MultiViews FollowSymLinks
        AllowOverride None
        Order deny,allow
        Deny from all
        Allow from 127.0.0.0/255.0.0.0 ::1/128
    < /Directory >
< /VirtualHost >

Below is a sample VirtualHost file for a domain.

< VirtualHost *:80 >
        ServerAdmin dmccoy @ server .com
        ServerName server.com
        ServerAlias server
        DocumentRoot /var/www
        < Directory />
                Options FollowSymLinks
                AllowOverride None
        < /Directory >
        < Directory /var/www/server.com >
                Options Indexes FollowSymLinks MultiViews
                AllowOverride None
                Order allow,deny
                allow from all
        < /Directory >
 
        ScriptAlias /cgi-bin/ /usr/lib/cgi-bin/
        < Directory "/usr/lib/cgi-bin" >
                AllowOverride None
                Options +ExecCGI -MultiViews +SymLinksIfOwnerMatch
                Order allow,deny
                Allow from all
        < /Directory >
 
        ErrorLog /var/log/apache2/server.com-error.log
 
        # Possible values include: debug, info, notice, warn, error, crit,
        # alert, emerg.
        LogLevel warn
 
        CustomLog /var/log/apache2/server.com-access.log combined
 
    Alias /doc/ "/usr/share/doc/"
    < Directory "/usr/share/doc/" >
        Options Indexes MultiViews FollowSymLinks
        AllowOverride None
        Order deny,allow
        Deny from all
        Allow from 127.0.0.0/255.0.0.0 ::1/128
    < /Directory >
< /VirtualHost >

You’ll Notice that I added (2) extra lines. I added “ServerName” and “ServerAlias“.

ServerName : Description : Hostname and port that the server uses to identify itself
ServerName : Syntax : ServerName fully-qualified-domain-name[:port]
ServerName : Usage : ServerName www.example.com:80

ServerAlias : Description : Alternate names for a host used when matching requests to name-virtual hosts
ServerAlias : Syntax : ServerAlias hostname [hostname] …
ServerAlias : Usage : ServerAlias server server2.domain.com server2

If by change you need manage another domain, you can copy the default virtualhost file and edit those same 4 lines to create another domain. And if you have another, you repeat the steps until all your domains are entered.

sudo cp /etc/apache2/sites-available/default /etc/apache2/sites-available/domain.name

Now that you’ve made your sites available to httpd (Apache), you can now enable them to be used by httpd. The easiest way to do this, is to make a symbolic link from the sites-available directory to the sites-enabled directory. Below is how I would enable the default domain for this server.

sudo ln -s /etc/apache2/sites-available/default /etc/apache2/sites-enabled/default

Once you have enabled all the domains you need, you can now restart Apache to make them active.

sudo /etc/init.d/apache2 restart
[sudo] password for dmccoy: 
 * Restarting web server apache2
 ... waiting    ...done.

A beginners guide to setting up MySQL – Ubuntu

Posted by | Posted in Code, Command-Line, Guides, Linux, MySQL, Ubuntu | Posted on 20-07-2009

MySQL is a very popular and widely used database system that can be used on almost any operating system, but primarly MySQL is used on LAMP (Linux/Apache/MySQL/PHP) systems.

Setting up a MySQL server is fairly easy. If you already know how to work a commmand-line interface, then you are well on your way to setting up a MySQL system. There are (4) things that you need to do, in-order to use a MySQL database system.

1) Install MySQL Server
2) Set a password for the root account
3) Set limitation privileges on the root account
4) Create a database

Installing mysql server via apt-get

$ sudo apt-get install mysql-server

On a default install, the root users does not need a password to access any databases and you can login with no password like this.

$ mysql -u root

If a password is required, use the extra switch -p:

$ mysql -u root -p
Enter password:

To add a root password when there isn’t one. Use the following command. Be sure to change the PASSWORD to your own password and to make sure that it is encapsulated with quotation marks.

mysqladmin -u root password "PASSWORD"

Now that we have the root account locked down with a password, we need to change its accessibility. While you are logged into mysql used these following commands to secure the root account.

mysql> use mysql;
mysql> delete from user where Host like "%";
mysql> grant all privileges on *.* to root@"%.DOMAIN.COM" identified by 'PASSWORD' with grant option;
mysql> grant all privileges on *.* to root@localhost identified by 'PASSWORD' with grant option;
mysql> flush privileges;
mysql> exit;

Now that we can login and feel secure, we create a database. Change DATABASE to any name you’d like.

mysql> create database DATABASE;
Query OK, 1 row affected (0.00 sec)

And that’s it. You can now check that you can connect to the MySQL server using this command:

$ mysql -u USER -p 'PASSWORD' DATABASE_NAME
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 61
Server version: 5.0.75-0ubuntu10.2 (Ubuntu)
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql>

Or, If you want to check your version

mysqladmin -u root -p version
 
Enter password: 
mysqladmin  Ver 8.41 Distrib 5.0.75, for debian-linux-gnu on i486
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
 
Server version          5.0.75-0ubuntu10.2
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 3 hours 7 min 4 sec
 
Threads: 2  Questions: 6596  Slow queries: 0  Opens: 969  Flush tables: 1  Open tables: 19  Queries per second avg: 0.588

If you need to start|stop|restart MySQL, use the commands in /etc/init.d/mysql

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start
sudo /etc/init.d/mysql restart

Notice :

preforming the above security options for the root account will break the /etc/init.d/mysql scripts.

sudo ./mysql status
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'
 * 
@:/etc/init.d$ sudo /etc/init.dmysql restart
 * Stopping MySQL database server mysqld
   ...fail!
 * Starting MySQL database server mysqld
   ...done.
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'

To fix this issue, you need to edit the /etc/mysql/debian.cnf file. There are (2) lines that need to be changed. Change the password hash to a new password. Unfortunately, this password is plain text.

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = XXXXXXXXXXXXX
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
user     = debian-sys-maint
password = XXXXXXXXXXXXXX
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

Then you need to log into MySQL and permissions to the “debian-sys-maint” user account.

> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'NEWPASSWORD' WITH GRANT OPTION
 
> FLUSH PRIVILEGES;

Now, if for some reason, you overwrite, or toast your root password, You need to reset it. At this point in time, the start|stop features of /etc/init.d/mysql will probably no longer work and you need to kill the process old school style.

ps -aux |grep mysql
 
root      5340  0.0  0.0   1872   548 pts/2    S    05:17   0:00 /bin/sh /usr/bin/mysqld_safe
mysql     5379  0.0  3.5 129412 27116 pts/2    Sl   05:17   0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
root      5381  0.0  0.0   3052   704 pts/2    S    05:17   0:00 logger -p daemon.err -t mysqld_safe -i -t mysqld
root      5543  0.0  0.1   3336   792 pts/2    R+   05:23   0:00 grep mysql

What you are seeing here is a list of processes that contain the word “mysql”. From left to right you can see there is a USER and the PROCESS ID (4 digits), LOAD variables, RUN time and then the COMMAND. What we are interested in is the (4) digit process ID numbers. In this case, 5340, 5379 and 5381. The 5543 process, as you can see is “grep mysql”. This is our search process ID and most likely doesn’t exist anymore.

In-order to stop those (3) processes, you need to kill them. Literally. You need to use the “kill” command to stop the process from running, and to keep it from relaunching, you add the “-9″. This refers to the type of kill you are preforming. In this case the kill cannot be blocked and won’t relaunch.

# sudo kill -9 5340
# sudo kill -9 5379
# sudo kill -9 5381
kill: No such process

As you can see the 5381 process no longer existed after we killed the 5379 process.

Now that we have mysql completely stopped, we can restart the mysql server and change the root password. To be able to do this start mysql with the “–skip-grant-tables” followed by the “&” character. The skip grant tables will bypass the mysql database and won’t load the current usernames and passwords. the “&” will embed the process in the background, so you can keep working from the same command-shell.

sudo mysqld --skip-grant-tables &
 
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
090720  5:42:50  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
090720  5:42:50  InnoDB: Started; log sequence number 0 115369069
090720  5:42:50 [Note] mysqld: ready for connections.
Version: '5.0.75-0ubuntu10.2'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

Now that MySQL is restarted, go ahead and log back into the server and use the mysql database. Notice, we don’t need a password this time.

mysql -u root
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.75-0ubuntu10.2 (Ubuntu)
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> use mysql;
 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed

Now lets change the root password.

 
mysql> UPDATE user SET password = password('NEW PASSWORD') WHERE user = 'root' AND host='localhost';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

The Rows mached:1, means (1) row was updated and the password was updated successfully!

Flush your privileges and quit.

mysql> FLUSH privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> quit;
Bye

Now restart your mysql server to reactivate the accounts and passwords.

sudo /etc/init.d/mysql restart
 * Stopping MySQL database server mysqld
090720  5:52:22 [Note] mysqld: Normal shutdown
090720  5:52:22  InnoDB: Starting shutdown...
090720  5:52:23  InnoDB: Shutdown completed; log sequence number 0 115369069
090720  5:52:23 [Note] mysqld: Shutdown complete
   ...done.
 * Starting MySQL database server mysqld
   ...done.
 * Checking for corrupt, not cleanly closed and upgrade needing tables.
[1]+  Done                    sudo mysqld --skip-grant-tables

Join the mailing list

Check your email and confirm the subscription