mysql数据库实时备份for windows

mysql数据库实时备份for windows

步骤一：

主机：182.2.209.50

Mysql –uroot

GRANT REPLICATION SLA VE ON *.* TO backup@'182.2.209.24' IDENTIFIED BY '123123' ;

flush privileges;

备机：182.2.209.24

Mysql –uroot

GRANT REPLICATION SLA VE ON *.* TO backup@'182.2.209.50' IDENTIFIED BY '123123' ;

flush privileges;

步骤二：

将主机的localbuffer数据导入备机,保持主备机数据库的一致性

主机：命令行下进入c:\mysql\bin目录下，执行

Mysqldump –add-drop-table -uroot localbuffer>localbuffer.sql

把localbuffer.sql传到备份机（182.2.209.24）c:\mysql\bin\目录下

备机：命令行下进入c:\mysql\bin目录下，执行

mysql -uroot 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=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\bin>net 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\bin>net start mysql

mysql -uroot

slave stop;

show slave status; // Slave_IO_Running=NO,Slave_SQL_Running=NO 表示同步已经关闭了+----------------+-------------+-------------+---------------+-----------------+

---------------------+--------------------+---------------+---------------------

--+------------------+-------------------+-----------------+--------------------

-+------------+------------+--------------+---------------------+---------------

| Master_Host | Master_User | Master_Port | Connect_retry | Master_Log_File |

Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_Fil e | Slave_IO_Running | Slave_SQL_Running | Replicate_do_db | Replicate_ignore_db

| Last_errno | Last_error | Skip_counter | Exec_master_log_pos | Relay_log_spac

e |

+----------------+-------------+-------------+---------------+-----------------+

---------------------+--------------------+---------------+---------------------

--+------------------+-------------------+-----------------+--------------------

-+------------+------------+--------------+---------------------+---------------

--+

| 182.2.209.50 | backup | 3306 | 20 | HP-bin.001 |

79 | yyxt-relay-bin.001 | 4 | HP-bin.001

| No | No | localbuffer |

| 0 | | 0 | 79 | 4

|

+----------------+-------------+-------------+---------------+-----------------+

---------------------+--------------------+---------------+---------------------

--+------------------+-------------------+-----------------+--------------------

-+------------+------------+--------------+---------------------+---------------

--+

CHANGE MASTER TO

>MASTER_HOST='182.2.209.50',

>MASTER_USER='backup',

>MASTER_PASSWORD='123123',

>MASTER_LOG_FILE='HP-bin.001'

>MASTER_LOG_POS=79;

>quit

步骤五：

主机：重新启动mysql

命令行执行：

C:\mysql\bin> net stop mysql

C:\mysql\bin>net start mysql

备机：重新启动mysql

命令行执行：

C:\mysql\bin> net stop mysql

C:\mysql\bin>net start mysql

$$$$$$$$$$$$$$$$$$$$$此部分内容供查看核对使用$$$$$$$$$$$$$$$$$$$$$$$$$

主机：