Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
 
Python and Tcl - public course schedule [here]
Private courses on your site - see [here]
Please ask about maintenance training for Perl, PHP, Lua, etc
 
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
  [4481] Extracting data from backups to restore selected rows from MySQL tables - (2015-05-01)
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [3061] Databases - why data is split into separate tables, and how to join them - (2010-11-20)
  [2647] Removing duplicates from a MySQL table - (2010-02-22)
  [2645] Optimising and caching your MySQL enquiries - (2010-02-22)
  [2644] Counting rows in joined MySQL tables - (2010-02-22)
  [2643] Relating tables with joins in MySQL - (2010-02-21)
  [2448] MySQL - efficiency and other topics - (2009-10-10)
  [2259] Grouping rows for a summary report - MySQL and PHP - (2009-06-27)
  [2110] MySQL - looking for records in one table that do NOT correspond to records in another table - (2009-03-31)
  [1904] Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23)
  [1735] Finding words and work boundaries (MySQL, Perl, PHP) - (2008-08-03)
  [1574] Joining MySQL tables revisited - finding nonmatching records, etc - (2008-03-15)
  [1331] MySQL joins revisited - (2007-09-03)
  [1235] Outputting numbers as words - MySQL with Perl or PHP - (2007-06-17)
  [1213] MySQL - the order of clauses and the order of actions - (2007-06-01)
  [673] Helicopter views and tartans - (2006-04-06)
  [591] Key facts - SQL and MySQL - (2006-02-04)
  [581] Saving a MySQL query results to your local disc for Excel - (2006-01-29)
  [572] Giving the researcher power over database analysis - (2006-01-22)
  [567] Combining similar rows from a MySQL database select - (2006-01-17)
  [517] An occasional chance, and reducing data to manageable levels - (2005-12-04)
  [515] MySQL - an FAQ - (2005-12-03)
  [513] MySQL - JOIN or WHERE to link tables correctly? - (2005-12-01)
  [502] SELECT in MySQL - choosing the rows you want - (2005-11-22)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [449] Matching in MySQL - (2005-09-24)
  [279] Getting a list of unique values from a MySQL column - (2005-04-14)
  [159] MySQL - Optimising Selects - (2004-12-21)
  [158] MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN - (2004-12-20)

Replication in MySQL
  [2210] MySQL server replication - (2009-06-01)

Data Access and Security in MySQL
  [4493] Forgotten / lost MySQL root password - (2015-05-16)
  [4491] Web Server Admin - some of those things that happen, and solutions - (2015-05-10)
  [4481] Extracting data from backups to restore selected rows from MySQL tables - (2015-05-01)
  [4406] Fixing damaged MySQL tables - Error 1712 and Error 2013 - (2015-01-25)
  [4390] Checking MySQL database backups have worked (not failed) - (2015-01-10)
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [2647] Removing duplicates from a MySQL table - (2010-02-22)
  [2263] Mysqldump fails as a cron job - a work around - (2009-06-30)
  [2204] Images in a database? How big is a database? (MySQL) - (2009-05-28)
  [1131] MySQL - Password security (authentication protocol) - (2007-04-02)
  [947] What is an SQL injection attack? - (2006-11-27)
  [647] Checking for MySQL errors - (2006-03-15)
  [535] MySQL permissions and privileges - (2005-12-20)
  [401] What is an SQL injection attack? - (2005-08-02)
  [193] The wrong MySQL - (2005-01-29)
  [192] Current MySQL and PHP paths and upgrades - (2005-01-28)

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., 2019: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01225 708225 • FAX: 01225 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