mysql升级 oracle_MySQL应用DDL升级方法

MySQL应用DDL升级方法

对于一个稳定数据库应用来说，DDL变更操作很会非常少。但是一个新上线的应用或是经常需要增加功能

应用来说，这时候对数据库的变更可能就会变得很频繁。大部分mysql的DDL 操作对会阻止应用程序对数据库的

DML操作，这时候选择一种对应用程序影响较少的方法变得很重要.

在mysql的环境中，对应用程序的DDL的变更主要有以下几种方法

1,在主从的环境中，先在其中的一台从库上做变更，然后提升一台从库作为新的主库，其他的从库再从新提起来

从库做同步。等所有的从库都变更完成后，再把最开始的主库再次提升为从库，这种方法也称为ROLLING SCHEMA

UPGRADE(RSU)

在主库上有表

create table t1( a int primary key auto_increment);

假设现在需要在t1表上增加一个栏位b，默认值0

停止从库的同步，再增加这个栏位 ,

SLAVE>stop slave;

Query OK, 0 rows affected (0.01 sec)

SLAVE>alter table t1 add column b int default 0;

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

主库一直在继续操作。

在从库上再次启去slave;

SLAVE>start slave;

Query OK, 0 rows affected (0.00 sec)

SLAVE>select * from t1;

+---+------+

| a | b    |

+---+------+

| 1 |    0 |

| 2 |    0 |

+---+------+

2 rows in set (0.00 sec)

变更成功，等待再次完全同步成功，

提升从库为主库，应用程序也进行相应的切换.同时记住这个时候提升主库的上的binlog位置

SLAVE>stop slave;

Query OK, 0 rows affected (0.00 sec)

SLAVE>reset slave;

Query OK, 0 rows affected (0.00 sec)

其他的从库指向提升后的主库

在把原来的主库上做变更

mysql> alter table t1 add column b int default 0;

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0

主库变为从库，并指向提升后的主库

mysql> change master to

-> master_host='172.28.10.192',

-> master_user='root',

-> master_password='123456',

-> master_log_file='34.000001',

-> master_log_pos=825;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

再采用类似的办法对其他的从库一台一台做升级。

从以上的过程中可以看出这种方法十分繁杂，当从库数量比较多的时候进一步增加了复杂性.

同时有几点限制:1,不适用所有的变更，比如增加栏位，如果栏位不能为非空，且没有默认值

2,binlog的格式不能为ROW或是MIX;3,新增加的栏位必须要是表的最后面的栏位。

2,使用第三方工具，比如pt-online-schema-change(pos)或是fackbook开发的类似工具,使用

起来比较简单，对于从库来说透明，一直是mysql dba用得最多的变更工具.

比如现在新增一个栏位

[root@c12 data]# pt-online-schema-change --user=root --password=root --host=172.28.10.150  --alter "add column c int not null default 10" D=db1,t=t1 --execute --print

Cannot connect to D=db1,h=172.28.10.192,p=...,u=root

No slaves found.  See --recursion-method if host c12.fb.com has slaves.

Not checking slave lag because no slaves were found and --check-slave-lag was not specified.

Operation, tries, wait:

copy_rows, 10, 0.25

create_triggers, 10, 1

drop_triggers, 10, 1

swap_tables, 10, 1

update_foreign_keys, 10, 1

Altering `db1`.`t1`...

Creating new table...

CREATE TABLE `db1`.`_t1_new` (

`a` int(11) NOT NULL AUTO_INCREMENT,

`b` int(11) DEFAULT '0',

PRIMARY KEY (`a`)

) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1

Created new table db1._t1_new OK.

Altering new table...

ALTER TABLE `db1`.`_t1_new` add column c int not null default 10

Altered `db1`.`_t1_new` OK.

2015-07-08T03:45:39 Creating triggers...

CREATE TRIGGER `pt_osc_db1_t1_del` AFTER DELETE ON `db1`.`t1` FOR EACH ROW DELETE IGNORE FROM `db1`.`_t1_new` WHERE `db1`.`_t1_new`.`a` <=> OLD.`a`

CREATE TRIGGER `pt_osc_db1_t1_upd` AFTER UPDATE ON `db1`.`t1` FOR EACH ROW REPLACE INTO `db1`.`_t1_new` (`a`, `b`) VALUES (NEW.`a`, NEW.`b`)

CREATE TRIGGER `pt_osc_db1_t1_ins` AFTER INSERT ON `db1`.`t1` FOR EACH ROW REPLACE INTO `db1`.`_t1_new` (`a`, `b`) VALUES (NEW.`a`, NEW.`b`)

2015-07-08T03:45:40 Created triggers OK.

2015-07-08T03:45:40 Copying approximately 14 rows...

INSERT LOW_PRIORITY IGNORE INTO `db1`.`_t1_new` (`a`, `b`) SELECT `a`, `b` FROM `db1`.`t1` LOCK IN SHARE MODE /*pt-online-schema-change 21402 copy table*/

2015-07-08T03:45:40 Copied rows OK.

2015-07-08T03:45:40 Swapping tables...

RENAME TABLE `db1`.`t1` TO `db1`.`_t1_old`, `db1`.`_t1_new` TO `db1`.`t1`

2015-07-08T03:45:40 Swapped original and new tables OK.

2015-07-08T03:45:40 Dropping old table...

DROP TABLE IF EXISTS `db1`.`_t1_old`

2015-07-08T03:45:40 Dropped old table `db1`.`_t1_old` OK.

2015-07-08T03:45:40 Dropping triggers...

DROP TRIGGER IF EXISTS `db1`.`pt_osc_db1_t1_del`;

DROP TRIGGER IF EXISTS `db1`.`pt_osc_db1_t1_upd`;

DROP TRIGGER IF EXISTS `db1`.`pt_osc_db1_t1_ins`;

2015-07-08T03:45:40 Dropped triggers OK.

Successfully altered `db1`.`t1`.

基本过程是：1，创建一个和原来样一模一样的表; 2,对这个新表做变更，3，在原来的表上增加一个触发器，当对原来的表上有dml操作时，需要写到新的表中;4,拷贝原表的数据到新表中;5,把原来的表重命名，新生成的表更名为原来的表名.

限制:1,有主键约束为非常麻烦;2,如果表非常大，拷贝表的过程会产生空间占用的问题，比如表大小和产生的binlog等等.3,原来的表上不能有触发器；4，在rename的过程中会产生瞬间的中断.

3,依靠脆弱的mysql自身的在线ddl功能，在5.6开始已经有部分功能可以在线操作了，估计5.7会更多DDL操作

添加进来.现在并不是很好用，也没有pos那么强大，所以一般都不怎么用.