Oracle 5.0 Reference Manual Download

Page: 1509 / 3232

Using Replication for Scale-Out

1489

• If you used

mysqldump

to create the database snapshot on your master, you could edit the dump

file text to change the engine type used on each table.

Another alternative for

mysqldump

is to disable engine types that you do not want to use on the

slave before using the dump to build the data on the slave. For example, you can add the

--skip-

innodb

[1236]

option on your slave to disable the

InnoDB

engine. If a specific engine does not exist

for a table to be created, MySQL will use the default engine type, usually

MyISAM

. (This requires that

the

NO_ENGINE_SUBSTITUTION

[537]

SQL mode is not enabled.) If you want to disable additional

engines in this way, you may want to consider building a special binary to be used on the slave that
only supports the engines you want.

• If you are using raw data files (a binary backup) to set up the slave, you will be unable to change the

initial table format. Instead, use

ALTER TABLE

to change the table types after the slave has been

started.

• For new master/slave replication setups where there are currently no tables on the master, avoid

specifying the engine type when creating new tables.

If you are already running a replication solution and want to convert your existing tables to another
engine type, follow these steps:

1. Stop the slave from running replication updates:

mysql>

STOP SLAVE;

This will enable you to change engine types without interruptions.

2. Execute an

ALTER TABLE ... ENGINE='engine_type'

for each table to be changed.

3. Start the slave replication process again:

mysql>

START SLAVE;

Although the

storage_engine

[497]

and

table_type

[499]

variables are not replicated, be aware

that

CREATE TABLE

and

ALTER TABLE

statements that include the engine specification will be

correctly replicated to the slave. For example, if you have a CSV table and you execute:

mysql>

ALTER TABLE csvtable Engine='MyISAM';

The above statement will be replicated to the slave and the engine type on the slave will be converted
to

MyISAM

, even if you have previously changed the table type on the slave to an engine other than

CSV. If you want to retain engine differences on the master and slave, you should be careful to use the

storage_engine

[497]

variable on the master when creating a new table. For example, instead of:

mysql>

CREATE TABLE tablea (columna int) Engine=MyISAM;

Use this format:

mysql>

SET storage_engine=MyISAM;

mysql>

CREATE TABLE tablea (columna int);

When replicated, the

storage_engine

[497]

variable will be ignored, and the

CREATE TABLE

statement will execute on the slave using the slave's default engine.

16.3.3. Using Replication for Scale-Out

You can use replication as a scale-out solution; that is, where you want to split up the load of database
queries across multiple database servers, within some reasonable limitations.

Because replication works from the distribution of one master to one or more slaves, using replication
for scale-out works best in an environment where you have a high number of reads and low number of
writes/updates. Most Web sites fit into this category, where users are browsing the Web site, reading
articles, posts, or viewing products. Updates only occur during session management, or when making a
purchase or adding a comment/message to a forum.

Oracle 5.0, Reference Manual

Search results

Summary of Contents for 5.0

Reviews:

Related manuals for 5.0

Brands by name

Popular brands