If you want to back up all your MySQL databases at once (including the system databases such as mysql), you can use a command such as the next one, which would enable you to restore an entire MySQL database installation. Remember to use locking where required.

Use next command in terminal, do not use in mysql client.
Dumping all the MySQL databases to file:
Mariadb command is:
mysqldump -u user -p password -A > all_databases.sql
or mysql command is:
mysqldump -u user -p –all-databases > all_databases.sql

Of course, there’s a lot more than just a few lines of SQL code in
backed-up database files. I recommend that you take a few minutes
to examine a couple in order to familiarize yourself with the types
of commands that appear in backup files and how they work.

To back up only a single table from a database (such as the classics table from the publications database), you should first lock the table from within the MySQL command line, by issuing a command such as the following:

LOCK TABLES publications.classics READ;

This ensures that MySQL remains running for read purposes, but writes cannot be made. Then, while keeping the MySQL command line open, use another terminal window to issue the following command from the operating system command line:

mysqldump -u user -ppassword publications classics > classics.sql

You must now release the table lock by entering the following command from the MySQL command line in the first terminal window, which unlocks all tables that have been locked during the current session:

UNLOCK TABLES;

Now that you have mysqldump working, and have verified it outputs correctly to the screen, you can send the backup data directly to a file using the > redirect symbol.

mysqldump only works under terminal, not under mysql client.
Dumping the etunimitietokantadb database to a file:
mysqldump -u user –p password etunimitietokantadb > etunimitietokantadb.sql

The command in stores the backup file into the current directory. If you need it to be saved elsewhere, you should insert a file path before the filename. You must also ensure that the directory you are backing up to has the right permissions set to allow the file to be written.

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;