mysql同步配置.docx

Mysql同步配置目录一、环境说明1二、同步方式1三、主服务器上创建mysql同步用户1四、主mysql服务器配置文件1五、修改主mysql配置文件后重启mysql程序2六、从mysql服务器配置文件2七、修改从mysql配置文件后重启mysql程序3八、从mysql服务器连接主mysql服务器3九、查看同步状态3作者：陈志军时间：2012/7/13Mysql服务器主从复制，在从数据库上进行数据备份，当主mysql服务器down机时，从数据库接替主数据库继续工作。环境说明主数据库：51从数据库：52同步方式主数据库----从数据库主服务器上创建mysql同步用户mysql grant replication slave,file on *.* to replication@52 identified by Query OK, 0 rows affected (0.00 sec)mysql flush PRIVILEGES ;Query OK, 0 rows affected (0.00 sec)主mysql服务器配置文件server-id = 10log-bin = /data0/mysql/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配置文件后重启mysql程序/data0/mysql/3306/mysql restart从mysql服务器配置文件server-id = 20replicate-ignore-db = mysqlreplicate-ignore-db = performance_schemareplicate-ignore-db = information_schema # 不同步数据库#binlog-do-db= www5461 log-bin = /data0/mysql/3306/binlog/binloglog_slave_updates = 1relay-log = /data0/mysql/3306/relaylog/relaylogexpire_logs_days = 30binlog_format = MIXED修改从mysql配置文件后重启mysql程序/data0/mysql/3306/mysql restart从mysql服务器连接主mysql服务器mysql stop slave;Query OK, 0 rows affected, 1 warning (0.01 sec)mysql change master to - master_host=51, - master_user=replication, - master_password - master_log_file=binlog.000001,- master_log_pos=1214;mysql start slave;查看同步状态mysql show slave status;mysql show slave status\G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 51 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000003 Read_Master_Log_Pos: 107 Relay_Log_File: relaylog.000002 Relay_Log_Pos: 250 Relay_Master_Log_File: binlog.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql,test,information_schema Replicate_Do_Table: Replicate_Ignore_Table: