Altering Tables - Learning MySQL and MariaDB - page 68

Prudence When Altering Tables
Before doing anystructural changes to a table, especially if it contains data, you should
make a backup of the table to be changed. You should do this even if you're making simple
changes. You might lose part of the data if you inadvertently change the column to a differ-
ent size, and may lose all of the data contained in a column if you change the column type
to one that's incompatible (e.g., from a string to a numeric data type).
If you're altering only one table, you can make a copy of the table within the same database
to use as a backup in case you make a mistake and want to restore the table to how it was
before you started. A better choice would be to make a copy of the table and then alter the
copy. You may even want to put the copy in the test database and alter the table there.
When you're finished altering it, you can use it to replace the original table. We'll cover
this method in more detail later in this chapter.
The best precaution to take, in addition to working with copies of tables, would be touse
the mysqldump utility to make a backup of thetables you're altering or thewhole database.
This utility is covered in Chapter14 . However, to make it easier for you, here is an ex-
ample of what you should enter from the command line — not from the mysql client — to
make a backup of the birds table with mysqldump (you'll need to have read and write
permission for the directory where you're executing it; it's set to the /tmp directory here,
but you should change that to a different directory, perhaps one to which only you have ac-
cess and the filesystem mysql user has read and write permission):
mysqldump -- user = ' russell ' - p \
rookery birds > / tmp / birds . sql
As you can see, the username is given on the first line (you would enter your username in-
stead of mine) within single or double quotes, with the -p option to tell mysqldump to
prompt you for the password. There are many other mysqldump options, but for our pur-
poses, these are all that are necessary. Incidentally, this statement can be entered in one line
from the command line, or it can be entered on multiple lines as shown here by using the
back-slash ( \ ) to let the shell know that more is to follow. On the second line in the preced-
ing code block, the database name is given, followed by the table name. The redirect (>)
tells the shell to send the results of the dump to a text file calledbirds.sqlin the/tmpdirect-
ory.
The previous example makes a backup of just the birds table. It may be best to make a
backup of the whole rookery database. To do this with mysqldump , enter the following
from the command line: