怎樣簡(jiǎn)單解析MySQL主從復(fù)制，很多新手對(duì)此不是很清楚，為了幫助大家解決這個(gè)難題，下面小編將為大家詳細(xì)講解，有這方面需求的人可以來(lái)學(xué)習(xí)下，希望你能有所收獲。

成都創(chuàng)新互聯(lián)專(zhuān)注于企業(yè)成都營(yíng)銷(xiāo)網(wǎng)站建設(shè)、網(wǎng)站重做改版、子長(zhǎng)網(wǎng)站定制設(shè)計(jì)、自適應(yīng)品牌網(wǎng)站建設(shè)、H5高端網(wǎng)站建設(shè)、商城網(wǎng)站制作、集團(tuán)公司官網(wǎng)建設(shè)、外貿(mào)網(wǎng)站制作、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁(yè)設(shè)計(jì)等建站業(yè)務(wù)，價(jià)格優(yōu)惠性?xún)r(jià)比高，為子長(zhǎng)等各大城市提供網(wǎng)站開(kāi)發(fā)制作服務(wù)。

mysql主從復(fù)制（冷備）

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

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;

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)

看完上述內(nèi)容是否對(duì)您有幫助呢？如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章，請(qǐng)關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝您對(duì)創(chuàng)新互聯(lián)的支持。

新聞標(biāo)題：怎樣簡(jiǎn)單解析mysql主從復(fù)制
本文路徑：http://aaarwkj.com/article14/peeege.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供云服務(wù)器、網(wǎng)站建設(shè)、服務(wù)器托管、外貿(mào)建站、商城網(wǎng)站、定制開(kāi)發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)