一.主从复制的工作过程：
二.MySQL复制类型
基于SQL语句的复制
基于行的复制
混合复制
三.实验环境
OS：CentOS 6.5 x64
master：192.168.0.134
slave：192.168.0.135
三.配置主从复制
1.配置时间同步
master：配置为时间服务器
[root@master~]#yuminstallntp-y编辑/etc/ntp.conf添加如下两行：server127.127.1.0fudge127.127.1.0stratm8servicentpdstart
slave：同步master时间
[root@slave~]#yuminstallntpdate-y[root@slave~]#ntpdate192.168.0.1346May06:37:58ntpdate[6653]:adjusttimeserver192.168.0.134offset-0.469705sec
2.安装MySQL
slave 和 master：
[root@master~]#yuminstallmysql-servermysql-y[root@master~]#/etc/rc.d/init.d/mysqldstart[root@master~]#chkconfigmysqldon[root@master~]#mysqladmin-urootpassword""
3.编辑配置文件
master：
编辑/etc/my.conf添加如下几行server-id=134#设置id，主从不同log-bin=master-bin#开启二进制日志log-slave-update=true重启MySQL服务[root@smaster~]#servicemysqldrestart
slave：
编辑/etc/my.conf添加如下几行server-id=135relay-log=relay-log-binrelay-log-index=slave-relay-bin.indexread-only=1#这里可以设置mysql为仅读，不对root生效重启MySQL服务[root@slave~]#servicemysqldrestart
3.登录mysql，给slave授权
master：
mysql>grantreplicationslaveon*.*to'slave'@'192.168.0.%'identifiedby'123456';QueryOK,0rowsaffected(0.00sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)mysql>showmasterstatus;+-------------------+----------+--------------+------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|+-------------------+----------+--------------+------------------+|master-bin.000001|181|||+-------------------+----------+--------------+------------------+1rowinset(0.00sec)
# File：日志名 Position：偏移量
4.登录MySQL，配置同步
slave：
mysql>changemastertomaster_host='192.168.0.134',master_user='slave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=181;QueryOK,0rowsaffected(0.12sec)mysql>startslave;QueryOK,0rowsaffected(0.00sec)mysql>showslavestatus\G***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:192.168.0.134Master_User:slaveMaster_Port:3306Connect_Retry:60Master_Log_File:master-bin.000001Read_Master_Log_Pos:181Relay_Log_File:relay-log-bin.000002Relay_Log_Pos:252Relay_Master_Log_File:master-bin.000001Slave_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:181Relay_Log_Space:405Until_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:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:1rowinset(0.00sec)
#查看同步状态Slave_IO和Slave_SQL是YES说明主从同步成功。
四.测试
1.在master上面新建一个数据库
mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||mysql||test|+--------------------+3rowsinset(0.00sec)mysql>createdatabaseguoxhcharset'utf8';QueryOK,1rowaffected(0.00sec)mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||guoxh||mysql||test|+--------------------+4rowsinset(0.00sec)
2.在slave上面查看数据库
mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||guoxh||mysql||test|+--------------------+4rowsinset(0.00sec)
#在slave上面可以看到刚才新建的数据库，则说明主从复制配置成功。
