北京SEO

配置Mysql数据库的主从同步教程

2019/10/10/17:32:32  阅读：1744  来源：谷歌SEO算法  标签： SEO工具

主从同步是实现网站分布式数据处理一个非常常用的方案了,今天我来为各位介绍配置Mysql数据库的主从同步(双主)教程,希望下文能帮助到各位哦.

配置Mysql数据库的主从同步(一主一从).

一、主库开启BINLOG、server-id

  1. [root@Master-Mysql~]#grep-E"server-id|log-bin"/etc/my.cnf
  2. log-bin=/usr/local/mysql/data/mysql-bin
  3. server-id=1
  4. mysql>showvariableslike'%log_bin%';
  5. +---------------------------------+---------------------------------------+
  6. |Variable_name|Value|
  7. +---------------------------------+---------------------------------------+
  8. |log_bin|ON|
  9. |log_bin_basename|/usr/local/mysql/data/mysql-bin|
  10. |log_bin_index|/usr/local/mysql/data/mysql-bin.index|
  11. |log_bin_trust_function_creators|OFF|
  12. |log_bin_use_v1_row_events|OFF|
  13. |sql_log_bin|ON|
  14. +---------------------------------+---------------------------------------+
  15. 6rowsinset(0.01sec)--phpfensi.com
  16. mysql>showvariableslike'%server_id%';
  17. +----------------+-------+
  18. |Variable_name|Value|
  19. +----------------+-------+
  20. |server_id|1|
  21. |server_id_bits|32|
  22. +----------------+-------+
  23. 2rowsinset(0.00sec)

备注:以上两个信息必须在[mysqld]模块下！！！

二、给从库授权

  1. mysql>grantreplicationslaveon*.*tobyrd@'192.168.199.%'identifiedby'admin';
  2. mysql>flushprivileges;
  3. mysql>selectuser,hostfrommysql.user;
  4. +------+---------------+
  5. |user|host|
  6. +------+---------------+
  7. |root|127.0.0.1|
  8. |byrd|192.168.199.%|
  9. |root|::1|
  10. |root|lamp|
  11. |root|localhost|
  12. +------+---------------+
  13. 5rowsinset(0.00sec)

锁表前建立点数据:

  1. mysql>createdatabasehitest;
  2. mysql>showdatabases;
  3. +--------------------+
  4. |Database|
  5. +--------------------+
  6. |hitest|
  7. +--------------------+
  8. 6rowsinset(0.00sec)
  9. mysql>usehitest;
  10. mysql>createtabletest(
  11. ->idint(4)notnullprimarykeyauto_increment,
  12. ->namechar(20)notnull
  13. ->);
  14. QueryOK,0rowsaffected(1.80sec)
  15. mysql>showtables;
  16. +------------------+
  17. |Tables_in_hitest|
  18. +------------------+
  19. |test|
  20. +------------------+
  21. mysql>insertintotest(id,name)values(1,'zy');
  22. mysql>select*fromtest;
  23. +----+------+
  24. |id|name|
  25. +----+------+
  26. |1|zy|
  27. +----+------+

三、锁表、备份、解锁

  1. mysql>flushtablewithreadlock;#锁表
  2. mysql>showvariableslike'%timeout%';#锁表时间
  3. +-----------------------------+----------+
  4. |Variable_name|Value|
  5. +-----------------------------+----------+
  6. |interactive_timeout|28800|
  7. |wait_timeout|28800|
  8. +-----------------------------+----------+
  9. 12rowsinset(0.06sec)
  10. mysql>showmasterstatus;#binlog日志位置
  11. +------------------+----------+--------------+------------------+-------------------+
  12. |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
  13. +------------------+----------+--------------+------------------+-------------------+
  14. |mysql-bin.000004|1305||||
  15. +------------------+----------+--------------+------------------+-------------------+
  16. 1rowinset(0.03sec)
  17. [root@Master-Mysql~]#/usr/local/mysql/bin/mysqldump-uroot-p''-B-A|gzip>/tmp/all.sql.gz#新窗口备份
  18. Enterpassword:
  19. mysql>unlocktable;#解锁
  20. ###############解锁后主库操作如下：###############
  21. mysql>usehitest
  22. mysql>insertintotest(id,name)values(2,'binghe');
  23. mysql>select*fromtest;
  24. +----+--------+
  25. |id|name|
  26. +----+--------+
  27. |1|zy|
  28. |2|binghe|
  29. +----+--------+
  30. mysql>createdatabasehxy;
  31. ###############解锁后主库操作完成~###############

备注:备份数据需要重新打开新窗口,不然锁表就自动失效.

四、主库导入到从库

  1. ################主库操作################
  2. [root@Master-Mysqltmp]#ll
  3. -rw-r--r--.1rootroot162236Jul821:30all.sql.gz
  4. [root@Master-Mysqltmp]#gzip-dall.sql.gz
  5. [root@Master-Mysqltmp]#ll
  6. -rw-r--r--.1rootroot590351Jul821:30all.sql
  7. ################主库完成################
  8. ##备注：将主库导出的all.sql通过scp、ssh、sftp等方式拷贝到从库服务器，此处略##
  9. [root@Slave-Mysql~]#greplog-bin/etc/my.cnf
  10. #log-bin=/usr/local/mysql/data/mysql-bin
  11. [root@Slave-Mysql~]#grepserver-id/etc/my.cnf
  12. server-id=2
  13. [root@Slave-Mysql~]#/etc/init.d/mysqldrestart
  14. [root@Slave-Mysqltmp]#/usr/local/mysql/bin/mysql-uroot-p'admin'</tmp/all.sql
  15. Warning:Usingapasswordonthecommandlineinterfacecanbeinsecure.
  16. [root@Slave-Mysqltmp]#/usr/local/mysql/bin/mysql-uroot-p'admin'
  17. mysql>usehitest;
  18. mysql>select*fromtest;
  19. +----+------+
  20. |id|name|
  21. +----+------+
  22. |1|zy|
  23. +----+------+
  24. 1rowinset(0.00sec)

六、从库配置信息

  1. mysql>CHANGEMASTERTO
  2. ->MASTER_HOST='192.168.199.177',
  3. ->MASTER_PORT=3306,
  4. ->MASTER_USER='byrd',
  5. ->MASTER_PASSWORD='admin',
  6. ->MASTER_LOG_FILE='mysql-bin.000004',
  7. ->MASTER_LOG_POS=1305;
  8. QueryOK,0rowsaffected,2warnings(1.96sec)
  9. [root@Slave-Mysql~]#ll/usr/local/mysql/data/master.info
  10. ##备注：master.info记录MASTER的相关信息！

七、启动从库同步

  1. mysql>startslave;
  2. mysql>showslavestatus＼G
  3. Slave_IO_Running:Yes
  4. Slave_SQL_Running:Yes
  5. Seconds_Behind_Master:0

八、结果测试

  1. mysql>usehitest;
  2. mysql>select*fromtest;
  3. +----+--------+
  4. |id|name|
  5. +----+--------+
  6. |1|zy|
  7. |2|binghe|
  8. +----+--------+
  9. 2rowsinset(0.00sec)
  10. [root@Master-Mysql~]#/usr/local/mysql/bin/mysql-uroot-p''-e"createdatabasezhihu;"#主库建立了一个zhihu的数据库
  11. Enterpassword:
  12. [root@Slave-Mysql~]#/usr/local/mysql/bin/mysql-uroot-p''-e"showdatabaseslike'zhihu'";
  13. Enterpassword:
  14. +------------------+
  15. |Database(zhihu)|
  16. +------------------+
  17. |zhihu|
  18. +------------------+

配置Mysql数据库的主从同步(双主)

已经配置好的:

主库:192.168.199.177

从库:192.168.199.178

  1. [root@Master-Mysql~]#egrep"server-id|log-slave|log-bin|auto_increment|slave-skip-errors"/etc/my.cnf
  2. log-bin=/usr/local/mysql/data/mysql-bin#必须
  3. server-id=1#必须
  4. log-slave-updates#必须
  5. auto_increment_increment=2#必须
  6. auto_increment_offset=1#必须
  7. slave-skip-errors=1032,1062,1007#非必须，建议
  8. ########################主库、从库分隔符########################
  9. [root@Slave-Mysqldata]#egrep"server-id|log-slave|log-bin|auto_increment|slave-skip-errors|read-only"/etc/my.cnf
  10. #log-bin=/usr/local/mysql/data/mysql-bin
  11. server-id=2
  12. log-slave-updates
  13. log-bin=/usr/local/mysql/data/mysql-bin
  14. #read-only#双主，此选项要注释掉
  15. slave-skip-errors=1032,1062,1007
  16. auto_increment_increment=2#ID自增间隔
  17. auto_increment_offset=2#ID初始位置
  18. 192.168.199.178:
  19. mysql>stopslave;
  20. mysql>flushtablewithreadlock;
  21. mysql>showmasterstatus;
  22. +------------------+----------+--------------+------------------+-------------------+
  23. |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
  24. +------------------+----------+--------------+------------------+-------------------+
  25. |mysql-bin.000004|120||||
  26. +------------------+----------+--------------+------------------+-------------------+
  27. mysql>system/usr/local/mysql/bin/mysqldump-uroot-p''-A-B>/tmp/192.168.199.178.sql#如果主、从一致非必须
  28. mysql>unlocktables;#同上
  29. mysql>systemls-l/tmp/
  30. -rw-r--r--.1rootroot2887406Jul1222:24192.168.199.178.sql
  31. mysql>startslave;
  32. 192.168.199.177:
  33. [root@Master-Mysql~]#/usr/local/mysql/bin/mysql-uroot-p''</tmp/192.168.199.178.sql#如果主、从一致非必须
  34. mysql>updatemysql.usersetpassword=PASSWORD('admin')whereuser='root';
  35. [root@Master-Mysql~]#cat|/usr/local/mysql/bin/mysql-uroot-p'admin'<<EOF#必须
  36. >CHANGEMASTERTO
  37. >MASTER_HOST='192.168.199.178',
  38. >MASTER_PORT=3306,
  39. >MASTER_USER='byrd',
  40. >MASTER_PASSWORD='admin',
  41. >MASTER_LOG_FILE='mysql-bin.000004',
  42. >MASTER_LOG_POS=120;
  43. >EOF
  44. mysql>startslave;
  45. mysql>showslavestatus＼G
  46. ***************************1.row***************************
  47. Slave_IO_State:Waitingformastertosendevent
  48. Master_Host:192.168.199.178
  49. Master_User:byrd
  50. Master_Port:3306
  51. Connect_Retry:60
  52. Master_Log_File:mysql-bin.000004
  53. Read_Master_Log_Pos:938
  54. Relay_Log_File:mysqld-relay-bin.000002
  55. Relay_Log_Pos:1101
  56. Relay_Master_Log_File:mysql-bin.000004
  57. Slave_IO_Running:Yes
  58. Slave_SQL_Running:Yes
  59. Last_Errno:0
  60. Skip_Counter:0
  61. Exec_Master_Log_Pos:938
  62. Relay_Log_Space:1275
  63. Until_Condition:None
  64. Until_Log_Pos:0
  65. Master_SSL_Allowed:No
  66. Seconds_Behind_Master:0
  67. Master_SSL_Verify_Server_Cert:No
  68. Last_IO_Errno:0
  69. Last_SQL_Errno:0
  70. Master_Server_Id:2
  71. Master_UUID:34d672c3-d292-11e3-9ff5-00155dc7834c
  72. Master_Info_File:/usr/local/mysql/data/master.info
  73. SQL_Delay:0--phpfensi.com
  74. SQL_Remaining_Delay:NULL
  75. Slave_SQL_Running_State:Slavehasreadallrelaylog;waitingfortheslaveI/Othreadtoupdateit
  76. Master_Retry_Count:86400

测试:192.168.199.177:

  1. mysql>usehitest;
  2. mysql>CREATETABLE`ces`(
  3. ->`REL_ID`bigint(12)NOTNULLauto_incrementCOMMENT'id',
  4. ->`TITLE`varchar(255)NOTNULLCOMMENT'biaoti',
  5. ->PRIMARYKEY(`REL_ID`)
  6. ->)ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8;
  7. mysql>insertintoces(TITLE)values('test');
  8. mysql>insertintoces(TITLE)values('test');
  9. mysql>insertintoces(TITLE)values('test');
  10. mysql>insertintoces(TITLE)values('test25');
  11. mysql>select*fromces;
  12. +--------+-------+
  13. |REL_ID|TITLE|
  14. +--------+-------+
  15. |1|test|
  16. |3|test|
  17. |5|test|
  18. |25|test25|
  19. +--------+--------+
  20. 3rowsinset(0.03sec)
  21. 192.168.199.178:
  22. mysql>usehitest;
  23. mysql>insertintoces(TITLE)values('test26');
  24. mysql>insertintoces(TITLE)values('test28');
  25. mysql>insertintoces(TITLE)values('test30');
  26. mysql>select*fromces;
  27. +--------+--------+
  28. |REL_ID|TITLE|
  29. +--------+--------+
  30. |1|test|
  31. |3|test|
  32. |5|test|
  33. |26|test26|
  34. |28|test28|
  35. |30|test30|
  36. +--------+--------+
  37. 17rowsinset(0.00sec)

说明:如果一主、一丛已经做好,只要知道从库位置点(show master status;)、然后之前主库执行(CHANGE MASTER)、之前主库开启slave(start slave)即可,其中数据库备份等步骤可以省略,如果主从有一些数据库不一致则同上操作.

广告内容

配置Mysql数据库的主从同步教程 配置Mysql数据库的主从同步教程 配置Mysql数据库的主从同步教程

相关阅读

热门评论

小浪SEO博客 小浪SEO博客

专注SEO优化思维、SEO技术的实战分享类博客~

总篇数183

精选文章

RMAN中catalog和nocatalog区别介绍 小技巧:为Linux下的文件分配多个权限 zimbra8.5.1安装第三方签名ssl证书的步骤 解决mysql不能远程连接数据库方法 windows服务器mysql增量备份批处理数据库 mysql中slow query log慢日志查询分析 JavaScript跨域问题总结 Linux下负载均衡软件LVS配置(VS/DR)教程 mysql中权限参数说明 MYSQL(错误1053)无法正常启动

SEO最新算法