Mysql 主从复制

GA666666 2022-11-28 AM 36455℃ 1条

准备两台mysql服务器

这里用docker 演示

1.Docker部署mysql

docker run -p 3307:3306 --name mysql-master  -v /Users/ga666666/Desktop/master/data:/var/lib/mysql -v /Users/ga666666/Desktop/master/conf:/etc/mysql 
-e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7


docker run -p 3308:3306 --name mysql-salve  -v /Users/ga666666/Desktop/salve/data:/var/lib/mysql -v /Users/ga666666/Desktop/salve/conf:/etc/mysql 
-e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

2.修改Master配置文件my.cnf

[client]
default_character_set=utf8
[mysqld]
collation_server = utf8_general_ci
character_set_server = utf8
## 设置server_id，同一局域网中需要唯一
server_id=101 
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql  
## 开启二进制日志功能
log-bin=mall-mysql-bin  
## 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M  
## 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed  
## 二进制日志过期清理时间。默认值为0，表示不自动清理。
expire_logs_days=7  
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

3.修改Master & Slave配置文件auto.cnf

默认目录在 /var/lib/mysql

# Master
[auto]
server-uuid=49932dc6-6ebf-11ed-aa9a-0242ac110003


#Salve
[auto]
server-uuid=159d0c6b-2d39-11ed-ad53-000c29c9f5d4

#保证master和salve的uuid不相同

4.Master运行SQL

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';

FLUSH PRIVILEGES;

SHOW MASTER STATUS;

这两个参数下面会用到

5.Salve运行SQL

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

CHANGE MASTER TO MASTER_HOST='172.17.0.3',
MASTER_USER='slave',MASTER_PASSWORD='123456', MASTER_PORT=3306,
MASTER_LOG_FILE='mall-mysql-bin.000001',MASTER_LOG_POS=559;

start slave;

show slave status;

MASTER_HOST改成master所在的ip

如果没有启动，下面有报错原因，大多为UUID冲突

标签: none

非特殊说明，本博所有文章均为博主原创。

如若转载，请注明出处：https://ga666666.cn/index.php/archives/151/

上一篇 docker-compose容器编排

下一篇 SpringCloud存储统一请求日志

评论啦~

唉呀 ~ 仅有一条评论

GA666666 博主

{!{data:image/webp;base64,UklGRjoWAABXRUJQVlA4WAoAAAAwAAAADwQAKwEASUNDUBgCAAAAAAIYAAAAAAQwAABtbnRyUkdCIFhZWiAAAAAAAAAAAAAAAABhY3NwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAQAA9tYAAQAAAADTLQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAlkZXNjAAAA8AAAAHRyWFlaAAABZAAAABRnWFlaAAABeAAAABRiWFlaAAABjAAAABRyVFJDAAABoAAAAChnVFJDAAABoAAAAChiVFJDAAABoAAAACh3dHB0AAAByAAAABRjcHJ0AAAB3AAAADxtbHVjAAAAAAAAAAEAAAAMZW5VUwAAAFgAAAAcAHMAUgBHAEIAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAFhZWiAAAAAAAABvogAAOPUAAAOQWFlaIAAAAAAAAGKZAAC3hQAAGNpYWVogAAAAAAAAJKAAAA+EAAC2z3BhcmEAAAAAAAQAAAACZmYAAPKnAAANWQAAE9AAAApbAAAAAAAAAABYWVogAAAAAAAA9tYAAQAAAADTLW1sdWMAAAAAAAAAAQAAAAxlblVTAAAAIAAAABwARwBvAG8AZwBsAGUAIABJAG4AYwAuACAAMgAwADEANkFMUEiYEQAAAbBA/58hO9Y8xba5erFt2zaXSY5t21jbiG2sort3bcc27832mfx/XV31/9e9eafuvtMRAVGSpLrNSCnnITsHKjDc7foDIqH/R+g/of+E/hP6T+g/of+E/hP6T+g/of+E/hP6T+g/of+E/hP6T+g/of/8/wCh/5SoVevvBTQ55l2vnNDoPbhHUiZ7PM87mLB+8/pRz/N+faFPkiZehYRDy289//FnUuU3Y4lNCYWnvvSUx1+Lk2AoMHlebGxvyykJc0LBhMGLXtBxNemFwadhvL5oeePuEn3NGbFnhDyFYf6hi/YcxQCEab5uxeQCrp2TOFxvpbebr4k3jTnsed5hcXYi+5+IRCIDL3ued0CUNLupDccHu1UseiNvajfjiBPGwDJCPGEKsf/kE1n0uupd6SVJqzi8I7FunX1+ZtlNBWhaDftpDKH/ooijyOQiko14L3pH3Czib+SKhN/3lrcPkgjTrSff70BFS3qqA90Q/MG9/w4zfmk42Hzj+gwpnie+tZ69EPkeS+5ImSvA3VU2XVKrfxP3yadAHaA6AzdBWiREdxjULFjwv3ZackPSH4BGfJ3Jpns8zPO81m4M0a257438Z2Kgufj4nGjhMD+EXMVyrsIayxZ1xTrohA123eTvPc/7Ob3bJOVN3z9ZTrxkfNm9fbzoTLER/mA3VSHuVlalc45ksAusHrlNHvOn/foRvxn/VppHGCoEucSZRey2JZ2Dksrylt3lHVA9clbY9O77cSQSqQjjLjVDzU92pviMYjYb2XOJc5KAX6KIJeCk28W2uzyG+MJpUgJG20ORSCS1Z2p8fNd3Lcidq0oxkpitDbUdNk2rBClFYR/ivCFmMyLWR6y7zXWhbuM0+YhYDyH8SfQx0AvfEwiCM3I1LLVFZ71vjAVZ11IK8hIxxg5ae7Bes+82p70Js7iDwqIyPAy3zsArxAJtusWhHqh3pAjxpWxNtaQ+OaFtIyW78TJR1o6LYL64U9HGKpLD+oNNpbzbKYAhxFbtZyjzFYcogFKi3on3DdMaN7NKP/uyHfdjvwcrHAtvtG8Wd4DorzY85dxTjzhmYLSCVQvk4qAgKkU5mGm0k6WPQ9teE3/288yYTbub7bvVqlncVZI8hrhYWF1jzKPpfsbXxx1EWXj3l91J9NWktIfv2YnBszPfgv9l4b1WzOJ7XSWjYeSP9ofxB6En63/Hpxf2zyUgb5TiLWK7JqhKbhFJDJ49FK1Y/mxKnWdeH1HICpSzuPtDd/dxkYhJ7mc9MUrLP3CD+EERxwvgjxRrq0E1dRtE9IxoP66yJ9fwBbEfrB5rYo5Ht2igENvYgxmDXC2YRxwlW2GAdQva9r+hQ1N0LqvkjYwfRKA/3SzXoT407g+WbjSoxN605tk9oQuyRirMOuupj5Pr61jh1YQWuDyMREd3iHWqQHoEKaWD5qdfguWNYgLiNsRZHbYDj4hSHTRG9khFP1vzjRd0wCzNjvak/pGbpC+MrFrKooP2QP4O1v8a8kax5p7QlFsNgDYflO3HgcS3NlDNMzo6SOOzp7znAtFeCexTBpLqLyKnhvMSBmBdDXmjGFP0BId5ITtwIu89JEWRsAMSZ8mZy3FqwLZ94Jujfq6UEWc8sdhJUgHGVWF1KLBNL6PhvAStQlodeaOcU0OrGnkQghkg3JEniX7S/KS8Fl5m7fTqOSigBydsuQiRHBD/1IQVkCx2kPg+Z2FbkIkCPhVAcxu/QUfe+KZYg9cSqwPo4EH504rPNKgpHXvAb06NKhEQ1CYPEj3SgO2kpzNEYxHSJkgOCO/sBs7LwAzHJTkxJmiz86hz/VeA+lZQzRpufblWI7eS4YiY11JjSdfQRdIHEgVBsSwmxgeyEZyXgcHchT0OFxp5kJugm1DqKg5YIks+DVVbW374KZleVOWvepDZkeUCbPFcJB8TU4IYRawglJPELXBeBvKz7DriXeK8yi71QfDvRCWXM+QvgUkyfrBuVF0wmSEL5IpzOB+s9PfBiCoRKACCjEIQg+DqFEjQ1PG4GP8BwcU8RV4EuuBIBhF0kimr5KPfqek2V/WRQIcHTusU918u8nEDsTYwmIbEp1oWqjXBYc8k5so1egRxLR0y2QPTUnkLurI98acM6lrvddPsRyz0UmlBCoFzzTlhh3NQb5NfkjgdQHMYd7mD6Q1rAEFFJjix5mDdDxMFXUT6MrjX0BcqTWOK/c5/DGiBei3p6vJXrgk7nIOw+0gZSA5YROtc/bpG3DXROiH8EJcsEkmzN/qls/ARy4/C3DqTF21N+FajwJ7xhIWNTaAy4pqwwzkIGQrdJFtWJQX9WupgHSNoBAXJeAqEE1HNtK+MsMjXmeKSAqi+ZhJmr4E3U+FGhzKojBoKjJrEB64Ji5yDGpwPliy+qf82CjuN/JI5R9A+3PV9TbalN5PBw3xAOMVaWwuVqxS6apAYQ8BM7u4wTM8/74sn2DBZQ8kVfe3xEfBSCJL9ttqsaNHuC2a3FKLUgwxrSsPYPiQOizEdNB+OCXucg1qOoK1EWxV10DKhxdvEY5JNf1nB3cEW9We6c9SQvaJMJTaZxpYbuqu5EeZpjyHOkff19f+vB1b1lyr8ERovBdIF0aTjNT+ZrOrQMfJeaPishQnq4PTvzkY29AQj3V0juWC0ttBiQeAw/BMqh5ofHwCJUNHGj/Sj1aOS259b6IUWXNhATzQwphZEV1Byh9jEARJklwK5k15Eo4mlCjqD9VIz9rrEMVGy3vJhWZe+Ju2FbgSkY5hMJEuzXxEVHCN5IIf4pMFq423VfiZQoRRQAIUnijb/hXvYOCg3Q1vel/3LNwHhGdyeGzyTnW7lqJCrQi61DslJTZtCK2KHwlccR2ol/9V6zsoeUSS1D9GSohX+KX0vtJhpAv7CQFpoW2MWdJUl6R0UshsEeGt816Tg8bmfJymKF/QdyRiU8AM6JiS7VdcLjZOEqGmCbbcxTYTCLtJGNQ+dAboaH1AW+vmIaKa/pIePJ4jIPsj/wcwL/Xs+SdMEm4xrlwj1iRNuCsHUPRQYR+gGlQXeNtWpiV9MtRNlZbvgpDQmXmioQwqaJlgCrAFaaRHB1eMOUiY9h0bC5dTaUaknhrWmXmrYD4ySNjoAFnbtDrxGzjTBQvKbUA+VYC5YvRwR1ngL4vGRg/wQQ3VTRc9m6V6wDv+C6ii6McRMEzwhbhL7wyqit1NkFw2EUxlZ6AEiZpMWPEFcTwTQLM/gBmiZlGmCh/GQG5Eglqjp1GCS9I0znRiUZYfvTCgOMdazASu7d030BLNTJhHTBFc+krfHgn1peV0i6yBzn90UZSFzqrnHYLYN2Nm/i/CMH83Lz6B6YKIkLNpY8e39IGnkEr+6wehczzMxlPXMNQAr4UVtwNIenuwrok7k7QXO5GQDBpm4ibr8iCuDJx0fQ5wvzMMo0CgZCgzgvdkGrDWH+M6snLCYJgIkYu8J8Bjo0B0dmjYpGGmjzScGNCAwVOjzeeC6tABrO7l9PPChDb2gb0CdLqFBc1mvJSPSvUTEJGcCJMNPGLbjDFHRAuzt5jSfQrMKW4ZaE9JXSujRz91gq2CwG8NQhJofRFCco2ba1naEhdhg5d5vJ38SdQT4jajr4jCyRq/jGoqzWRzHrxCDLMDmnn4N2vWmFRR55ZVGasMV2CMLGqFtRoVwnSF9gVpc/ECsNGUaCKktwOqu/tEjp3hWC+h11fPin1VRRqrDcjpGV+WbxB8sQ5E+WQDeeLsQRspIeNdP3KgIDdthAetxPgsq6fzJRuD0ddodcgIkkVzUhgCMvcotiH12YaOlCqq1NoAlkiAF5TsCNCU+cmPweINhLFXgYiR4rI1bUo44bgc2PwVaZsMPN9CZpRBVgIJSgH6glnCGTCeO8gxFGmg8KYlcsA1PCFjQMhtirBwHQU3y8SXRJgBG99ViBwZrae55Nn4lWjO4gqDad79B/wMTUGX5tNKQXVSAFcQoBwarE7A7F4XgH+Rnyowuvd9g4BLxWTXBRQVutP9IyaB6ukK2M00t8aAkjObtuSxSeSC9L4+wb7xWCeswd2bNj0QinYjPJHoJhJoNHSEDYBDtZdjMoLVwCfEaR2u2UXydEwba6YTr3rEmZlQgzjH3gvk52PfMNyQ4TZR0g+SFIXA2ryH+/OWTka9hwDGA65f3JZ0N8hz0PG+PWcNiwA1mzaz4CZ4dIh/AMyUAMUkON8gWGEEDuIofC3BE1jCG5FJxpA/KIEd1FkxXHaYMjOYbYSXAi1kvHwCuVz9KdAyGY1vnEGWRKaFMt51NS722KnEjDUuDXoB1jClG8wTxqyTvEGP1afK9h4dFu6aHf4LQfoTnlZJSaZ50gzwGI6c+B3UgMzmLeJmnQc2ElX4vEcsluWi06mgXdZH6juHWZFFILhp1n/uPW6kEaEvscogO65083ktYSsNfijC16BelU1DCdgwfQyCKgdJz7AH/fBE/nTurapyoUbBFopsGEa84QUrAyHmIZywqxtmhwCaZ6YOelb2yoiQv6+6+/jvjNHQfTL6ZbKuyDCegGKyG9R5Nc0iI7+/XM4X4h58xrKnIy7JXppYC9Ejgkgp0SgyE1/aplh7jbQZTkmMh8rNWNzHJxp5wglzmlCe85aesr01MWukqVl0pPHOM2nRV9d2aetyt4Ep6RndA6KmSYB/Rwh0hXg68mYKJcfdQLTX4njxd9Eqb+OeAaAFSdXxRTagVDOnJPp7vlJJBlXOAFPiKeIsryPb3gDD5eISIFeIoXGkB/uTMkguq31xaL2JX4lqPfez5T/40Dcigcrk/Bp9CMRBjKgCTnozt+ozCLC8zi+CVspzzPTFZxeWfKq/f0D294LzYzZgi0eYpThnVlwN8jMV9B59i6pYHSU/Odo0kZsuAV4qCguXjh2NvK39zakwhnVZw5mxmEOZi6e98rmvuNA2Y0xwggUOGwzkB3ij2Iuo3Mpi9T/OtooKPbaM1W8GZs1lFmJmpYPEy2ee6lkjTuEDgQwIymhBYJwRS8LqH5dyCVqzbgzgzUXt7xZazAdu48RfkhFMqXNes/AppGlfIXPbZQeDt/M2EynklJ7r9MyK+yjFONNLsA4sLDAHMp4ykN03T5KwzfdXSySv2LJq8Ys+2PcNdFj+ReYc3Tv7ddTnidh7LXNaMVda/fC6GL9cOM7lDjDkbmLINdZggdQ5wXfPRFNM0qjlnMsw6dLx06OPjiv2Wbwfkss4lv81jj3OJiMxq5j0SZJ6/+Z4Vy9eNLpPMkju0CjYhQZlVparihK+cEei6ZmMW5HcenLn38+icY3pg/cPlHs+D0iCTlCzSqg604eEadUVMSjUqVYW/uhnouv7hwQin4wylGc6bZLcVKb1EKs/PWrNFC1mlYK3Xs4dVYgcN1/XHabhSrqaHxg9O9xgKpZMU/LLIxCjPnzUOJoG7CwnfdqTdMz8q54f3tFzXn1rC0Y8OvKq8xO2S7hwYnoVkkYlND07wTQrHnsgO+caXY66p54rPewe6rlkFksFzjv8S+gfrxlfPEXHKxxgYXfVlZJGJTg9Oio9OFXjBN9F8o4aqQsd1zXlSBYZKRvsW3ZtznPeRPU7kISGl65irth/pbCNAjR1VVRi5riextOjC3fi9Q6Mabkd+rIPROCAy4WVOB0Wila9LsTUALGbun25Q1PGfSQxH3pMeya3Ig7yFjwRUzxMowB6JV/P1nMYGlQuVQHJBJFJgcoEkhHOAatzykajV93OPP4n5xpg9ywpDkGanz9w9+Lh3fHASwrldgFOJnN6n11xFvpHtpKkrNgmB+gTIChk9D8RviV2fs53cckZn/hQfGTk9D78en3e/6PM1SRJkvI02gvt0py/yk6TwgvNI6fOdSKffX/6YtPCvWq2JrX8f7/Qd/yOQtLBNSzKHsUyD/J3gAfk7wXHJ87yLSRLcz59ycl8k9P8I/Sf0n9B/Qv8J/Sf0n9B/Qv8J/Sf0n9B/Qv8J/Sf0n9B/Qv8J/Sf0n9B/Qv8J/Sf0n9B/Qv8J/Sf0nxAsQv+JAFZQOCBcAgAAsEUAnQEqEAQsAT9xuNlltK8rpyAIApAuCWlu4XdhG0AJ7APfbJyHvtk5D32ych77ZOQ99snIe+2TkPfbJyHvtk5D32ych77ZOQ99snIe+2TkPfbJyHvtk5D32ych77ZOQ99snIe+2TkPfbJyHvtk5D32ych77ZOQ99snIe+2TkPfbJyHvtk5D32ych77ZOQ99snIe+2TkPfbJyHvtk5D32ych77ZOQ99snIe+2TkPfbJyHvtk5D32ych77ZOQ99snIe+2TkPfbJyHvtk5D32ych77ZOQ99snIe+2TkPfbJyHvtk5D32ych77ZOQ99snIe+2TkPfbJyHvtk5D32ych77ZOQ99snIe+2TkPfbJyHvtk5D32ych77ZOQ99snIe+2TkPfbJyHvtk5D32ych77ZOQ99snIe+2TkPfbJyHvtk5D32ych77ZOQ99snIe+2TkPfbJyHvtk5D32ych77ZOQ99snIe+2TkPfbJyHvtk5D32ych77ZOQ99snIe+2TkPfbJyHvtk5D32ych77ZOQ99snIe+2TkPfbJyHvtk5D32ych77ZOQ99snIe+2TkPfbJyHvtk5D32ych77ZOQ99snIe+2TkPfbJyHvtk5D32ych77ZOQ99snIe+2TkPfbJyHvtk5D32ych77ZOQ99snIe+2TkPfbJyHvtk5D32ych77ZOQ99snIe+2TkPfbJyHvtk5D32ych77ZOQ99snIe+2TkPfbJyHvtk5D32ych77ZOQ99scAAA/v+tHgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA==}!}
回复 2022-11-28 14:55

Mysql 主从复制

1.Docker部署mysql

2.修改Master配置文件my.cnf

3.修改Master & Slave配置文件auto.cnf

4.Master运行SQL

5.Salve运行SQL

 评论啦~

唉呀 ~ 仅有一条评论

评论啦~