阿坝州网站建设公司

怎样简单解析mysql主从复制

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

成都创新互联专注于企业成都营销网站建设、网站重做改版、子长网站定制设计、自适应品牌网站建设、H5高端网站建设、商城网站制作、集团公司官网建设、外贸网站制作、高端网站制作、响应式网页设计等建站业务，价格优惠性价比高，为子长等各大城市提供网站开发制作服务。

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

  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)

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


标题名称：怎样简单解析mysql主从复制
网页URL：http://azwzsj.com/article/peeege.html

其他资讯