mysql数据过大后修改字段

数据库运维方法

1.  Percona Toolkit安装

下载安装包

wget https://www.percona.com/downloads/percona-toolkit/2.2.19/tarball/percona-toolkit-2.2.19.tar.gz

安装步骤

tar zxvf percona-toolkit-2.2.19.tar.gz

 cd percona-toolkit-2.2.19

 perl Makefile.PL

 make

 make install

默认安装位置

/usr/local/bin/

1. Shell批处理

建sh文件

Vi pt.sh

文件内容

#!/bin/bash

table=$1

alter_conment=$2

cnn_host='127.0.0.1'

cnn_user='cctischoolyard'

cnn_pwd='cct123456'

cnn_db='bayg'

echo "$table"

echo "$alter_conment"

/usr/local/bin/pt-online-schema-change --charset=utf8 --no-version-check --user=${cnn_user} --password=${cnn_pwd} --host=${cnn_host}  P=3306,D=${cnn_db},t=$table --alter "${alter_conment}" --no-check-replication-filters --alter-foreign-keys-method=rebuild_constraints --recursion-method=none   --print --execute

改变权限

chmod +x pt.sh

执行Shell

pt.sh HONOR "ADD COLUMN column1 tinyint(4) DEFAULT NULL"

HONOR 为表名

1. Online DDL操作

pt-online-schema-change介绍

pt-online-schema-change是percona公司开发的一个工具，在percona-toolkit包里面可以找到这个功能，它可以在线修改表结构

原理:

• 首先它会新建一张一模一样的表，表名一般是_new后缀
• 然后在这个新表执行更改字段操作
• 然后在原表上加三个触发器，DELETE/UPDATE/INSERT，将原表中要执行的语句也在新表中执行
• 最后将原表的数据拷贝到新表中，然后替换掉原表

添加字段

    pt-online-schema-change --user=root --password=123456 --host=127.0.0.1  --alter "ADD COLUMN content text" D=bayg,t=tmp_test --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --execute

删除字段

    pt-online-schema-change --user=root --password=123456 --host=127.0.0.1  --alter "DROP COLUMN content text" D=bayg,t=tmp_test --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --quiet --execute

修改字段

    pt-online-schema-change --user=root --password=123456 --host=127.0.0.1  --alter "MODIFY COLUMN age TINYINT NOT NULL DEFAULT 0" D=bayg,t=tmp_test --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --quiet --execute

字段改名

    pt-online-schema-change --user=root --password=123456 --host=127.0.0.1  --alter "CHANGE COLUMN age address varchar(30)" D=bayg,t=tmp_test --no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --quiet --execute

增加索引

    pt-online-schema-change --user=root --password=123456 --host=127.0.0.1  --alter "ADD INDEX idx_address(address)" D=bayg,t=tmp_test --no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --execute

删除索引

    pt-online-schema-change --user=root --password=123456 --host=127.0.0.1  --alter "DROP INDEX idx_address" D=bayg,t=tmp_test --no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --execute

收缩表空间

pt-online-schema-change --user=root --password=123456 --host=127.0.0.1 --alter "ENGINE=InnoDB" D=bayg,t=tmp_test --no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --execute

1. Mysqldump

导出数据库表结构

mysqldump -hlocalhost -ucctischoolyard -P3306 -pcct123456 -d db_stydy>/home/dba/db_stydy.sql

导出数据库表结构及数据

mysqldump -hlocalhost -ucctischoolyard -P3306 -pcct123456  db_stydy>/home/dba/db_stydy_data.sql

导出特定表结构与数据

mysqldump -hlocalhost -ucctischoolyard -pcct123456 -P3306 db_stydy test1 > /home/dba/test1.sql

导出特定表结构（不含数据）

mysqldump -ucctischoolyard -hlocalhost -pcct123456  -P3306 -d db_stydy test1 > /home/dba/test1_nodata.sql

导出特定表数据（不含结构）

mysqldump -ucctischoolyard -hlocalhost -pcct123456  -P3306 -t db_stydy test1 > /home/dba/test1_data.sql

1. XtraBackup 2.4

下载包

wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.15/binary/tarball/percona-xtrabackup-2.4.15-Linux-x86_64.libgcrypt153.tar.gz

解压包

tar  -zxvf  percona-xtrabackup-2.4.15-Linux-x86_64.libgcrypt153.tar.gz

备份数据库

./innobackupex --defaults-file=/etc/my.cnf --user=cctischoolyard --password=cct123456 --host=127.0.0.1 --databases=db_stydy  /home/dba/xtrabackup/

部分备份文件

xtrabackup_binlog_info：记录当前最新的LOG Position

xtrabackup_binlog_pos_innodb:innodb log postion

xtrabackup_checkpoints: 存放备份的起始位置beginlsn和结束位置endlsn,增量备份需要这个lsn[增量备份可以在这里面看from和to两个值的变化

xtrabackup_info:备份的一些具体信息日志