Mysql – Large MYSQL DB on single file

backupMySQLmysqldumpreplication

I have a large MYSQL innodb database (115GB) running on single file mode in MySQL server.

I NEED to move this to file per table mode to allow me to optimize and reduce the overall DB size.

Im looking at various options to do this, but my problem falls in there only being a small window of downtime (roughly 5 hours).

Setup a clone of the server as a slave. Set the slave up with file_per_table, take a mysqldump from the main DB, run in the slave and have this replicating.

I will then look to fail over to the slave.

The other option is the usual mysqldump, drop DB and then import.

My concern is around the time to take the mysqldump and the quality of the dump being such a large size. I have BLOB data in the DB also.

Can anyone offer advise on a good approach?

Thanks

Best Answer

If you're concerned about the downtime mysqldump might cause you can look into hot backups. For example percona-xtrabackup.

Here is what I would do:

Create a slave with xtrabackup (set master_info_repository to table if you have MySQL 5.6.2+)
Have it replicated and let it catch up
Stop the replication: stop slave
(optional) Make a backup of master info file if you have MySQL older than 5.6.2
Make a note of the position with show slave status
Make the mysqldump
Destroy the database and all the files in mysql_datadir and remove all the relay logs
Restore the database with mysql_install_db and execute the dump (make sure you have file_per_table settings)
(only on <5.6.2) If necessary restore replication (if it was in table than the dump contains the information so no need)

Then you can do the switchover and you'll have only a couple of seconds of downtime.

METHOD #1

This method requires that you stop using the good Slave for the duration of the recovery

STEP 01 : On SL2, Run STOP SLAVE;
STEP 02 : On SL1, Run STOP SLAVE;
STEP 03 : On SL1, Run SHOW SLAVE STATUS\G
STEP 04 : Record Read_Master_Log_File and Exec_Master_Log_Pos From STEP 03
STEP 05 : mysqldump data from SL1 to a SQL text file
STEP 06 : Load mysqldump into SL2
STEP 07 : ON SL2, CHANGE MASTER TO master_log_file='(Read_Master_Log_File From STEP 03)',master_log_pos=(Exec_Master_Log_Pos From STEP 03)
STEP 08 : On SL2, Run START SLAVE;
STEP 09 : On SL2, Run SHOW SLAVE STATUS\G and Look at Seconds_Behind_Master
STEP 10 : Repeat STEP 09 until Seconds_Behind_Master is 0

After this, you can use SL1 and SL2 for reads

METHOD #2

This method requires that you shutdown mysqld on SL1 and SL2

STEP 01 : On SL1, Run STOP SLAVE;
STEP 02 : On SL2, Run STOP SLAVE;
STEP 03 : On SL1, Run SHOW SLAVE STATUS\G
STEP 04 : On SL1, Run net stop mysql
STEP 05 : On SL2, Run net stop mysql
STEP 06 : Copy entire datadir of SL1 over to SL2. Make sure you copy master.info file because it contains replication coordinates needed of SL2 to continue replication on startup. Make sure you copied the relay logs as well.
STEP 07 : On SL1, Run net start mysql
STEP 08 : On SL2, Run net start mysql

CAVEAT

If every table is InnoDB, MAS does not need to be shutdown at all. SL1 just needs a stable place to stop replication before copying to SL2.

Give it a Try !!!

UPDATE 2014-03-14 15:51 EDT

If you are planning to use the Master (since you are moving the Master to another Data Center), here is some advice.

STEP 01 : Stop application writes to MAS
STEP 02 : On MAS, run RESET MASTER; (Deletes all binary logs)
STEP 03 : On MAS, net stop mysql
STEP 04 : Copy the MAS server's datadir to the Slave's datadir
STEP 05 : On the new MAS, net start mysql (DO not have any application write to MAS yet)
STEP 06 : ON the New MAS, SHOW MASTER STATUS;
STEP 07 : On the Slave, net start mysql
STEP 08 : On the Slave, run CHANGE MASTER TO master_log_file='binlog from STEP 06',master_log_pos=(Position from STEP 06);
STEP 09 : On the Slave, run START SLAVE;
STEP 10 : Start application writes to MAS

No need to copy binlogs doing it like this. All a Slave cares about is the latest binlog.

MySQL – Partitioning Large Table

First, you should consider solving the problem in another way.

Upgrade to MySQL 5.6, where OPTIMIZE TABLE works without blocking (for an InnoDB table), as it is supported by InnoDB Online DDL.
If you can't upgrade, try using Percona Toolkit's pt-online-schema-change, which can perform the table rebuild without blocking.
```
$ pt-online-schema-change h=localhost,D=mydatabase,t=mytable --execute
    --alter="ENGINE=InnoDB"
```

If you're stuck on using partition, yes, you must make id the partition key in the table you show. You can convert the table to partitioning with ALTER TABLE. If you need the conversion operation to be non-blocking, use pt-online-schema-change.

There's no way to partition to fixed-size partitions. You have to partition by values. But is it really that important to hit a specific size per partition?

Re your comment about partition size:

When using RANGE partitioning, what I do is set up a schedule to ALTER TABLE and split the last partition from time to time. If you have a regular rate of growth, this is easy, but if you have irregular patterns of growth, you might instead set up a periodic check that examines the number of rows per partition (use the INFORMATION_SCHEMA.PARTITIONS), and email you if it's getting full.

For example, let's set up a table partitioned by range on id.

CREATE TABLE `mytable` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `transactionid` int(11) NOT NULL,
  `parent` int(11) NOT NULL,
  `headers` longtext,
  `creator` int(11) NOT NULL,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `transactionid` (`transactionid`,`parent`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (3000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

As the MAX(id) approaches 3000, it's getting close to filling up p3 and spilling over into p4. So it's time to reorganize. It's good to do this before any data spills over into p4, because the reorg will affect only the last, empty partition and will therefore be very quick.

ALTER TABLE mytable REORGANIZE PARTITION p4 INTO 
(PARTITION p4 VALUES LESS THAN (4000), PARTITION p5 VALUES LESS THAN MAXVALUE);

Even if you miss a day and you get some data into the old p4, chances are it's not much data. But if you neglect this for a month or two, and p4 fills up with a lot of data, then the REORGANIZE will take longer.