This page is the answer to questions like "can I save the results from a query into a table of its own?" ... but before we you start, ask yourself the question WHY are you copying (duplicating) your data? If you produce a second copy of your information then:
* You'll have to maintain both copies in future or
* The second copy will get out of step from the first and
* You may have a hard time resynchronising.
Still reading? Ok - there ARE good reasons for duplicating data, tables, structures, etc., sometimes. Here are some options.
USING CSV FILE
If you want to transfer information across to a spread sheet such as Excel, or export it from one database and export it into another in a different structure, you may find that CSV (Comma Separated Variable) files are the best way to do it. ((You'll find with Excel that you would in fact use "TSV" or tab separated variable files.))
Example
select * into outfile "/tmp/orch.tsv"
fields terminated by "\t"
from instruments;
trumpet brass 5 1
trombone brass 3 2
viola string 3 3
cello string 2 4
cor anglais wind 1 6
You can the use a LOAD DATA INFILE to reload the data.
USING MYSQLDUMP
The MySQLdump utility takes a complete database (or all the databases that are looked after by a single daemon if you use the -A option) and produces a file of MySQL commands that can be used to recreate the database(s). Great for backup and restore purposes, and great if you want to copy a complete database onto a different server.
Example - a command sequence to copy a database called copsource from a machine called fire into a new database called music on a machine called wind. It's assumed that both systems have a login called trainee with sufficient access rights, and that the MySQL daemon is already running on both.
mysqldump -utrainee -p -hfire copsource > sdata.sql
mysql -utrainee -p -hwind
create database music;
use music;
source sdata.sql;
exit;
CREATE TABLE LIKE
If you want to create a new table which has the same STRUCTURE as a table that already exists, but is empty, you can use the CREATE TABLE LIKE command. Available in version 4.1 of MySQL and later.
create table orchestra like instruments;
REPLICA SERVERS
If you're working with a MySQL server that's answering very large numbers of select queries, or if you require extremely high availability, you may choose to set up one or more replica servers. All data updates are performed on your master server, with the replica servers grabbing any changes as they're made to stay in sync and to provide a wider and more robust SELECT resource.
More ...
MYSQLHOTCOPY
Mysqlhotcopy is a Perl script that uses LOCK TABLES, FLUSH TABLES, and cp or scp to make a database or table backup quickly. It can be run only on the same machine where the database directories are located. Mysqlhotcopy works only for backing up MyISAM tables and runs on Unix, Linux and Netware systems.
INSERT ... SELECT
Allows you to select rows from one table and insert them into another, renaming fields if you wish to do so. The destination table must already exist.
Example:
insert into orchestra (variety, section, oid)
select variety, section, iid
from instruments
where section = "brass";
mysql> select * from instruments;
+-------------+---------+----------------+-----+
| variety | section | orchestracount | iid |
+-------------+---------+----------------+-----+
| trumpet | brass | 5 | 1 |
| trombone | brass | 3 | 2 |
| viola | string | 3 | 3 |
| cello | string | 2 | 4 |
| cor anglais | wind | 1 | 6 |
+-------------+---------+----------------+-----+
5 rows in set (0.00 sec)
mysql> select * from orchestra;
+----------+---------+----------------+-----+
| variety | section | orchestracount | oid |
+----------+---------+----------------+-----+
| trumpet | brass | NULL | 1 |
| trombone | brass | NULL | 2 |
+----------+---------+----------------+-----+
2 rows in set (0.00 sec)
See also
MySQL training course
Please note that articles in this section of our
web site were current and correct to the best of our ability when published,
but by the nature of our business may go out of date quite quickly. The
quoting of a price, contract term or any other information in this area of
our website is NOT an offer to supply now on those terms - please check
back via
our main web site
More MySQL commands [158] - ()
[159] - ()
[279] - ()
[449] - ()
[494] - ()
[502] - ()
[513] - ()
[515] - ()
[517] - ()
[567] - ()
[572] - ()
[581] - ()
[591] - ()
[673] - ()
[1213] - ()
[1235] - ()
[1331] - ()
[1574] - ()
[1735] - ()
[1904] - ()
[2110] - ()
[2259] - ()
[2448] - ()
[2643] - ()
[2644] - ()
[2645] - ()
[2647] - ()
[3061] - ()
[3270] - ()
[4481] - ()
Replication in MySQL [2210] - ()
Data Access and Security in MySQL [192] - ()
[193] - ()
[401] - ()
[535] - ()
[647] - ()
[947] - ()
[1131] - ()
[2204] - ()
[2263] - ()
[2647] - ()
[3270] - ()
[4390] - ()
[4406] - ()
[4481] - ()
[4491] - ()
[4493] - ()
resource index - MySQL
Solutions centre home page
You'll find shorter technical items at
The Horse's Mouth and
delegate's questions answered at
the
Opentalk forum.
At Well House Consultants, we provide
training courses on
subjects such as Ruby, Lua, Perl, Python, Linux, C, C++,
Tcl/Tk, Tomcat, PHP and MySQL. We're asked (and answer)
many questions, and answers to those which are of general
interest are published in this area of our site.