mysql5.6主从搭建以及不同步问题详解

大家好,本篇文章主要讲了mysql5.6主从搭建以及不同步问题详解,感兴趣的同学赶快来看一看吧,对你有帮助的话记得收藏一下,方便下次浏览

目录

  • 一、mysql主从复制原理
  • 二、mysql编译安装
  • 三、主从配置
  • 四、主从不同步

系统:centos6.6

主:192.168.142.129 mysql-5.6.30.tar.gz

从:192.168.142.130 192.168.142.131 mysql-5.6.30.tar.gz

一、mysql主从复制原理

mysql5.6主从搭建以及不同步问题详解

(1) master将改变记录到二进制日志(binary log)中;

(2) slave将master的binary log events拷贝到它的中继日志(relay log);slave的i/o线程从master的二进制日志中读取事件并写入中继日志;

(3) slave重做中继日志中的事件,将改变反映它自己的数据。slave的sql线程从中继日志读取事件,并在本地重放其中的事件,使其与master中的数据一致。

mysql主从实现的步骤:

1、使用mysqldump 命令备份数据库

2、查看主节点二进制的位置点

3、创建备份用户,并授权(replication client.replication slave)

4、从服务器修改server-id,必须与主mysql的server-id不同,开启中继日子,关闭二进制日子

5、从数据库,倒入数据,并使用授权用户,连接主mysql

6、start slave

sql语言共分为以下几大类:查询语言dql,控制语言dcl,操纵语言dml,定义语言ddl。事务控制tcl.

dql(data query languages)语句:即数据库定义语句,用来查询select子句,from子句,where子句组成的查询块,比如:select–from–where–grouop by–having–order by–limit

ddl(data definition languages)语句:即数据库定义语句,用来创建数据库中的表、索引、视图、存储过程、触发器等,常用的语句关键字有create,alter,drop,truncate,comment,rename。增删改表的结构

dml(data manipulation language)语句:即数据操纵语句,用来查询、添加、更新、删除等,常用的语句关键字有:select,insert,update,delete,merge,call,explain plan,lock table,包括通用性的增删改查。增删改表的数据

dcl(data control language)语句:即数据控制语句,用于授权/撤销数据库及其字段的权限(dcl is short name of data control language which includes commands such as grant and mostly concerned with rights, permissions and other controls of the database system.)。常用的语句关键字有:grant,revoke。

tcl(transaction control language)语句:事务控制语句,用于控制事务,常用的语句关键字有:commit,rollback,savepoint,set transaction。

二、mysql编译安装

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

#!/bin/bash

yum -y install make gcc gcc-c++ openssl openssl-devel pcre-devel gd cmake ncurses ncurses-devel

id -u mysql

if [ `echo $?` -ne 0 ];

then

groupadd mysql

useradd -m -g mysql -s /sbin/nologin mysql

fi

if [ ! -d "/usr/local/mysql" ];

then

mkdir -p /usr/local/mysql

fi

mkdir -p /data/mysql

chown -r mysql:mysql /data/mysql

cd /home/soft/ #软件存放目录

tar zxvf mysql-5.6.30.tar.gz

cd mysql-5.6.30

cmake -dcmake_install_prefix=/usr/local/mysql -dsysconfdir=/etc -dmysql_datadir=/data/mysql/data -dinstall_mandir=/usr/share/man -dmysql_tcp_port=3306 -dmysql_unix_addr=/tmp/mysql.sock -ddefault_charset=utf8 -dextra_charsets=all -ddefault_collation=utf8_general_ci -dwith_readline=1 -dwith_ssl=system -dwith_embedded_server=1 -denabled_local_infile=1 -dwith_innobase_storage_engine=1

make && make install

chown -r mysql:mysql .

chmod +x scripts/mysql_install_db

./scripts/mysql_install_db –user=mysql –basedir=/usr/local/mysql –datadir=/data/mysql

cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld

chmod 755 /etc/init.d/mysqld

chkconfig mysqld on

cat> /etc/rc.d/init.d/mysqld <<'eof' #mysql启动脚本

#!/bin/sh

# copyright abandoned 1996 tcx datakonsult ab & monty program kb & detron hb

# this file is public domain and comes with no warranty of any kind

# mysql daemon start/stop script.

# usually this is put in /etc/init.d (at least on machines sysv r4 based

# systems) and linked to /etc/rc3.d/s99mysql and /etc/rc0.d/k01mysql.

# when this is done the mysql server will be started when the machine is

# started and shut down when the systems goes down.

# comments to support chkconfig on redhat linux

# chkconfig: 2345 64 36

# description: a very fast and reliable sql database engine.

# comments to support lsb init script conventions

### begin init info

# provides: mysql

# required-start: $local_fs $network $remote_fs

# should-start: ypbind nscd ldap ntpd xntpd

# required-stop: $local_fs $network $remote_fs

# default-start: 2 3 4 5

# default-stop: 0 1 6

# short-description: start and stop mysql

# description: mysql is a very fast and reliable sql database engine.

### end init info

# if you install mysql on some other places than /usr/local/mysql, then you

# have to do one of the following things for this script to work:

#

# – run this script from within the mysql installation directory

# – create a /etc/my.cnf file with the following information:

# [mysqld]

# basedir=<path-to-mysql-installation-directory>

# – add the above to any other configuration file (for example ~/.my.ini)

# and copy my_print_defaults to /usr/bin

# – add the path to the mysql-installation-directory to the basedir variable

# below.

#

# if you want to affect other mysql variables, you should make your changes

# in the /etc/my.cnf, ~/.my.cnf or other mysql configuration files.

# if you change base dir, you must also change datadir. these may get

# overwritten by settings in the mysql configuration files.

basedir=/usr/local/mysql

datadir=/data/mysql

# default value, in seconds, afterwhich the script should timeout waiting

# for server start.

# value here is overriden by value in my.cnf.

# 0 means don't wait at all

# negative numbers mean to wait indefinitely

service_startup_timeout=900

# lock directory for redhat / suse.

lockdir='/var/lock/subsys'

lock_file_path="$lockdir/mysql"

# the following variables are only set for letting mysql.server find things.

# set some defaults

mysqld_pid_file_path=

if test -z "$basedir"

then

basedir=/usr/local/mysql

bindir=/usr/local/mysql/bin

if test -z "$datadir"

then

datadir=/data/mysql/data

fi

sbindir=/usr/local/mysql/bin

libexecdir=/usr/local/mysql/bin

else

bindir="$basedir/bin"

if test -z "$datadir"

then

datadir="$basedir/data"

fi

sbindir="$basedir/sbin"

libexecdir="$basedir/libexec"

fi

# datadir_set is used to determine if datadir was set (and so should be

# *not* set inside of the –basedir= handler.)

datadir_set=

# use lsb init script functions for printing messages, if possible

#

lsb_functions="/lib/lsb/init-functions"

if test -f $lsb_functions ; then

. $lsb_functions

else

log_success_msg()

{

echo " success! $@"

}

log_failure_msg()

{

echo " error! $@"

}

fi

path="/sbin:/usr/sbin:/bin:/usr/bin:$basedir/bin"

export path

mode=$1 # start or stop

[ $# -ge 1 ] && shift

other_args="$*" # uncommon, but needed when called from an rpm upgrade action

# expected: "–skip-networking –skip-grant-tables"

# they are not checked here, intentionally, as it is the resposibility

# of the "spec" file author to give correct arguments only.

case `echo "testing\\c"`,`echo -n testing` in

*c*,-n*) echo_n= echo_c= ;;

*c*,*) echo_n=-n echo_c= ;;

*) echo_n= echo_c='\\c' ;;

esac

parse_server_arguments() {

for arg do

case "$arg" in

–basedir=*) basedir=`echo "$arg" | sed -e 's/^[^=]*=//'`

bindir="$basedir/bin"

if test -z "$datadir_set"; then

datadir="$basedir/data"

fi

sbindir="$basedir/sbin"

libexecdir="$basedir/libexec"

;;

–datadir=*) datadir=`echo "$arg" | sed -e 's/^[^=]*=//'`

datadir_set=1

;;

–pid-file=*) mysqld_pid_file_path=`echo "$arg" | sed -e 's/^[^=]*=//'` ;;

–service-startup-timeout=*) service_startup_timeout=`echo "$arg" | sed -e 's/^[^=]*=//'` ;;

esac

done

}

wait_for_pid () {

verb="$1" # created | removed

pid="$2" # process id of the program operating on the pid-file

pid_file_path="$3" # path to the pid file.

i=0

avoid_race_condition="by checking again"

while test $i -ne $service_startup_timeout ; do

case "$verb" in

'created')

# wait for a pid-file to pop into existence.

test -s "$pid_file_path" && i='' && break

;;

'removed')

# wait for this pid-file to disappear

test ! -s "$pid_file_path" && i='' && break

;;

*)

echo "wait_for_pid () usage: wait_for_pid created|removed pid pid_file_path"

exit 1

;;

esac

# if server isn't running, then pid-file will never be updated

if test -n "$pid"; then

if kill -0 "$pid" 2>/dev/null; then

: # the server still runs

else

# the server may have exited between the last pid-file check and now.

if test -n "$avoid_race_condition"; then

avoid_race_condition=""

continue # check again.

fi

# there's nothing that will affect the file.

log_failure_msg "the server quit without updating pid file ($pid_file_path)."

return 1 # not waiting any more.

fi

fi

echo $echo_n ".$echo_c"

i=`expr $i + 1`

sleep 1

done

if test -z "$i" ; then

log_success_msg

return 0

else

log_failure_msg

return 1

fi

}

# get arguments from the my.cnf file,

# the only group, which is read from now on is [mysqld]

if test -x ./bin/my_print_defaults

then

print_defaults="./bin/my_print_defaults"

elif test -x $bindir/my_print_defaults

then

print_defaults="$bindir/my_print_defaults"

elif test -x $bindir/mysql_print_defaults

then

print_defaults="$bindir/mysql_print_defaults"

else

# try to find basedir in /etc/my.cnf

conf=/etc/my.cnf

print_defaults=

if test -r $conf

then

subpat='^[^=]*basedir[^=]*=\\(.*\\)$'

dirs=`sed -e "/$subpat/!d" -e 's//\\1/' $conf`

for d in $dirs

do

d=`echo $d | sed -e 's/[ ]//g'`

if test -x "$d/bin/my_print_defaults"

then

print_defaults="$d/bin/my_print_defaults"

break

fi

if test -x "$d/bin/mysql_print_defaults"

then

print_defaults="$d/bin/mysql_print_defaults"

break

fi

done

fi

# hope it's in the path … but i doubt it

test -z "$print_defaults" && print_defaults="my_print_defaults"

fi

#

# read defaults file from 'basedir'. if there is no defaults file there

# check if it's in the old (depricated) place (datadir) and read it from there

#

extra_args=""

if test -r "$basedir/my.cnf"

then

extra_args="-e $basedir/my.cnf"

else

if test -r "$datadir/my.cnf"

then

extra_args="-e $datadir/my.cnf"

fi

fi

parse_server_arguments `$print_defaults $extra_args mysqld server mysql_server mysql.server`

#

# set pid file if not given

#

if test -z "$mysqld_pid_file_path"

then

mysqld_pid_file_path=$datadir/`hostname`.pid

else

case "$mysqld_pid_file_path" in

/* ) ;;

* ) mysqld_pid_file_path="$datadir/$mysqld_pid_file_path" ;;

esac

fi

case "$mode" in

'start')

# start daemon

# safeguard (relative paths, core dumps..)

cd $basedir

echo $echo_n "starting mysql"

if test -x $bindir/mysqld_safe

then

# give extra arguments to mysqld with the my.cnf file. this script

# may be overwritten at next upgrade.

$bindir/mysqld_safe –datadir="$datadir" –pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &

wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?

# make lock for redhat / suse

if test -w "$lockdir"

then

touch "$lock_file_path"

fi

exit $return_value

else

log_failure_msg "couldn't find mysql server ($bindir/mysqld_safe)"

fi

;;

'stop')

# stop daemon. we use a signal here to avoid having to know the

# root password.

if test -s "$mysqld_pid_file_path"

then

mysqld_pid=`cat "$mysqld_pid_file_path"`

if (kill -0 $mysqld_pid 2>/dev/null)

then

echo $echo_n "shutting down mysql"

kill $mysqld_pid

# mysqld should remove the pid file when it exits, so wait for it.

wait_for_pid removed "$mysqld_pid" "$mysqld_pid_file_path"; return_value=$?

else

log_failure_msg "mysql server process #$mysqld_pid is not running!"

rm "$mysqld_pid_file_path"

fi

# delete lock for redhat / suse

if test -f "$lock_file_path"

then

rm -f "$lock_file_path"

fi

exit $return_value

else

log_failure_msg "mysql server pid file could not be found!"

fi

;;

'restart')

# stop the service and regardless of whether it was

# running or not, start it again.

if $0 stop $other_args; then

$0 start $other_args

else

log_failure_msg "failed to stop running server, so refusing to try to start."

exit 1

fi

;;

'reload'|'force-reload')

if test -s "$mysqld_pid_file_path" ; then

read mysqld_pid < "$mysqld_pid_file_path"

kill -hup $mysqld_pid && log_success_msg "reloading service mysql"

touch "$mysqld_pid_file_path"

else

log_failure_msg "mysql pid file could not be found!"

exit 1

fi

;;

'status')

# first, check to see if pid file exists

if test -s "$mysqld_pid_file_path" ; then

read mysqld_pid < "$mysqld_pid_file_path"

if kill -0 $mysqld_pid 2>/dev/null ; then

log_success_msg "mysql running ($mysqld_pid)"

exit 0

else

log_failure_msg "mysql is not running, but pid file exists"

exit 1

fi

else

# try to find appropriate mysqld process

mysqld_pid=`pidof $libexecdir/mysqld`

if test -z $mysqld_pid ; then

if test -f "$lock_file_path" ; then

log_failure_msg "mysql is not running, but lock file ($lock_file_path) exists"

exit 2

fi

log_failure_msg "mysql is not running"

exit 3

else

log_failure_msg "mysql is running but pid file could not be found"

exit 4

fi

fi

;;

*)

# usage

basename=`basename "$0"`

echo "usage: $basename {start|stop|restart|reload|force-reload|status} [ mysql server options ]"

exit 1

;;

esac

exit 0

eof

cat> /etc/my.cnf <<'eof' #mysql配置文件

[client]

#password = your_password

port = 3306

socket = /tmp/mysql.sock

# here follows entries for some specific programs

# the mysql server

[mysqld]

port = 3306

socket = /tmp/mysql.sock

skip-external-locking

key_buffer_size = 384m

max_allowed_packet = 64m

table_open_cache = 512

sort_buffer_size = 2m

read_buffer_size = 2m

read_rnd_buffer_size = 8m

myisam_sort_buffer_size = 64m

thread_cache_size = 8

query_cache_size = 32m

# try number of cpu's*2 for thread_concurrency

thread_concurrency = 8

basedir = /usr/local/mysql

datadir = /data/mysql

max_connections = 5000

long_query_time = 1

slow_query_log = 1

slow_query_log_file = /var/log/mysql/slow.log

# don't listen on a tcp/ip port at all. this can be a security enhancement,

# if all processes that need to connect to mysqld run on the same host.

# all interaction with mysqld must be made via unix sockets or named pipes.

# note that using this option without enabling named pipes on windows

# (via the "enable-named-pipe" option) will render mysqld useless!

lower_case_table_names = 1

# replication master server (default)

# binary logging is required for replication

#log-bin=mysql-bin

skip-name-resolve

# required unique id between 1 and 2^32 – 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

#server-id = 1

# binary logging – not required for slaves, but recommended

#log-bin=mysql-bin

#

# binary logging format – mixed recommended

#binlog_format=mixed

# uncomment the following if you are using innodb tables

#innodb_data_home_dir = /data/mysql/data

#innodb_data_file_path = ibdata1:2000m;ibdata2:10m:autoextend

#innodb_log_group_home_dir = /data/mysql/data

# you can set .._buffer_pool_size up to 50 – 80 %

# of ram but beware of setting memory usage too high

innodb_buffer_pool_size = 4096m

#innodb_additional_mem_pool_size = 20m

# set .._log_file_size to 25 % of buffer pool size

innodb_log_file_size = 512m

#innodb_log_buffer_size = 8m

innodb_flush_log_at_trx_commit = 0

#innodb_lock_wait_timeout = 50

[mysqldump]

quick

max_allowed_packet = 64m

[mysql]

no-auto-rehash

# remove the next comment character if you are not familiar with sql

#safe-updates

[myisamchk]

key_buffer_size = 256m

sort_buffer_size = 256m

read_buffer = 2m

write_buffer = 2m

[mysqlhotcopy]

interactive-timeout

eof

ln -s /usr/local/mysql/bin/mysqladmin /usr/bin

ln -s /usr/local/mysql/lib/mysql /usr/lib

ln -s /usr/local/mysql/include/mysql /usr/include/mysql

mkdir /var/lib/mysql

ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock

echo 'export path=$path:/usr/local/mysql/bin' >> /etc/profile

sleep 2

source /etc/profile

service mysqld start

sleep 5

cd /usr/local/mysql/bin && mysqladmin -uroot password 'mysql' #授权root用户的password

source /etc/profile

三、主从配置

1、这里验证主库有数据的情况,然后授权有复制权限的用户

?

1

2

3

4

5

6

7

mysql> create database db1;

mysql> use db1

mysql> create table t1(id int, name varchar(12));

mysql> insert into t1 values(1, 'tom'), (2, 'jerry'), (3, 'jack');

mysql> grant replication slave,replication client on *.* to 'backuser'@'192.168.142.130' identified by 'mysqll';

mysql> grant replication slave,replication client on *.* to 'backuser'@'192.168.142.131' identified by 'mysql';

mysql> flush privileges;

2、修改各个数据库的配置文件后重启数据库

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

vi /etc/my.cnf #主库配置文件

server-id=1

log-bin=mysql-bin

binlog-do-db=db1

binlog-ignore-db=mysql

vi /etc/my.cnf #从库配置文件

server-id=2 #从库id不能和主库一样,其他从库往后面排

log-bin=relay-bin

replicate-do-db=db1 #同步db1库

replicate-ignore-db=mysql #不会同步mysql库

read_only #只读

service mysqld restart

3、主库锁表备份,然后文件传给从库

?

1

2

3

4

5

6

7

mysql> flush tables with read lock; #主库锁表防止新的数据写入

mysql> show master status; #查看主库位置节点

新打开一个终端备份:

mysqldump -u root -p –default-character-set=utf8 –opt -q -r –skip-lock-tables db1 > /root/db1.sql

scp /root/db1.sql root@192.168.142.130:/root

scp /root/db1.sql root@192.168.142.130:/root

4、从库导入数据,然后change到主库的节点

?

1

2

3

4

5

6

7

mysql -u root -p

mysql> create database db1;

mysql> use db1

mysql> source /root/db1.sql

mysql> change master to master_host='192.168.142.129',master_user='backuser',master_password='mysql',master_log_file='mysql-bin.000001',master_log_pos=120;

mysql> start slave;

mysql> show slave status\\g

mysql5.6主从搭建以及不同步问题详解

5、主库解锁

mysql> unlock tables;

以上配置对主从不同步,重新配置主从同样适用。

四、主从不同步

1、造成不同步的原因

​网络的延迟主从两台机器的负载不一致max_allowed_packet设置不一致key自增键开始的键值跟自增步长设置不一致引起的主从不一致​mysql异常宕机情况下,如果未设置sync_binlog=1或者innodb_flush_log_at_trx_commit=1很有可能

出现binlog或者relaylog文件出现损坏,导致主从不一致mysql本身的bug引起的主从不同步版本不一致,特别是高版本是主,低版本为从的情况下,主数据库上面支持的功能,从数据库上面不支持该功能

2、解决办法

(1)忽略错误后,继续同步

该方法适用于主从库数据相差不大,或者要求数据可以不完全统一的情况,数据要求不严格的情况

?

1

stop slave; set global sql_slave_skip_counter =1; start slave; show slave status\\g

(2)重新做主从

参考上面配置主库锁表重新做主从。

到此这篇关于mysql5.6主从搭建以及不同步问题详解的文章就介绍到这了,更多相关mysql主从搭建和不同步内容请搜索钦钦技术栈以前的文章或继续浏览下面的相关文章希望大家以后多多支持钦钦技术栈!

原文链接:https://blog.51cto.com/qidian510/4735249

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/3816/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022年 7月 20日 10:14:30
下一篇 2022年 7月 20日 10:14:45

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!