PHP群:95885625 Hbuilder+MUI群:81989597 站长QQ:634381967
    您现在的位置: 首页 > 数据库 > MySQL教程 > 正文

    Mysql-5.7.20主从复制测试

    作者:admin来源:网络浏览:时间:2018-01-14 18:47:25我要评论
    分享到
    导读:前言, Mysql 5.7.20测试主从复制,环境,主库 192.168.1.59 t-xi-sonar01,从库 192.168.1.51 t-xi-orc01
    前言
        Mysql 5.7.20测试主从复制

    环境
        主库 192.168.1.59  t-xi-sonar01
        从库 192.168.1.51  t-xi-orc01

    设定主机host文件

    1. 主库 
    2. [root@t-xi-sonar01 ~]# cat /etc/hosts 
    3. 127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4 
    4. ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6 
    5. 192.168.1.59 t-xi-sonar01 
    6. 192.168.1.51 t-xi-orc01 
    1. 从库 
    2. [root@t-xi-orc01 ~]# cat /etc/hosts 
    3. 127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4 
    4. ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6 
    5. 192.168.1.51 t-xi-orc01 
    6. 192.168.1.59 t-xi-sonar01 

    Mysql数据库配置
        【Master-Server】
     

    1. [root@t-xi-sonar01 ~]# service mysqld stop 
    2.  Stopping mysqld:                                           [  OK  ] 
    3. [root@t-xi-sonar01 ~]# vim /etc/my.cnf 
    4. #Server ID,一般设置成IP地址的最后一位,如下测试就按后两位 
    5. server_id=59 
    6. #开启log bin,名字最好有意义用来区分 
    7. log-bin=dev-bin 
    8. #需要进行复制的数据库,可以指定数据库 
    9. #binlog-do-db=DB_master 
    10. #不需要备份的数据库,可以设置多个数据库,一般不会同步mysql这个库 
    11. binlog-ignore-db=mysql 
    12. binlog-ignore-db=information_schema 
    13. binlog-ignore-db=performance_schema 
    14. #为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存 
    15. binlog_cache_size=1m 
    16. #二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。 
    17. expire_logs_days=7 
    18. # 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。 
    19. # 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致 
    20. slave_skip_errors=1062 
    21. [root@t-xi-sonar01 ~]# service mysqld start 
    22. Starting mysqld:                                           [  OK  ] 
    23. [root@t-xi-sonar01 ~]# 
    24.   
    25. mysql 5.7.20登陆报错解决 
    26.  
    27.   ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) 
    28. mysqld_safe --user=mysql --skip-grant-tables --skip-networking & 
    29. mysql -u root 
    30. update user set authentication_string=PASSWORD("****") where User='root'
    31. flush privileges; 
    32.   
    33. 主库创建同步账户 
    34. service mysqld start 
    35. mysql> mysql -u root -p 
    36. mysql> CREATE USER 'replication'@'192.168.1.51' IDENTIFIED BY 'slave'
    37. mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.51'
    38. mysql> flush privileges; 
    39.   
    40. 主库锁定后备份将资料同步到从库 
    41. mysql>use sonar 
    42. mysql>FLUSH TABLES WITH READ LOCK; 
    43. mysqldump -u root -p --databases sonar > sonar.sql 
    44. scp sonar.sql @192.168.1.51:/root 
    45. mysql> unlock tables; 
    46. [master-server] 
    47. mysql> show master status ; 
    48. +----------------+----------+--------------+---------------------------------------------+-------------------+ 
    49. | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set | 
    50. +----------------+----------+--------------+---------------------------------------------+-------------------+ 
    51. | dev-bin.000004 |      783 |              | mysql,information_schema,performance_schema |                   | 
    52. +----------------+----------+--------------+---------------------------------------------+-------------------+ 

    【Slave-Server】

     

    1. service mysqld stop 
    2. vim /etc/my.cnf 
    3. #add slave-server 
    4. server_id=51 
    5. #binlog-ignore-db=mydql 
    6. #binlog-ignore-db=information_schema 
    7. #binlog-ignore-db=performance_schema 
    8. #log-bin=dev-slave-bin 
    9. binlog_cache_size=1M 
    10. binlog_format=mixed 
    11. expire_logs_days=7 
    12. slave_skip_errors=1062 
    13. relay_log=dev-relay-bin 
    14. #log_slave_updates=1 
    15. read_only=1 
    16.   
    17. service mysqld start 
    18. 将主库备份导入从库 
    19. mysql>source /root/sonar.sql 
    20. 添加链接到主库同步复制的账户 
    21. mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.59', MASTER_USER='replication', MASTER_PASSWORD='slave', MASTER_LOG_FILE='dev-bin.000001', MASTER_LOG_POS=0; 
    22.      
    23. MASTER_LOG_FILE:指定log bin日志文件名称 
    24. MASTER_LOG_POS :指定同步复制log分区号,可以从0开始。 
    25.   
    26. 查看slave状态 
    27. show slave status \G 
    28.      
    29. Slave_IO_State #从站的当前状态 
    30. Slave_IO_Running: Yes #读取主程序二进制日志的I/O线程是否正在运行 
    31. Slave_SQL_Running: Yes #执行读取主服务器中二进制日志事件的SQL线程是否正在运行。与I/O线程一样 
    32. Seconds_Behind_Master #是否为0,0就是已经同步了 
    33. 启动slave 
    34. start slave; 
    1. mysql>     show slave status \G 
    2. *************************** 1. row *************************** 
    3.                Slave_IO_State: Waiting for master to send event 
    4.                   Master_Host: 192.168.1.59 
    5.                   Master_User: replication 
    6.                   Master_Port: 3306 
    7.                 Connect_Retry: 60 
    8.               Master_Log_File: dev-bin.000004 
    9.           Read_Master_Log_Pos: 1255 
    10.                Relay_Log_File: dev-relay-bin.000005 
    11.                 Relay_Log_Pos: 1464 
    12.         Relay_Master_Log_File: dev-bin.000004 
    13.              Slave_IO_Running: Yes 
    14.             Slave_SQL_Running: Yes 
    15.               Replicate_Do_DB: 
    16.           Replicate_Ignore_DB: 
    17.            Replicate_Do_Table: 
    18.        Replicate_Ignore_Table: 
    19.       Replicate_Wild_Do_Table: 
    20.   Replicate_Wild_Ignore_Table: 
    21.                    Last_Errno: 0 
    22.                    Last_Error: 
    23.                  Skip_Counter: 0 
    24.           Exec_Master_Log_Pos: 1255 
    25.               Relay_Log_Space: 1878 
    26.               Until_Condition: None 
    27.                Until_Log_File: 
    28.                 Until_Log_Pos: 0 
    29.            Master_SSL_Allowed: No 
    30.            Master_SSL_CA_File: 
    31.            Master_SSL_CA_Path: 
    32.               Master_SSL_Cert: 
    33.             Master_SSL_Cipher: 
    34.                Master_SSL_Key: 
    35.         Seconds_Behind_Master: 0 
    36. Master_SSL_Verify_Server_Cert: No 
    37.                 Last_IO_Errno: 0 
    38.                 Last_IO_Error: 
    39.                Last_SQL_Errno: 0 
    40.                Last_SQL_Error: 
    41.   Replicate_Ignore_Server_Ids: 
    42.              Master_Server_Id: 59 
    43.                   Master_UUID: d6901902-ea28-11e7-b859-000c29255261 
    44.              Master_Info_File: /var/lib/mysql/master.info 
    45.                     SQL_Delay: 0 
    46.           SQL_Remaining_Delay: NULL 
    47.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 
    48.            Master_Retry_Count: 86400 
    49.                   Master_Bind: 
    50.       Last_IO_Error_Timestamp: 
    51.      Last_SQL_Error_Timestamp: 
    52.                Master_SSL_Crl: 
    53.            Master_SSL_Crlpath: 
    54.            Retrieved_Gtid_Set: 
    55.             Executed_Gtid_Set: 
    56.                 Auto_Position: 0 
    57.          Replicate_Rewrite_DB: 
    58.                  Channel_Name: 
    59.            Master_TLS_Version: 
    60. row in set (0.00 sec) 

    【Master-Server】

     

    1. 创建table和database测试 
    2.       
    3. 在sonar下建立测试表 
    4. mysql> use sonar;    
    5. mysql> create table slave_t( 
    6.     -> id int(10) not null, name varchar(20) 
    7.     -> ) 
    8.     -> ; 
    9. Query OK, 0 rows affected (1.57 sec) 
    10.   
    11. mysql> insert into slave_t values(1,'name01'); 
    12. Query OK, 1 row affected (0.33 sec) 
    13.    
    14. 创建slave_db测试数据库 
    15. mysql> use mysql 
    16. Reading table information for completion of table and column names 
    17. You can turn off this feature to get a quicker startup with -A 
    18.   
    19. Database changed 
    20. mysql> create database slave_db; 
    21. Query OK, 1 row affected (0.17 sec) 

     【Slave-Server】
     

    1. mysql> show databases; 
    2. +--------------------+ 
    3. | Database           | 
    4. +--------------------+ 
    5. | information_schema | 
    6. | mysql              | 
    7. | performance_schema | 
    8. | slave_db           | 
    9. | sonar              | 
    10. | sys                | 
    11. +--------------------+ 
    12. rows in set (0.00 sec) 
    13.   
    14. mysql> use sonar; 
    15. Database changed 
    16. mysql> select * from slave_t; 
    17. +----+--------+ 
    18. | id | name   | 
    19. +----+--------+ 
    20. |  1 | name01 | 
    21. +----+--------+ 
    22. row in set (0.00 sec) 
    23.  
    24. 主库上的table和database已同步复制过来 

    【命令参考】

     

    1. 查看主库master状态 
    2. mysql> show master status; 
    3. +----------------+----------+--------------+---------------------------------------------+-------------------+ 
    4. | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set | 
    5. +----------------+----------+--------------+---------------------------------------------+-------------------+ 
    6. | dev-bin.000004 |     1426 |              | mysql,information_schema,performance_schema |                   | 
    7. +----------------+----------+--------------+---------------------------------------------+-------------------+ 
    8. row in set (0.00 sec) 
    9.   
    10. 查看从库主机列表 
    11. mysql> show slave hosts; 
    12. +-----------+------+------+-----------+--------------------------------------+ 
    13. | Server_id | Host | Port | Master_id | Slave_UUID                           | 
    14. +-----------+------+------+-----------+--------------------------------------+ 
    15. |        51 |      | 3306 |        59 | 86fff1d0-f62d-11e7-834d-000c29477dac | 
    16. +-----------+------+------+-----------+--------------------------------------+ 
    17. row in set (0.00 sec) 
    18.   
    19. 查看bin log文件列表 
    20. mysql> show binary logs; 
    21. +----------------+-----------+ 
    22. | Log_name       | File_size | 
    23. +----------------+-----------+ 
    24. | dev-bin.000001 |       177 | 
    25. | dev-bin.000002 |       177 | 
    26. | dev-bin.000003 |       177 | 
    27. | dev-bin.000004 |      1426 | 
    28. +----------------+-----------+ 
    29. rows in set (0.00 sec) 
    30.   
    31. 查看bin log文件的内容 
    32. mysql> show binlog events; 
    33. +----------------+-----+----------------+-----------+-------------+---------------------------------------+ 
    34. | Log_name       | Pos | Event_type     | Server_id | End_log_pos | Info                                  | 
    35. +----------------+-----+----------------+-----------+-------------+---------------------------------------+ 
    36. | dev-bin.000001 |   4 | Format_desc    |        59 |         123 | Server ver: 5.7.20-log, Binlog ver: 4 | 
    37. | dev-bin.000001 | 123 | Previous_gtids |        59 |         154 |                                       | 
    38. | dev-bin.000001 | 154 | Stop           |        59 |         177 |                                       | 
    39. +----------------+-----+----------------+-----------+-------------+---------------------------------------+ 
    40. rows in set (0.00 sec) 
    41.   
    42. mysql> show binlog events in 'dev-bin.000004'
    43. +----------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------+ 
    44. | Log_name       | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                                            | 
    45. +----------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------+ 
    46. | dev-bin.000004 |    4 | Format_desc    |        59 |         123 | Server ver: 5.7.20-log, Binlog ver: 4                                                                                           | 
    47. | dev-bin.000004 |  123 | Previous_gtids |        59 |         154 |                                                                                                                                 | 
    48. | dev-bin.000004 |  154 | Anonymous_Gtid |        59 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                            | 
    49. | dev-bin.000004 |  219 | Query          |        59 |         417 | CREATE USER 'replication'@'192.168.1.51' IDENTIFIED WITH 'mysql_native_password' AS '*51125B3597BEE0FC43E0BCBFEE002EF8641B44CF' | 
    50. | dev-bin.000004 |  417 | Anonymous_Gtid |        59 |         482 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                            | 
    51. | dev-bin.000004 |  482 | Query          |        59 |         631 | GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.51'                                                                  | 
    52. | dev-bin.000004 |  631 | Anonymous_Gtid |        59 |         696 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                            | 
    53. | dev-bin.000004 |  696 | Query          |        59 |         783 | flush privileges                                                                                                                | 
    54. | dev-bin.000004 |  783 | Anonymous_Gtid |        59 |         848 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                            | 
    55. | dev-bin.000004 |  848 | Query          |        59 |         985 | use `sonar`; create table slave_t( 
    56. id int(10) not null, name varchar(20) 
    57. )                                                      | 
    58. | dev-bin.000004 |  985 | Anonymous_Gtid |        59 |        1050 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                            | 
    59. | dev-bin.000004 | 1050 | Query          |        59 |        1123 | BEGIN                                                                                                                           | 
    60. | dev-bin.000004 | 1123 | Table_map      |        59 |        1177 | table_id: 326 (sonar.slave_t)                                                                                                   | 
    61. | dev-bin.000004 | 1177 | Write_rows     |        59 |        1224 | table_id: 326 flags: STMT_END_F                                                                                                 | 
    62. | dev-bin.000004 | 1224 | Xid            |        59 |        1255 | COMMIT /* xid=178006 */                                                                                                         | 
    63. | dev-bin.000004 | 1255 | Anonymous_Gtid |        59 |        1320 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                            | 
    64. | dev-bin.000004 | 1320 | Query          |        59 |        1426 | create database slave_db                                                                                                        | 
    65. +----------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------+ 
    66. rows in set (0.13 sec) 
    67.   
    68. 查看当前数据库线程列表 
    69. 【master-server】 
    70. mysql> SHOW PROCESSLIST \G 
    71. *************************** 1. row *************************** 
    72.      Id: 199 
    73.    User: root 
    74.    Host: localhost 
    75.      db: mysql 
    76. Command: Query 
    77.    Time: 0 
    78.   State: starting 
    79.    Info: SHOW PROCESSLIST 
    80. *************************** 2. row *************************** 
    81.      Id: 201 
    82.    User: replication 
    83.    Host: t-xi-orc01:41452 
    84.      db: NULL 
    85. Command: Binlog Dump 
    86.    Time: 1450 
    87.   State: Master has sent all binlog to slave; waiting for more updates 
    88.    Info: NULL 
    89. *************************** 3. row *************************** 
    90.      Id: 203 
    91.    User: sonar 
    92.    Host: localhost:57162 
    93.      db: sonar 
    94. Command: Sleep 
    95.    Time: 434 
    96.   State: 
    97.    Info: NULL 
    98. *************************** 4. row *************************** 
    99.      Id: 204 
    100.    User: sonar 
    101.    Host: localhost:57358 
    102.      db: sonar 
    103. Command: Sleep 
    104.    Time: 123 
    105.   State: 
    106.    Info: NULL 
    107. *************************** 5. row *************************** 
    108.      Id: 205 
    109.    User: sonar 
    110.    Host: localhost:57524 
    111.      db: sonar 
    112. Command: Sleep 
    113.    Time: 2 
    114.   State: 
    115.    Info: NULL 
    116. *************************** 6. row *************************** 
    117.      Id: 206 
    118.    User: sonar 
    119.    Host: localhost:57720 
    120.      db: sonar 
    121. Command: Sleep 
    122.    Time: 3 
    123.   State: 
    124.    Info: NULL 
    125. rows in set (0.00 sec) 
    126.   
    127. 【slave-server】 
    128. mysql> SHOW PROCESSLIST  \G 
    129. *************************** 1. row *************************** 
    130.      Id: 4 
    131.    User: root 
    132.    Host: localhost 
    133.      db: sonar 
    134. Command: Query 
    135.    Time: 0 
    136.   State: starting 
    137.    Info: SHOW PROCESSLIST 
    138. *************************** 2. row *************************** 
    139.      Id: 5 
    140.    User: system user 
    141.    Host: 
    142.      db: NULL 
    143. Command: Connect 
    144.    Time: 1445 
    145.   State: Waiting for master to send event 
    146.    Info: NULL 
    147. *************************** 3. row *************************** 
    148.      Id: 6 
    149.    User: system user 
    150.    Host: 
    151.      db: NULL 
    152. Command: Connect 
    153.    Time: 26717 
    154.   State: Slave has read all relay log; waiting for more updates 
    155.    Info: NULL 
    156. rows in set (0.00 sec) 
    157.   
    158.   
    159.   
    160. 从库启动复制 
    161. mysql> START SLAVE; 
    162. 从库停止复制 
    163. mysql> STOP SLAVE; 


    参考:
    https://segmentfault.com/a/1190000010867488
    https://www.jianshu.com/p/208667156faf
     

    转载请注明(B5教程网)原文链接:http://www.bcty365.com/content-35-6158-1.html
    相关热词搜索: mysql主从复制
    网友评论: