With mysqldump, you can dump a database or collection of databases into one or more files containing all the instructions necessary to re-create all your tables and repopulate them with your data. This command can also generate files in CSV (comma-separated values) and other delimited text formats, or even in XML format. Its main drawback is that you must make sure that no one writes to a table while you’re backing it up. There are various ways to do this, but the easiest is to shut down the MySQL server before running mysqldump and start up the server again after mysql dump finishes. Alternatively, you can lock the tables you are backing up before running mysqldump. To lock tables for reading (as we want to read the data), from the MySQL command line issue this command:
LOCK TABLES tablename1 READ, tablename2 READ …

Then, to release the lock(s), enter the following:

UNLOCK TABLES;

By default, the output from mysqldump is simply printed out, but you can capture it in a file through the > redirect symbol.
Use next command in terminal, do not use in mysql client.
The basic format of the mysqldump command is shown here:

mysqldump -u user -p password databasename

However, before you can dump the contents of a database, you must make sure that mysqldump is in your path, or else specify its location as part of your command.

Dumping the publications database to screen:

mysqldump -u user -p etunimitietokantadb publications

Make sure that you replace user and password with the correct details for your installation of MySQL. If there is no password set for the user, you can omit that part of the command, but the -u user part is mandatory unless you have root access without a password and are executing as root (not recommended).

Whatever kind of data you are storing in your database, it must have some value to you, even if it’s only the cost of the time required for reentering it should the hard disk fail. Therefore, it’s important that you keep backups to protect your investment. Also, there will be times when you have to migrate your database over to a new server; the best way to do this is usually to back it up first. It is also important that you test your backups from time to time to ensure that they are valid and will work if they need to be used. Thankfully, backing up and restoring MySQL data is easy with the mysqldump command.

You can also use the logical operators AND, OR, and NOT in your MySQL WHERE queries to further narrow down your selections. Next example shows one instance of each, but you can mix and match them in any way you need.
Using logical operators:

SELECT author,title FROM classics WHERE
author LIKE “Aro%” AND author LIKE “%Markku”;
SELECT author,title FROM classics WHERE
author LIKE “%Matti ja Teppo%” OR author LIKE “%Esa Mäkelä%”;
SELECT author,title FROM classics WHERE
author LIKE “Aro%” AND author NOT LIKE “%Markku”;

You can also save yourself some typing and improve query readability by creating aliases using the AS keyword. Simply follow a table name with AS and the alias to use.

SELECT name,author,title from
customers AS cust, classics AS class WHERE cust.isbn=class.isbn;

You can also use AS to rename a column (whether or not joining tables), like this:

SELECT name AS customer FROM customers ORDER BY customer;

Aliases can be particularly useful when you have long queries that reference the same table names many times 🙂

If you wish to specify the column on which to join two tables, use the JOIN…ON construct:

SELECT name,author,title FROM customers
JOIN classics ON customers.isbn=classics.isbn;

Using NATURAL JOIN, you can save yourself some typing and make queries a little clearer. This kind of join takes two tables and automatically joins columns that have the same name.

SELECT name,author,title FROM customers NATURAL JOIN classics;

It is quite normal to maintain multiple tables within a database, each holding a different type of information. For example, consider the case of a customers table that needs to be able to be cross-referenced with publications purchased from the classics table.
Next example to create this new table and populate it with
three customers and their purchases.

Creating and populating the customers table:

CREATE TABLE customers (
name VARCHAR(128),
isbn VARCHAR(13),
PRIMARY KEY (isbn)) ENGINE InnoDB;

INSERT INTO customers(name,isbn)
VALUES(‘Mikko Mekaanikko’,’5051011157622′);
INSERT INTO customers(name,isbn)
VALUES(‘Tony Asentaja’,’5052498183227′);
INSERT INTO customers(name,isbn)
VALUES(‘Katto Kassinen’,’6417832081437′);

SELECT * FROM customers;

There’s also a shortcut for inserting multiple rows of data, you can replace the three separate INSERT INTO queries with a single one listing the data to be inserted, separated by commas, like this 🙂

INSERT INTO customers(name,isbn) VALUES
(‘Mikko Mekaanikko’,’ 5051011157622 ‘),
(‘Tony Asentaja’,’ 5052498183227 ‘),
(‘Katto Kassinen’,’ 6417832081437 ‘);

Of course, in a proper table containing customers’ details there would also be addresses, phone numbers, email addresses, and so on, but they aren’t necessary for this explanation. While creating the new table, you should have noticed that it has something in common with the classics table: a column called isbn. Because it has the same meaning in both tables (an ISBN refers to a book or product key in CD:s, and always the same book/product), we can use this column to tie the two tables together into a single query:

SELECT name,author,title FROM customers,classics
WHERE customers.isbn=classics.isbn;

Sometimes you need to ensure that every row in your database is guaranteed to be unique. You could do this in your program by carefully checking the data you enter and making sure that there is at least one value that differs in any two rows, but this approach is error-prone and works only in certain circumstances. In the classics table, for instance, an author may appear multiple times. Likewise, the year of publication will also be frequently duplicated, and so on. It would be hard to guarantee that you have no duplicate rows.

The general solution is to use an extra column just for this purpose. In a while, we’ll look at using a publication’s ISBN (International Standard Book Number) or product code, but first I’d like to introduce the AUTO_INCREMENT data type.

As its name implies, a column given this data type will set the value of its contents to that of the column entry in the previously inserted row, plus 1. Next example shows how to add a new column called id to the table classics with auto-incrementing.

Adding the auto-incrementing column id:
ALTER TABLE classics ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY;

This is your introduction to the ALTER command, which is very similar to CREATE. ALTER operates on an existing table and can add, change, or delete columns. Our example adds a column named id with the following characteristics:

INT UNSIGNED -> Makes the column take an integer large enough for us to store more than 4 billion records in the table.

NOT NULL -> Ensures that every column has a value. Many programmers use NULL in a field to indicate that it doesn’t have any value. But that would allow duplicates, which would violate the whole reason for this column’s existence, so we disallow NULL values.

AUTO_INCREMENT -> Causes MySQL to set a unique value for this column in every row, as described earlier. We don’t really have control over the value that this column will take in each row, but we don’t care: all we care about is that we are guaranteed a unique value.

KEY -> An auto-increment column is useful as a key, because you will tend to search for rows based on this column.

Each entry in the column id will now have a unique number, with the first starting at 1 and the others counting upward from there. And whenever a new row is inserted, its id column will automatically be given the next number in the sequence. Rather than applying the column retroactively, you could have included it by issuing the CREATE command in a slightly different format.

Adding the auto-incrementing id column at table creation:

CREATE TABLE classics (
author VARCHAR(128),
title VARCHAR(128),
type VARCHAR(16),
year CHAR(4),
id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY) ENGINE InnoDB;

The main remaining data types supported by MySQL relate to the date and time and can be seen in next table.

Data type Time/date format
DATETIME ‘0000-00-00 00:00:00’
DATE ‘0000-00-00’
TIMESTAMP ‘0000-00-00 00:00:00’
TIME ’00:00:00′
YEAR 0000 (Only years 0000 and 1901–2155)

The DATETIME and TIMESTAMP data types display the same way. The main difference is that TIMESTAMP has a very narrow range (from the years 1970 through 2037), whereas DATETIME will hold just about any date you’re likely to specify, unless you’re interested in ancient history or science fiction. TIMESTAMP is useful, however, because you can let MySQL set the value for you. If you don’t specify the value when adding a row, the current time is automatically inserted. You can also have MySQL update a TIMESTAMP column each time you change a row.