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 wrong or 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/
########################################################################################

ITPub博客

mysql主从复制（冷备）

最新文章