##将server51和server52搭建成MySQL主从结构

#分析：

server51(主服务器)

1）开启binlog日志

2）授权主从同步用户

3）备份已有数据

server52(从服务器)

1）设置serverid，可不开启binlog日志

2）还原数据(实现主从结构前保证服务器基础数据统一)

3）搭建主从关系

##server51操作

[root@server51~]#sed-rn'4,6p'/etc/my.cnf#确保启用binlog日志

[mysqld]

log_bin=/mylog/db1

server_id=51

[root@server51~]#ls/mylog/#查看binlog日志文件

[root@server51~]#mysql-hlocalhost-uroot-p'123qqq...A'#登录MySQL服务

mysql>GRANTREPLICATIONSLAVEON*.*TO

->repluser@'%'IDENTIFIEDBY'123qqq...A';#授权主从同步用户

mysql>SHOWGRANTSFORrepluser@'%';#确认用户权限

mysql>SHOWMASTERSTATUS;#查看活跃binlog日志信息

+------------+----------+--------------+------------------+-------------------+

|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|

+------------+----------+--------------+------------------+-------------------+

|db1.000003|28900637||||

+------------+----------+--------------+------------------+-------------------+

1rowinset(0.01sec)

[root@server51~]#

[root@server51~]#mysqldump-hlocalhost-uroot-p'123qqq...A'-A>ab1.sql#备份已有数据

[root@server51~]#scpab1.sql192.168.88.52:/root#同步备份文件

##server52操作

[root@server52~]#vim/etc/my.cnf#修改MySQL主配置文件

[root@server52~]#sed-rn'4,5p'/etc/my.cnf

[mysqld]

server_id=52#设置serverid

[root@server52~]#systemctlrestartmysqld#重启服务使配置生效

[root@server52~]#lsab1.sql#确认主服务器备份数据同步成功

[root@server52~]#mysql-hlocalhost-uroot-p'123qqq...A'

[root@server52~]#mysql-hlocalhost-uroot-p'123qqq...A'#登录MySQL服务

mysql>CHANGEMASTERTO#修改主服务为

->MASTER_HOST="192.168.88.51",#主服务器地址

->MASTER_USER="repluser",#连接主服务器用户

->MASTER_PASSWORD="123qqq...A",#连接主服务器用户密码

->MASTER_LOG_FILE="db1.000003",#主服务器正在使用的binlog日志

->MASTER_LOG_POS=28900637;#从binlog日志什么位置开始同步

QueryOK,0rowsaffected,2warnings(0.00sec)

mysql>STARTSLAVE;#启动IO/SQL线程

QueryOK,0rowsaffected(0.00sec)

mysql>SHOWSLAVESTATUS\G#查看主从同步状态

***************************1.row***************************

Slave_IO_State:Waitingformastertosendevent

Master_Host:192.168.88.51

Master_User:repluser

Master_Port:3306

Connect_Retry:60

Master_Log_File:db1.000003

Read_Master_Log_Pos:28900637

Relay_Log_File:server52-relay-bin.000002

Relay_Log_Pos:314

Relay_Master_Log_File:db1.000003

Slave_IO_Running:Yes#IO线程正常工作

Slave_SQL_Running:Yes#SQL线程正常工作

...

Last_IO_Errno:0

Last_IO_Error:

Last_SQL_Errno:0

Last_SQL_Error:

mysql>exit

Bye

[root@server52~]#ls/var/lib/mysql/master.info

##测试主从同步

#server51执行写操作

[root@server51~]#mysql-hlocalhost-uroot-p'123qqq...A'-e"CREATEDATABASEmsdb";

[root@server51~]#mysql-hlocalhost-uroot-p'123qqq...A'-e"CREATEtablemsdb.user(nameCHAR(20));"

[root@server51~]#mysql-hlocalhost-uroot-p'123qqq...A'-e"INSERTINTOmsdb.userVALUES('zhangsan');"

[root@server51~]#mysql-hlocalhost-uroot-p'123qqq...A'-e"SELECT*FROMmsdb.user;"

+----------+

|name|

+----------+

|zhangsan|

+----------+

#server52自动同步

[root@server52~]#mysql-hlocalhost-uroot-p'123qqq...A'\

>-e"SELECT*FROMmsdb.user;"#操作server51的过程中server52自动同步server51的新增数据

+----------+

|name|

+----------+

|zhangsan|