以下語句也可以查看：
show table status from dbname like 'tablename'\G # Rows 的值不準，有時誤差有2倍

SELECT a.table_schema,a.table_name,concat(round(sum(DATA_LENGTH/1024/1024)+sum(INDEX_LENGTH/1024/1024),2) ,'MB')total_size,concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') AS data_size,concat(round(sum(INDEX_LENGTH/1024/1024),2),'MB') AS index_size FROM information_schema.TABLES a WHERE a.table_schema = 'dbname' AND a.table_name = 'tablename'; #看下此表的數(shù)據(jù)量

環(huán)境裝備:開啟Gtid,注意M,S1 binlog保存時長,磁盤剩余空間大于待變更表的2倍
show global variables like 'binlog_expire_logs_seconds'; # 默認604800
set global binlog_expire_logs_seconds=1209600; # 主庫和級聯(lián)主庫都需要設(shè)置
1.搭建 1主2從的級聯(lián)復制，M -> S1 -> S2 ，安裝MySQL注意本次環(huán)境lower_case_table_names = 0 
2.在S2 上做字段擴容。 預估 10個小時
`參數(shù)設(shè)置:`
set global slave_type_conversions='ALL_NON_LOSSY'; # 防止復制報錯SQL_Errno: 13146，屬于字段類型長度不一致無法回放
set global interactive_timeout=144000;set global wait_timeout =144000;
`磁盤IO參數(shù)設(shè)置:`
set global innodb_buffer_pool_size=32*1024*1024*1024;# 增加buffer_pool 防止Error1206The total number of locks exceeds the lock table size 資源不足
set global sync_binlog=20000;set global innodb_flush_log_at_trx_commit=2;
set global innodb_io_capacity=600000;set global innodb_io_capacity_max=1200000; # innodb_io_capacity需要設(shè)置兩次
show variables like '%innodb_io%'; # 驗證以上設(shè)置
screen 下執(zhí)行:
time mysql -S /data/mysql/3306/data/mysqld.sock -p'' dbname -NBe "ALTER TABLE tablename MODIFY COLUMN open_id VARCHAR(500) NULL DEFAULT NULL COMMENT 'Id' COLLATE 'utf8mb4_bin';"
查看DDL進度:
SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED  FROM performance_schema.events_stages_current;
3.擴容完成后，等待延遲同步M-S1-S2 
數(shù)據(jù)同步至主從一致,對比主從Gtid
4.移除S1,建立M-S2的主從關(guān)系
S1 (可選)
stop slave;
reset slave all;
systemctl stop mysql_3306
S2
stop slave;
reset slave all;
# MASTER_HOST='M主機IP'  
CHANGE MASTER TO
  MASTER_HOST='',
  MASTER_USER='',
  MASTER_PASSWORD=',
  MASTER_PORT=3306,
  MASTER_AUTO_POSITION=1,
  MASTER_CONNECT_RETRY=10;
start slave; (flush privileges;# 驗證數(shù)據(jù)可正常同步)
5.備份S2恢復S1，建立M-S2-S1級聯(lián)復制
物理備份S2,重做S2->S1 級聯(lián)主從
rm -rf binlog/*
rm -rf redolog/*
xtrabackup --defaults-file=/data/mysql/3306/my.cnf.3306 --move-back --target-dir=/data/actionsky/xtrabackup_recovery/data
chown -R mysql. data/
chown -R mysql. binlog/*
chown -R mysql. redolog/*
systemctl start mysql_3306
set global gtid_purged='';
reset slave all;
# MASTER_HOST='S2主機IP'  ,已擴容變更完的主機
CHANGE MASTER TO
  MASTER_HOST='',
  MASTER_USER='',
  MASTER_PASSWORD='',
  MASTER_PORT=3306,
  MASTER_AUTO_POSITION=1,
  MASTER_CONNECT_RETRY=10;
`MySQL8.0版本需要在上面語句中添加 GET_MASTER_PUBLIC_KEY=1; #防止 Last_IO_Errno: 2061 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.`
start slave;
6.應(yīng)用停服，等待主從數(shù)據(jù)一致
主庫停服+可設(shè)置read_only+flush privileges，對比主從Gtid
7.最終S2成為主庫，S1為從庫
應(yīng)用更改配置連接新主庫。
S2上:
stop slave;reset slave all;
set global read_only=0;set global super_read_only=0;
`show master status\G 觀察是否有新事務(wù)寫入`

收尾：還原第2步的參數(shù)設(shè)置。
set global interactive_timeout=28800;set global wait_timeout =28800;
set global innodb_buffer_pool_size=8*1024*1024*1024;
set global slave_type_conversions='';
set global sync_binlog=1;set global innodb_flush_log_at_trx_commit=1;
set global innodb_io_capacity=2000;set global innodb_io_capacity_max=4000;