MySQL主从复制 读写分离配置

MySQL主从配置

配置准备将要配置的数据库进行主从同步，主从的服务器上都要有同一个数据库

一、配置mysql主服务器

[root@master~]#vim/etc/f....................................log-bin=mysql-bin<==打开日志格式....................................server-id=1<==主从标记...............................可选添加配置，筛选库binlog-do-db=db1,db2#需要同步的库binlog-ignore-db=db1,db2#忽略不同步的库.............................[root@masterbackup]#mysql-uroot-pEnterpassword:mandsendwith;or\g.YourMySQLconnectionidis1Serverversion:5.5.42-logMySQLCommunityServer(GPL)Copyright(c)2000,,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>grantreplicationslaveon*.*to'repl'@'192.168.1.13'identifiedby'123';QueryOK,0rowsaffected(0.00sec)mysql>mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)mysql>flushtableswithreadlock;QueryOK,0rowsaffected(0.00sec)mysql>showmasterstatus;+------------------+----------+--------------+------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|+------------------+----------+--------------+------------------+|mysql-bin.000014|332|||+------------------+----------+--------------+------------------+

二、配置mysql从服务器

#修改从mysql的配置文件[root@slave~]#vim/etc/f...............................server-id=2修改id号，用来区分主从服务器标识...............................[root@slavetmp]#mysql-uroot-pEnterpassword:mandsendwith;or\g.YourMySQLconnectionidis8Serverversion:5.5.43-logMySQLCommunityServer(GPL)Copyright(c)2000,,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>slavestop;QueryOK,0rowsaffected,1warning(0.00sec)mysql>changemastertomaster_host='192.168.1.11',master_port=3306,master_user='repl',master_password='123',master_log_file='mysql-bin.000014',master_log_pos=332;QueryOK,0rowsaffected(0.08sec)mysql>slavestart;QueryOK,0rowsaffected(0.01sec)mysql>showslavestatus\G;***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:192.168.1.11Master_User:replMaster_Port:3306Connect_Retry:60Master_Log_File:mysql-bin.000014Read_Master_Log_Pos:332Relay_Log_File:slave-relay-bin.000003Relay_Log_Pos:253Relay_Master_Log_File:mysql-bin.000014#这部分提示为，YESSlave_IO_Running:YesSlave_SQL_Running:YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_Error:Skip_Counter:0Exec_Master_Log_Pos:332Relay_Log_Space:409Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert:No#这部分没有错误提示，OKLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:11rowinset(0.00sec)ERROR:Noqueryspecified

接下来就可以测试主从服务器了

如果觉得《MySQL主从复制 读写分离配置》对你有帮助，请点赞、收藏，并留下你的观点哦！