mysql mysql 主从配置
一、先修改服务器的配置文件
?????? 1、Master服务器配置简单，修改my.cnf为:
server-id?????? = 1
log-bin=n01-bin
relay-log=ns01-relay-bin
set-variable=binlog-ignore-db=mysql
2、slave1的配置加入
master-host = 172.16.20.135
master-user = rep
master-password = cnrep
master-port = 3306
server-id?????? = 2
log-bin=n01-bin
replicate-do-db =mydnsreplicate-ignore-db=mysql
log-slave-updates
slave-net-timeout=60
master-connect-retry=10
relay-log=ns01-relay-bin
log-slave-updates
read-only
3、slave2服务器的配置
master-host = 172.16.20.3
master-user = rep1
master-password = cnrep
master-port = 3306
server-id?????? = 3
log-bin=n01-bin
replicate-do-db =mydnsreplicate-ignore-db=mysql
log-slave-updates
slave-net-timeout=60
?
master-connect-retry=10
relay-log=ns01-relay-bin
log-slave-updates
read-only
二、重启master数据库
?
三、然后锁定master数据库的表：
FLUSH TABLES WITH READ LOCK;
四、在master数据库中添加用于slave1同步的用户，并赋予相关权限：
GRANT REPLICATION SLAVE ON *.* TO rep@sa_cfengine1 IDENTIFIED BY ‘cnrep’;
GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO rep@sa_cfengine1 IDENTIFIED BY ‘cnrep’;
?
五、在slave1数据库中添加用于slave2同步的用户，并赋予相关权限：
GRANT REPLICATION SLAVE ON *.* TO rep1@sa_cfengine2 IDENTIFIED BY ‘cnrep’;
GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO rep1@sa_cfengine2 IDENTIFIED BY ‘cnrep’;
?
六、同步数据库：
方法很多，可以打包之后scp，再解压，由于sa_cfengine1到mysql master服务器通道打通了，切sa_cfengine2到sa_cfengine1通道也打了，故直接scp整个数据库目录即可。
注意：此时要注意删除同步过来的日志文件，最好把与数据库无关的文件全删除（可以将非目录的文件全删了）。
?
七、重启salve1的mysql，起来之后锁定表
?
八、重启slave2的mysql，然后先后给slave1和master服务器的mysql表解锁
UNLOCK TABLES；
?
九、分别登录slave1和slave2的mysql，查看同步状态：
SHOW SLAVE STATUS\G
*************************** 1. row ***************************
???????????? Slave_IO_State: Waiting for master to send event
??????????????? Master_Host: 172.16.20.135
??????????????? Master_User: rep
??????????????? Master_Port: 3306
????????????? Connect_Retry: 60
??????????? Master_Log_File: mysql-bin.000051
??????? Read_Master_Log_Pos: 13856842
???????????? Relay_Log_File: sa_cfengine1-relay-bin.000013
????????????? Relay_Log_Pos: 624419
????? Relay_Master_Log_File: mysql-bin.000051
???????????Slave_IO_Running: Yes
????????? Slave_SQL_Running: Yes
??????????? Replicate_Do_DB: AliSMS,lcd,loginmanager,samis,sareport,syslog,web_speed
??????? Replicate_Ignore_DB: mysql,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: 13856842
??????????? Relay_Log_Space: 624419
??????????? 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
1 row in set (0.01 sec)
注意标注为红色的地方，两个都是yes说明一切正常，否则要检查原因，可以看error log查找原因后做相应的处理。
?
十、测试：
?????? 在master数据库中update在同步列表中的一个表的一个字段，如果slave服务器的做相应改变，则测试用过。
?
实例：
67：
mysql> show master status \G;
*************************** 1. row ***************************
File: n01-bin.000001
Position: 327
Binlog_Do_DB:?
Binlog_Ignore_DB:?
1 row in set (0.00 sec)
ERROR:?
No query specified
66
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.221
Master_User: reply
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: n01-bin.000001
Read_Master_Log_Pos: 327
Relay_Log_File: no02-relay-bin.000039
Relay_Log_Pos: 233
Relay_Master_Log_File: n01-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mydns,mydns,mydns
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: 327
Relay_Log_Space: 233
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
1 row in set (0.00 sec)
ERROR:?
No query specified
.5 (既做67的从，有做10的主)
mysql> show master status;
+----------------+----------+--------------+------------------+
| File?????????? | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| n01-bin.000004 |????? 220 |????????????? |????????????????? |?
+----------------+----------+--------------+------------------+
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: 10.0.250.67
Master_User: reply
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: n01-bin.000001
Read_Master_Log_Pos: 327
Relay_Log_File: ns01-relay-bin.000006
Relay_Log_Pos: 355
Relay_Master_Log_File: n01-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mydns,mydns,mydns
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: 327
Relay_Log_Space: 355
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
1 row in set (0.00 sec)
10.
CHANGE MASTER TO MASTER_LOG_FILE='n01-bin.000004', MASTER_LOG_POS=220;