You can backup a
table in a MySQL or MariaDB database by using the
mysqldump backup tool included with both database
management systems. To backup a specific table you can use the command
mysqldump database_name table_name where
database_name is the name of the database that contains the table and
table_name is the name of the particular table you wish to backup. E.g.:
mysqldump -u ptolemy -p Planets Mars > Mars.sql
In the above case, if I have a database named Planets with
a table within it named Mars, I could backup just that one table
from the database with the command above. The -u option to the
command allows you to specify a MySQL/MariaDB username that has access to
the database and the -p option will prompt you for the password
associated with that username. The Mars.sql file will then
contain all of the
Structured Query
Language (SQL) commands needed to recreate the table structure and the
data in the table. E.g., it will contain insert SQL commands that will insert entries in the table.
If you wish to backup the entire database, you can omit the table name
after the database name, e.g., if I wanted to backup the entire Planets
database in the example above, I could use the command
mysqldump Planets > Planets.sql. If the account from which
I was running the command wasn't one with access to the database, I could
again specify a username with the -u option and ask to be
prompted for the password with the -p option. To backup
multiple databases at once, I could use mysqldump --databases db1 db2
db3 > outputfile.sql. You can also use --databases
for just one database or you can omit it, if there is only one database to
backup. To backup all databases on the system, you can use
mysqldump --alldatabases, which might require specification of
the root MySQL/MariaDB account, e.g. mysqldump -u root -p --alldatabases
.
To restore a table to a database from a dump file of SQL commands, you
can issue the command mysql -u user_name -p database_name
< backup_filename. E.g. to restore the Mars table in the
Planets database from the first example, I could use mysql -u ptolemy -p
Planets < Mars.sql. You need to specify the database, e.g., Planets
in this case, to which the table should be placed. Any existing table
of that name will be dropped first. If you backed up one database,
you can specify the databasename on the command line, or you can edit
the backup file to put a use database_name; command at the
top of the file. Alternatively, you can obtain the MySQL/MariaDB prompt
by just entering mysql at the command line and then typing
commands as shown below:
mysql -u username -p mysql> use database_name; mysql> source backup_filename;
If the database doesn't already exist, you will need to create it first
with the create database database_name command. E.g.,
if I backed up the Mars table as in the example above, but then needed to
put it in a new database called Planets:
mysql -u username -p mysql> create database Planets; mysql> use Planets; mysql> source Mars.sql;
References: