1.安装环境数据库地址：* 192.168.0.212（master）* 192.168.0.221（slave）2.Master配置1.修改MySQL配置文件[root@localhost~]#vim/etc/my.cnf[mysqld]#开启二进制日志log-bin=mysql-bin#标识唯一id（必填），一般使用ip的最后一位server-id=212#Asynchronousdatabase，可以设置多个binlog-ignore-db=mysql#指定需要同步的数据库（与slave匹配），可以设置多个binlog-do-db=xxxx_dt_businessbinlog-do-db=xxxx_dt_homebinlog_format=MIXED#日志清理时间expire_logs_days=7#日志大小max_binlog_size=200m#缓存大小binlog_cache_size=4m#最大缓存大小max_binlog_cache_size=521m2。重启MySQL[root@localhost~]#mysql-uroot-p#给从库mysql权限>GRANTFILEON_._TO'xxxxrep'@'192.168.0.221'IDENTIFIEDBY'xxxxrep123.';查询正常，0行受影响（0.00秒）mysql>GRANTREPLICATIONSLAVEON_._TO'xxxxrep'@'192.168.0.221'IDENTIFIEDBY'xxxxrep123.';查询正常，0行受影响（0.00秒）mysql>FLUSHPRIVILEGES；QueryOK,0rowsaffected(0.00sec)注意：如果数据库有数据，需要进行数据迁移，保证数据一致性 数据迁移创建数据库：在从库中创建一个与主库相同的数据库，否则两个数据库无法同步（数据迁移后跳过）CREATEDATABASExxxx_dt_homeCHARACTERSETutf8COLLATEutf8_general_ci;创建数据库xxxx_dt_business字符集utf8整理utf8_general_ci;4。重启MySQL，登录MySQL，查看master数据库信息mysql>showmasterstatus;+----------------+----------+--------------------------------+------------------+-------------------+|文件|当前位置|Binlog_Do_DB|Binlog_Ignore_DB|已执行_Gtid_Set|+--------------------+----------+------------------------------+------------------+-------------------+|mysql-bin.000149|1670048|xxxx_dt_business，xxxx_dt_home|||+----------------+----------+---------------------------------+----------------+------------------+1rowinset(0.00sec)mysql> 注意：如果这一步一直是Emptyset(0.00sec)，说明前面的my.cnf没有配置正确#打开二进制日志（不能被配置)log-bin=mysql-binserver-id=221replicate-do-db=xxxx_dt_businessreplicate-do-db=xxxx_dt_homereplicate-ignore-db=mysqllog-slave-updatesslave-skip-errors=allslave-net-timeout=60#关闭Slavemysql>changemastertomaster_host='192.168.0.212',master_user='xxxxrep',master_password='xxxxrep123.',master_log_file='mysql-bin.000149',master_log_pos=33345737;查询正常，0行受影响，2个警告（0.01秒）mysql>startslave;查询正常，0行受影响（0.00秒）注意：上面的master_log_file是配置Master时的File字段，master_log_pos是配置Master时的Position字段。一定要一一对应5.查看信息mysql>showslavestatus\G;***************************1.行**************************Slave_IO_State：等待主机发送事件Master_Host：192.168.0.212Master_User：xxxxrepMaster_Port：3306Connect_Retry：60Master_Log_File：mysql-bin.000149Read_Master_Log_Pos：44484424Relay_Log_File：mysql-relay-bin.000002Relay_101_Pos7Relay_Master_Log_File:mysql-bin.000149Slave_IO_Running:YesSlave_SQL_Running:YesReplicate_Do_DB:xxxx_dt_business,xxxx_dt_homeReplicate_Ignore_DB:mysqlReplicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_Error:Skip_Counter:0Exec_Master_Log_Pos:44484424Relay_Log_Space:11139143Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed：无Master_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:Replicate_Ignore_Server_Ids:Master_Server_Id:212Master_UUID:7e1414e2-8dd5-11e9-a10f-000c29f686d6Master_Info_File:/data/mysql/data/master.infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Slave已读取所有中继日志；等待从I/O线程更新它Master_Retry_Count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position:01rowinset(0.00sec) 出现下面的错误Last_IO_Error:Fatalerror:TheslaveI/OthreadstopsbecausemasterandslavehaveequalMySQLserverUUIDs;这些UUID必须不同才能使复制工作。 说明主服务器的UUID和从服务器的UUID重复，修改方法为[root@localhost~]#vim/usr/local/mysql/data/auto.cnf #Thisismy修改auto.cnf-uuidserver的安装路径 注意：如果Slave_IO_Running:Connecting出现如下错误 errorconnectingtomaster['root@192.168.3.28]('root@192.168.3.28):3306'-retry-time:60retries:1 解决方法是检查master库是否授权，检查是否有用户密码ip填写错误：'Couldnotfindfirstlogfilenameinbinarylogindexfile' 解决方法：resetmysql>stopslave; //stopmysql>resetslave; //clearmysql>startslave; //启用扩展只同步部分库的某张表时，如下，只同步本库的hello表和他库的biao表：replicate-do-db=homereplicate-wild-do-table=home.hello//当只同步几张或几张表时，可以这样设置。请注意，这应与上述库规范结合使用；replicate-do-db=otherreplicate-wild-do-table=other.biao//如果synchronized库中的表很多，不能一一指定，就把这个选项配置去掉，和直接根据指定的库进行同步。查询binlog主从日志的方法#查看所有binlog文件mysql>showbinlogs;#查看是否启用了binlogNO表示启用mysql>showvariableslike'log_bin%';#详细信息mysql>showvariableslike'binlog%';#查看binlog日志mysql>showbinlogeventsin'mysql-bin.000017';#或者使用mysqlbinlog，如果报错，使用--no-defaults（使用完整路径）[root@localhost~]#/usr/local/mysql/bin/mysqlbinlog--no-defaults/usr/local/mysql/数据/mysql-bin.000017 手动清除master日志，最好关闭日志，在/etc/my.cnf#Manuallyrefreshthelogmysql>showmasterstatus;#删除所有mysql>resetslave;或restmaster;#DeleteMySQL-bin.011mysql>PURGEMASTERLOGSTO'MySQL-bin.011';mysql>显示状态如“Innodb_buffer_pool_%”；+--------------------------------------+------------------------------------------------+|变量名|价值|+--------------------------------------+--------------------------------------------+|Innodb_buffer_pool_dump_status|未开始||Innodb_buffer_pool_load_status|缓冲池加载于20032016:18:07|完成|Innodb_buffer_pool_pages_data|93721||Innodb_buffer_pool_bytes_data|1535524864||Innodb_buffer_pool_pages_dirty|0||Innodb_buffer_pool_bytes_dirty|0||Innodb_buffer_pool_pages_flushed|328774||Innodb_buffer_pool_pages_free|37327||Innodb_buffer_pool_pages_misc|16||Innodb_buffer_pool_pages_total|131064||Innodb_buffer_pool_read_ahead_rnd|0||Innodb_buffer_pool_read_ahead|28||Innodb_buffer_pool_read_ahead_evicted|0||Innodb_buffer_pool_read_requests|27973283||Innodb_buffer_pool_reads|90917||Innodb_buffer_pool_wait_free|0||Innodb_buffer_pool_write_requests|1205702|+------------------------------------+------------------------------------------------+17行一组
