在使用MySQL的时候,经常需要修改表结构,修改方法也很简单,直接使用alter table进行修改。

1、给某个表添加字段

alter table  add    [];

例如:

alter table table1 add comments varchar(10) not Null;

alter table table1 add id int unsigned not Null auto_increment primary key

2、修改某个表的字段类型及指定为空或非空

alter table  modify   [];

例如:

alter table table1 modify comments varchar(255) not Null;

3、修改某个表的字段名称、类型及指定为空或非空

alter table  change    [];

例如:

alter table table1 change comments remark varchar(100) not Null;

4、修改某个表的字段名称及指定为空或非空

alter table  modify   [];

例如:

alter table table1 modify remark varchar(255) not Null;

5、删除某一字段(一般都不会删除,实在占用名称,直接rename掉就好了)

ALTER TABLE mytable DROP ;

alter table执行过程:

  1. 按照原始表的表结构和ddl语句,新建一个不可见的临时表 (temporary_table)

  2. 在原表上面加上 WRITE LOCK 阻塞所有的更新操作 (insert、delete、update等操作)

  3. 执行 insert into tmp_table select * from original_table

  4. rename original_table 和 tmp_table 最后 drop original_table

  5. 最后释放掉 write lock

alter table会导致锁表,而且一旦执行就不可被kill,一旦执行就不可回退,表越大,锁表的时间就会很长。因此,我们可以选择一些可以在线修改的工具进行字段的变更。

percona-toolkit

percona-toolkit是一组高级命令行工具的集合,可以查看当前服务的摘要信息,磁盘检测,分析慢查询日志,查找重复索引,实现表同步等等,源自Maatkit 和Aspersa工具。

1、安装

这里是针对CentOS,如果是Debian系列的可以使用apt的源

yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm

yum install -y  perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes

yum -y install  percona-toolkit

也可以直接现在二进制的包直接使用。

下载地址:https://www.percona.com/downloads/percona-toolkit/LATEST/

2、使用

pt-online-schema-change --no-version-check --user=root --password=root --host=127.0.0.1  P=3306,D=testdb,t=t1 --alter "ADD COLUMN comments varchar(255) DEFAULT ''" --execute
No slaves found.  See --recursion-method if host tosomeone has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `testdb`.`t1`...
Creating new table...
Created new table testdb._t1_new OK.
Altering new table...
Altered `testdb`.`_t1_new` OK.
2019-04-26T22:07:46 Creating triggers...
2019-04-26T22:07:46 Created triggers OK.
2019-04-26T22:07:46 Copying approximately 1 rows...
2019-04-26T22:07:46 Copied rows OK.
2019-04-26T22:07:46 Analyzing new table...
2019-04-26T22:07:46 Swapping tables...
2019-04-26T22:07:46 Swapped original and new tables OK.
2019-04-26T22:07:46 Dropping old table...
2019-04-26T22:07:46 Dropped old table `testdb`.`_t1_old` OK.
2019-04-26T22:07:46 Dropping triggers...
2019-04-26T22:07:46 Dropped triggers OK.
Successfully altered `testdb`.`t1`.

3、参数说明

--user:
-u

--password:
-p

--database:
-D

--port
-P

--host:
-h

--socket:
-S

--ask-pass
MySQL的密码

--charset


--defaults-file
-F

--alter:
alter table关键字
    RENAME来重命名表        
    
    
    (drop foreign key constrain_name)_constraint_nameconstraint_name
    CONSTRAINT `fk_foo` FOREIGN KEY (`foo_id`) REFERENCES `bar` (`foo_id`)--alter "DROP FOREIGN KEY _fk_foo"

--alter-foreign-keys-method
?,.,
,,
    auto rebuild_constraints和drop_swap两种处理方式中选择一个
    rebuild_constraints使 ALTER TABLE语句先删除外键约束,.,
    drop_swap FOREIGN_KEY_CHECKS=0,,,,,
    1, ,,
    2, ,.
    none "drop_swap",,

--[no]check-alter
yes--dry-run 和 --print 一起运行,来检查是否有问题(change column,drop primary key)。

--max-lag
1schunk拷贝完成后Slave的延迟情况使Seconds_Behind_Master--check-interval指定的时间,再检查。如果从被停止,将会永远等待,直到从开始同步,并且延迟小于该值。如果指定--check-slave-lag,该工具只检查该服务器的延迟,而不是所有服务器。

--check-slave-lag
DSN连接地址,--max-lag参数设置的值,就会暂停操作。

--recursion-method
show processlisthostreport_hostshow slave hosts来找到none来不检查Slave
METHOD       USES
===========  ==================
processlist  SHOW PROCESSLIST
hosts        SHOW SLAVE HOSTS
dsn=DSN      DSNs from a table
none         Do not find slaves
none则表示不在乎从的延迟
--check-interval 
1--max-lag检查的睡眠时间。 

--[no]check-plan 
yes

--[no]check-replication-filters 
yesbinlog_ignore_db和replicate_do_db此类退Master上存在Slave上不存在使no-check-replication-filters选项来禁用该检查 

--[no]swap-tables 
yes--[no]drop-old-table。 

--[no]drop-triggers 
yes --no-drop-triggers 会强制开启 --no-drop-old-table 即:不删除触发器就会强制不删除原表。 

--new-table-name 
%T_new 

--[no]drop-new-table 
yes 

--[no]drop-old-table 
yes 

--max-load 
Threads_running=25chunk拷贝完后SHOW GLOBAL STATUS的内容 status指标=MAX_VALUE或者status指标:MAX_VALUEMAX_VALUE120% 

--critical-load 
Threads_running=50--max-load类似,如果不指定MAX_VALUE,那么工具会这只其为当前值的200%。如果超过指定值,则工具直接退出,而不是暂停。 

--default-engine 
使InnoDB的使InnoDB的使使 

--set-vars 
MySQL变量 wait_timeout=10000 innodb_lock_wait_timeout=1 lock_wait_timeout=60 

--chunk-size-limit
chunk-size大小,.4.0,

--chunk-time
chunk-time执行的时间内,chunk-size的大小,0,chunk-size,

--chunk-size
,1000,k,M,G后缀.--chunk-time匹配,如果明确指定这个选项,那么每个块就会指定行数的大小.	

--[no]check-plan
yes,.,EXPLAIN,,EXPLAIN,EXPALIN,EXPLAIN不同的结果,	

--statistics


--dry-run
--dry-run与--execute必须指定一个,二者相互排斥。和--print配合最佳。

--execute
--dry-run与--execute必须指定一个,二者相互排斥。

--print
SQL语句到标准输出--dry-run配合最佳。

--progress


--quiet
-q