您现在的位置: 首页 > 数据库 > 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

设定主机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
 

相关热词搜索: mysql主从复制