Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
 
For 2023 (and 2024 ...) - we are now fully retired from IT training.
We have made many, many friends over 25 years of teaching about Python, Tcl, Perl, PHP, Lua, Java, C and C++ - and MySQL, Linux and Solaris/SunOS too. Our training notes are now very much out of date, but due to upward compatability most of our examples remain operational and even relevant ad you are welcome to make us if them "as seen" and at your own risk.

Lisa and I (Graham) now live in what was our training centre in Melksham - happy to meet with former delegates here - but do check ahead before coming round. We are far from inactive - rather, enjoying the times that we are retired but still healthy enough in mind and body to be active!

I am also active in many other area and still look after a lot of web sites - you can find an index ((here))
Copying databases and tables in MySQL

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

Related Material

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.

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2024: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 793803 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/solutions/mysql-co ... mysql.html • PAGE BUILT: Wed Mar 28 07:47:11 2012 • BUILD SYSTEM: wizard