SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table tab_02 as select * from tab_tmp
/*!*/;
# at 213
#140130 12:21:32 server id 1 end_log_pos 338 Query thread_id=3 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1391055692/*!*/;
create table tab01
(id int(10) primary key ,
name varchar(20))
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@mynode1 mysql]#
可以将此DDL语句在slave库执行
[plain] view plaincopyprint
SLAVE@root@test 12:26:39>alter table tab01 add index(name);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
SLAVE@root@test 12:27:57>show create table tab01\G
*************************** 1. row ***************************
Table: tab01
Create Table: CREATE TABLE `tab01` (
`id` int(10) NOT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
SLAVE@root@test 12:28:10>
SLAVE@root@test 12:26:39>alter table tab01 add index(name);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
SLAVE@root@test 12:27:57>show create table tab01\G
*************************** 1. row ***************************
Table: tab01
Create Table: CREATE TABLE `tab01` (
`id` int(10) NOT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
SLAVE@root@test 12:28:10>
在大型master-slave环境下执行DDL语句时，在每一台slave上手动去执行DDL命令能绕过MySQL数据复制单线程对某些命令的限制。
相关阅读：