Mysql_数据库实时备份_linux

 

步骤一：

主机： 182.2.209.67

Mysql –uroot

GRANT REPLICATION SLAVE  ON  *.*   TO   backup@'182.2.209.23'   IDENTIFIED   BY '123123' ;

flush privileges;

 

备机： 182.2.209.23

Mysql –uroot

GRANT   REPLICATION   SLAVE   ON   *.*   TO   backup@'182.2.209.67'   IDENTIFIED   BY '123123' ;

flush privileges;

 

步骤二：　

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

 

主机： 

Mysqldump - -add-drop-table  -uroot ais>ais.sql

把ais.sql传到备份机（182.2.209.23）一个目录下

备机：　进入传入主机数据的目录下，执行

mysql -uroot ais<ais.sql

 

步骤三：

 

主机：

修改/etc/my.cnf

 

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-bin

server-id=1

binlog-do-db=ais

#master-host=182.2.209.23

#master-user=backup

#master-password='123123'

#master-port=3306

#master-connect-retry=20

#replicate-do-db=ais    

[mysql.server]

user=mysql

basedir=/var/lib

[safe_mysqld]

err-log=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

 

说明：如果配置双向复制，需要把注释掉的打开

 

备机：

修改/etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-bin

server-id=2

binlog-do-db=ais

master-host=182.2.209.67

master-user=backup

master-password='123123'

master-port=3306

master-connect-retry=20

replicate-do-db=ais    

[mysql.server]

user=mysql

basedir=/var/lib

[safe_mysqld]

err-log=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

 

步骤四：

 

主机：

用root用户执行：

service mysql restart

mysql -uroot

reset master;

show master status;

-----------------------------------------------------------------------------------------

|File         |Position  |    Binlog_do_db |    Binlog_ignore_db  |

-----------------------------------------------------------------------------------------

|HP-bin.001   | 79      | ais              |                    |

-----------------------------------------------------------------------------------------

退出数据库

 

备机：

service mysql restart

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.67  | backup      | 3306        | 20            | HP-bin.001      |

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

  | No               | No                | ais             |

 | 0          |            | 0            | 79                  | 4

  |

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

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

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

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

--+

CHANGE MASTER TO

>MASTER_HOST='182.2.209.67',

>MASTER_USER='backup',

>MASTER_PASSWORD='123123',

>MASTER_LOG_FILE='HP-bin.001'

>MASTER_LOG_POS=79;

>quit

 

 

步骤五：

主机：　重新启动mysql

service mysql restart

 

备机：　重新启动mysql

service mysql restart

 

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

主机：

mysql -uroot

show master status;

 

----------------------------------------------------------------

|File         |Position|    Binlog_do_db   |    Binlog_ignore_db|

----------------------------------------------------------------

|HP-bin.001   | 79     | ais               |                    |

----------------------------------------------------------------

 

备机：

mysql -uroot

show slave status;  查看Slave_IO_Running | Slave_SQL_Running |对应的值是否是YES,如果不是YES,执行slave start，打开同步

 

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

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

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

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

--+

| 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 |

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

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

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

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

--+

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

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

  | YES              | YES                | ais             |

 | 0          |            | 0            | 79                  | 4

  |

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

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

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

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

--+

 

  备机查出来的Slave_IO_Running=YES,Slave_SQL_Running=YES 表示同步已经启用了，并且查看

  Replicate_do_db是否对应ais（需要复制的数据库),Exec_master_log_pos对应的值是否是

  在主机show master status时Position的值，如果正确说明以前的配置正确，继续

$$$$$$$$$$$$$$$$$$$$$$$$$$$以上部分内容供查看使用$$$$$$$$$$$$$$$$$$$$$$$

 

步骤六：

主机： 在主机aiss 数据库的aid_res表内插入一条记录

mysql -uroot

use ais;

insert into ais_res values('111111',2000,3000);

select *  from ais_res;

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

| netno  | drflag | crflag |

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

| 066661 |   5000 |   6000 |

| 066836 |   6000 |   6000 |

| 066616 |   5000 |   6000 |

| 066001 |   6000 |   6000 |

| 066865 |   5000 |   6000 |

| 111111 |   2000 |   3000 |

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

6 rows in set (0.00 sec)

 

备机：查询在主机插入的记录是否存在，如果存在配置同步成功

select *  from ais_res;

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

| netno  | drflag | crflag |

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

| 066661 |   5000 |   6000 |

| 066836 |   6000 |   6000 |

| 066616 |   5000 |   6000 |

| 066001 |   6000 |   6000 |

| 066865 |   5000 |   6000 |

| 111111 |   2000 |   3000 |　　

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

6 rows in set (0.00 sec)

 

 

至此说明配置的mysql数据库同步设置成功。