Linux MySQL主备 - 实现双主热备的方法

Linux 下mysql的双主热备

作者：浩浩哥来了

1、数据库同步设置

主机操作系统：RedHatEnterprise Linux 6

数据库版本

前提：MYSQL数据库正常启动

假设两台主机地址分别为：

ServA：192.168.18.112

ServB：192.168.18.114

注意：操作前，全文查找替换，将server A、B替换成实际IP

2、配置同步账号

在ServA上增加一个ServB可以登录的帐号：

mysql -uroot -p123456

grant all privileges on *.* tolwh@192.168.18.114 identified by '123456' with grant option;

在ServB上远程登录查看192.168.18.114使用lwh这个账号密码为123456登录上去拥有全部权限

mysql -h 192.168.18.112 -ulwh -p123456

在ServB上增加一个ServA可以登录的帐号：

mysql -uroot -p123456

grant all privileges on *.* tolwh@192.168.18.114 identified by '123456' with grant option;

在ServA上远程登录查看192.168.18.112使用lwh这个账号密码为123456登录上去拥有全部权限

3、以root用户登录ServA，修改ServA的my.cnf文件

vi /etc/my.cnf

在[mysqld]的配置项中增加如下配置：

default-character-set=utf8

log-bin=mysql-bin

relay-log=relay-bin

relay-log-index=relay-bin-index

server-id=1     表示主机号

master-host=192.168.18.114  另一台主机IP

master-user=lwh     同步的用户为lwh

master-password=123456  同步用户密码为123456

master-port=3306  主机mysql的端口是3306

master-connect-retry=30  主机重试时间为30秒

set-variable=replicate-do-db=haohaoge 同步的数据库名称为haohaoge

set-variable=replicate-do-db=aoffice 有同步多个数据库就重复写多个名称

slave-net-timeout=60  辅助连接超时为60秒

4、以root用户登录ServB，修改ServB的my.cnf文件

vi /etc/my.cnf

在[mysqld]的配置项中增加如下配置：

default-character-set=utf8

log-bin=mysql-bin

relay-log=relay-bin

relay-log-index=relay-bin-index

server-id=2     表示主机号

master-host=192.168.18.112  另一台主机IP

master-user=lwh     同步的用户为lwh

master-password=123456  同步用户密码为123456

master-port=3306  主机mysql的端口是3306

master-connect-retry=30  主机重试时间为30秒

set-variable=replicate-do-db=haohaoge 同步的数据库名称为haohaoge

set-variable=replicate-do-db=aoffice 有同步多个数据库就重复写多个名称

slave-net-timeout=60  辅助连接超时为60秒

5、重启 ServA ServB的mysql服务

service mysqld restart

用sysorg登录后台，在servA添加一个用户，B上查看是否同步。

至此完成

备注：

查看同步状态

Mysql>show slave status\G

查看这两项是否为YES，yes为正常。

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

手工执行数据库同步

假设以ServA为主服务器，在ServB上重启mysql：

service mysqld restart

在ServB上用root用户登录mysql，执行：

Mysql> stop slave;

Mysql> load data from master;

Mysql> start slave;

在ServA上重启mysql：

service mysqld restart

scp /home/administrator/news.txtroot@192.168.6.129:/etc/squid

scp remote@www.abc.com:/usr/local/sin.sh/home/administrator

mysql> CHANGE MASTER TO

-> MASTER_HOST=’master_host_name’,

-> MASTER_USER=’replication_user_name’,

-> MASTER_PASSWORD=’replication_password’,

-> MASTER_LOG_FILE=’recorded_log_file_name’,

-> MASTER_LOG_POS=recorded_log_position;

同步过程中出现下列问题

mysql> show slave status\G;

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

Slave_IO_State:

Master_Host: 192.168.18.112

Master_User: tongbu

Master_Port: 3306

Connect_Retry: 30

Master_Log_File:

Read_Master_Log_Pos: 4

Relay_Log_File: relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File:

Slave_IO_Running: No

Slave_SQL_Running: Yes

Replicate_Do_DB: lwh

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

Relay_Log_Space: 106

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

Last_IO_Error: Got fatal error 1236 from master when reading dat                                                                                       a from binarylog: 'Binary log is not open'

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.00 sec)

[root@luowenhao ~]# tail/var/log/mysqld.log

140921 0:02:42 [Note] /usr/libexec/mysqld: ready for connections.

Version: '5.1.52-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306 Source distribution

140921 0:02:42 [Note] Slave SQL thread initialized, starting replication in log'FIRST' at position 0, relay log './relay-bin.000001' position: 4

140921 0:02:42 [Note] Slave I/O thread: connected to master'tongbu@192.168.18.112:3306',replication started in log 'FIRST' at position 4

140921 0:02:42 [ERROR] Error reading packet from server: Binary log is not open( server_errno=1236)

140921  0:02:42 [ERROR] Slave I/O: Got fatal error1236 from master when reading data from binary log: 'Binary log is not open',Error_code: 1236  翻译大概是有致命错误1236从二进制日志读取数据时从主:“二进制日志不开放，下面就进行了刷新日志

140921 0:02:42 [Note] Slave I/O thread exiting, read up to log 'FIRST',position 4

140921 2:15:04 [Note] Error reading relay log event: slave SQL thread waskilled

140921 2:16:06 [Note] Slave SQL thread initialized, starting replication in log'FIRST' at position 0, relay log './relay-bin.000001' position: 4

140921 2:16:06 [Note] Slave I/O thread: connected to master'tongbu@192.168.18.112:3306',replication started in log 'FIRST' at position 4

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> flush logs;

Query OK, 0 rows affected (0.14 sec)

mysql> start slave;

Query OK, 0 rows affected (0.08 sec)

mysql> show slave status\G;

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

Slave_IO_State: Waiting formaster to send event

Master_Host: 192.168.18.112

Master_User: tongbu

Master_Port: 3306

Connect_Retry: 30

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 106

Relay_Log_File: relay-bin.000008

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000006

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: lwh

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

Relay_Log_Space: 545

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)

ERROR:

No query specified