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那么强大，所以一般都不怎么用.