mysql数据库实时备份forwindows.docVIP

mysql数据库实时备份for windows 步骤一： 主机： 182.2.209.50 Mysql –uroot GRANT REPLICATION SLAVE ON *.* TO backup@182.2.209.24 IDENTIFIED BY 123123 ; flush privileges; 备机： 182.2.209.24 Mysql –uroot GRANT REPLICATION SLAVE ON *.* TO backup@182.2.209.50 IDENTIFIED BY 123123 ; flush privileges; 步骤二：　 将主机的localbuffer数据导入备机,保持主备机数据库的一致性 主机： 命令行下进入c:\mysql\bin目录下，执行 Mysqldump –add-drop-table -uroot localbufferlocalbuffer.sql 把localbuffer.sql传到备份机（182.2.209.24）c:\mysql\bin\目录下 备机：　命令行下进入c:\mysql\bin目录下，执行 mysql -uroot localbufferlocalbuffer.sql 步骤三： 主机： 修改c:\winnt\my.ini [WinMySQLAdmin] Server=C:/mysql/bin/mysqld-nt.exe [mysqld] basedir=C:/mysql datadir=C:/mysql/data log-bin server-id=1 #binlog-do-db=localbuffer #master-host=182.2.209.24 #master-user=backup #master-password=123123 #master-port=3306 #master-connect-retry=20 #replicate-do-db=localbuffer 说明：如果配置双向复制，需要把注释掉的打开 备机： 修改c:\winnt\my.ini [WinMySQLAdmin] Server=C:/mysql/bin/mysqld-nt.exe [mysqld] basedir=C:/mysql datadir=C:/mysql/data log-bin server-id=2 binlog-do-db=localbuffer master-host=182.2.209.50 master-user=backup master-password=123123 master-port=3306 master-connect-retry=20 replicate-do-db=localbuffer 步骤四： 主机： 命令行执行： C:\mysql\bin net stop mysql C:\mysql\binnet start mysql mysql -uroot reset master; show master status; ----------------------------------------------------------------------------------------- |File |Position | Binlog_do_db | Binlog_ignore_db | ----------------------------------------------------------------------------------------- |HP-bin.001 | 79 | ais | | ----------------------------------------------------------------------------------------- 退出数据库 备机： 命令行执行： C:\mysql\bin net stop mysql C:\mysql\binnet start mysql mysql -uroot slave stop; show slave status; // Slave_IO_Running=NO,Slave_SQL_Running=NO 表示同步已经关闭了 +----------------+-------------+-------------+---------------+-----------------+