Database Reference
In-Depth Information
To restore the preceding backup, we can use the
mysql
command-line client as
follows:
mysql -u root -p test < test.sql
As with the
mysqldump
example, we use a redirect character, but this time it is
redirecting in the opposite direction, from the
test.sql
file to the
mysql
client.
The
mysql
client reads the file and executes all of the SQL commands, restoring the
backed up tables and their data.
We can also use
mysqldump
to create tab-delimited files. This is done using the
--tab
option. When using this option,
mysqldump
will create two files. A
tablename.sql
file with the SQL commands to recreate the table, and a
tablename.txt
file with the
actual data in tab-delimited format. Here is an example using
mysqldump
and
--tab
to backup up the employees table in our test database:
mysqldump --tab /tmp/ -u root -p test employees
The
--tab
option needs a directory after it where it can write the files. The SQL file is
owned by whichever user we used to run the
mysqldump
command. The TXT file, on
the other hand is owned by the
mysql
user, so whatever directory we specify needs
to have permissions so that both users can write to it. The
/tmp/
directory is used in
the example since, by default on Linux, this directory can be written to by anyone.
So why would a tab-delimited file of our data be useful? Well, for starters, the
mysqlimport
program reads tab-delimited files. Popular spreadsheets also read
and write to tab-delimited files. So, for example, if we've been keeping our data in
a spreadsheet, and have decided to move it to a MariaDB database, we can export
our spreadsheet data as a tab-delimited file, create the tables in MariaDB, and then
use
mysqlimport
to import our data. At a later point, we could use
mysqldump
to
dump the data or a subset of the data to a file and then open it with our spreadsheet
program, and create some nice pie charts or other graphs.
There are scores of other options that we can use to tweak and customize what and
how
mysqldump
backs up our data. It's well worth it to take the time necessary to
learn all of its many options.
Full documentation of the
mysqldump
utility is found at
https://mariadb.com/kb/en/mysqldump/
.
