mysql主从数据库同步详细配置测试步骤图例

1、mysql主从同步配置测试步骤图例目的：测试主从 mysql 之间的数据库同步效果测试环境：Master （主） :RHEL 5.5 x86-64Mysql.5.0.77 ip: 10.86.21.147Slave （从）：RHEL 5.5 x86-64Mysql.5.0.77 ip: 10.86.21.146测试数据库名称： leadtest测试表 :userMysql 账号 root 密码 :redhat主服务器端建立同步测试数据库一：配置 Master 的配置文件 /etc/f ，供同步使用。vi /etc/f 在原有的基础上添加如下内容：Server_id=11 代表主端 2 代表从端

2、binlog-do-db=leadtest 要同步的数据库log-bin=mysql-bin数据库二进制日志ff.ysqltljdaitadir=.rrar/Lib 'irysala-ncteE-/Tar/lLt/nysql/myaql sock* Default ed a±iaa all passvord for mat for ccwazitoility with my a al+(those uaing t-hc nyjqlslie&rlO eoBf>itibilix:y pickiags) dCldi_J»33VCrdl3»l+ Di

3、aihling jynbo lia -1 inka ! rcaixmcnccd tc prevent aagrted a c cur it/ risks;t 匸。ic 9C# uncornrenx; tmg Linie:* wbcl i c-1 Jea=0I*ad；a3Erverwid=ltin!cg-dodkleadtea匸_og-Dliirysql-tln莊垃 m qld_3 af&J_og-erroi=/var/ Log/EiyssTld legDid-fil es/var/run/mysi ld/rrysald，zidi二：重新启动 /etc/i nit.d/mysqld re

4、start三：mysql -u root -p 在主端上登陆 mysql服务器四：创建库： create database leadtest;toatlcealhi3t rysgl -u rset -pEnterWeLccnye to the HySL Tneuitoir” Ccrjiani? with ; or j, Yeur MySL connection id is 匚Server vsEaLan.：! 5.0.77 Seurc* disributioTLIype Thelp;r or 1hf for help. Type *cf to clear the 1?口已r.mysql>

5、 create然后我们要添加一个MySQL帐号为同步专用的用户，这里以root用户为例子，同步账号 必须要有对操作数据库的增删改查权限同步账号实质上是供从端使用的。GRANT ALL PRIVILEGES ON *.* TO 'root''%' IDENTIFIED BY 'redhat' WITH GRANT OPTION;这句的意思说允许root用户以redhat为密码从任意网段远程登陆10.86.21.147（主端）raQtlccaliiost 宀1弃-u root -pEnD±r password:WLccine ec Lhe

6、MvSQL mcniEar. 世口:血由rri自 end wtrh : or 甘-Yaur Uy SQL C-oeitle 匚匸:Lon id is 7Server veE3io-n: 5.o»T7 Source distrit）u.匚icnwnfi 沁Type Thelp;1 or 1iir for help. Type Tcr to clear buffer*my5q：I> GRAMI ALL PRIVILEGES OK TO 'tdOtJ秒 L IDEUTIflED By NT OP71ON;|保存退出，letc/init.d/mysqldrestart重启服务即

7、可。Slave端的配置：一：安装slave端的Mysql。安装步骤与主端一样。安装完成以后启动mysqld服务，检查是否可以正常启动 /登陆/etc/ in it.d/mysqld start二：测试是否可以登陆到本地的mysql服务器mysql -u root -p三：修改slave （从端）的配置文件/etc/f，供同步使用。Vi 尼 tc/my.c nf在原有的基础上添加如下内容：server-id=2master-host=10.86.21.147主端地址master-user=root同步专用用户（主端提供的用户）master-password=redhat 该用户的密码master

8、-port=3306 主端 mysql 端口master-connect-retry=60 断开重连次数 replicate-do-db=leadtest接受要同步的数据库Inysaidid.a tsdi r=Zva.r/lib/myaql3 nctet=/va r/1 ib/:mysql /niys ql. sockusernysqi4 Default to iaj±ng aid paaswcrd format;兰口h 匚OEpatibility with itjp3ql 9 .at* clie社上3 (those usinc the mosaiclienlC coirraxibi

9、lity packag皂).old_paaswords=1# Disabling 日書址朮丄丄住一丄mrs is re*oinmeH&ci r prevent as9or匸" security risks;> tc da 3d r iwiscjinnent tha Ixne:i S'iTfibolie»llnfs=：Ostrve：r_id=2Eisster-hcstslO* 8f, 21 147juj s ter-usiu r-roQLstst -paa aws rdr e-dha tmaster- pert3336r li c a .e-d c-db

10、-1 e adzes tmasxer-eenneet-retr/»OTyaqld_3afc1 g-error-Zva丿1旳”1片呵目，1旳pid-f i L e»/va r/run/m/sqld/mys qld. p i-d；-INSERT -保存退出，重启服务即可。/etc/init.d/mysqld restart检查数据库同步情况：一：首先登陆主端show databases查看的数据库“ leadtest "是否创建成功KeIccjtlS tp the MySQL iricnitcr.Xaur HyS口“亦口皿 ii is 4Ccirnn.nd3 end

11、with ; ar g di 3 匸 EitKTticm金Serwer version:5,0-77 SourceIyp Fhelp：' or'W foi heLp.Iyp* fc* raclear thebuffer.show dabaijaaea;d+I ?atsbase,1->X+1 infermaCion j11 bin11 leadtest11 mysql1I Qtt11 shiyang11 tea 匸11 teatdb11 testdbZ11 testdb31+二+10 rows irt aet(O-DG aecj3列冲|:在Slave端show databa

12、ses;查看是否有leadtest数据库被同步（创建）过来在主端创建测试表“ user”并设置字段命令:USE leadtest;CREATE TABLE 'user' ('id' i nt(11) NOT NULL auto_in creme nt,'n ame' varchar(20) character set utf8 NOT NULL,'sex' varchar(2) character set utf8 NOT NULL,PRIMARY KEY ('id')ENGINES nn oDB DEFAULT

13、CHARSET=lat ini;:在主端使用show tables;命令查看创建的表是否生效Database ch.an?ed mysql> CREATE TA3LE 'uaerb ->->->*id'NULL AQi:c_increir*ni:r'naoe ' varcharcharacter set ut55 M*T IjULL, '3es 1 varcliL (2) chazasLer sec utffl NOT MULL, PRIMARY KEV ri-d'-> J EKSrNE=IncDB DEFAULT

14、UElAR5E7=latiTiL;Query OK, 0 rows a-ectea (0 - 00 sec)msl>niysqi inaert into user (s_dr aame, sex) values (* 21 r 'user2 * r 'n.aT Gnery1 row 亡比亡cn巳d (0,00 ec|jrysql> shew tables?1 row in aet (0.00 sec)IG冋i|二：在从端查看是否有user表被创建（同步）useleadtest;show tables;I itya qlI皿I shiyang testI uestdb

15、I testdfci I LEStZibS10 rows in 日圮匸 <0 . 口J sec)irysql> us岸 leadt«3匸；Reading tabl informaEioa icr acinplcricn of rable and column Danes You e.an turn off this feature tc gst a. guicker startup with -A2atsrfise e?i=TL7Ed iryszjl> shew t:able3?I Tables ia_ leanest I+1 mow In. get (OQO g-

16、ec)皿冋|数据库增删改操作后的数据库同步效果验 证【查询操作】主端对user表插入数据并查询是否插入成功：insert into user(id, n ame, sex)values(2, 'user2', 'n a');select * from user;->)£NGINE=Innc3B DEFAULT CHARSET=latinl;ffuery Q rt-ws q二；0QQ sq)mysgl>insert into user (id, aar：?, aex J values (' 21 r 'ujerZ' r

17、 1 n.aT); a&ry 二比 1 raw af?ected (0, 00 secfirym辽shew tables;4" “ 一 - +I Table3_in_leadtesr I+ - - - +I userI+1 rew in. s亡匸 tD.tlO secjmy3ql> select * frem user;iMl !I id I ciaire I 吕乜 x II 2 I tiaerS I na +N一+"+1 fou In sen (.() g-ec)IE 冋 |从端查询插入的数据是否出现记录:Select * from user;【增加操作】主

18、端增加一条记录insert into user(id, n ame, sex)values(3, 'user3', 'n a');回 10-21.147 - dault-5SH Secure*丨亠| Ete Edit Yiew ndaw JelpH #Ei| 畠£ Quick Connect _| Profile_i+1 tew in set (0,00 aecmy3ql> select 1 from user;+I id I naat I sexI 2 I us*r2 I na I1 row in set (0.00 曰已u)mysql> iTisert intc U3er id, name, geji values (' 3 ' P 1 uerS 1 , r aa' );Query OKF 1 row affected (0,00 secmy3ql> select * from user;IidInasIsexI+/*I2Iuaer2|naI3Iaaer3|na|+d+42 rcK3 in set (0.00 3ec)nifsql> rConnected to 10.86*21.147tSSH2 - a&5128-<bc - hmac-md5 3