A beginners guide to setting up MySQL – Ubuntu

Posted by dmccoy | 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
(0) Comments
Read More

Using MyTop – Ubuntu

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

If you ever need to know how well your databases are being queried, mytop is the key. MyTop is a clone of Top for MySQL.

Your Ubuntu server won’t come pre-installed with mytop, so you’ll have to to apt-get to install it.

/usr/bin/sudo /usr/bin/apt-get install mytop

Once mytop is installed and your mysql server is setup, you can view your database stats.

-u : user account
-p : password
-h : server, normally localhost
-d : database you want to view
-P : port, if different from default

mytop -u USER -h SERVER -d DATABASE -p PASSWORD

You should see something like this come up.

MySQL on localhost (5.0.75-0ubuntu10.2)    up 0+00:32:06 [02:09:34] Queries: 5.0
qps:    0 Slow:     0.0         Se/In/Up/De(%):    00/00/00/00   qps now:    0 
Slow qps: 0.0  Threads:    1 (   1/   0) 00/00/00/00  Key Efficiency: 90.0%  
Bps in/out:   0.1/  6.9   Now in/out:   8.3/ 1.3k
 
 Id      User         Host/IP         DB      Time    Cmd Query or State 
 --      ----         -------         --      ----    --- ----------  
38      root       localhost      mysql         0  Query show full processlist

to exit mytop, press the “Q” key.

(0) Comments
Read More

Using PHP to return an IP address

Posted by dmccoy | Posted in Code, Command-Line, Functions, OS X, PHP | Posted on 19-07-2009

After I’ve determine which interfaces are active on my computer(s), I can use this script to report back the IP address of said interface.

# input String ("en0")
# output String IP
function get_network_interface_ip($interface) {
  @exec('/usr/sbin/ipconfig getifaddr '. $interface, $retval);
 return $retval[0];
}// end get_network_iterface_ip
echo get_network_interface_ip($interface);
(0) Comments
Read More

Using PHP to report an active network interface

Posted by dmccoy | Posted in Code, Command-Line, Functions, OS X, PHP | Posted on 19-07-2009

I use this script, after I determine what available interfaces the computer has, to report which interfaces are active.

# input String ("en0")
# output "NULL or "Active" 
function check_interface_activity($interface){
  @exec('ifconfig| awk \'/flags=|media|inet / {if (substr($2, 1, 6) == "flags=") printf("\n%s ", $1);
  else if ($1 == "inet") printf("%s ", $2);
  else if ($1 == "media:") printf(substr($0, 9))}\' | awk \'$1 ~ /^'.$interface.'/\' | awk \'{print $7}\'',$retval);
 return $retval[0];
} // end check_interface
echo check_interface($interface);
(0) Comments
Read More

Using PHP to gather all active network interfaces

Posted by dmccoy | Posted in Code, Command-Line, Functions, OS X, PHP | Posted on 19-07-2009

I use this script to gather all Ethernet based network activity to determine which network port is active and then determine what the hardware address and IP address are.

# input NONE
# output String ("en0, en1")
 
function get_all_active_network_interfaces() {
  $inclusion = array(en0,en1);
  @exec('/sbin/ifconfig -lu', $retval);
  $interfaces = explode(" ",$retval[0]);
  $ret=NULL;
  foreach($interfaces as $interface){
     if (in_array($interface,$inclusion)){
       $ret.=$interface ." \n \r";
     } // end if
  }// end foreach
return $ret;
} // end get_all_active_network_interfaces
echo get_all_active_network_interfaces();

This script is similar to the above script, but doesn’t include the constraints of the inclusion list.

# input NONE
# output String ("en0, en1, etc") 
function get_all_active_network_interfaces() {
  @exec('/sbin/ifconfig -lu', $retval);
  $interfaces = explode(" ",$retval[0]);
  $ret=NULL;
    foreach($interfaces as $interface){
      $ret .= $interface ." \n \r";
    }// end foreach
return $ret;
} // end get_all_active_network_interfaces
 
echo get_all_active_network_interfaces();
(0) Comments
Read More

Using PHP to Connect to a MySQL server

Posted by dmccoy | Posted in Code, MySQL, PHP | Posted on 18-07-2009

This is how I normally link PHP to my MySQL servers. I use this code normally for my shell scripting, but it also works for websites.

$mysql_server = "SERVER_ADDRESS";
$mysql_user = "SERVER_USER";
$mysql_password = "SERVER_PASSWORD";
$mysql_database = 'SERVER_DATABASE';
$mysql_table = 'SERVER_TABLE';
$mysql_connection = @mysql_connect($mysql_server,$mysql_user,$mysql_password);
 
if (!$mysql_connection)
  DIE ('ERROR 10: No Connection to '.$mysql_server.' '. mysql_error() ."\n");
 
$mysql_database_connection = @mysql_select_db($mysql_database,$mysql_connection);
 
if (!$mysql_database_connection)
  DIE ('ERROR 30 : Could not select database '. $mysql_database .' '. mysql_error() ."\n");
 
echo "MySQL : Connection Active \n";
(0) Comments
Read More

How to use scp – Ubuntu

Posted by dmccoy | Posted in Command-Line, Linux, Ubuntu | Posted on 17-07-2009

I am currently switching web servers at work from a physical server to a virtual server. The differences are for a different topic, but during this switch, I am going to be copying files from my old server to my new one. Normally you’d copy your files down to your local computer, lets say your laptop or desktop, before upload them to your new server. The not only takes a lot of time, but also eats up hard drive space. I am going to show you how to copy directly from one server to another.

I am going to use SSH’s SCP application. /usr/bin/scp – secure copy (remote file copy program).

For general discussion, I am already remotely logged into my old server via SSH and I have the files that I want to transfer up already TAR’d up. I’ll cover SSH and TAR in later posts. But for right now, I am just going to demonstrate how you should use SCP.

The usage format to transfer a file “up” to another system normally looks something like this.,

/usr/bin/scp /path/to/local.file user@server.address:/path/to/new/location.file

I am going to copy a mysql (database) file from my old server’s backup directory to my home directory on the new server

/usr/bin/scp /backups/mysql_database_dump.sql.tar dmccoy@new.server.com:~/
dmccoy@new.server.com's password: 
mysql_database_dump.sql.tar              100%  391MB   6.4MB/s   01:01

Now, if I wanted to copy a file from my new server to my old server, while still on my old server, I would reverse the sequence.

scp dmccoy@new.server.com:/path/to/mysql_database_dump.sql.tar /path/to/new/location/
dmccoy@new.server.com's password: 
mysql_database_dump.sql.tar              100%  391MB   6.4MB/s   01:01

Family Commands of scp are

/usr/bin/rcp - alias of scp - secure copy (remote file copy program)
/usr/bin/sftp - secure file transfer program
/usr/bin/ssh - OpenSSH SSH client (remote login program)
/usr/bin/ssh-add - adds RSA or DSA identities to the authentication agent
/usr/bin/ssh-agent - authentication agent
/usr/bin/ssh-keygen - authentication key generation, management and conversion
/etc/ssh/ssh_config - OpenSSH SSH client configuration files
/usr/sbin/sshd - OpenSSH SSH daemon
(0) Comments
Read More

How to change your default shell – Ubuntu

Posted by dmccoy | Posted in Command-Line, Linux, Ubuntu | Posted on 17-07-2009

If I am not using OS X, then I normally use Ubuntu. On a normal install of Ubuntu, your default command-line shell is “/bin/sh” – command interpreter (shell). To me, a low powered command shell. I normally switch it from “/bin/sh” to “/bin/bash” or “/usr/bin/bash” depending on where bash is installed.

The easiest way to do this is to use the “chsh” command. chsh stands for “change login shell”

/usr/bin/chsh

A typical change shell sequence looks something like this.

1) Find the current location of the shell you want to run. To to this, you use “which”.
2) Use the “chsh” program.
3) Enter your password.
4) Enter the new shell you want to use.

$ which bash
/bin/bash
$ chsh    
Password: 
Changing the login shell for USER
Enter the new value, or press ENTER for the default
	Login Shell [/bin/sh]: /bin/bash
$ exit

In-order to use your new shell, you have to logout and log back in.

Family Commands for chsh are

/usr/bin/chfn - change real user name and information
/etc/login.defs - shadow password suite configuration
/usr/bin/passwd - change user password
(0) Comments
Read More

Upgrading Software – Ubuntu Server Edition

Posted by dmccoy | Posted in Command-Line, Linux, Ubuntu | Posted on 17-07-2009

When it comes to any server, you normally have to update its software remotely. Either by command shell or remote desktop. For this demonstration, I am going to access my server via SSH and use the Ubuntu/Debian package manager called “/usr/bin/apt-get” – APT package handling utility — command-line interface.

user@server:~$ sudo apt-get upgrade
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following packages have been kept back:
  linux-image-server linux-restricted-modules-server linux-server
The following packages will be upgraded:
  acpid apache2 apache2-mpm-prefork apache2-utils apache2.2-common ..... etc.....
47 upgraded, 0 newly installed, 0 to remove and 3 not upgraded.
Need to get 57.4MB of archives.
After this operation, 20.5kB of additional disk space will be used.
Do you want to continue [Y/n]? Y
Get:1 http://us.archive.ubuntu.com jaunty-updates/main perl-modules 5.10.0-19ubuntu1.1 [3182kB]
...
...
...
...
etc until all the applications have downloaded
...
Fetched 57.4MB in 48s (1173kB/s)                                                                                                                                                  
Extracting templates from packages: 100%
Preconfiguring packages ...
(Reading database ... 24637 files and directories currently installed.)
Preparing to replace perl-modules 5.10.0-19ubuntu1 (using .../perl-modules_5.10.0-19ubuntu1.1_all.deb) ...
Unpacking replacement perl-modules ...
...
...
...
Now everything application that needed updated, will unpack and install.
..
..
If any application (daemons) that are running, are upgraded, apt-get automatically restarts those programs.
....
Setting up apache2 (2.2.11-2ubuntu2.2) ...
Setting up libapache2-mod-php5 (5.2.6.dfsg.1-3ubuntu4.1) ...
 * Reloading web server config apache2
   ...done.

and it is just as simple as that.

Family Commands for apt-get are

/usr/bin/apt-cache - APT package handling utility -- cache manipulator
/usr/bin/apt-cdrom - APT CDROM management utility
/usr/bin/dpkg - package manager for Debian
/usr/bin/dselect - Debian package management frontend
/etc/apt/sources.list - https://help.ubuntu.com/community/SourcesList
/etc/apt/apt.conf -  Configuration file for APT
/usr/bin/apt-config - APT Configuration Query program
/usr/bin/apt-secure - Archive authentication support for APT
/etc/apt/preferences - Preference control file for APT - apt_preferences
APT User’s guide in /usr/share/doc/apt-doc/
(0) Comments
Read More

How to change your password – Ubuntu

Posted by dmccoy | Posted in Command-Line, Linux, Ubuntu | Posted on 17-07-2009

Changing your password is something you should do on a monthly or weekly basis. This prevents anyone that has glanced over your shoulder while walking by from accessing your private information.

On a normal Linux computer, you can simply use the command “passwd” – change user password.

A typical Linux sequence looks like this

user@server:/$ passwd
Changing password for user.
(current) UNIX password: 
Enter new UNIX password: 
Retype new UNIX password: 
passwd: password updated successfully

Family Commands for passwd are

/etc/group - user group file - group
/usr/bin/passwd - change user password
/etc/passwd- encrypted password file - shadow
(0) Comments
Read More
Previous
Next