怎样简单解析mysql主从复制

怎样简单解析mysql主从复制，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

mysql主从复制（冷备）

1.create repl user on master
grant replication slave on *.* to 'repl'@'10.124.110.116' identified by 'doudou123';

1. root@(none) 10:13>grant replication slave on *.* to 'repl'@'10.124.110.116' identified by 'doudou123';
   

2. 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

1. root@(none) 10:35>show variables like '%log_bin%';
   

2. +---------------------------------+-------------------------------+
   

3. | Variable_name | Value |
   

4. +---------------------------------+-------------------------------+
   

5. | log_bin | ON |
   

6. | log_bin_basename | /mysql/data/3307/mysql-bin.log| <==success
   

7. | log_bin_index | /mysql/data/3307/binlog.index |
   

8. | log_bin_trust_function_creators | ON |
   

9. | log_bin_use_v1_row_events | OFF |
   

10. | sql_log_bin | ON |
    

11. +---------------------------------+-------------------------------+
    

12. 
    

13. root@(none) 10:36>show variables like '%server_id%';
    

14. +----------------+-------+
    

15. | Variable_name | Value |
    

16. +----------------+-------+
    

17. | server_id | 113 | <==success
    

18. | server_id_bits | 32 |
    

19. +----------------+-------+
    

20. 2 rows in set (0.00 sec)

3.flush tables
flush tables with read lock;

1. root@(none) 10:42>flush tables with read lock;
   

2. Query OK, 0 rows affected (0.00 sec)

4.show file and position
show master status;

1. root@(none) 14:57>show master status;
   

2. +------------------+----------+--------------+------------------+-------------------+
   

3. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   

4. +------------------+----------+--------------+------------------+-------------------+
   

5. | mysql-bin.000008 | 120 | | | |
   

6. +------------------+----------+--------------+------------------+-------------------+

5.cold backup
tar -cvf 3307data.tar 3307

6.unlock tables on master
unlock tables;

1. root@(none) 14:18>unlock tables;
   

2. 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;

1. mysql> change master to
   

2. -> master_host='10.124.110.113',
   

3. -> master_port=3307,
   

4. -> master_user='repl',
   

5. -> master_password='doudou123',
   

6. -> master_log_file='binlog.000008',
   

7. -> master_log_pos=120;
   

8. Query OK, 0 rows affected, 2 warnings (0.05 sec)

10.startup slave process
start slave;

1. mysql> start slave;
   

2. 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)

1. this is all right !!!!
   

2. mysql> show processlist \G
   

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

4. Id: 1
   

5. User: system user
   

6. Host:
   

7. db: NULL
   

8. Command: Connect
   

9. Time: 31769
   

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

11. Info: NULL
    

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

13. Id: 2
    

14. User: system user
    

15. Host:
    

16. db: NULL
    

17. Command: Connect
    

18. Time: 946
    

19. State: Waiting for master to send event
    

20. Info: NULL
    

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

22. Id: 3
    

23. User: event_scheduler
    

24. Host: localhost
    

25. db: NULL
    

26. Command: Daemon
    

27. Time: 946
    

28. State: Waiting on empty queue
    

29. Info: NULL
    

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

31. Id: 5
    

32. User: root
    

33. Host: localhost
    

34. db: test
    

35. Command: Query
    

36. Time: 0
    

37. State: init
    

38. Info: show processlist
    

39. 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;

1. select * from repl_test;
   

2. 
   

3. +------+
   

4. | id |
   

5. +------+
   

6. | 1 |
   

7. | 2 |
   

8. | 3 |
   

9. | 4 |
   

10. | 5 |
    

11. | 6 |
    

12. | 7 |
    

13. | 8 |
    

14. | 9 |
    

15. +------+
    

16. 9 rows in set (0.00 sec)

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注行业资讯频道，感谢您对的支持。