近期项目上需要重建两个从库,因为主库无法停库且数据量有800G之多,而且主库本身在华为云上有创建备份策略,使用的是xtrabackup工具实现的全备,故我们在重建从库时也直接以此为基础即可
我的主库是5.7版本,从库采用yum的方式安装的也是5.7版本的mysql,具体安装可以参考使用yum安装MySQL5.7,最主要的是要下载对应版本的Yum Repository:
wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql-community-server
安装对应版本的xtrabackup软件
因为是使用华为云的数据库备份文件为基础,有要求是XtraBackup 2.4.9版本即以上,我选择的安装最新版本,具体的可以去Percona Xtrabackup官方下载页面根据提示操作下载并安装。
[NOTE]
mysql 8.0对应的是Xtrabackup 8.0系列,mysql 5.7对应的是Xtrabackup 2.4系列,我都是使用yum的方式进行的安装。
安装qpress程序
因数据库较大,在备份的时候有进行压缩,qpress程序用于配合xtrabackup软件进行解压,在qpress软件包下载页面上可以通过下载tar包的方式进行安装,因上一步操作时有下载了xtrabackup的yum Repository,所以此部我依旧是使用yum的方式进行安装:
yum install -y qpress
创建一个临时目录用于存储解压文件
mkdir /backupdir
进行解压
xbstream -x -p 4 < 全备文件.qp -C /backupdir/
innobackupex --parallel 4 --decompress /backupdir
删除多余的“.qp”备份文件
find /backupdir/ -name ‘*.qp‘ | xargs rm -f
innobackupex --apply-log /backupdir
先将目前的数据库关闭
systemctl stop mysqld
将原有的data目录做备份并移除
mv /var/lib/mysql /var/lib/mysql_bak
创建新的数据库目录并修改目录权限
mkdir -p /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
根据需求配置好my.cnf文件
还原从库时主要是datadir参数,当然其他参数也可以一并配置好,这边提供一个大致的配置作为参考:
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[client]
port=3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
############################## Basic configuration ###############################
#
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
pid-file=/var/run/mysqld/mysqld.pid
# lower_case_table_names = 1
# sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
################################## Connection ####################################
#
skip_name_resolve = 1
max_connections = 5000
max_connect_errors = 2000
interactive_timeout = 1800
wait_timeout = 1800
############################## Session Buffer Cache ###############################
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
# tmp_table_size =
max_allowed_packet = 128M
##################################### Logfile #####################################
#
log-error=/var/log/mysqld.log
log_timestamps=SYSTEM
slow_query_log = 1
slow_query_log_file = /var/log/mysqld-slow.log
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10
log_slow_admin_statements = 1
log_slow_slave_statements = 1
long_query_time = 2
min_examined_row_limit = 100
###################################### Binlog #####################################
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
#
log_bin = mysql-bin
binlog_format = row
expire_logs_days = 7
sync_binlog = 1000
innodb_flush_log_at_trx_commit = 2
binlog_rows_query_log_events = 1
log-bin-trust-function-creators = 1
###################################### InnoDB #####################################
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
#
innodb_buffer_pool_size = 40G
innodb_flush_method = O_DIRECT
#################################### Replication ##################################
#
server-id = 111
skip_slave_start=1
log-slave-updates = 1
relay_log = relaylogs
relay_log_recovery = 1
relay_log_info_repository = TABLE
master_info_repository = TABLE
log_bin_trust_function_creators=true
gtid_mode = on
enforce_gtid_consistency = 1
slave_skip_errors = 1062,1053,1007,1008,1050,1051,1054,1060,1061,1068,1091,1146
# replicate-ignore-table =
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
拷贝全备文件
innobackupex --defaults-file=/etc/my.cnf --copy-back /backupdir
完成后确认下目录的权限
systemctl start mysqld
在新的数据库目录下有xtrabackup_binlog_pos_innodb
文件,查看其中记录的复制日志信息
change master to
语句重建从库链接,其中master_log_file和master_log_pos是上一步所确认的信息。show slave status\G
原文:https://www.cnblogs.com/ariesblog/p/14759537.html