|
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
[ More Info ]
> filename to redirect its output to a file.
The format of the command to backup one or more databases is
mysqldump --databases db1 db2 db3 > outputfile.sql where
db1, db2, etc. are the names of the databases
you wish to back up and outputfile.sql is the name of the output
file to which the dababase will be dumped.. The --databases
option causes all names on the command line to be treated as database
names. Without this option, mysqldump treats the first name as a database
name and those following as table names.
# mysqldump -p --databases partsdb > partsdb-20111016.sql Enter password:
In the above example, the -p option causes mysql to prompt
for the mysql account password with the username assumed to be the same as
the login name for the account from which you are running the command.
If you wish to specify the username, use --user=user_name or
-u user_name.
If you want to back up all databases you can use the command
mysqldump --all-databases > dump.sql to back up all databases
to which the account has access on the system to a file named dump.sql
in this case.
A dump file can be used in several ways:
As a backup to enable data recovery in case of data loss.
As a source of data for setting up replication slaves.
As a source of data for experimentation:
To make a copy of a database that you can use without changing the original data.
To test potential upgrade incompatibilities.
An example script to dump mysql databases to a gzip file which will be transferred to another server via SSH for safekeeping can be found at How to have a rock solid Linux backup without pro budget. Placing you dump file on another system ensures you don't lose your data should the system on which your databases reside suffer a hard drive crash or some other catastrophe.
References:
SHOW WARNINGS statement in MySQL to show
the error, warning, and note messages that resulted from the last
statement that generated messages in the current session. It shows nothing
if the last statement used a table and generated no messages. (That is, a
statement that uses a table but generates no messages clears the message
list.) Statements that do not use tables and do not generate messages
have no effect on the message list.
For example, if I issue the statement DROP TABLE IF EXISTS
smf_testing statement as below, I see a notice that 0 rows were
affected, but there was 1 warning. To see the warning, I can immediately
afterwards issue the statement SHOW WARNINGS. I see that the
warning was simply indicating that the table I asked be dropped, didn't
actually exist.
mysql> DROP TABLE IF EXISTS smf_testing; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +-------+------+-----------------------------+ | Level | Code | Message | +-------+------+-----------------------------+ | Note | 1051 | Unknown table 'smf_testing' | +-------+------+-----------------------------+ 1 row in set (0.00 sec) mysql>
References:
create table New_Table like Existing_Table;
New_Table = the name you wish to give to the new table
Existing_Table = the name of the existing table
Remember to use the same case of letters for the existing table name as were actually used for that table name. E.g., if the table name is "Existing_Table" use "Existing_Table" not "existing_table"
Once you have the software installed and configured, you can set up a new database using the instructions in Creating a MySQL Database.
-u testacct parameter specifies that the client should
be started using the account named testacct, while the
-p parameter indicates that the system should prompt you
for the password.
delivered, which
will hold a delivery date for a shipment, to the table requests,
you could use the command below:delivered after the column orderdate.
I could use the command below:requests, I could use the command below:References:
