|
2.配置复制#e#
我按照我之前那篇文章配置好了之后,在从上面查看mysql> show slave status\G发现Slave_IO_State:这一项的内容一直都是connecting.在主上面查看root的权限,发现只能从本机登陆.为了安全起见,在主上面建了一个专门用于复制的账户允许它远程登陆
mysql > GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'slavepass';
可是问题变得更加严重,在从上面查看复制状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.1.3.106
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: ifi-db01-bin.000019
Read_Master_Log_Pos: 4653237
Relay_Log_File: ifi-db02-relay-bin.000006
Relay_Log_Pos: 10072
Relay_Master_Log_File: ifi-db01-bin.000019
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
…..
可以看到IO进程并没有运行,查看进程列表.(准确的说应该是线程,此处为了方便理解写为进程)
mysql> show processlist\G
*************************** 1. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 14
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 2. row ***************************
Id: 8
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
3 rows in set (0.00 sec)
也并没有I/0进程.
通过查看文档的FAQ,上面说要看MYSQL的错误日志.即数据目录下的"机器名.err"文件
[root@localhost var]# less xxx.err
070806 2:19:12 [Note] Slave I/O thread: connected to master 'repl@10.1.3.106:3306', replication started in log 'ifi-db01-bi
n.000019' at position 4540467
070806 2:19:12 [ERROR] While trying to obtain the list of slaves from the master '10.1.3.106:3306', user 'repl' got the foll
owing error: 'Access denied; you need the REPLICATION SLAVE privilege for this operation'
070806 2:19:12 [ERROR] Slave I/O thread exiting, read up to log 'ifi-db01-bin.000019', position 4540467
可见是repl用户的权限不足.明明增加了权限为什么还报这样的错误??刷新权限!!
在主上面执行
mysql > FLUSH PRIVILEGES;
然后在从上面执行
mysql > STOP SLAVE;
等一会让它完全停止
mysql > CHANGE MASTER TO
->MASTER_HOST='10.1.3.106',
->MASTER_USER='repl',
->MASTER_PASSWORD='slavepass',
->MASTER_LOG_FILE='ifi-db01-bin.000019',
->MASTER_LOG_POS=4540467;
启动复制
mysql > START SLAVE
这样就正常了,查看它们的信息如下
主上面的
mysql > SHOW PROCESSLIST\G
*************************** 22. row ***************************
Id: 4274
User: repl
Host: 10.1.3.107:32770
db: NULL
Command: Binlog Dump
Time: 1629
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
22 rows in set (0.00 sec)
从上面的
[root@ifi-db02 var]# less xxx.err
070806 3:28:51 [Note] Slave SQL thread initialized, starting replication in log 'ifi-db01-bin.000019' at position 4677741, r
elay log './ifi-db02-relay-bin.000006' position: 34576
070806 3:28:56 [Note] Slave I/O thread: connected to master 'repl@10.1.3.106:3306', replication started in log 'ifi-db01-bi
n.000019' at position 4677741
没有报错误了
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.3.106
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: ifi-db01-bin.000019
Read_Master_Log_Pos: 4677185
Relay_Log_File: ifi-db02-relay-bin.000006
Relay_Log_Pos: 34020
Relay_Master_Log_File: ifi-db01-bin.000019
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
…..
mysql> show processlist\G
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 1709
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 24
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 33
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
3 rows in set (0.00 sec)
可见所有进程都已经正常启动了.
(责任编辑：IT路路通) |
IT技术交流群:116393