mysql 重建_Mysql重建超大从库

Myth丶恋晨 2023-01-11 03:43 232阅读 0赞

1说明

简介：本方法可在不停止主库，不锁表的情况下，快速完成超大从库的重建

环境：以下代码的环境为Centos 6.x，其他环境请自行查询对应的安装包。

原理：innobackupex可在不影响主库正常运行的情况下，进行物理备份(mysqldump为逻辑备份，速度慢很多，对于巨额数据量备份简直是灾难)，配合压缩神器pigz进行传输，备份速度变得更快！

2具体实例

2.1软件安装

主从库都需要安装好相同的mysql版本及innobackupex，pigz

2.1.1导入文件

(1)安装innobackupex时的依赖包：perl-DBD-MySQL-4.013-3.el6.x86\_64.rpm, libev-4.03-3.el6.x86\_64.rpm

(2)压缩神器：pigz-2.3.4.tar.gz

2.1.2安装pigz

\#wget http://zlib.net/pigz/pigz-2.3.4.tar.gz

tar -xvzf pigz-2.3.4.tar.gz

cd pigz-2.3.4

make

cd ..

mv pigz-2.3.4 /usr/local/

ln -s /usr/local/pigz-2.3.4/pigz /usr/bin/

ln -s /usr/local/pigz-2.3.4/unpigz /usr/bin/

2.1.3安装innobackupex

\#wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.3/binary/redhat/6/x86\_64/percona-xtrabackup-24-2.4.3-1.el6.x86\_64.rpm

rpm -ivh perl-DBD-MySQL-4.013-3.el6.x86\_64.rpm

rpm -ivh libev-4.03-3.el6.x86\_64.rpm

rpm -ivh percona-xtrabackup-24-2.4.3-1.el6.x86\_64.rpm

2.2具体命令

说明：确保导出数据前，主库已开启二进制日志

2.2.1主库导出并传输到从库：

2.2.1.1从库操作：

mv /home/mysql/data/ /home/mysql/data\_bak/  \#备份mysql原数据文件夹

mkdir /home/mysql/data/  \#mysql的数据文件夹

chmod -R 777 /home/mysql/data/  \#保证主库对此文件夹(从库)有权限，可忽略此命令

2.2.1.2主库操作

\#如数据量大，建议在screen下执行导出命令

yum -y install screen

screen -R mysql\_backup

mkdir /home/mysql/backup

innobackupex --user=backup --password=\*\*\* --stream=xbstream --defaults-file=/usr/local/mysql/data/3306/my.cnf --socket=/usr/local/mysql/tmp/3306/mysql.sock --parallel=2 --tmpdir=/home/mysql/backup  /home/mysql/backup 2>>/home/mysql/backup/xbstreambackup.log | pigz -2 -p 6 -c | ssh -p 9922  mysql@10.32.59.207 "unpigz |xbstream -x -C /home/mysql/data/"

注：/home/mysql/data/是从库的数据目录，记得提前清空或重建

2.2.2从库恢复及启动

2.2.2.1恢复从库数据

\#保证从库mysql已经停止，

yum -y install screen

screen -R mysql\_restore

innobackupex  --defaults-file=/home/mysql/data/backup-my.cnf --parallel=2 --apply-log /home/mysql/data/  \#从库恢复数据

2.2.2.2重建从库的日志目录，启动从库。

cp /home/mysql/data\_bak/auto.cnf  /home/mysql/data/  \#拷贝从库本机原有的auto.cnf

cp 主库的my.cnf  /etc/my.cnf  \#拷贝主库my.cnf到从库，并修改server\_id

chown -R mysql.mysql /home/mysql/data

/etc/init.d/mysqld start

2.2.3添加主备同步

2.2.3.1主库设置

echo "grant replication slave on \*.\* to 'repl'@"$\{db\_slave\_ip\}" identified by 'zabbix\_repl';" | mysql -uroot -p$\{MysqlPassword\}

echo "flush privileges;" | mysql -uroot -p$\{MysqlPassword\}

2.2.3.2从库设置

cat /home/mysql/data/xtrabackup\_binlog\_info \#获取到binlog和pos位置

update.001822  387886382

\#根据上步的结果，设置主备同步的开始点

echo "CHANGE MASTER TO

MASTER\_HOST='10.44.66.19',

MASTER\_USER='repl',

MASTER\_PASSWORD='zabbix\_repl',

MASTER\_PORT=3306,

MASTER\_LOG\_FILE='update.001822',

MASTER\_LOG\_POS=387886382;" | mysql -uroot -p123321

echo "start slave;" | mysql -uroot -p123321

echo "show slave status\\G;" | mysql -uroot -p123321 2>/dev/null | grep -E "Slave\_IO\_Running|Slave\_SQL\_Running"

2.3参考：

http://blog.51yip.com/mysql/1650.html

http://blog.itpub.net/27099995/viewspace-1295099/

http://www.2cto.com/database/201504/390859.html

3操作记录

3.1 zabbix-db-slave从库重建

主库备份及传输到从库:

time: 75分钟

data: 358G(/home/mysql/data/)

eth0 speed: 180Mpbs

从库恢复时间：27分钟

3.2 xx管理系统

3.2.1主从库例子

\# 在主库和从库的/etc/my.cnf添加

\[mysqld\]

server-id = 1 \# 主数据库端ID号，主从不同

log-bin = mysql-bin \# 日志文件名

\#/etc/init.d/mysqld restart

3.2.2从库例子：

rpm -ivh libev-4.03-3.el6.x86\_64.rpm

rpm -ivh perl-DBD-MySQL-4.013-3.el6.x86\_64.rpm

rpm -ivh percona-xtrabackup-24-2.4.3-1.el6.x86\_64.rpm

\#wget http://zlib.net/pigz/pigz-2.3.4.tar.gz

tar -xvzf pigz-2.3.4.tar.gz

cd pigz-2.3.4

make

cd ..

mv pigz-2.3.4 /usr/local/

ln -s /usr/local/pigz-2.3.4/pigz /usr/bin/

ln -s /usr/local/pigz-2.3.4/unpigz /usr/bin/

/etc/init.d/mysqld stop

mv /var/lib/mysql /var/lib/mysql\_bak

mkdir /var/lib/mysql

chmod 777 /var/lib/mysql

useradd zengzp

passwd zengzp

\# 请完成下一节(主库例子)后，执行以下从库恢复数据

innobackupex --defaults-file=/var/lib/mysql/backup-my.cnf --parallel=2 --apply-log /var/lib/mysql

chown -R mysql.mysql /var/lib/mysql

cp 主库的my.cnf  /etc/my.cnf  \#拷贝主库my.cnf到从库，并修改server\_id

/etc/init.d/mysqld start

cat /var/lib/mysql/xtrabackup\_binlog\_info

update.001822  387886382

\#根据上步的结果，设置主备同步的开始点

echo "CHANGE MASTER TO

MASTER\_HOST='10.42.79.148',

MASTER\_USER='repl',

MASTER\_PASSWORD='mysql\_repl',

MASTER\_PORT=3306,

MASTER\_LOG\_FILE='update.001822',

MASTER\_LOG\_POS=387886382;" | mysql -uroot -p

echo "start slave;" | mysql -uroot -p

echo "show slave status\\G;" | mysql -uroot -p 2>/dev/null | grep -E "Slave\_IO\_Running|Slave\_SQL\_Running"

3.2.3主库例子：

rpm -ivh libev-4.03-3.el6.x86\_64.rpm

rpm -ivh perl-DBD-MySQL-4.013-3.el6.x86\_64.rpm

rpm -ivh percona-xtrabackup-24-2.4.3-1.el6.x86\_64.rpm

\#wget http://zlib.net/pigz/pigz-2.3.4.tar.gz

tar -xvzf pigz-2.3.4.tar.gz

cd pigz-2.3.4

make

cd ..

mv pigz-2.3.4 /usr/local/

ln -s /usr/local/pigz-2.3.4/pigz /usr/bin/

ln -s /usr/local/pigz-2.3.4/unpigz /usr/bin/

cd /tmp

mkdir mysql\_tmp

innobackupex --user=root --password='' --stream=xbstream --socket=/var/lib/mysql/mysql.sock --parallel=2 --tmpdir=/tmp/mysql\_tmp  /tmp/mysql\_tmp 2>>/tmp/mysql\_tmp/xbstreambackup.log | pigz -2 -p 6 -c | ssh -p 9922  zengzp@10.42.71.219 "unpigz |xbstream -x -C /var/lib/mysql"

echo "grant replication slave on \*.\* to 'repl'@"10.42.71.219" identified by 'mysql\_repl';" | mysql -uroot -p

echo "flush privileges;" | mysql -uroot -p

3.3 xx采购系统

说明：因为数据量较小，使用原生的gzip替换pigz

3.3.1主从库例子

\# 在主库和从库的/etc/my.cnf添加

\[mysqld\]

server-id = 1 \# 主数据库端ID号，主从不同

log-bin = mysql-bin \# 日志文件名

\#/etc/init.d/mysqld restart

3.3.2从库例子：

rpm -ivh libev-4.03-3.el6.x86\_64.rpm

rpm -ivh perl-DBD-MySQL-4.013-3.el6.x86\_64.rpm

rpm -ivh percona-xtrabackup-24-2.4.3-1.el6.x86\_64.rpm

/etc/init.d/mysqld stop

mv /home/mysql/data/ /home/mysql/data\_bak/  \#备份mysql原数据文件夹

mkdir /home/mysql/data/  \#mysql的数据文件夹

chmod -R 777 /home/mysql/data/  \#保证主库对此文件夹(从库)有权限，可忽略此命令

useradd zengzp

passwd zengzp

\# 请完成下一节(主库例子)后，执行以下从库恢复数据

innobackupex --defaults-file=/home/mysql/data/backup-my.cnf --parallel=2 --apply-log /home/mysql/data

chown -R mysql.mysql /home/mysql/data

cp 主库的my.cnf  /etc/my.cnf  \#拷贝主库my.cnf到从库，并修改server\_id

/etc/init.d/mysqld start

cat /home/mysql/data/xtrabackup\_binlog\_info

mysql-bin.000004        23361038

\#根据上步的结果，设置主备同步的开始点

echo "CHANGE MASTER TO

MASTER\_HOST='11.2.88.3',

MASTER\_USER='repl',

MASTER\_PASSWORD='mysql\_repl',

MASTER\_PORT=3306,

MASTER\_LOG\_FILE='mysql-bin.000004',

MASTER\_LOG\_POS=23361038;" | mysql -uroot -p

echo "start slave;" | mysql -uroot -p

echo "show slave status\\G;" | mysql -uroot -p 2>/dev/null | grep -E "Slave\_IO\_Running|Slave\_SQL\_Running"

3.3.3主库例子：

rpm -ivh libev-4.03-3.el6.x86\_64.rpm

rpm -ivh perl-DBD-MySQL-4.013-3.el6.x86\_64.rpm

rpm -ivh percona-xtrabackup-24-2.4.3-1.el6.x86\_64.rpm

cd /tmp

mkdir mysql\_tmp

\# 添加gzip压缩，传输时间明显减小为65s(5.1G)

innobackupex --user=root --password='' --stream=xbstream --socket=/home/mysql/tmp/mysql.sock --parallel=4 --tmpdir=/tmp/mysql\_tmp  /tmp/mysql\_tmp 2>>/tmp/mysql\_tmp/xbstreambackup.log | gzip | ssh -p 9922  zengzp@11.4.88.37 "gzip -d | xbstream -x -C /home/mysql/data"

echo "grant replication slave on \*.\* to 'repl'@"11.4.88.37" identified by 'mysql\_repl';" | mysql -uroot -p

echo "flush privileges;" | mysql -uroot -p

4附

多线程gzip压缩神器—pigz

特点：

1)pigz默认用法(默认并发线程是逻辑cpu个数)可比gzip快5.3倍，CPU消耗则是gzip的8倍，压缩比则相当；

2)支持并行的gzip。并发8线程对比4线程提升：41.2%，16线程对比8线程提升：27.9%，32线程对比16线程提升：3%；

3)在对压缩效率要求较高、但对短时间内CPU消耗较高不受影响的场景，使用pigz非常合适。

pig默认用当前逻辑cpu个数来并发压缩，无法检测个数的话，则并发8个线程。

安装pigz

wget http://zlib.net/pigz/pigz-2.3.3.tar.gz

tar -xvzf pigz-2.3.3.tar.gz