Hey there

I have a not-so-big (50GB, including indexes) database (MySQL 4.0.24,
mixed MyISAM and InnoDB tables) that I need to migrate to a MySQL 5.1
Master + Slave Replication server, with InnoDB tables only, over a new
hardware.

Unfortunately, this is the first time I play a role as MySQL DBA, and
don't have a complete migration plan (yet).

Can someone here please review my migration plan and me
adding/prunning missing/exceeding bits?

My current plan is more or less like below. I removed task descriptions
removed, guess the task name is descriptive enough. Indenting marks
sub-tasks, as expected.

Many thanks in advance.
Cheers!

----- BEGIN TASK LIST -----
Database Migration Project
Current Database Procedures Mapping
Plan Database Test Procedure
Determine Current Database Backup Procedures
Determine Current Database Restore Procedures
Obtain Copies of the Current Database Backup Scripts
Obtain Copies of the Current Database Restore Scripts
Determine Current Database Stored Procedures / Triggers
Database Statistics Collection
Determine Current Database Size
Determine Current Database Grow Ratio
Determine Current Database Schema
Operating System Installation and Configuration Plan
Determine Hardware Requirements
Determine Operating System Requirements
Determine Required Operating System Configuration
Plan Operating System Installation
Plan Operating System Configuration
Document Changes on Operating System
Database Software Installation and Configuration Plan
Study Need for Database Build
Plan Database Build
Plan Database Installation
Plan Database Configuration
Database Performance Tunning
Determine Current Database Queries
Determine Current Database Performance Indicators
Plan Measurement Procedure for the Performance Indicators
Measure Current Database Performance
Database Schema Refactoring
Obtain Current Database Schema Definition
Review Database Schema
Review Database Stored Procedures and Triggers
Suggest Changes for Database Schema
Study Desired Database Backward Compatibility Level
Design Views to Allow Database Backward Compatibility
Redesign Database
Write SQL for Changes
Staging System Deployment and Testing
Install Operating System in the Staging Environment
Configure Operating System in the Staging Envinronment
Build Database Software in Staging Environment
Install Database Software in Staging Environment
Configure Database Software in Staging Environment
Obtain Current Copy of the Database Backup from Production
Restore Database Using the Restore Procedure
Fix or Improve Restore Procedure
Test Database in Staging Environment
Measure Staging Database Performance Indicators
Compare Performance Figures for the Staging Database
Production Database Deployment
Plan Database Maintenance Stop
Advertise Database Maintenance Stop
Install Operating System in the Production Environment
Configure Operating System in the Production Envinronment
Build Database Software in Production Environment
Install Database Software in Production Environment
Configure Database Software in Production Environment
Stop Production Database
Obtain Current Copy of the Database Backup from Production
Restore Database Using the Restore Procedure
Test Database in Production Environment
Start Database in Production Environment
Database Monitoring
Follow Up Database Behavior and Performance
----- END TASK LIST -----

--
Luis Motta Campos (a.k.a. Monsieur Champs) is a software engineer,
Perl fanatic evangelist, and amateur {cook, photographer}

Hi Luis,

On Jan 2, 2008 8:53 AM, Luis Motta Campos <luismottacampos@yahoo.co.uk> wrote:
> Hey there
>
> I have a not-so-big (50GB, including indexes) database (MySQL 4.0.24,
> mixed MyISAM and InnoDB tables) that I need to migrate to a MySQL 5.1
> Master + Slave Replication server, with InnoDB tables only, over a new
> hardware.
>
> Unfortunately, this is the first time I play a role as MySQL DBA, and
> don't have a complete migration plan (yet).
>
> Can someone here please review my migration plan and me
> adding/prunning missing/exceeding bits?
>
> My current plan is more or less like below. I removed task descriptions
> removed, guess the task name is descriptive enough. Indenting marks
> sub-tasks, as expected.
>
> Many thanks in advance.
> Cheers!
>
> ----- BEGIN TASK LIST -----
> Database Migration Project
> Current Database Procedures Mapping
> Plan Database Test Procedure
> Determine Current Database Backup Procedures
> Determine Current Database Restore Procedures
> Obtain Copies of the Current Database Backup Scripts
> Obtain Copies of the Current Database Restore Scripts
> Determine Current Database Stored Procedures / Triggers
> Database Statistics Collection
> Determine Current Database Size
> Determine Current Database Grow Ratio
> Determine Current Database Schema
> Operating System Installation and Configuration Plan
> Determine Hardware Requirements
> Determine Operating System Requirements
> Determine Required Operating System Configuration
> Plan Operating System Installation
> Plan Operating System Configuration
> Document Changes on Operating System
> Database Software Installation and Configuration Plan
> Study Need for Database Build
> Plan Database Build
> Plan Database Installation
> Plan Database Configuration
> Database Performance Tunning
> Determine Current Database Queries
> Determine Current Database Performance Indicators
> Plan Measurement Procedure for the Performance Indicators
> Measure Current Database Performance
> Database Schema Refactoring
> Obtain Current Database Schema Definition
> Review Database Schema
> Review Database Stored Procedures and Triggers
> Suggest Changes for Database Schema
> Study Desired Database Backward Compatibility Level
> Design Views to Allow Database Backward Compatibility
> Redesign Database
> Write SQL for Changes
> Staging System Deployment and Testing
> Install Operating System in the Staging Environment
> Configure Operating System in the Staging Envinronment
> Build Database Software in Staging Environment
> Install Database Software in Staging Environment
> Configure Database Software in Staging Environment
> Obtain Current Copy of the Database Backup from Production
> Restore Database Using the Restore Procedure
> Fix or Improve Restore Procedure
> Test Database in Staging Environment
> Measure Staging Database Performance Indicators
> Compare Performance Figures for the Staging Database
> Production Database Deployment
> Plan Database Maintenance Stop
> Advertise Database Maintenance Stop
> Install Operating System in the Production Environment
> Configure Operating System in the Production Envinronment
> Build Database Software in Production Environment
> Install Database Software in Production Environment
> Configure Database Software in Production Environment
> Stop Production Database
> Obtain Current Copy of the Database Backup from Production
> Restore Database Using the Restore Procedure
> Test Database in Production Environment
> Start Database in Production Environment
> Database Monitoring
> Follow Up Database Behavior and Performance
> ----- END TASK LIST -----

What are the biggest changes you anticipate? I'd say they will be the
version upgrade, converting to InnoDB, and using replication. It
looks like you have planned well for all but using replication.
Unless you are familiar with it, that is likely to be a bigger change
than the version upgrade and switch to InnoDB. There's a lot to learn
about replication if you haven't used it before. I'd suggest that you
read the manual chapters about replication and binary logging, and
definitely experiment with replication.

Cheers
Baron

Baron Schwartz wrote:
> What are the biggest changes you anticipate? I'd say they will be the
> version upgrade, converting to InnoDB, and using replication. It
> looks like you have planned well for all but using replication.

That's interesting. What kind of activities (besides configuration,
maybe?) are interesting to add?

> Unless you are familiar with it, that is likely to be a bigger change
> than the version upgrade and switch to InnoDB.

That's alarming. I was expecting replication to be a lesser change, not
something this big. Isn't predicting that the replication is one of the
three biggest changes I have in my database a bit of an exaggeration?

> There's a lot to learn about replication if you haven't used it
> before. I'd suggest that you read the manual chapters about
> replication and binary logging, and definitely experiment with
> replication.

Thanks for the advice. I will start reading right away.

I would like to read more comments from the members of this list.

Thank you all for your patience and .
Cheers!
--
Luis Motta Campos (a.k.a. Monsieur Champs) is a software engineer,
Perl fanatic evangelist, and amateur {cook, photographer}

Hi,

On Jan 2, 2008 10:04 AM, Luis Motta Campos <luismottacampos@yahoo.co.uk> wrote:
> Baron Schwartz wrote:
> > What are the biggest changes you anticipate? I'd say they will be the
> > version upgrade, converting to InnoDB, and using replication. It
> > looks like you have planned well for all but using replication.
>
> That's interesting. What kind of activities (besides configuration,
> maybe?) are interesting to add?

Lots of experimentation!

Let me ask it another way: how are you planning to use replication?
-- for load balancing/scaling reads, backups, a hot standby machine?

> > Unless you are familiar with it, that is likely to be a bigger change
> > than the version upgrade and switch to InnoDB.
>
> That's alarming. I was expecting replication to be a lesser change, not
> something this big. Isn't predicting that the replication is one of the
> three biggest changes I have in my database a bit of an exaggeration?

Definitely not. It is a huge change. From one server to a two-server
replication setup is a quantum leap. You'll have all kinds of new
things to think about, such as data consistency, dealing with
replication lag, performance changes on the master due to binary
logging, etc.

It depends a lot on WHY you're using replication (see my earlier question).

Baron Schwartz wrote:
> On Jan 2, 2008 10:04 AM, Luis Motta Campos wrote:
>> Baron Schwartz wrote:
>>> What are the biggest changes you anticipate? I'd say they will be
>>> the version upgrade, converting to InnoDB, and using
>>> replication. It looks like you have planned well for all but
>>> using replication.
>> That's interesting. What kind of activities (besides configuration,
>> maybe?) are interesting to add?
>
> Lots of experimentation!
>
> Let me ask it another way: how are you planning to use replication?
> -- for load balancing/scaling reads, backups, a hot standby machine?

OK, I guess a bit more of information can . I have a very sensitive
database at the company, almost unprotected at the moment. It's big
hardware, lots of hot-replaceable parts, and a quite big energy
generator attached, but nothing guarantees our data between the last
backup and the present moment case the building burns down to the ground.

The database I/O activity obey the 80-20 rule: 80% reads and 20% writes.
I have an average of 80,000 read-queries per second (that's average - I
got 'nice' peaks sometimes, with 4 times more activity). We have 20,000
write-queries per second in the same database.

As we're addressing the risk of major disasters (like the building
burning down to ashes), the plan is spreading this database through the
two data centers we hire: one data center would hold the master
database, and a spare reading slave, and the other data center would
hold "the" reading slave and a spare master database.

Don't worry about connectivity issues: I have plenty of bandwidth
between those two sites, and I can ask for more. I just need to know in
advance how much to ask for, and I will surely get it.

The master/spare and slave/spare machines would be connected through
heartbeat and will keep the database in a DRDB filesystem. This will
guarantee that, in case of failure, the other machine can raise the same
IP address in it's own interface and continue operations after a short
delay.

Now, about the existing database: it runs Debian Stable, and a
pre-compiled mysql server. As I said before, it's a big machine, but
it's getting old (more than 2 years already), and must be replaced.

The system running on it is quite old, more than 10 years old. This
means that the design and implementation aren't nice. And the
maintenance added new improvements where needed (as InnoDB tables).

The basic idea is to completely re-design and re-implement the database
as soon as the new server is in place (being stability and failure
resistance the priorities fixed by management for the first stage).

>> Isn't predicting that the replication is one of the three biggest
>> changes I have in my database a bit of an exaggeration?
>
> Definitely not. It is a huge change. From one server to a
> two-server replication setup is a quantum leap. You'll have all
> kinds of new things to think about, such as data consistency, dealing
> with replication lag, performance changes on the master due to
> binary logging, etc.
>
> It depends a lot on WHY you're using replication (see my earlier
> question).

Well, I guess I don't need to worry about multi-master replication for
now, the application must be re-designed and re-implemented before we
can start thinking about this. It's currently quite hard to maintain,
and we don't know a lot of important things about it. There is a team
working on this already.

About data consistency, I would like to have some pointers: is this an
issue, provided that I stick to single-master architectures?

About binary logging, I must first read more about this. Thanks for the
warning, though.

Replication lag is another thing that worries me a lot - is there a
mathematical model I can use in order to forecast the expected average
replication lag?

The application can handle quite big replication lags at the moment.
It's not web-based, and there is no users directly interacting with it
at the present moment, what makes everything much simpler - you can have
a program waiting for 5 minutes to "see" a change without a lot of fuss
about it.

Sorry, I guess this email is a bit bigger than I was expecting...
I hope this gives you a general idea about my current problem.
Please feel free to ask more about it if you feel that this details
aren't enough.

Kind regards.
--
Luis Motta Campos (a.k.a. Monsieur Champs) is a software engineer,
Perl fanatic evangelist, and amateur {cook, photographer}

Hi Luis,

On Jan 2, 2008 11:12 AM, Luis Motta Campos <luismottacampos@yahoo.co.uk> wrote:
> Baron Schwartz wrote:
> > On Jan 2, 2008 10:04 AM, Luis Motta Campos wrote:
> >> Baron Schwartz wrote:
> >>> What are the biggest changes you anticipate? I'd say they will be
> >>> the version upgrade, converting to InnoDB, and using
> >>> replication. It looks like you have planned well for all but
> >>> using replication.
> >> That's interesting. What kind of activities (besides configuration,
> >> maybe?) are interesting to add?
> >
> > Lots of experimentation!
> >
> > Let me ask it another way: how are you planning to use replication?
> > -- for load balancing/scaling reads, backups, a hot standby machine?
>
> OK, I guess a bit more of information can . I have a very sensitive
> database at the company

[snip]

> The master/spare and slave/spare machines would be connected through
> heartbeat and will keep the database in a DRDB filesystem. This will
> guarantee that, in case of failure, the other machine can raise the same
> IP address in it's own interface and continue operations after a short
> delay.

I suspect that this won't go smoothly on the first try, and if it's
very sensitive... No offense, but you're talking about a lot of
complexity here, and you're not familiar with replication yet. DRBD,
failover etc are even more complex.

I personally would hire an expert to me plan and implement this
upgrade. MySQL AB has consultants, but there's also Pythian, Proven
Scaling, and Percona, to name a few whom I think are very familiar
with the tools and technologies you're talking about.

(Why do the consulting companies all have names that start with P, I wonder?)

I know now how to reply to all mail list

On Jan 2, 2008 9:53 PM, Luis Motta Campos <luismottacampos@yahoo.co.uk>
wrote:

> Hey there
>
> I have a not-so-big (50GB, including indexes) database (MySQL 4.0.24,
> mixed MyISAM and InnoDB tables) that I need to migrate to a MySQL 5.1
> Master + Slave Replication server, with InnoDB tables only, over a new
> hardware.
>
> Unfortunately, this is the first time I play a role as MySQL DBA, and
> don't have a complete migration plan (yet).
>
> Can someone here please review my migration plan and me
> adding/prunning missing/exceeding bits?
>
> My current plan is more or less like below. I removed task descriptions
> removed, guess the task name is descriptive enough. Indenting marks
> sub-tasks, as expected.
>
> Many thanks in advance.
> Cheers!
>
> ----- BEGIN TASK LIST -----
> Database Migration Project
> Current Database Procedures Mapping
> Plan Database Test Procedure
> Determine Current Database Backup Procedures
> Determine Current Database Restore Procedures
> Obtain Copies of the Current Database Backup Scripts
> Obtain Copies of the Current Database Restore Scripts
> Determine Current Database Stored Procedures / Triggers
> Database Statistics Collection
> Determine Current Database Size
> Determine Current Database Grow Ratio
> Determine Current Database Schema
> Operating System Installation and Configuration Plan
> Determine Hardware Requirements
> Determine Operating System Requirements
> Determine Required Operating System Configuration
> Plan Operating System Installation
> Plan Operating System Configuration
> Document Changes on Operating System
> Database Software Installation and Configuration Plan
> Study Need for Database Build
> Plan Database Build
> Plan Database Installation
> Plan Database Configuration
> Database Performance Tunning
> Determine Current Database Queries
> Determine Current Database Performance Indicators
> Plan Measurement Procedure for the Performance Indicators
> Measure Current Database Performance
> Database Schema Refactoring
> Obtain Current Database Schema Definition
> Review Database Schema
> Review Database Stored Procedures and Triggers
> Suggest Changes for Database Schema
> Study Desired Database Backward Compatibility Level
> Design Views to Allow Database Backward Compatibility
> Redesign Database
> Write SQL for Changes
> Staging System Deployment and Testing
> Install Operating System in the Staging Environment
> Configure Operating System in the Staging Envinronment
> Build Database Software in Staging Environment
> Install Database Software in Staging Environment
> Configure Database Software in Staging Environment
> Obtain Current Copy of the Database Backup from Production
> Restore Database Using the Restore Procedure
> Fix or Improve Restore Procedure
> Test Database in Staging Environment
> Measure Staging Database Performance Indicators
> Compare Performance Figures for the Staging Database
> Production Database Deployment
> Plan Database Maintenance Stop
> Advertise Database Maintenance Stop
> Install Operating System in the Production Environment
> Configure Operating System in the Production Envinronment
> Build Database Software in Production Environment
> Install Database Software in Production Environment
> Configure Database Software in Production Environment
> Stop Production Database
> Obtain Current Copy of the Database Backup from Production
> Restore Database Using the Restore Procedure
> Test Database in Production Environment
> Start Database in Production Environment
> Database Monitoring
> Follow Up Database Behavior and Performance
> ----- END TASK LIST -----
>
> --
> Luis Motta Campos (a.k.a. Monsieur Champs) is a software engineer,
> Perl fanatic evangelist, and amateur {cook, photographer}
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=y...0608@gmail.com
>
>


--
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn