1、实验环境准备：
A、MysqL主服务器 名称：abao68 IP地址：192.168.1.68 数据库版本为：MysqL5.1.72
[root@centos67-68 ~]# MysqL -V
MysqL Ver 14.14 distrib 5.1.73,for redhat-linux-gnu (x86_64) using readline 5.1
[root@centos67-68 ~]# uname -a
Linux centos67-68 2.6.32-642.15.1.el6.x86_64 #1 SMP Fri Feb 24 14:31:22 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
B、MysqL从服务器 名称：abao67 IP地址：192.168.1.67 数据库版本为：MysqL5.5.32
[root@abao67 ~]# MysqL -V
MysqL Ver 14.14 distrib 5.5.32,for Linux (x86_64) using readline 5.1
[root@abao67 ~]# uname -a
Linux abao67 2.6.32-573.el6.x86_64 #1 SMP Thu Jul 23 15:44:03 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
2、试验数据准备：
2.1、在主服务器abao68创建数据库名称为abaotest数据库，并导入测试数据。
[root@centos67-68~]#MysqL-V MysqLVer14.14distrib5.1.73,forredhat-linux-gnu(x86_64)usingreadline5.1 [root@centos67-68~]#MysqL WelcometotheMysqLmonitor.Commandsendwith;or\g. YourMysqLconnectionidis2 Serverversion:5.1.73Sourcedistribution copyright(c)2000,2013,Oracleand/oritsaffiliates.Allrightsreserved. OracleisaregisteredTrademarkofOracleCorporationand/orits affiliates.OthernamesmaybeTrademarksoftheirrespective owners. Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement. MysqL>showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |MysqL| |test| +--------------------+ 3rowsinset(0.00sec) MysqL>createdatabaseabaotest; QueryOK,1rowaffected(0.00sec) MysqL>showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |abaotest| |MysqL| |test| +--------------------+ 4rowsinset(0.00sec)
2.2、把测试数据导入刚才我们新建的abaotest数据库：
方法一：
[root@centos67-68~]#MysqL-uroot-pabaotest<book_utf8.sql Enterpassword:
方法二：
MysqL>userabaotest; ERROR1064(42000):YouhaveanerrorinyoursqlSyntax;checkthemanualthatcorrespondstoyourMysqLserverversionfortherightSyntaxtousenear'userabaotest'atline1 MysqL>useabaotest Readingtableinformationforcompletionoftableandcolumnnames Youcanturnoffthisfeaturetogetaquickerstartupwith-A Databasechanged MysqL>source/root/book_utf8.sql
查看刚导入的数据表情况：
MysqL>showtables; +--------------------+ |Tables_in_abaotest| +--------------------+ |books| |category| +--------------------+ 2rowsinset(0.00sec) MysqL>select*fromcategory; +---------+---------------+ |bTypeId|bTypeName| +---------+---------------+ |1|windows应用| |2|网站| |3|3D动画| |4|linux学习| |5|Delphi学习| |6|黑客| |7|网络技术| |8|安全| |9|平面| |10|AutoCAD技术| +---------+---------------+ 10rowsinset(0.00sec)
知识扩展：在centos6.7下使用yum安装数据库之后需要改变MysqL数据库的默认编码
查看数据库默认编码 MysqL>status; -------------- MysqLVer14.14distrib5.1.73,forredhat-linux-gnu(x86_64)usingreadline5.1 Connectionid: 2 Currentdatabase: abaotest Currentuser: root@localhost SSL: notinuse Currentpager: stdout Usingoutfile: '' Usingdelimiter: ; Serverversion: 5.1.73Sourcedistribution Protocolversion: 10 Connection: LocalhostviaUNIXsocket Servercharacterset: latin1 Dbcharacterset: utf8 Clientcharacterset: latin1 Conn.characterset: latin1 UNIXsocket: /var/lib/MysqL/MysqL.sock Uptime: 6min33sec Threads:1Questions:61Slowqueries:0Opens:22Flushtables:1Opentables:9Queriespersecondavg:0.155 安装MysqL数据库时的默认编码是latin1，实际使用时可能要使用其他编码。下文描述如何将默认编码改为utf8： 首先修改MysqL的配置文件/etc/my.cnf： 在[client]下追加： default-character-set=utf8 在[MysqLd]下追加： character-set-server=utf8 在[MysqL]下追加： default-character-set=utf8 修改完毕后，使用如下命令之一重启MysqL服务： serviceMysqLrestart /etc/init.d/MysqLrestart 改变后，之前手工创建的数据库如未显式指定编码，则其编码仍是默认的latin1，可使用如下命令更改编码： MysqL>alterdatabasedb_nameCHaraCTERSETutf8;
2.3、停止主服务器 并 配置
[root@centos67-68 ~]# service MysqLd stop
停止 MysqLd： [确定]
修改my.cnf文件并在[MysqLd]选项中加入以下内容：
[root@centos67-68 ~]# vi /etc/my.cnf
[MysqLd] datadir=/var/lib/MysqL socket=/var/lib/MysqL/MysqL.sock user=MysqL #disablingsymbolic-linksisrecommendedtopreventassortedsecurityrisks character-set-server=utf8 symbolic-links=0 #---------以下是在原配置文件中添加的内容--------- #启用二进制日志，默认存在/var/lib/MysqL下面 log-bin=MysqLlog #本机数据库ID标示。其中master_id必须为1到232之间的一个正整数值 server-id=1 #可以被从服务器复制的库。二进制需要同步的数据库名abaotest binlog-do-db=abaotest #不可以被从服务器复制的库MysqL #binlog-ignore-db=MysqL #---------以上是在原配置文件中添加的内容--------- [MysqL] default-character-set=utf8 [client] default-character-set=utf8 [MysqLd_safe] log-error=/var/log/MysqLd.log pid-file=/var/run/MysqLd/MysqLd.pid
2.4、重启服务
[root@centos67-68 ~]# service MysqLd restart
正在启动 MysqLd： [确定]
2.5、授权从服务器abao67可以使用账号slave连接到主服务器abao68
MysqL>grantreplicationslaveon*.*toslave@192.168.1.67identifiedby"123456"; QueryOK,0rowsaffected(0.00sec)
2.6、查看服务器状态信息和二进制日志
[root@centos67-68~]#ls/var/lib/MysqL/ abaotestibdata1ib_logfile0ib_logfile1MysqLMysqLlog.000001MysqLlog.indexMysqL.socktest
[root@centos67-68~]#MysqL WelcometotheMysqLmonitor.Commandsendwith;or\g. YourMysqLconnectionidis3 Serverversion:5.1.73-logSourcedistribution copyright(c)2000,Oracleand/oritsaffiliates.Allrightsreserved. OracleisaregisteredTrademarkofOracleCorporationand/orits affiliates.OthernamesmaybeTrademarksoftheirrespective owners. Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement. MysqL>showmasterstatus; +-----------------+----------+--------------+------------------+ |File|Position|binlog_Do_DB|binlog_Ignore_DB| +-----------------+----------+--------------+------------------+ |MysqLlog.000001|256|abaotest|| +-----------------+----------+--------------+------------------+ 1rowinset(0.00sec) MysqL>showbinlogevents\G ***************************1.row*************************** Log_name:MysqLlog.000001 Pos:4 Event_type:Format_desc Server_id:1 End_log_pos:106 Info:Serverver:5.1.73-log,binlogver:4 ***************************2.row*************************** Log_name:MysqLlog.000001 Pos:106 Event_type:Query Server_id:1 End_log_pos:256 Info:grantreplicationslaveon*.*toslave@192.168.1.67identifiedby"123456" 2rowsinset(0.00sec)
注意：这里记住File的值：MysqLlog.000001和Position的值：256，后面会用到。
2.7、为了保证主从数据库的一致性，可以使用如下方法：
[root@centos67-68~]#MysqLdump-uroot-p-A>all.sql Enterpassword: MysqLdump:Goterror:1045:Accessdeniedforuser'root'@'localhost'(usingpassword:YES)whentryingtoconnect [root@centos67-68~]#MysqLdump-uroot-p-A>all.sql Enterpassword: --Warning:SkippingthedataoftableMysqL.event.Specifythe--eventsoptionexplicitly. [root@centos67-68~]#ls all.sqlanaconda-ks.cfgbook_utf8.sqlinstall.loginstall.log.syslog [root@centos67-68~]#rm-rfall.sql [root@centos67-68~]#MysqLdump-uroot-p-A>all.sql Enterpassword: --Warning:SkippingthedataoftableMysqL.event.Specifythe--eventsoptionexplicitly. [root@centos67-68~]#scpall.sql192.168.1.67:/root Theauthenticityofhost'192.168.1.67(192.168.1.67)'can'tbeestablished. RSAkeyfingerprintisc8:62:61:31:24:fa:77:44:da:be:26:d8:85:80:95:3a. Areyousureyouwanttocontinueconnecting(yes/no)?yest^H Warning:Permanentlyadded'192.168.1.67'(RSA)tothelistofkNownhosts. root@192.168.1.67'spassword: all.sql100%521KB521.3KB/s00:00
3、配置从服务器abao67
3.1、测试是否可以正常连接到主服务器
[root@abao67~]#MysqL-uslave-h192.168.1.68-p123456 WelcometotheMysqLmonitor.Commandsendwith;or\g. YourMysqLconnectionidis7 Serverversion:5.1.73-logSourcedistribution copyright(c)2000,Oracleand/oritsaffiliates.Allrightsreserved. OracleisaregisteredTrademarkofOracleCorporationand/orits affiliates.OthernamesmaybeTrademarksoftheirrespective owners. Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement. MysqL>showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |test| +--------------------+ 2rowsinset(0.01sec)
测试可以正常连接，但是没有发现abaotest数据库。
3.2、导入数据库，使其与主服务器一致：
[root@abao67~]#MysqL-uroot-p12345<all.sql [root@abao67~]#MysqL-uroot-p Enterpassword: WelcometotheMysqLmonitor.Commandsendwith;or\g. YourMysqLconnectionidis6 Serverversion:5.5.32-logSourcedistribution copyright(c)2000,Oracleand/oritsaffiliates.Allrightsreserved. OracleisaregisteredTrademarkofOracleCorporationand/orits affiliates.OthernamesmaybeTrademarksoftheirrespective owners. Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement. MysqL>showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |abaotest| |MysqL| |performance_schema| |test| +--------------------+ 5rowsinset(0.00sec) MysqL>useabaotest Databasechanged MysqL>select*fromcategory; +---------+---------------+ |bTypeId|bTypeName| +---------+---------------+ |1|windows应用| |2|网站| |3|3D动画| |4|linux学习| |5|Delphi学习| |6|黑客| |7|网络技术| |8|安全| |9|平面| |10|AutoCAD技术| +---------+---------------+ 10rowsinset(0.00sec)
3.3、修改从服务器abao67配置文件：
配置MysqL从服务器的my.cnf文件
vi /etc/my.cnf #编辑配置文件，在[MysqLd]部分添加下面内容
server-id=2 #配置文件中已经有一行server-id=1，修改其值为2，表示为从数据库
log-bin=MysqL-bin #启动MySQ二进制日志系统，注意：如果原来的配置文件中已经有这一行，就不用再添加了。
replicate-do-db=osyunweidb #需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行
replicate-ignore-db=MysqL #不同步MysqL系统数据库
:wq! #保存退出
注意：MysqL 5.1.7版本之后，已经不支持把master配置属性写入my.cnf配置文件中了，只需要把同步的数据库和要忽略的数据库写入即可。
3.4、保存并重启从服务器：
MysqL>slavestop; QueryOK,0rowsaffected(0.00sec) MysqL>changemastertomaster_host='192.168.1.68',master_user='slave',master_password='123456',master_log_file='MysqLlog.000001',master_log_pos=256; QueryOK,0rowsaffected(0.04sec) MysqL>startslave; QueryOK,0rowsaffected(0.00sec) MysqL>SHOWSLAVESTATUS\G ***************************1.row*************************** Slave_IO_State:Waitingformastertosendevent Master_Host:192.168.1.68 Master_User:slave Master_Port:3306 Connect_Retry:60 Master_Log_File:MysqLlog.000001 Read_Master_Log_Pos:256 Relay_Log_File:abao67-relay-bin.000002 Relay_Log_Pos:251 Relay_Master_Log_File:MysqLlog.000001 Slave_IO_Running:Yes Slave_sql_Running:Yes Replicate_Do_DB:abaotest Replicate_Ignore_DB:MysqL Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno:0 Last_Error: Skip_Counter:0 Exec_Master_Log_Pos:256 Relay_Log_Space:408 Until_Condition:None Until_Log_File: Until_Log_Pos:0 Master_SSL_Allowed:No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master:0 Master_SSL_Verify_Server_Cert:No Last_IO_Errno:0 Last_IO_Error: Last_sql_Errno:0 Last_sql_Error: Replicate_Ignore_Server_Ids: Master_Server_Id:1 1rowinset(0.00sec)
注意查看：
Slave_IO_Running: Yes
Slave_sql_Running: Yes
以上这两个参数的值为Yes，即说明配置成功！
4、测试
1、进入MysqL主服务器abao68 #进入主服务器MysqL控制台 MysqL-uroot-p #进入数据库 MysqL>useabaotest Readingtableinformationforcompletionoftableandcolumnnames Youcanturnoffthisfeaturetogetaquickerstartupwith-A Databasechanged #创建test表 MysqL>CREATETABLEtest(idintnotnullprimarykey,namechar(20)); QueryOK,0rowsaffected(0.02sec) 2、进入MysqL从服务abao67 #进入MysqL控制台 MysqL-uroot-p #进入数据库 MysqL>useabaotest #查看abaotest表结构，会看到有一个新建的表test，表示数据库同步成功 MysqL>useabaotest Databasechanged MysqL>showtables; +--------------------+ |Tables_in_abaotest| +--------------------+ |books| |category| |test| +--------------------+ 3rowsinset(0.00sec)
您若能看到如上内容，证明您成功啦！