Counting all rows in all tables of a MySQL database

The count() function works on a single table at a time. A way around is to write a small program in a programming language which would call count() on every table of the database. But you know that this is the wrong way of doing this. There is a simpler method. One simple query:

select sum(table_rows)
from information_schema.tables
where table_schema = 'your_database';

Delete (Drop) MySQL database

It is very easy to drop a MySQL database:

drop database database_name

Tons of blogs and articles online mention this command. Recently, however, I came across a database name which had spaces in them. For example:

company balances
electronics inventory
store employees
...

If I do:

drop database company balances

MySQL would complain that the databases company and balances do not exists. Naturally, the first thought on my mind was to enclose them the database.

drop database 'company balances'

Once again MySQL complains about incorrect syntax. Using double quotes produces the same error. Every MySQL site and blog was giving drop database examples but not one documented a solution to this problem. After a bit of trial and error, I was finally able to drop the database. Following is the command:

$ mysqladmin -u root -p drop 'company balances'

Where 'company balances' is the name of the database.

Handling spaces in MySQL

For database names, use double quotes.
For table name, use the backtick character
For field name, use the backtick character

How to empty a MySQL database

Emptying a databases involves dropping (deleting) all its tables. The easiest way to do this is to drop and recreate a database:

mysql> drop database mole;
mysql> create database mole;

Depending on the system you are working, the database administrator might not give the rights to drop or create databases. In such cases, you have no choice but to drop and recreate each table manually or with a script. I found a couple of really nifty shell scripts to do this at this site. The code is as follows:

$ mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] \
 | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

or you can use gawk

$ mysql -u uname dbname -e "show tables" | grep -v Tables_in | grep -v "+" | \
gawk '{print "drop table " $1 ";"}' | mysql -u uname dbname

If you prefer to use PHP instead, you can run the following code:

<?php
    // connect to db
    mysql_connect("host","user","password") or die(mysql_error());
    mysql_select_db("database") or die(mysql_error());

    // list all tables
    $aTableNames = array();
    $rs = mysql_query("show tables") or die(mysql_error());  
    while($rw = mysql_fetch_array($rs)) { 
        $aTableNames[] = $rw[0]; 
    }

    // drop tables
    foreach ($aTableNames as $tn) {
        mysql_query("drop table {$tn}");
    }
?>

The following would work without modifications. Simply copy paste the code somewhere and type the following command:

$ php empty_database.php localhost username password database

empty_database.php

<?php
    if (count($argv) !== 5) {
        print "Syntax: php empty_database locahost user pass database\n";
    } else {
        // connect to db
        mysql_connect($argv[1],$argv[2],$argv[3]) or die(mysql_error());
        mysql_select_db($argv[4]) or die(mysql_error());

        // list all tables
        $aTableNames = array();
        $rs = mysql_query("show tables") or die(mysql_error());  
        while($rw = mysql_fetch_array($rs)) { 
            $aTableNames[] = $rw[0]; 
        }       

        // drop tables
        foreach ($aTableNames as $tn) {
            mysql_query("drop table {$tn}");
        }       
    }
?>

Installing and running dbSchema to visualize database schema on Linux

DbSchema is a really cool tool which provides powerful functionalities for visualizing, browsing and running queries. It is free and can be downloaded from http://www.dbschema.com/download.html. Unfortunately, it is not free.

Installing DbSchema on Linux

$ wget http://www.dbschema.com/dbschema.zip
$ unzip dbschema.zip
$ cd DbSchema
$ chmod +x dbschema.sh

To run DbSchema

$ ./dbschema.sh

Installing MySQL on Ubuntu

Installation from commandline
Open a terminal window, and use the following command:

$ sudo apt-get install mysql-server

If you are running PHP you will also need to install the php module for mysql 5:

$ sudo apt-get install php5-mysql

To create a new database, use the mysqladmin command:

$ mysqladmin create

Relational Database Design

Relational Database Design

Introduction

This tutorial concerns database design. Here you would learn how to design better databases rather than simply the commands you need to create a database. Essentially databases are programs that allow a user to store, search and retrieve data. A user can be a human being or a software. Only relational databases would be covered here.

Relational Model
The relational model is a set of best practices which can be used to design relational databases. A relational model describes:

1. how data should be divided into tables
2. how tables should be linked to each other

Since the introduction of the relational model, several software products supporting relational model specifications have appeared in the market. They are called Relational Database Management Systems (RDMS). They include Oracle, MySQL, PostGreSQL, Microsoft SQL server, and many others.

So what are the characteristics of a RDMS

• storage and retrieval of data
• linking of tables using keys
• data types - text, numeric, data, etc.
• data integrity
• SQL support

Primary key
Relational databases store all data in tables. A primary key is used to uniquely identify a record. A primary key is a unique number assigned to each record. A social security number or an identity card number can be thought of as a primary key as no two persons have the same numbers. Thus we can uniquely identify the person even if their names and dates of birth are exactly the same.

If we have a table called employee which stores employee information, it would be useful to use primary keys so that we don't confuse Linda working in accounting with Linda working in marketing.

Primary keys can be integers or text, however, using integers is far more efficient. Most relational databases have capabilities to automatically assign primary keys and they also make sure that there are not duplicates.

Linking tables with keys
Relational database tables are linked by keys. Suppose we have two tables called employee and department. The employee table contains data of 500 employees such as names, phone numbers, identification, etc. The department table contains data concerning the department such as budget, director, address, etc. Obviously, each employee works in a department so his data must be linked to that department. To do so, we simply add a the primary key of the department (did) in the employee table, as follows:

+--------------+   +------------+
| employee     |   | department |
+---------------   +------------+
| eid          |   | did        |
| name         |   | name       |
| phone number |   | manager    |
| did          |   | budget     |
+--------------+   +------------+

Instead if we decide to add primary key of employee (eid) to the department table, we would only be able to link one employee to the department table. This obviously is not desirable as a department would most likely have more than one employee. However, it is logical for each employee to be linked to one departement.

In the employee table 'eid' is the primary key and 'did' is the foreign key as it refers to a primary key in another table.

The relation in the tables above is called a one-to-many relationship. Each employee works in one department. Each department can have many employees. The relational model allows three kinds of relationships between data:

• one-to-many relationship
• one-to-one relationship
• many-to-many relationship

When linking tables, it is important to consider which one of the three relationships would be produced and whether it is desirable.

Starting MySQL on Reboot

Author: Khuram Shahzad

$ chkconfig --list mysqld
mysqld         	0:off	1:off	2:off	3:off	4:off	5:off	6:off

solution on Red Hat / Fedora

$ chkconfig --list mysqld
mysqld         	0:off	1:off	2:off	3:off	4:off	5:off	6:off

Visualizing database schemas with sqldesigner

I have been looking around for a tool to visualize database structure. dbSchema is a good tool but it has to be licensed. schemaSpy seems like a good tool but I wasn't able to get it to work properly and uninformative Java error messages did not assist in locating source of the problem(s). Instead I found sqldesigner and it provides the visualization functionalities I require.

sqldesigner allows users to manually draw ER diagrams, import DB schemas from your databases, manually tables and keys and generate SQL from diagrams. The source code is pretty interesting and worth a peak.

Installing sqldesigner
Download sqldesigner zip file from google code. Unzip the file inside your DocumentRoot and simply open the file with your browser. Same steps for Windows, Linux, Mac. Following are command line instructions for Linux.

$ unzip -d /var/www wwwsqldesigner-2.5.zip
$ mv /var/www/wwwsqldesigner-2.5 /var/www/sqldesigner

With the above settings, open the address http://localhost/sqldesiger.

Drawing schema manually
The GUI interface is pretty intuitive. The one page instruction on http://code.google.com/p/wwwsqldesigner/wiki/Manual should be sufficient to get you started.

To draw a connection between two fields, click on "connect foreign key" button and then click on target field in the other table.

Loading schema from your database
To load a database schema from your database, open the file

/var/www/sqldesigner/backend/index.php

and set the values for SERVER, USER, and PASSWORD inside the setup_import() function.

Then click on "Save / Load" button in http://localhost/sqldesiger in the browser. Then click on "Import from DB". Type the name of your database and you are good to go.

Make sure you save your diagram before exiting.

Connecting to the MySQL Server for the first time and setting a root password

The MySQL client program, has the following command format.

%>mysql [options] [database]

where [options] can be a series of options, and [database] is the name of the database we wish to use. Both are optional parameters. A useful command for first time users is:

%>mysql --help

To start, type the following command.

%>mysql -u root

Here were defined that we would like to log in with the username root (-u root). Something like the following should appear:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 78 to server version: 5.0.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Creating root password

Now you need to create a password for this account

mysql>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('my_password');

root is the username
localhost is the hostname

To update your password

%>mysqladmin -u root password 'my_password'

Reconnecting and exiting MySQL

To connect to mysql, you need to do the following.

%>mysql -u root -p

This would prompt you for your password. Type your password and hit enter. You can also type your password in the mysql command as follows, but it is not a good idea.

%>mysql -u root -pMY_password

To quit MySQL you can type any of the following commands:

mysql>\q
mysql>quit
mysql>exit

Selecting MySQL Database

To get a list of database installed on your MySQL, you need to type the following command:

mysql> show databases;

To select a database, type the following command:

mysql> use xyzdatabase

Alternately, you can select a database when connecting to mysql

%> mysql -u root -p xyzdatabase

Starting stoping restarting MySQL Server

To start

$ /sbin/service mysqld start

To stop

$ /sbin/service mysqld stop
or
$ pkill mysqld

To restart

$ /sbin/service mysqld restart

Creating New User

In mysql, there are many ways to create a user. Let's begin my logging in as root

mysql -u root -p

The following command adds a user to MySQL database.

CREATE USER 'me'@'localhost' IDENTIFIED BY 'mypass';

To check if a user has been added, check mysql.user table.

use mysql;
select * from user;

You may have noticed that all privileges are set to no so the new user can pretty much do nothing. This leads us to your next question. How do I grant access to my new user? To grant privileges, we use the GRANT command as follows:

GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.* TO 'me'@'localhost';

Grants privileges to select, insert, update, and delete all tables of the database mydatabase.

GRANT ALL ON mydatabase.* TO 'me'@'localhost';

Grants all privileges on the database mydatabase.

GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'me'@'localhost';

Grants privileges to select, insert, update, and delete on all databases.

There are several ways to add a user.

INSERT INTO user(Host,User,Password)

VALUES ('localhost','me',PASSWORD('mypass'));

You can also grant privileges while creating a user.

INSERT INTO user(Host,User,Password,Select_priv,Insert_priv,Update_priv)

VALUES ('localhost','me',PASSWORD('mypass'),'Y','Y','Y');

Alter Table

Alter table command is used for altering tables, table fields and table indexes

To rename a table:

ALTER TABLE mytable RENAME yourtable;

To add a column:

ALTER TABLE mytable ADD COLUMN update TIMESTAMP FIRST

ALTER TABLE mytable ADD COLUMN update TIMESTAMP AFTER id

ALTER TABLE mytable ADD COLUMN update TIMESTAMP LAST

The first example create a column in the first position. The second command creates a column after the column id. The last example creates a column in the last position.

To drop a column:

ALTER TABLE mytable DROP COLUMN update

To drop an index:

ALTER TABLE mytable DROP INDEX myindex

To modify a column:

ALTER TABLE mytable CHANGE sid sidi INT(10) NOT NULL

We simply specified that the column we wish to modify is sid and then specified its properties.

Resetting MySQL Root Password

To reset MySQL password, you need to have root access to the machine. After logging in as root, start by shutting down MySQL:

$ /sbin/service mysqld stop

Verify that all mysql processes have been shut down.

$ ps waux | grep 'mysql'

For all processes that are listed

$ kill -9

Find your mysqld_safe.

$ locate mysqld_safe

My mysqld_safe is installed at /usr/bin/mysqld_safe. Yours might be elsewhere. Start MySQL without the grant tables.

$ /usr/bin/mysqld_safe --skip-grant-tables

At this point, you would need to open a new terminal.

$ locate mysql | grep 'bin/mysql'$

My mysql binary is located ate /usr/bin/mysql. Run mysql.

$ /usr/bin/mysql mysql

Update your root password

mysql> UPDATE user SET password=password('new-password') WHERE host='localhost' and user = 'root';

Log out of MySQL.

mysql> exit

Restart MySQL.

$ /sbin/service mysqld restart

Backing up MySQL databases using mysqldump

Backup

mysqldump command is used to backup up MySQL databases. mysqldump has the following format:

mysqldump -u [login] -p [pass] [dbname] > [backup.sql]

where [backup.sql] is the file to which the backup should be written.

Backing up specific tables
By default, mysqldump would backup the entire database. If you need to backup only specific tables, list the tables as follows:

mysqldump -u [user] -p [pass] [dbname] [tables] > [backup.sql]

where [tables] is the list of tables you wish to back up.

Backing up all databases

mysqldump --all-databases -u [user] -p [pass] > [backup.sql]

Backing up specific databases
By default, mysqldump would backup the entire database. If you need to backup only specific tables, list the tables as follows:

mysqldump -u [user] -p [pass] --databases [dbnames] > [backup.sql]

where [dbnames] is the list of databases you wish to back up.

Adding drop table commands

mysqldump --add-drop-table -u [login] -p [pass] [dbname] > [backup.sql]

Drop the table if it already exists.

Backup only the structure

mysqldump --no-data -u [login] -p [pass] [dbname] > [backup.sql]

Writing a backup shell script
#!/bin/sh
dt=`date -I`
mysqldump --all-databases | gzip > backup-$dt.sql.gz

Restore

To restore a mysql database backup, do the following:

mysql -u [login] -p [pass] [dbname] < [backup.sql]

Resources
- http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html">

mysql> ALTER TABLE tbl DROP COLUMN col;
(Full ALTER TABLE syntax available at mysql.com.)

Batch mode (feeding in a script):

# mysql -u user -p < batch_file
(Use -t for nice table layout and -vvv for command echoing.)
Alternatively: mysql> source batch_file;

Backing up a database with mysqldump:

# mysqldump --opt -u username -p database > database_backup.sql
(Use 'mysqldump --opt --all-databases > all_backup.sql' to backup everything.)
(More info at MySQL's docs.)

Change column type
alter table tablename modify fieldname varchar(20)

Notes
cannot use distinct on longtext