mysql 主从互备份_MySQL 双机互为主从备份

#12 楼 @akin520 为什么配不起呢? 我在做实验, 先配一个主从, ok, 没问题. 再反过来配置第二个主从的时候, 就有问题了.

第一个主从管理配置:

[mysqld]

datadir=/var/lib/mysql

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

user=mysql

log-bin=mysql-bin

server-id=25

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

replicate-ignore-db=testcharacter_set_server= utf8

read_only=0

master-host=192.168.10.48

master-user=mysync

master-password=123456

master-port=3306

master-connect-retry=60

[mysqld_safe]

log-error=/var/log/mysqld.log

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

mysql> show slave status\G;

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

Slave_IO_State: Waiting formaster to send event

Master_Host: 192.168.10.48

Master_User: mysync

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000011

Read_Master_Log_Pos: 106

Relay_Log_File: mysqld-relay-bin.000027

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000011

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: testReplicate_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: 552

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 inset (0.00 sec)

ERROR:

No query specified

第二个主从关系中的从数据库的配置如下:

[mysqld]

datadir=/var/lib/mysql

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

user=mysql

log-bin=mysql-bin

server-id=24

expire-logs-days=10

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

replicate-ignore-db=testreplicate-ignore-db=syn

character_set_server= utf8

read_only=0

master-host=192.168.10.49

master-user=mysync1

master-password=123456

master-port=3306

master-connect-retry=60

[mysqld_safe]

log-error=/var/log/mysqld.log

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

mysql> show slave status\G;

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

Slave_IO_State: Connecting to master

Master_Host: 192.168.10.48

Master_User: mysync

Master_Port: 3306

Connect_Retry: 60

Master_Log_File:

Read_Master_Log_Pos: 4

Relay_Log_File: mysqld-relay-bin.000009

Relay_Log_Pos: 4

Relay_Master_Log_File:

Slave_IO_Running: No

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: test,syn

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

Last_IO_Error: error connecting to master 'mysync@192.168.10.48:3306' - retry-time: 60 retries: 86400

Last_SQL_Errno: 0

Last_SQL_Error:

1 row inset (0.00 sec)

ERROR:

No query specified

疑问 1, 用户名我明明配置的是 mysyn1, 为嘛在这里变成 mysqn 了

疑问 2, master_host 我配置的 192.168.10.49, 也变成 192.168.10.48, 也就是自己的 ip 了.