mysql热备设置神秘失败_mysql replication cluster 双机热备集群搭建及常见错误排除...

一.参考网址

https://174.136.43.78/prox2/

http://www.clusterdb.com/mysql-cluster/setting-up-mysql-asynchronous-replication-for-high-availability/

http://oskb.blogbus.com/logs/53722298.html

http://www.howtoforge.com/mysql_master_master_replication

二.环境

master主机        192.168.100.114

slave主机机       192.168.100.15

数据存放目录      /opt/cluster/mysql/var

mysql安装目录     /opt/cluster/mysql

mysql配置文件     /etc/my.cnf

三.安装mysql

# cd mysql-5.1.44

# ./configure --help

# ./configure --prefix=/opt/cluster/mysql;make;makeinstall;

# useradd mysql

# cd /opt/cluster/mysql/bin/

#./mysqladmin -u root password 123456

# cp ./mysql.server /etc/init.d/mysql

# chmod 755 /etc/init.d/mysql

# chkconfig --level 345 mysql on

# service mysql restart

# cp support-files/my-medium.cnf /etc/my.cnf

四.master配置

1、远程同步用户授权

在master(192.168.100.114)上

[root@localhost bin]# pwd

/opt/cluster/mysql/bin

[root@localhost bin]# ./mysql -uroot -p123456

mysql>grant replication slave on *.* to jsson@192.168.100.15 identified by 'jason';

mysql>GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO jason@192.168.100.15 IDENTIFIED BY 'jason';

mysql>\q

2、同步数据库

在master上

[root@localhost bin]# ./mysql -uroot -p123456

mysql>USE appdb;

mysql>FLUSH TABLES WITH READ LOCK;

注意：此时不能退出mysql的shell否则数据库的只读锁会自动取消。我们可以在新的窗口dump数据库，或者直接拷贝数据文件目录

手工同步完成后解锁

(打开一个新的远程窗口

# cd /opt/cluster/mysql/var

# scp -r appdb/ root@192.168.100.15:/opt/cluster/mysql/var

# 提示输入密码：15机器root用户密码

开始传输文件，同步备份完成。)

返回刚才添加只读锁的窗口，对数据库表解锁

mysql>UNLOCK TABLES;

mysql>\q

起始数据库同步完成。

3、修改配置文件

打开master上/etc/my.cnf

在[mysqld] 标签下添加

server-id=1

binlog-do-db= appdb

重启master的mysql服务

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

五.配置slave(192.168.100.15)

1、修改配置文件

打开/etc/my.cnf

在[mysqld] 标签下添加

master-host=192.1668.100.114

master-user=jason

master-password=jason

master-port=3306

server-id=2

master-connect-retry=60

replicate-do-db=appdb

log-slave-updates

2、检验远程用户及权限

mysql -h 192.168.100.114 -u jason -pjason

mysql> SHOW GRANTS;

mysql>\q

重新启动mysql服务

3、更改master主机

[root@localhost bin]# pwd

/opt/cluster/mysql/bin

[root@localhost bin]# ./mysql -uroot -p123456

mysql> CHANGE MASTER TO MASTER_HOST='192.168.100.114', MASTER_PORT=3306, MASTER_USER='jason', MASTER_PASSWORD='jason', MASTER_LOG_FILE='', MASTER_LOG_POS=4;

Query OK, 0 rows affected (0.00 sec)

mysql> use appdb;

Database changed

mysql> show master status\G

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

File: mysql-bin.000031

Position: 439

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.100.114

Master_User: jason

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000020

Read_Master_Log_Pos: 439

Relay_Log_File: localhost-relay-bin.000034

Relay_Log_Pos: 584

Relay_Master_Log_File: mysql-bin.000020

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: appdb

Replicate_Ignore_DB:

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

Relay_Log_Space: 888

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

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)

mysql>\q

设置成功！

4、测试同步情况

当前情况下数据库两台服务器上的appdb数据库是相同的

(1)到master下修改数据库

[root@localhost bin]# ./mysql -uroot -p123456

mysql> USE appdb

Database changed

mysql> create table numbers (num1 int, num2 int);

Query OK, 0 rows affected (0.01 sec)

mysql> insert into numbers values (1,10),(2,20),(3,30);

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from numbers;

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

| num1 | num2 |

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

|    1 |   10 |

|    2 |   20 |

|    3 |   30 |

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

3 rows in set (0.00 sec)

(2)到slave服务器上查看同步情况

[root@localhost bin]# ./mysql -uroot -p123456

mysql> show databases;

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

| Database           |

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

| information_schema |

| appdb            |

| mysql              |

| test               |

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

4 rows in set (0.00 sec)

mysql> use appdb

Database changed

mysql> show tables;

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

| Tables_in_cluster |

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

| numbers           |

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

1 row in set (0.00 sec)

mysql> select * from numbers;

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

| num1 | num2 |

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

|    1 |   10 |

|    2 |   20 |

|    3 |   30 |

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

3 rows in set (0.00 sec)

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

以同样方法在 将两台机器角色互换一下，安装配置一次，双机高可用的集群配置完成

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

六.常见问题解决方案

(1) 如果你的第一个主数据库(master1)包含的状态(在撰写本文时)，您就无法使用命令“从主服务器同步数据”。由于此错误：

你不得不要用现有系统的一些其他方法复制数据，例如通过mysqldump的复制和粘贴文件或数据流数据。需要打开数据库表的只读锁，方法参见上文(四、2章节)。

(2) 如果你的表里使用了自动增长的主键，而且你用的是双master的mysql集群，可能会遇到两台master添加了同一个id，从而出现crash。

那你就应该使用以下选项添加到my.cnf文件中,重启服务。

Make Master 1 only auto-increment odd numbers by adding this to my.cnf under [mysqld]:

auto_increment_increment= 2

auto_increment_offset   = 1

Make Master 2 only auto-increment even numbers by adding this to my.cnf under [mysqld]:

auto_increment_increment= 2

auto_increment_offset   = 2

此方法原理是通过设置是两个master在写入时自动增长的主键相互岔开,以避免出现crash。

(3)如果是在一个机子上做好后，将整套环境转移过去的话，最常见的就是文件权限问题。需要用chown来将文件的所属组更改正确。

并使用chmod将新机器上对应的配置文件及相关目录的权限修改好。

(4) Slave_IO_Running: No  Slave_SQL_Running: No

在master端msql登录后 show slave status\G后发现Slave_IO_Running: No  Slave_SQL_Running: No 而且在执行命令式出现此类错误报告

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

可能原因是你的master端已经有了前一个slave的日志文件，对更改新的slave没有日志文件。你需要重置一下你的slave

解决方案

登录到slave机器的mysql下

[root@localhost bin]# pwd

/opt/cluster/mysql/bin

[root@localhost bin]# ./mysql -uroot -p123456

mysql> STOP SLAVE;

mysql> RESET SLAVE;

mysql> CHANGE MASTER TO MASTER_HOST='192.168.100.114(根据实际主机ip改变)', MASTER_PORT=3306, MASTER_USER='jason',

MASTER_PASSWORD='jason', MASTER_LOG_FILE='', MASTER_LOG_POS=4;

mysql> START SLVE;

mysql> show master status\G

mysql>\q

(5) Slave_IO_Running: Yes  Slave_SQL_Running: No

1、首先定位同步不成功的原因，查数据库日志。

2、若是从主机重启，事物回滚，则

登录到slave机器的mysql下

[root@localhost bin]# pwd

/opt/cluster/mysql/bin

[root@localhost bin]# ./mysql -uroot -p123456

mysql> slave stop;

mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

mysql> slave start;

mysql> show master status\G

mysql>\q

3、其他网络故障，则登录先到master下的mysql，查看主机的日志情况

[root@localhost bin]# pwd

/opt/cluster/mysql/bin

[root@localhost bin]# ./mysql -uroot -p123456

mysql> show master status;

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

| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000004 | 244274056 | appdb        |                  |

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

1 row in set (0.00 sec)

mysql>\q

现在登录到slave的mysql下

[root@localhost bin]# pwd

/opt/cluster/mysql/bin

[root@localhost bin]# ./mysql -uroot -p123456

mysql> slave stop;

CHANGE MASTER TO MASTER_HOST='192.168.100.114(根据实际主机ip改变)', MASTER_PORT=3306, MASTER_USER='jason', MASTER_PASSWORD='jason', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=244274056;

(注意将MASTER_LOG_FILE改为上文看到的master的日志表的file下文件名称，同时将MASTER_LOG_POS改为上文看到的master的日志表的Position下文件名称)

mysql> slave start;

mysql> show master status\G

mysql>\q

(6) Slave_IO_Running: No  Slave_SQL_Running: Yes

两种办法，重置slave，表解锁同步数据

1、重置slave

登录到master下的mysql，查看主机的日志情况

[root@localhost bin]# pwd

/opt/cluster/mysql/bin

[root@localhost bin]# ./mysql -uroot -p123456

mysql> show master status;

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

| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000005 | 244274178 | appdb        |                  |

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

1 row in set (0.00 sec)

mysql>\q

现在登录到slave的mysql下

[root@localhost bin]# pwd

/opt/cluster/mysql/bin

[root@localhost bin]# ./mysql -uroot -p123456

mysql> slave stop;

mysql> reset slave;

mysql> CHANGE MASTER TO MASTER_HOST='192.168.100.114(根据实际主机ip改变)', MASTER_PORT=3306, MASTER_USER='jason', MASTER_PASSWORD='jason', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=244274178;

(注意将MASTER_LOG_FILE改为上文看到的master的日志表的file下文件名称，同时将MASTER_LOG_POS改为上文看到的master的日志表的Position下文件名称)

mysql> slave start;

mysql> show master status\G

mysql>\q

2、表解锁同步数据

[root@localhost bin]# pwd

/opt/cluster/mysql/bin

[root@localhost bin]# ./mysql -uroot -p123456

mysql> slave stop;

mysql> FLUSH TABLES WITH READ LOCK;

mysql> UNLOCK TABLES;

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.00000*';

mysql> CHANGE MASTER TO MASTER_LOG_POS=*;

mysql> slave start;

mysql> show master status\G

mysql>\q