MySQL新增从库

项目背景描述:

创新互联建站专注于企业营销型网站、网站重做改版、黄梅网站定制设计、自适应品牌网站建设、html5成都做商城网站、集团公司官网建设、外贸网站建设、高端网站制作、响应式网页设计等建站业务,价格优惠性价比高,为黄梅等各大城市提供网站开发制作服务。

    在项目的开始只有一个MySQL实例在运行,后期因为安全性,压力,备份等原因需要在此实例的基础上面新增一个从库。

分析:

    MySQL主从是基于binlog日志来实现的,那么需要主服务器开启binlog,此选项默认是关闭。我这边服务器在部署的时候就开启了,因为使用binlog可以用来恢复MySQL数据,并且还为以后做主从做好准备,所以我们推荐大家在部署主服务器的时候就开启此选项。另外在做主从的时候还需要主库和从库的server-id唯一,server-id在做主从的时候也是相当关键的一荐参数。

    另外因为我们MySQL主库已经运行一段时间了,里面已经有相当多数据,我们需要将这些数据备份出来,然后从库再从备份的节点同步数据,这样来保持主从的数据一致性,并且在操作过程中最好不要影响我们的业务正常运行。最终决定使用xtrabackup来备份数据,因为用xtrabackup备份数据的时候不需要琐表,但只限于InnoDB引擎的数据库和XtraDB引擎的数据库,对于MyISAM引擎的数据库还是会琐表,刚好我们的数据库引擎使用的是InnoDB

操作步骤:

第一步:部署从数据库服务器,最好数据库版本一致,部署过程省略

第二步:修改配置文件

# 以下为主服务器需要修改的配置,server_id不一定为82,可以为任何一个数字,比如我们可以用本机的IP最后一位
[mysqld]
datadir=/data/mysql       #此选项一定要指定,就算有默认也需要指定
log_bin=mysql-bin
server_id=10
expire_logs_days=5        #指定binlog文件保留的天数,如果硬盘空间够大可以不指定,或者指定天数多一点

# 以下为可选项
binlog-ignore-db=mysql,information_schema,performance_schema,sys    #指定忽略不同步的数据库
binlog-do-db=discuz,phpcms                                          #指定要同步的数据库

在主服务器上面授权从服务器一个主从同步账号

mysql> grant replication slave on *.* to 'slave'@'%' identified by '123qweASD';

说明:以上授权命令根据自己需求更改,比如权限,可访问数据库,用户名,授权客户端,密码等信息

# 以下为从服务器需要修改的配置
datadir=/data/mysql                      #此选项一定要指定,就算有默认也需要指定
server_id = 2

# 以下为可选项
replicate-do-db                          #指定需要同步的数据库
replicate-ignore-db                      #指定不同步的数据库
replicate-do-table                       #指定需要同步的表
replicate-ignore-table                   #指定不同步的表
replicate-wild-do-table                  #指定需要同步的表,可以使用通配符,如test.tables1%
replicate-wild-ignore-table              #指定不需要同步的表,可以使用通配符
# 优化同步延迟,根据项目设置
slave_parallel_workers = 16     #多少个线程用于主从复制
slave_parallel_type= logical_clock

第三步:安装xtrabackup工具(主从服务器都需要安装)

至以下连接下载适合自己操作系统版本和MySQL版本的xtrabackup

xtrabackup下载

下载完成后执行以下命令安装 

# 这样可以自动安装所需要依赖包
yum -y install percona-xtrabackup-24-2.4.10-1.el7.x86_64.rpm

第四步:备份数据

注意:以需要使用root用户执行,或者使用sudo权限执行

备份整个库

xtrabackup --user=root --password=123456 --target-dir=/root/mysql_bak -S /tmp/mysql.sock --backup

--user             指定数据库访问用户名

--password    指定数据库访问密码,如果密码有特殊字符需要使用单引号引起来

--target-dir    指定备份路径,最好写绝对路径

--backup        与--target-dir选项一起使用

-S                   指定mysql.sock文件位置

备份单个库

xtrabackup --user=root --password=123456 --databases=test1 --target-dir=/root/mysql_bak -S /tmp/mysql.sock --backup

--databases    指定需要备份的库名

备份多个库

xtrabackup --user=root --password=123456 --databases="test1 test2 test3" --target-dir=/root/mysql_bak -S /tmp/mysql.sock --backup

--databases    多库用双引号引起来,使用空格进行分隔

备份某个库的指定表

xtrabackup --user=root --password=123456 --databases="test1.tables1 test2.tables2 test3.tables3" --target-dir=/root/mysql_bak -S /tmp/mysql.sock --backup

--databases    备份表使用库名.表名的方式,如果是多个表就使用双引号引起来,不同表使用空格分隔

第五步:将备份数据复制到从服务器

scp -pr /root/mysql_bak/* root@172.16.10.11:/data/mysql_slave

第六步:将数据还原到从服务器

注意:还原之前需要停止从库数据库服务并清空从服务器数据目录,如果有需要的数据就先备份到其实地方

# 停止服务
service mysqld restart || systemctl restart mysqld
# 备份原数据
mkdir /data/mysql_bak
mv /data/mysql/* /data/mysql_bak

还原操作

xtrabackup --prepare --target-dir=/data/mysql_slave
xtrabackup --copy-back --target-dir=/data/mysql_slave

第七步:启动数据库并启用主从

# 将原备份数据复制回原目录,注意:ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1四个文件不要覆盖
# 如果从库没有数据需要往回复制的话就不需要cp这步操作
cp -r /data/mysql_bak/* /data/mysql
chown -R mysql:mysql /data/mysql
service mysqld restart || systemctl restart mysqld

执行以下命令

mysql> change master to master_host='172.16.10.10',master_port=3306,master_user='slave',master_password='123qweASD',master_log_file='mysql-bin.000011',master_log_pos=154;

master_host:             master服务器的IP地址

master_port:             master服务器的端口

master_user:             master服务器授权从服务器主从同步的用户名

master_password:    master服务器授权从服务器主从同步的密码

master_log_file:        从主服务器备份出来的文件:xtrabackup_info中获取

grep "binlog_pos" xtrabackup_info | awk -F "'" '{print $2}'

master_log_pos:       从主服务器备份出来的文件:xtrabackup_info中获取

grep "binlog_pos" xtrabackup_info | awk -F "'" '{print $4}'

执行以下命令启用主从同步

mysql> start slave;

小知识:停止主从同步命令为

# 以下为扩展知识命令,如果执行后需要再执行上条命令start slave;
mysql> stop slave;

第八步:测试并检查主从状态

mysql> show slave status \G;

如果看以以下两个值为Yes说明主从同步正常

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

第九步:测试

可以修改下需要同步的库,或者表的数据看上是否能正常同步去,或者在备份完的时候就去修改下数据,这时候备份里同是没有此修改记录,当启用主从同步的时候看下数据是否会同步过来


当前名称:MySQL新增从库
浏览地址:http://scyanting.com/article/gihjhp.html