Syntax

LOAD DATA FROM MASTER

Description

This feature has been removed from recent versions of MariaDB.

Since the current implementation of LOAD DATA FROM MASTER and LOAD TABLE FROM MASTER is very limited, these statements are deprecated in versions 4.1 of MySQL and above. We will introduce a more advanced technique (called "online backup") in a future version. That technique will have the additional advantage of working with more storage engines.

For MySQL 5.1 and earlier, the recommended alternative solution to using LOAD DATA FROM MASTER orLOAD TABLE FROM MASTER is using mysqldump or mysqlhotcopy. The latter requires Perl and two Perl modules (DBI and DBD:mysql) and works forMyISAM and ARCHIVE tables only. With mysqldump, you can create SQL dumps on the master and pipe (or copy) these to a mysql client on the slave. This has the advantage of working for all storage engines, but can be quite slow, since it works using SELECT.

This statement takes a snapshot of the master and copies it to the slave. It updates the values of MASTER_LOG_FILE andMASTER_LOG_POS so that the slave starts replicating from the correct position. Any table and database exclusion rules specified with the--replicate-*-do-* and--replicate-*-ignore-* options are honored.--replicate-rewrite-db is not taken into account because a user could use this option to set up a non-unique mapping such as--replicate-rewrite-db="db1->db3" and--replicate-rewrite-db="db2->db3", which would confuse the slave when loading tables from the master.

Use of this statement is subject to the following conditions:

• It works only for MyISAM tables. Attempting to load a non-MyISAM table results in the following error:ERROR 1189 (08S01): Net error reading from master

• It acquires a global read lock on the master while taking the snapshot, which prevents updates on the master during the load operation.

If you are loading large tables, you might have to increase the values ofnet_read_timeout and net_write_timeout on both the master and slave servers. See Server System Variables.

Note that LOAD DATA FROM MASTER does not copy any tables from the mysql database. This makes it easy to have different users and privileges on the master and the slave.

To use LOAD DATA FROM MASTER, the replication account that is used to connect to the master must have the RELOAD andSUPER privileges on the master and theSELECT privilege for all master tables you want to load. All master tables for which the user does not have theSELECT privilege are ignored byLOAD DATA FROM MASTER. This is because the master hides them from the user: LOAD DATA FROM MASTER callsSHOW DATABASES to know the master databases to load, butSHOW DATABASES returns only databases for which the user has some privilege. On the slave side, the user that issues LOAD DATA FROM MASTER must have privileges for dropping and creating the databases and tables that are copied.

_{This page is licensed: GPLv2, originally from fill_help_tables.sql}

Syntax

LOAD TABLE tbl_name FROM MASTER

Description

This feature has been removed from recent versions of MariaDB.

Since the current implementation of LOAD DATA FROM MASTER and LOAD TABLE FROM MASTER is very limited, these statements are deprecated in versions 4.1 of MySQL and above. We will introduce a more advanced technique (called "online backup") in a future version. That technique will have the additional advantage of working with more storage engines.

For MariaDB and MySQL 5.1 and earlier, the recommended alternative solution to using LOAD DATA FROM MASTER orLOAD TABLE FROM MASTER is using mysqldump or mysqlhotcopy. The latter requires Perl and two Perl modules (DBI and DBD:mysql) and works forMyISAM and ARCHIVE tables only. With mysqldump, you can create SQL dumps on the master and pipe (or copy) these to a mysql client on the slave. This has the advantage of working for all storage engines, but can be quite slow, since it works using SELECT.

Transfers a copy of the table from the master to the slave. This statement is implemented mainly debugging LOAD DATA FROM MASTER operations. To use LOAD TABLE, the account used for connecting to the master server must have the RELOAD andSUPER privileges on the master and theSELECT privilege for the master table to load. On the slave side, the user that issues LOAD TABLE FROM MASTER must have privileges for dropping and creating the table.

The conditions for LOAD DATA FROM MASTER apply here as well. For example, LOAD TABLE FROM MASTER works only for MyISAM tables. The timeout notes for LOAD DATA FROM MASTER apply as well.

_{This page is licensed: GPLv2, originally from fill_help_tables.sql}

Note: This page is obsolete. The information is old, outdated, or otherwise currently incorrect. We are keeping the page for historical reasons only. Do not rely on the information in this article.

This page describes a "feature preview release" which previewed some replication-related features which are included in MariaDB 5.3. If you would like to try out the features mentioned here, it is recommended that you use MariaDB 5.3 (download MariaDB 5.3 here) instead of the actual release described below. Likewise, the code is available in the MariaDB 5.3 tree on Launchpad.

About this release

There has been quite a lot of interest in these features, and providing this feature preview release allows the developers to get more and earlier feedback, as well as allowing more users an early opportunity to evaluate the new features.

This feature preview release is based on MariaDB 5.2, adding a number of fairly isolated features that are considered complete and fairly well-tested. It is however not a stable or GA release, nor is it planned to be so.

The stable release including these features will be MariaDB 5.3. That being said, we greatly welcome any feedback / bug reports, and will strive to fix any issues found and we will update the feature preview until MariaDB 5.3 stable is ready.

Download/Installation

These packages are generated the same way as "official" MariaDB releases. Please see the main download pages for more detailed instructions on installation etc.

The instructions below use the mirrorftp.osuosl.org, but any of the MariaDB mirrors can be used by replacing the appropriate part of the URLs. See themain download page for what mirrors are available.

Debian/Ubuntu

For Debian and Ubuntu, it is highly recommended to install from the repositories, using apt-get, aptitude, or other favorite package managers.

First import the public key with which the repositories are signed, so that apt can verify the integrity of the packages it downloads. For example like this:

wget -O- http://ftp.osuosl.org/pub/mariadb/PublicKey | sudo apt-key add -

Now add the appropriate repository. An easy way is to create a file calledmariadb-5.2-rpl.list in /etc/apt/sources.list.d/ with contents like this for Debian:

deb http://ftp.osuosl.org/pub/mariadb/mariadb-5.2-rpl/debian squeeze main
deb-src http://ftp.osuosl.org/pub/mariadb/mariadb-5.2-rpl/debian squeeze main

Or this for Ubuntu:

deb http://ftp.osuosl.org/pub/mariadb/mariadb-5.2-rpl/ubuntu maverick main
deb-src http://ftp.osuosl.org/pub/mariadb/mariadb-5.2-rpl/ubuntu maverick main

Replace "squeeze" or "maverick" in the examples above with the appropriate distribution name. Supported are "lenny" and "squeeze" for Debian, and "hardy", "jaunty", "karmic", "lucid", and "maverick" for Ubuntu.

Now run

sudo apt-get update

The packages can now be installed with your package manager of choice, for example:

sudo apt-get install mariadb-server-5.2

(To manually download and install packages, browse the directories below- the .debs are indebian/pool/ and ubuntu/pool/, respectively.)

Generic Linux binary tarball

Generic linux binary tarballs can be downloaded here:

• i386 (32-bit):

• amd64 (64-bit):

Centos 5 RPMs

• i386 (32-bit):

• amd64 (64-bit):

Windows (32-bit)

Source tarball

Launchpad bzr branch:

• lp:~maria-captains/maria/mariadb-5.2-rpl

New Features in the MariaDB 5.2 replication feature preview

Here is a summary of the new features included in this preview release. The headings link to more detailed information.

Group commit for the binary log

This preview release implements group commit which works when using XtraDB with the binary log enabled. (In previous MariaDB releases, and all MySQL releases at the time of writing, group commit works in InnoDB/XtraDB when the binary log is disabled, but stops working when the binary log is enabled).

Enhancements for START TRANSACTION WITH CONSISTENT SNAPSHOT

START TRANSACTION WITH CONSISTENT SNAPSHOT now also works with the binary log. This means it is possible to obtain the binlog position corresponding to a transactional snapshot of the database without blocking any other queries. This is used by mysqldump --single-transaction --master-data to do a fully non-blocking backup which can be used to provision a new slave.

START TRANSACTION WITH CONSISTENT SNAPSHOT now also works consistently between transactions involving more than one storage engine (currently XTraDB and PBXT support this).

Annotation of row-based replication events with the original SQL statement

When using row-based replication, the binary log does not contain SQL statements, only discrete single-row insert/update/delete events. This can make it harder to read mysqlbinlog output and understand where in an application a given event may have originated, complicating analysis and debugging.

This feature adds an option to include the original SQL statement as a comment in the binary log (and shown in mysqlbinlog output) for row-based replication events.

Row-based replication for tables with no primary key

This feature can improve the performance of row-based replication on tables that do not have a primary key (or other unique key), but which do have another index that can help locate rows to update or delete. With this feature, index cardinality information from ANALYZE TABLE is considered when selecting the index to use (before this feature is implemented, the first index was selected unconditionally).

PBXT consistent commit ordering

This feature implements the new commit ordering storage engine API in PBXT. With this feature, it is possible to use START TRANSACTION WITH CONSISTENT SNAPSHOT and get consistency among transactions which involve both XtraDB and InnoDB. (Without this feature, there is no such consistency guarantee. For example, even after running START TRANSACTION WITH CONSISTENT SNAPSHOT it was still possible for the InnoDB/XtraDB part of some transaction T to be visible and the PBXT part of the same transaction T to not be visible.)

Miscellaneous

• This preview also includes a small change to make mysqlbinlog omit redundant use statements around BEGIN, SAVEPOINT, COMMIT, and ROLLBACK events when reading MySQL 5.0 binlogs.

• The preview included a feature--innodb-release-locks-early. However we decided to omit this feature from future MariaDB releases because of a fundamental design bug, lp:798213.

_{This page is licensed: CC BY-SA / Gnu FDL}

The --innodb-release-locks-early feature (MWL#163) was included in the 5.2 replication preview. However, it was omitted from MariaDB 5.3 due to the buglp:798213.

_{This page is licensed: CC BY-SA / Gnu FDL}