mysql 主从skip_mysql主从复制（冷备）

mysql主从复制(冷备)

1.create repl user on master

grant replication slave on *.* to

'repl'@'10.124.110.116'

identified by 'doudou123';

root@

(none

) 10

:13

>grant replication slave on

*

.

* to

'repl'

@

'10.124.110.116' identified by

'doudou123'

;

Query OK

, 0 rows affected

(0

.04 sec

)

2.enable BINLOG and set server-id in my.conf and restart server

[mysqld]

log-bin = /mysql/data/3307/mysql-bin.log

server-id = 113

root@

(none

) 10

:35

>show variables like

'

%log_bin

%

'

;

+

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

+

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

+

| Variable_name

| Value

|

+

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

+

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

+

| log_bin

|

ON

|

| log_bin_basename

|

/mysql

/data

/3307

/mysql

-bin

.

log

|

<

=

=success

| log_bin_index

|

/mysql

/data

/3307

/binlog

.index

|

| log_bin_trust_function_creators

|

ON

|

| log_bin_use_v1_row_events

| OFF

|

| sql_log_bin

|

ON

|

+

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

+

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

+

root@

(none

) 10

:36

>show variables like

'

%server_id

%

'

;

+

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

+

-

-

-

-

-

-

-

+

| Variable_name

| Value

|

+

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

+

-

-

-

-

-

-

-

+

| server_id

| 113

|

<

=

=success

| server_id_bits

| 32

|

+

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

+

-

-

-

-

-

-

-

+

2 rows

in

set

(0

.00 sec

)

3.flush tables

flush tables with read lock;

root@

(none

) 10

:42

>flush tables with read

lock

;

Query OK

, 0 rows affected

(0

.00 sec

)

4.show file and position

show master status;

root@

(none

) 14

:57

>show master status

;

+

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

+

-

-

-

-

-

-

-

-

-

-

+

-

-

-

-

-

-

-

-

-

-

-

-

-

-

+

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

+

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

+

| File

| Position

| Binlog_Do_DB

| Binlog_Ignore_DB

| Executed_Gtid_Set

|

+

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

+

-

-

-

-

-

-

-

-

-

-

+

-

-

-

-

-

-

-

-

-

-

-

-

-

-

+

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

+

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

+

| mysql

-bin

.000008

| 120

|

|

|

|

+

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

+

-

-

-

-

-

-

-

-

-

-

+

-

-

-

-

-

-

-

-

-

-

-

-

-

-

+

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

+

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

+

5.cold backup

tar -cvf 3307data.tar 3307

6.unlock tables on master

unlock tables;

root@

(none

) 14

:18

>unlock tables

;

Query OK

, 0 rows affected

(0

.02 sec

)

7.set only server-id in my.conf on slave

[mysqld]

server-id = 114

vi auto.cnf

server_uuid=57735006-38f1-11e6-862c-005056beb65f

8.startup server with --skip-slave-start on slave

mysqld_multi start 3307

9.set user,ip,port,replication log and position on slave

change master to

master_host='10.124.110.113',

master_port=3307,

master_user='repl',

master_password='doudou123',

master_log_file='mysql-bin.000008',

master_log_pos=120;

mysql

> change master to

-

> master_host

=

'10.124.110.113'

,

-

> master_port

=3307

,

-

> master_user

=

'repl'

,

-

> master_password

=

'doudou123'

,

-

> master_log_file

=

'binlog.000008'

,

-

> master_log_pos

=120

;

Query OK

, 0 rows affected

, 2 warnings

(0

.05 sec

)

10.startup slave process

start slave;

mysql

> start slave

;

Query OK

, 0 rows affected

(0

.01 sec

)

11.show processlist on slave

show processlist \G

mysql> show processlist \G

*************************** 1. row ***************************

Id: 1

User: event_scheduler

Host: localhost

db: NULL

Command: Daemon

Time: 10060

State: Waiting on empty queue

Info: NULL

*************************** 2. row ***************************

Id: 2

User: root

Host: localhost

db: NULL

Command: Query

Time: 0

State: init

Info: show processlist

*************************** 3. row ***************************

Id: 3

User: system user

Host:

db: NULL

Command: Connect

Time: 19

State: Connecting to master

<=="Waiting for master to send event" is right. and some errors because

password is wrongor

server_uuid is the same.

## find errors using "show slave status \G"  and modify server_uuid using "auto.cnf" and find server_uuid using "show variables like '%server_uuid%';" ##

Info: NULL

*************************** 4. row ***************************

Id: 4

User: system user

Host:

db: NULL

Command: Connect

Time: 19

State: Slave has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

4 rows in set (0.00 sec)

this is all right !!!!

mysql

> show processlist \G

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

* 1

. row

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

Id

: 1

User

: system user

Host

:

db

: NULL

Command

: Connect

Time

: 31769

State

: Slave has read all relay log

; waiting

for the slave I/O thread to update it

Info

: NULL

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

* 2

. row

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

Id

: 2

User

: system user

Host

:

db

: NULL

Command

: Connect

Time

: 946

State

: Waiting

for master to send event

Info

: NULL

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

* 3

. row

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

Id

: 3

User

: event_scheduler

Host

: localhost

db

: NULL

Command

: Daemon

Time

: 946

State

: Waiting on empty queue

Info

: NULL

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

* 4

. row

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

Id

: 5

User

: root

Host

: localhost

db

:

test

Command

: Query

Time

: 0

State

: init

Info

: show processlist

4 rows

in set

(0

.00 sec

)

12.test change rows on master

use test

show tables;

create table repl_test(id int);

insert into repl_test values(1),(2),(3);

insert into repl_test values(4),(5),(6);

insert into repl_test values(7),(8),(9);

13.show test rows on slave

use test

show tables;

select

* from repl_test

;

+

-

-

-

-

-

-

+

| id

|

+

-

-

-

-

-

-

+

| 1

|

| 2

|

| 3

|

| 4

|

| 5

|

| 6

|

| 7

|

| 8

|

| 9

|

+

-

-

-

-

-

-

+

9 rows

in set

(0

.00 sec

)

########################################################################################

版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!【QQ交流群：53993419】

QQ：14040928 E-mail：

dbadoudou@163.com

本文链接：

http://blog.itpub.net/blog/post/id/2123509/

########################################################################################