资源描述:
《mysql同步配置》由会员上传分享,免费在线阅读,更多相关内容在教育资源-天天文库。
1、Mysql同步配置目录一、环境说明1二、同步方式1三、主服务器上创建mysql同步用户1四、主mysql服务器配置文件1五、修改主mysql配置文件后重启mysql程序2六、从mysql服务器配置文件2七、修改从mysql配置文件后重启mysql程序3八、从mysql服务器连接主mysql服务器3九、查看同步状态3作者:陈志军时间:2012/7/13Mysql服务器主从复制,在从数据库上进行数据备份,当主mysql服务器down机时,从数据库接替主数据库继续工作。一、环境说明主数据库:192.168.4.251从数据库:19
2、2.168.4.252二、同步方式主数据库---->从数据库三、主服务器上创建mysql同步用户mysql>grantreplicationslave,fileon*.*to'replication'@'192.168.4.252'identifiedby'12345678';QueryOK,0rowsaffected(0.00sec)mysql>flushPRIVILEGES;QueryOK,0rowsaffected(0.00sec)一、主mysql服务器配置文件server-id=10log-bin=/data0/my
3、sql/3306/binlog/binlogexpire_logs_days=30#binlog_do_db=wangdouer#同步数据库binlog_ignore_db=information_schema#不同步数据库binlog_ignore_db=mysqlbinlog_ignore_db=performance_schemaskip_slave_startbinlog_format=MIXEDslave-skip-errors=1032,1062,126,1114,1146,1048,1396二、修改主mysql
4、配置文件后重启mysql程序/data0/mysql/3306/mysqlrestart三、从mysql服务器配置文件server-id=20replicate-ignore-db=mysqlreplicate-ignore-db=performance_schemareplicate-ignore-db=information_schema#不同步数据库#binlog-do-db=www5461log-bin=/data0/mysql/3306/binlog/binloglog_slave_updates=1relay-l
5、og=/data0/mysql/3306/relaylog/relaylogexpire_logs_days=30binlog_format=MIXED一、修改从mysql配置文件后重启mysql程序/data0/mysql/3306/mysqlrestart二、从mysql服务器连接主mysql服务器mysql>stopslave;QueryOK,0rowsaffected,1warning(0.01sec)mysql>changemasterto->master_host='192.168.4.251',->master
6、_user='replication',->master_password='12345678',->master_log_file='binlog.000001',->master_log_pos=1214;mysql>startslave;一、查看同步状态mysql>showslavestatus;mysql>showslavestatusG;***************************1.row***************************Slave_IO_State:Waitingformaste
7、rtosendeventMaster_Host:192.168.4.251Master_User:replicationMaster_Port:3306Connect_Retry:60Master_Log_File:binlog.000003Read_Master_Log_Pos:107Relay_Log_File:relaylog.000002Relay_Log_Pos:250Relay_Master_Log_File:binlog.000003Slave_IO_Running:YesSlave_SQL_Running:Y
8、esReplicate_Do_DB:Replicate_Ignore_DB:mysql,test,information_schemaReplicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wi