Copying Tables or Databases to Another Server
10.17.1 Problem
You want to copy tables or databases from one MySQL server to another.
10.17.2 Solution
Use mysqldump and mysql together, connected by a pipe.
10.17.3 Discussion
SQL-format output from mysqldump can be used to copy tables or databases from one server to another. Suppose you want to copy the states table from the cookbook database on the local host to the cb database on the host other-host.com. One way to do this is to dump the output into a file (as described in Recipe 10.16):
% mysqldump cookbook states > dump.txt
Then copy dump.txt to other-host.com and run the following command there to import the table into that server's cb database:
% mysql cb < dump.txt
Another way to accomplish this without using an intermediary file is to send the output of mysqldump directly over the network to the remote MySQL server. If you can connect to both servers from the host where the cookbook database resides, use this command:
% mysqldump cookbook states | mysql -h other-host.com cb
The mysqldump half of the command connects to the local server and writes the dump output to the pipe. The mysql half of the command connects to the remote MySQL server on other-host.com. It reads the pipe for input and sends each statement to the other-host.com server.
If you cannot connect directly to the remote server using mysql from your local host, send the dump output into a pipe that uses ssh to invoke mysql remotely on other-host.com:
% mysqldump cookbook states | ssh other-host.com mysql cb
ssh connects to other-host.com and launches mysql there. Then it reads the mysqldump output from the pipe and passes it to the remote mysql process. Using ssh can be useful when you want to send a dump over the network to a machine that has the MySQL port blocked by a firewall but that allows connections on the SSH port.
If you don't have access to ssh, you may be able to use rsh instead. However, rsh is insecure, so ssh is much preferred.
To copy multiple tables over the network, name them all following the database argument of the mysqldump command. To copy an entire database, don't specify any table names after the database name. mysqldump will dump all the tables contained in the database.
If you're thinking about invoking mysqldump with the --all-databases option to send all your databases to another server, consider that the output will include the tables in the mysql database that contains the grant tables. If the remote server has a different user population, you probably don't want to replace that server's grant tables!
Using the mysql Client Program
- Introduction
- Setting Up a MySQL User Account
- Creating a Database and a Sample Table
- Starting and Terminating mysql
- Specifying Connection Parameters by Using Option Files
- Protecting Option Files
- Mixing Command-Line and Option File Parameters
- What to Do if mysql Cannot Be Found
- Setting Environment Variables
- Issuing Queries
- Selecting a Database
- Canceling a Partially Entered Query
- Repeating and Editing Queries
- Using Auto-Completion for Database and Table Names
- Using SQL Variables in Queries
- Telling mysql to Read Queries from a File
- Telling mysql to Read Queries from Other Programs
- Specifying Queries on the Command Line
- Using Copy and Paste as a mysql Input Source
- Preventing Query Output from Scrolling off the Screen
- Sending Query Output to a File or to a Program
- Selecting Tabular or Tab-Delimited Query Output Format
- Specifying Arbitrary Output Column Delimiters
- Producing HTML Output
- Producing XML Output
- Suppressing Column Headings in Query Output
- Numbering Query Output Lines
- Making Long Output Lines More Readable
- Controlling mysqls Verbosity Level
- Logging Interactive mysql Sessions
- Creating mysql Scripts from Previously Executed Queries
- Using mysql as a Calculator
- Using mysql in Shell Scripts
Writing MySQL-Based Programs
- Introduction
- Connecting to the MySQL Server, Selecting a Database, and Disconnecting
- Checking for Errors
- Writing Library Files
- Issuing Queries and Retrieving Results
- Moving Around Within a Result Set
- Using Prepared Statements and Placeholders in Queries
- Including Special Characters and NULL Values in Queries
- Handling NULL Values in Result Sets
- Writing an Object-Oriented MySQL Interface for PHP
- Ways of Obtaining Connection Parameters
- Conclusion and Words of Advice
Record Selection Techniques
- Introduction
- Specifying Which Columns to Display
- Avoiding Output Column Order Problems When Writing Programs
- Giving Names to Output Columns
- Using Column Aliases to Make Programs Easier to Write
- Combining Columns to Construct Composite Values
- Specifying Which Rows to Select
- WHERE Clauses and Column Aliases
- Displaying Comparisons to Find Out How Something Works
- Reversing or Negating Query Conditions
- Removing Duplicate Rows
- Working with NULL Values
- Negating a Condition on a Column That Contains NULL Values
- Writing Comparisons Involving NULL in Programs
- Mapping NULL Values to Other Values for Display
- Sorting a Result Set
- Selecting Records from the Beginning or End of a Result Set
- 18 Pulling a Section from the Middle of a Result Set
- Choosing Appropriate LIMIT Values
- Calculating LIMIT Values from Expressions
- What to Do When LIMIT Requires the Wrong Sort Order
- Selecting a Result Set into an Existing Table
- Creating a Destination Table on the Fly from a Result Set
- Moving Records Between Tables Safely
- Creating Temporary Tables
- Cloning a Table Exactly
- Generating Unique Table Names
Working with Strings
- Introduction
- Writing Strings That Include Quotes or Special Characters
- Preserving Trailing Spaces in String Columns
- Testing String Equality or Relative Ordering
- Decomposing or Combining Strings
- Checking Whether a String Contains a Substring
- Pattern Matching with SQL Patterns
- Pattern Matching with Regular Expressions
- Matching Pattern Metacharacters Literally
- Controlling Case Sensitivity in String Comparisons
- Controlling Case Sensitivity in Pattern Matching
- Using FULLTEXT Searches
- Using a FULLTEXT Search with Short Words
- Requiring or Excluding FULLTEXT Search Words
- Performing Phrase Searches with a FULLTEXT Index
Working with Dates and Times
- Introduction
- Changing MySQLs Date Format
- Telling MySQL How to Display Dates or Times
- Determining the Current Date or Time
- Decomposing Dates and Times Using Formatting Functions
- Decomposing Dates or Times Using Component-Extraction Functions
- Decomposing Dates or Times Using String Functions
- Synthesizing Dates or Times Using Formatting Functions
- Synthesizing Dates or Times Using Component-Extraction Functions
- Combining a Date and a Time into a Date-and-Time Value
- Converting Between Times and Seconds
- Converting Between Dates and Days
- Converting Between Date-and-Time Values and Seconds
- Adding a Temporal Interval to a Time
- Calculating Intervals Between Times
- Breaking Down Time Intervals into Components
- Adding a Temporal Interval to a Date
- Calculating Intervals Between Dates
- Canonizing Not-Quite-ISO Date Strings
- Calculating Ages
- Shifting Dates by a Known Amount
- Finding First and Last Days of Months
- Finding the Length of a Month
- Calculating One Date from Another by Substring Replacement
- Finding the Day of the Week for a Date
- Finding Dates for Days of the Current Week
- Finding Dates for Weekdays of Other Weeks
- Performing Leap Year Calculations
- Treating Dates or Times as Numbers
- Forcing MySQL to Treat Strings as Temporal Values
- Selecting Records Based on Their Temporal Characteristics
- Using TIMESTAMP Values
- Recording a Rows Last Modification Time
- Recording a Rows Creation Time
- Performing Calculations with TIMESTAMP Values
- Displaying TIMESTAMP Values in Readable Form
Sorting Query Results
- Introduction
- Using ORDER BY to Sort Query Results
- Sorting Subsets of a Table
- Sorting Expression Results
- Displaying One Set of Values While Sorting by Another
- Sorting and NULL Values
- Controlling Case Sensitivity of String Sorts
- Date-Based Sorting
- Sorting by Calendar Day
- Sorting by Day of Week
- Sorting by Time of Day
- Sorting Using Substrings of Column Values
- Sorting by Fixed-Length Substrings
- Sorting by Variable-Length Substrings
- Sorting Hostnames in Domain Order
- Sorting Dotted-Quad IP Values in Numeric Order
- Floating Specific Values to the Head or Tail of the Sort Order
- Sorting in User-Defined Orders
- Sorting ENUM Values
Generating Summaries
- Introduction
- Summarizing with COUNT( )
- Summarizing with MIN( ) and MAX( )
- Summarizing with SUM( ) and AVG( )
- Using DISTINCT to Eliminate Duplicates
- Finding Values Associated with Minimum and Maximum Values
- Controlling String Case Sensitivity for MIN( ) and MAX( )
- Dividing a Summary into Subgroups
- Summaries and NULL Values
- Selecting Only Groups with Certain Characteristics
- Determining Whether Values are Unique
- Grouping by Expression Results
- Categorizing Non-Categorical Data
- Controlling Summary Display Order
- Finding Smallest or Largest Summary Values
- Date-Based Summaries
- Working with Per-Group and Overall Summary Values Simultaneously
- Generating a Report That Includes a Summary and a List
Modifying Tables with ALTER TABLE
- Introduction
- Dropping, Adding, or Repositioning a Column
- Changing a Column Definition or Name
- The Effect of ALTER TABLE on Null and Default Value Attributes
- Changing a Columns Default Value
- Changing a Table Type
- Renaming a Table
- Adding or Dropping Indexes
- Eliminating Duplicates by Adding an Index
- Using ALTER TABLE to Normalize a Table
Obtaining and Using Metadata
- Introduction
- Obtaining the Number of Rows Affected by a Query
- Obtaining Result Set Metadata
- Determining Presence or Absence of a Result Set
- Formatting Query Results for Display
- Getting Table Structure Information
- Getting ENUM and SET Column Information
- Database-Independent Methods of Obtaining Table Information
- Applying Table Structure Information
- Listing Tables and Databases
- Testing Whether a Table Exists
- Testing Whether a Database Exists
- Getting Server Metadata
- Writing Applications That Adapt to the MySQL Server Version
- Determining the Current Database
- Determining the Current MySQL User
- Monitoring the MySQL Server
- Determining Which Table Types the Server Supports
Importing and Exporting Data
- Introduction
- Importing Data with LOAD DATA and mysqlimport
- Specifying the Datafile Location
- Specifying the Datafile Format
- Dealing with Quotes and Special Characters
- Importing CSV Files
- Reading Files from Different Operating Systems
- Handling Duplicate Index Values
- Getting LOAD DATA to Cough Up More Information
- Dont Assume LOAD DATA Knows More than It Does
- Skipping Datafile Lines
- Specifying Input Column Order
- Skipping Datafile Columns
- Exporting Query Results from MySQL
- Exporting Tables as Raw Data
- Exporting Table Contents or Definitions in SQL Format
- Copying Tables or Databases to Another Server
- Writing Your Own Export Programs
- Converting Datafiles from One Format to Another
- Extracting and Rearranging Datafile Columns
- Validating and Transforming Data
- Validation by Direct Comparison
- Validation by Pattern Matching
- Using Patterns to Match Broad Content Types
- Using Patterns to Match Numeric Values
- Using Patterns to Match Dates or Times
- Using Patterns to Match Email Addresses and URLs
- Validation Using Table Metadata
- Validation Using a Lookup Table
- Converting Two-Digit Year Values to Four-Digit Form
- Performing Validity Checking on Date or Time Subparts
- Writing Date-Processing Utilities
- Using Dates with Missing Components
- Performing Date Conversion Using SQL
- Using Temporary Tables for Data Transformation
- Dealing with NULL Values
- Guessing Table Structure from a Datafile
- A LOAD DATA Diagnostic Utility
- Exchanging Data Between MySQL and Microsoft Access
- Exchanging Data Between MySQL and Microsoft Excel
- Exchanging Data Between MySQL and FileMaker Pro
- Exporting Query Results as XML
- Importing XML into MySQL
- Epilog
Generating and Using Sequences
- Introduction
- Using AUTO_INCREMENT To Set Up a Sequence Column
- Generating Sequence Values
- Choosing the Type for a Sequence Column
- The Effect of Record Deletions on Sequence Generation
- Retrieving Sequence Values
- Determining Whether to Resequence a Column
- Extending the Range of a Sequence Column
- Renumbering an Existing Sequence
- Reusing Values at the Top of a Sequence
- Ensuring That Rows Are Renumbered in a Particular Order
- Starting a Sequence at a Particular Value
- Sequencing an Unsequenced Table
- Using an AUTO_INCREMENT Column to Create Multiple Sequences
- Managing Multiple SimultaneousAUTO_INCREMENT Values
- Using AUTO_INCREMENT Valuesto Relate Tables
- Using Single-Row Sequence Generators
- Generating Repeating Sequences
- Numbering Query Output Rows Sequentially
Using Multiple Tables
- Introduction
- Combining Rows in One Table with Rows in Another
- Performing a Join Between Tables in Different Databases
- Referring to Join Output Column Names in Programs
- Finding Rows in One Table That Match Rows in Another
- Finding Rows with No Match in Another Table
- Finding Rows Containing Per-Group Minimum or Maximum Values
- Computing Team Standings
- Producing Master-Detail Lists and Summaries
- Using a Join to Fill in Holes in a List
- Enumerating a Many-to-Many Relationship
- Comparing a Table to Itself
- Calculating Differences Between Successive Rows
- Finding Cumulative Sums and Running Averages
- Using a Join to Control Query Output Order
- Converting Subselects to Join Operations
- Selecting Records in Parallel from Multiple Tables
- Inserting Records in One Table That Include Values from Another
- Updating One Table Based on Values in Another
- Using a Join to Create a Lookup Table from Descriptive Labels
- Deleting Related Rows in Multiple Tables
- Identifying and Removing Unattached Records
- Using Different MySQL Servers Simultaneously
Statistical Techniques
- Introduction
- Calculating Descriptive Statistics
- Per-Group Descriptive Statistics
- Generating Frequency Distributions
- Counting Missing Values
- Calculating Linear Regressions or Correlation Coefficients
- Generating Random Numbers
- Randomizing a Set of Rows
- Selecting Random Items from a Set of Rows
- Assigning Ranks
Handling Duplicates
- Introduction
- Preventing Duplicates from Occurring in a Table
- Dealing with Duplicates at Record-Creation Time
- Counting and Identifying Duplicates
- Eliminating Duplicates from a Query Result
- Eliminating Duplicates from a Self-Join Result
- Eliminating Duplicates from a Table
Performing Transactions
- Introduction
- Verifying Transaction Support Requirements
- Performing Transactions Using SQL
- Performing Transactions from Within Programs
- Using Transactions in Perl Programs
- Using Transactions in PHP Programs
- Using Transactions in Python Programs
- Using Transactions in Java Programs
- Using Alternatives to Transactions
Introduction to MySQL on the Web
- Introduction
- Basic Web Page Generation
- Using Apache to Run Web Scripts
- Using Tomcat to Run Web Scripts
- Encoding Special Characters in Web Output
Incorporating Query Resultsinto Web Pages
- Introduction
- Displaying Query Results as Paragraph Text
- Displaying Query Results as Lists
- Displaying Query Results as Tables
- Displaying Query Results as Hyperlinks
- Creating a Navigation Index from Database Content
- Storing Images or Other Binary Data
- Retrieving Images or Other Binary Data
- Serving Banner Ads
- Serving Query Results for Download
Processing Web Input with MySQL
- Introduction
- Creating Forms in Scripts
- Creating Single-Pick Form Elements from Database Content
- Creating Multiple-Pick Form Elements from Database Content
- Loading a Database Record into a Form
- Collecting Web Input
- Validating Web Input
- Using Web Input to Construct Queries
- Processing File Uploads
- Performing Searches and Presenting the Results
- Generating Previous-Page and Next-Page Links
- Generating Click to Sort Table Headings
- Web Page Access Counting
- Web Page Access Logging
- Using MySQL for Apache Logging
Using MySQL-Based Web Session Management
- Introduction
- Using MySQL-Based Sessions in Perl Applications
- Using MySQL-Based Storage with the PHP Session Manager
- Using MySQL for Session BackingStore with Tomcat
Appendix A. Obtaining MySQL Software
- Appendix A. Obtaining MySQL Software
- A.1. Obtaining Sample Source Code and Data
- A.2. Obtaining MySQL and Related Software
Appendix B. JSP and Tomcat Primer
- Appendix B. JSP and Tomcat Primer
- B.1. Servlet and JavaServer Pages Overview
- B.2. Setting Up a Tomcat Server
- B.3. Web Application Structure
- B.4. Elements of JSP Pages
Appendix C. References
