2-四种主从搭建流程

概述文档：2-四种主从搭建流程.note 链接：http://note.youdao.com/noteshare?id=534e2789f549b5c55b7a98a1a7fda44b&sub=6EB5486BE915456D9876523BD3534845 异步主从复制搭建过程： 准备两台linux，主server，从agent 1、检查mysql配置文件有没有开启binlog [[email pr

@H_403_7@文档：2-四种主从搭建流程.note
链接：http://note.youdao.com/noteshare?ID=534e2789f549b5c55b7a98a1a7fda44b&sub=6EB5486BE915456D9876523BD3534845

异步主从复制搭建过程： @H_403_7@准备两台linux，主server，从agent

1、检查MysqL配置文件有没有开启binlog @H_403_7@[[email protected] ~]# vim /etc/my.cnf

@H_403_7@log_bin=server

@H_403_7@server_ID=1

@H_403_7@service MysqLd restart

@H_403_7@ls /data

@H_403_7@-rw-r----- 1 MysqL MysqL 177 May 1 14:58 server.000001

@H_403_7@-rw-r----- 1 MysqL MysqL 80 May 1 15:02 server.index

@H_403_7@手工切换logs文件：

@H_403_7@MysqL>flush logs;

2、主库安装xtrabackup

@H_403_7@# yum install perl-dbd-mysql -y

@H_403_7@# rpm -ivh libev4-4.15-7.1.x86_64.rpm

@H_403_7@# rpm -ivh libev-devel-4.15-21.1.x86_64.rpm

@H_403_7@# rpm -ivh percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

@H_403_7@# rpm -ivh percona-xtrabackup-24-deBUGinfo-2.4.4-1.el6.x86_64.rpm

@H_403_7@yum install percona-xtrabackup-test-24-2.4.4-1.el6.x86_64.rpm

@H_403_7@[[email protected] xtrabackup2.4.4]# yum localinstall ./*.rpm

@H_403_7@新建一个备份目录

@H_403_7@mkdir /backup

@H_403_7@做一个全备

@H_403_7@innobackupex --user=root --password=123 --socket=/tmp/MysqL.socket /backup/

@H_403_7@innobackupex --user=root --password=123 /backup/

@H_403_7@[[email protected] ~]# cd /backup/

@H_403_7@[[email protected] backup]# ll

@H_403_7@total 12

@H_403_7@drwxr-x--- 2 root root 4096 May 1 15:41 2019-05-01_15-41-35

@H_403_7@drwxr-x--- 2 root root 4096 May 1 15:43 2019-05-01_15-43-47

@H_403_7@drwxr-x--- 5 root root 4096 May 1 15:50 2019-05-01_15-50-52

@H_403_7@将备份内容放到客户机

@H_403_7@[[email protected] 2019-05-01_15-50-52]# scp -r 2019-05-01_15-50-52/
[email protected] /root/

3、从库配置bin_log @H_403_7@[[email protected] ~]# vim /etc/my.cnf

@H_403_7@read_only=1

@H_403_7@server_ID=2

@H_403_7@binlog_format=row

@H_403_7@log_bin=slave

@H_403_7@关闭数据库

@H_403_7@[[email protected] ~]# service MysqLd stop

@H_403_7@进入MysqL安装目录

@H_403_7@[[email protected] ~]# cd /var/lib/MysqL

@H_403_7@将库中全部删掉

@H_403_7@[[email protected] MysqL]# rm -rf *

@H_403_7@从库安装xtrabackup

@H_403_7@4、恢复备份

@H_403_7@应用日志

@H_403_7@[[email protected] xtrabackup2.4.4]# innobackupex --apply-log
/root/2019-05-01_15-50-52/

@H_403_7@查看状态

@H_403_7@[[email protected] 2019-05-01_15-50-52]# cd /root/2019-05-01_15-50-52

@H_403_7@[[email protected] 2019-05-01_15-50-52]# cat xtrabackup_checkpoints

@H_403_7@backup_type = full-prepared

@H_403_7@from_lsn = 0

@H_403_7@to_lsn = 2534580

@H_403_7@last_lsn = 2534589

@H_403_7@compact = 0

@H_403_7@recover_binlog_info = 0

@H_403_7@从库恢复

@H_403_7@[[email protected] 2019-05-01_15-50-52]# innobackupex --copy-back
/root/2019-05-01_15-50-52/

@H_403_7@[[email protected] 2019-05-01_15-50-52]# cd /var/lib/MysqL

@H_403_7@修改属主属组

@H_403_7@[[email protected] MysqL]# chown -R MysqL:MysqL .

@H_403_7@查看

@H_403_7@[[email protected] MysqL]# cat xtrabackup_info

@H_403_7@uuID = da8b5a8d-6be5-11e9-9925-000c2967eaf1

@H_403_7@name =

@H_403_7@tool_name = innobackupex

@H_403_7@tool_command = --user=root --password=... /backup/

@H_403_7@tool_version = 2.4.4

@H_403_7@ibbackup_version = 2.4.4

@H_403_7@server_version = 5.7.17

@H_403_7@start_time = 2019-05-01 15:50:52

@H_403_7@end_time = 2019-05-01 15:50:56

@H_403_7@lock_time = 0

@H_403_7@binlog_pos =

@H_403_7@innodb_from_lsn = 0

@H_403_7@innodb_to_lsn = 2534580

@H_403_7@partial = N

@H_403_7@incremental = N

@H_403_7@format = file

@H_403_7@compact = N

@H_403_7@compressed = N

@H_403_7@encrypted = N

@H_403_7@启动数据库

@H_403_7@[[email protected] MysqL]# service MysqLd start

@H_403_198@5、在主库上专门给从库建立一个复制用的用户 @H_403_7@MysqL> grant replication slave,replication clIEnt on *.* to ‘repl‘@‘%‘
IDentifIEd by ‘repl‘;

6、在从库上登录 @H_403_7@[[email protected] MysqL]# MysqL -urepl -prepl -hserver -P3306

@H_403_7@验证一下

@H_403_7@MysqL> show databases;

@H_403_7@+--------------------+

@H_403_7@| Database |

@H_403_7@+--------------------+

@H_403_7@| information_schema |

@H_403_7@+--------------------+

@H_403_7@1 row in set (0.01 sec)

7、确定主从关系 @H_403_7@MysqL> ? change master to

@H_403_7@CHANGE MASTER TO

@H_403_7@MASTER_HOST=‘master2.mycompany.com‘,

@H_403_7@MASTER_USER=‘replication‘,

@H_403_7@MASTER_PASSWORD=‘bigs3cret‘,

@H_403_7@MASTER_PORT=3306,

@H_403_7@MASTER_LOG_file=‘master2-bin.001‘,

@H_403_7@MASTER_LOG_POS=4,

@H_403_7@MASTER_CONNECT_RETRY=10;

@H_403_7@[[email protected] MysqL]# MysqL -uroot -p123

@H_403_7@MysqL> CHANGE MASTER TO
MASTER_HOST=‘172.16.88.220‘,MASTER_USER=‘repl‘,MASTER_PASSWORD=‘repl‘,MASTER_PORT=3306,MASTER_LOG_file=‘slave.000001‘,MASTER_LOG_POS=806978,MASTER_CONNECT_RETRY=10;

@H_403_7@query OK,0 rows affected,2 warnings (0.04 sec)

@H_403_7@开启从库

@H_403_7@MysqL> start slave;

@H_403_7@query OK,0 rows affected (0.02 sec)

@H_403_7@查看线程状态

@H_403_7@查看从库上有没有正常生成主从的IO应用线程

@H_403_7@MysqL> MysqL> show processList;

@H_403_7@

@H_403_7@详细判断主从关系是否正常跑

@H_403_7@MysqL> show slave status\G

@H_403_7@

@H_403_7@主库上

@H_403_7@查看当前服务器上bin_log(server.000002)已经跑到哪一个position了

@H_403_7@MysqL> show master status;

@H_403_7@

主从复制存在的问题 @H_403_7@主从存在问题：

@H_403_7@主库宕机后，数据可能丢失

@H_403_7@主从只有一个sql thread，主库写压力大，主从延时大

@H_403_7@主库故障，从库需要手工拉起对外提供服务

@H_403_7@如何解决：

@H_403_7@半同步复制——解决数据丢失问题

@H_403_7@并行复制——解决从库复制延迟问题

@H_403_7@MHA——解决故障、在线切换问题

半同步复制过程 @H_403_7@

@H_403_7@半同步复制检测

@H_403_7@检查当前版本是否支持半同步

@H_403_7@主从都需要检查

@H_403_7@MysqL> select @@have_dynamic_loading;

@H_403_7@模块目录：

@H_403_7@/usr/local/MysqL/lib/plugin/：semisync_master.so和semisync_slave.so

@H_403_7@/usr/lib64/MysqL/plugin

@H_403_7@主库加载模块：

@H_403_7@INSTALL PLUGIN rpl_semi_sync_master SOname ‘semisync_master.so‘;

@H_403_7@从库加载模块：

@H_403_7@INSTALL PLUGIN rpl_semi_sync_slave SOname ‘semisync_slave.so‘;

@H_403_7@检查模块加载情况：

@H_403_7@MysqL> select * from MysqL.plugin;

@H_403_7@开启主库半同步：

@H_403_7@set global rpl_semi_sync_master_enabled=1;

@H_403_7@set global rpl_semi_sync_master_timeout=30000; 单位毫秒

@H_403_7@从库开启半同步复制：

@H_403_7@set global rpl_semi_sync_slave_enabled=1;

@H_403_7@如果是异步复制转半同步复制：

@H_403_7@需要重启下从库上的IO线程：

@H_403_7@stop slave io_thread;start slave io_thread;

并行复制 @H_403_7@从库配置文件修改

@H_403_7@[[email protected] plugin]# vim /etc/my.cnf

@H_403_7@并行参数：

@H_403_7@slave_preserve_commit_order=1

@H_403_7@slave_parallel_type=logical_clock

@H_403_7@slave_parallel_workers=4

@H_403_7@log_slave_updates=1

@H_403_7@relay_log_recovery=ON

@H_403_7@#master_info_repository=table

@H_403_7@#relay_log_info_repository=table

@H_403_7@| MysqL | slave_master_info |

@H_403_7@| MysqL | slave_relay_log_info |

@H_403_7@重启从库数据库

@H_403_7@[[email protected] plugin]# service MysqLd restart

GTID主从 @H_403_7@全局事务ID

@H_403_7@优点：不用手工设置日志偏移量，很方便的进行故障转移;
如果启动log_slave_updates那么从库不会丢失主库上的任何修改

@H_403_7@MASTER_auto_position = {0|1}

@H_403_7@缺点：只支持5.6之后的版本

@H_403_7@主库配置：

@H_403_7@[[email protected] plugin]# vim /etc/my.cnf

@H_403_7@gtID-mode=on

@H_403_7@enforce-gtID-consistency=1

@H_403_7@重启数据库

@H_403_7@从库配置：

@H_403_7@[[email protected] plugin]# vim /etc/my.cnf

@H_403_7@gtID-mode=on

@H_403_7@enforce-gtID-consistency=1

@H_403_7@重启数据库

@H_403_7@在主库查看：

@H_403_7@可以查看 server_uuID 号：show global variables like "%uuID%";

@H_403_7@show master status;

@H_403_7@Executed_GtID_Set现在是空的,发现没有gtID记录（如果刚开始开启binlog的话）

@H_403_7@创建一张表

@H_403_7@create table g1;

@H_403_7@再次查看show master status;

@H_403_7@可以查看到在 Executed_GtID_Set 信息

@H_403_7@主库备份：

@H_403_7@innobackupex --user=root --password=123 /backup/

@H_403_7@应用

@H_403_7@（gtID模式此处不用看）

@H_403_7@查看xtrabackup_binlog_info或者xtrabackup_info：

@H_403_7@binlog_pos = filename ‘server.000001‘,position ‘186479‘,GTID of the last
change ‘7e48f5d6-25f7-11e8-bdb4-000c29208fda:1-616‘

@H_403_7@然后将 备份文件传输到从库 ：

@H_403_7@scp -r /backup/2018-06-25_06-50-33/ [email protected]:/backup/

@H_403_7@在从库上进行恢复：

@H_403_7@innobackupex --move-back /backup/2018-06-25_06-50-33/

@H_403_7@修改权限：chown -R MysqL:MysqL /MysqL/

@H_403_7@启动数据库：service MysqLd start

@H_403_7@主库创建复制用户

@H_403_7@然后从库进行同步 CHANGE TO *** 作

@H_403_7@CHANGE MASTER TO
MASTER_HOST=‘172.16.120.23‘,master_auto_position=1;

@H_403_7@启动从库：start slave; show slave status看状态

@H_403_7@然后进行测试即可

@H_403_7@测试从库 *** 作

@H_403_7@测试主库 *** 作同步情况

总结

以上是内存溢出为你收集整理的2-四种主从搭建流程全部内容，希望文章能够帮你解决2-四种主从搭建流程所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。