mysql数据库同步暖备（双向以及单向）

mysql数据库同步热备（双向以及单向）

环境：

Master server: 10.224.194.239

Slave server: 10.224.194.237

步骤：

1.分别在Master/Slaver mysql db 创建backup user：

GRANT FILE ON *.* TO backup@10.224.194.239 IDENTIFIED BY 'pass';

GRANT REPLICATION SLAVE ON *.* TO backup@10.224.194.239 IDENTIFIED BY 'pass';

GRANT FILE ON *.* TO backup@10.224.194.237 IDENTIFIED BY 'pass';

GRANT REPLICATION SLAVE ON *.* TO backup@10.224.194.237 IDENTIFIED BY 'pass';

2.在Master server配置/etc/my.cf 文件：

log-bin

server-id = 1

binlog-do-db=test

binlog-ignore-db = mysql

//只保存7天的log-bin日志

expire-logs-days = 7

replicate-do-db=test

replicate-ignore-db = mysql

master-host=10.224.194.237

master-user=backup

master-password=pass

master-port=3306

master-connect-retry=60

slave-skip-errors=all

3.在Master server配置/etc/my.cf 文件：

log-bin

server-id = 2

//只保存7天的log-bin日志

expire-logs-days = 7

binlog-do-db=test

binlog-ignore-db = mysql

replicate-do-db=test

replicate-ignore-db = mysql

master-host=10.224.194.239

master-user=backup

master-password=pass

master-port=3306

master-connect-retry=60

slave-skip-errors=all

4.重启mysql数据库，验证命令如下：

查看Master状态

show master status;

mysql> show master status;

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

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

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

1 row in set (0.00 sec)

查看Slave状态

show slave status \G;

mysql> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Reconnecting after a failed master event read

Master_Host: 10.224.194.239

Master_User: backup

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqllog.000003

Read_Master_Log_Pos: 301

Relay_Log_File: mysqlgsb-relay-bin.000082

Relay_Log_Pos: 348

Relay_Master_Log_File: mysqllog.000003

Slave_IO_Running: No

Slave_SQL_Running: Yes

Replicate_Do_DB: test

Replicate_Ignore_DB: mysql

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 301

Relay_Log_Space: 650

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.00 sec)

ERROR:

No query specified

查看同步进程：

mysql> show processlist \G;

*************************** 1. row ***************************

Id: 1

User: system user

Host:

db: NULL

Command: Connect

Time: 4186

State: Waiting for master to send event

Info: NULL

*************************** 2. row ***************************

Id: 2

User: system user

Host:

db: NULL

Command: Connect

Time: 3745

State: Has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

*************************** 3. row ***************************

Id: 5

User: root

Host: mysqlpri.webex.com:28293

db: NULL

Command: Query

Time: 0

State: NULL

Info: show processlist

*************************** 4. row ***************************

Id: 6

User: backup

Host: 10.224.194.237:41729

db: NULL

Command: Binlog Dump

Time: 135

State: Has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

4 rows in set (0.00 sec)

ERROR:

No query specified

PS:

一般备份只需要单向热备,请看下面这篇文章:

另外转一篇文章:http://blog.****.net/babydavic/article/details/8432120

http://blog.****.net/mackzhaozhonggang/article/details/6718831

以下是Mysql同步出错解决方法

一、主从不同步

mysql> show slave status;

报错:Error xxx dosn't exist

且show slave status\G:

Slave_SQL_Running: NO

Seconds_Behind_Master: NULL

解决办法一

1、首先停掉Slave服务:

mysql> SLAVE STOP;

2、到主服务器上查看主机状态：记录File和Position对应的值:

mysql> SHOW MASTER STATUS;

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

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

| updatelog.000001 | 215 +------------------+-----------+--------------+------------------+

3、到slave服务器上执行手动同步：

mysql> CHANGE MASTER TO

-> MASTER_HOST='master_host_name',

-> MASTER_USER='replication_user_name',

-> MASTER_PASSWORD='replication_password',

-> MASTER_PORT=3306

-> MASTER_LOG_FILE='recorded_log_file_name',

-> MASTER_LOG_POS=recorded_log_position;

mysql> SLAVE START;

// 再次查看slave状态发现：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

问题解决！

解决办法二

1、首先停掉Slave服务:

mysql> SLAVE STOP;

2、从主服务器中跳过后面的n个事件。要复原由语句导致的复制中止，这是有用的。仅当从属

线程没有正在运行时，本语句时有效的。否则，会产生一个错误。

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

3、恢复Slave服务:

mysql> SLAVE START;

注意: 方法一是强制性从某一个点开始同步,会有部分没有同步的数据丢失,后续主服务器上删

除记录同步也会有一些错误信息,不会影响使用. 方法二不一定会有效果.

// 还需要做的一些优化与监视:

show full processlist; # 查看mysql当前同步线程号

skip-name-resolve # 跳过dns名称查询，有助于加快连接及同步的速度

max_connections=1000 # 增大Mysql的连接数目，(默认100)

max_connect_errors=100 # 增大Mysql的错误连接数目,(默认10)

二、SLAVE启动问题

由于一些错误操作导致 CHANGE MASTER 和SLAVE 服务无法启动，系统报错如下：

*****************************************************************

Could not initialize master info structure; more error messages can be found in the MySQL

error log.

*****************************************************************

无法初始化master info结构，MySQL错误日志记录了更详细的错误信息。

解决方法：

1、查看MySQL错误日志，如：同步的上一个Position是多少，很多情况下无法启动服务是由于

mysql识别的同步始终停留在上一个Position上。

2、查看master.info和relay-log.info，master.info 记录MASTER相关信息，relay-log.info

记录当前同步日志信息。

3、停止myslq服务，删除master.info和relay-log.info。

4、启动mysql服务。

5、重新CHANGE MASTER，重新启动SLAVE服务。

或者最简单的方式:

mysql> slave stop;

mysql> reset slave;

mysql> CHANGE MASTER TO      
->     MASTER_LOG_FILE=’mysql-bin.000012’,      
->     MASTER_LOG_POS=4117873;  

mysql> slave start;

mysql数据库同步暖备（双向以及单向）

相关推荐