Slave_IO_Running: Yes Slave_SQL_Running: Yes 有这两句应该是没有什么问题了。 是可以按你说的那样来实现同步和测试,但是可能会有些延迟就是了。 也可以使用命令:load data from master;把要备份的数据从主服务器上直接传到从服务器上。
谢谢,hy2003fly() ,你也很忙吧,呵呵终于等到你了。:~( 嗯,我也觉得好像是连上了。但是我在执行load data from master;的时候,又出现这个错误了。 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 是我的权限不够吗? 但是我在192.168.1.7上运行下边的这句话的时候,表明另一台已具备所有权限了啊。 mysql> show grants for [email protected]; +------------------------------------------------------------------------------- ------------------------------------------------------------+ | Grants for [email protected] | +------------------------------------------------------------------------------- ------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'back'@'192.168.1.76' IDENTIFIED BY PASSWORD '* 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION | +------------------------------------------------------------------------------- ------------------------------------------------------------+ 1 row in set (0.38 sec)是这样的吧? 那哪儿出现问题了呢?急死了。
哈哈!请问大家一下,MYSQL的域有什么作用啊
show processlist \G 又有以下信息:*************************** 4. row *************************** Id: 15 User: back Host: YOUR-CC5A5138EA:1127 db: NULL Command: Binlog Dump Time: 961 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL 4 rows in set (0.00 sec)其中的 db:null是不是没发现配置同步的服务器啊? 但是我明明已经配置上了啊? 帮我看下这条信息吧。谢谢
mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.76 Master_User: back Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysqlbakmysqllog.000003 Read_Master_Log_Pos: 98 Relay_Log_File: liu-relay-bin.000004 Relay_Log_Pos: 242 Relay_Master_Log_File: mysqlbakmysqllog.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: backup 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: 98 Relay_Log_Space: 242 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)ERROR: No query specified最后一行有个error信息。这是什么?
mysql> GRANT REPLICATION SLAVE ON *.* -> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';为什么我按照教程说的执行这句然后提示我执行成功,影响0行,这是为什么啊?我用的ROOT帐号,我也想学一下同步
如果你觉得会是权限的问题的话,你可以用命令:grant all on *.* to 'backup'@'%' identified by 'password';来进行授权.flush privileges;退出再用新的帐号看看能不能登录就可以了.如果不能进行本地登录的话,再用grant all on *.* to 'backup'@'localhost' identified by 'password'; 至于你出现的错误可能是网络的原因或者其他的原因我就不太清楚,不过你自己查看一下数据库目录下的error日志就很清楚了。"host.err"文件上记录了很多信息,包括是否能正常同步,读取了多少数据等。
我也是一直在看.err里边的信息呢。 只是每当我执行load data from master 时,都显示正常。 Query OK, 0 rows affected (0.08 sec) 只是没有变更。而log里却显示下边的信息 这个信息是什么意思啊? 060901 15:59:34 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 060901 15:59:34 [Note] Slave I/O thread killed while reading event 060901 15:59:34 [Note] Slave I/O thread exiting, read up to log 'mysqlbakmysqllog.000002', position 98 060901 15:59:34 [Note] Error reading relay log event: slave SQL thread was killed 060901 15:59:34 [Note] Slave SQL thread initialized, starting replication in log 'mysqlbakmysqllog.000002' at position 98, relay log '.\liu-relay-bin.000001' position: 4 060901 15:59:34 [Note] Slave I/O thread: connected to master '[email protected]:3306', replication started in log 'mysqlbakmysqllog.000002' at position 98 怎么觉得好像它先不能读取,然后又杀掉进程,然后重新生成进程,然后继续等待同步信息但是遇到同步命令时,又不能读取,又杀掉进程 这是怎么一回事儿啊? 我觉得如果把这个问题解决了,那么所有的问题都可以迎刃而解了。还有,权限的问题,我觉得不应该是权限了,理由如下: 我在192.168.1.7上运行下边的这句话 mysql> show grants for [email protected]; +------------------------------------------------------------------------------- ------------------------------------------------------------+ | Grants for [email protected] | +------------------------------------------------------------------------------- ------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'back'@'192.168.1.76' IDENTIFIED BY PASSWORD '* 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION | +------------------------------------------------------------------------------- ------------------------------------------------------------+说明其已具备全部的权限。是这样吧?
还有一条进程信息,看下? (我的同步用户是back,同步db是backup,但是Waiting for master to send event 和Has read all relay log; waiting for the slave I/O thread to update 的用户是system user) 这样对吗?mysql> show processlist \G *************************** 1. row *************************** Id: 42 User: system user Host: db: NULL Command: Connect Time: 3092 State: Waiting for master to send event Info: NULL *************************** 2. row *************************** Id: 43 User: system user Host: db: NULL Command: Connect Time: 3091 State: Has read all relay log; waiting for the slave I/O thread to update Info: NULL *************************** 3. row *************************** Id: 44 User: back; Host: localhost:2790 db: backup Command: Query Time: 0 State: NULL Info: show processlist *************************** 4. row *************************** Id: 49 User: back Host: YOUR-CC5A5138EA:1629 db: NULL Command: Binlog Dump Time: 109 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL 4 rows in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Master_Host: 192.168.0.98
Master_User: backup
Master_Port: 3306
Connect_retry: 60
Master_Log_File: as4u-bin.021
Read_Master_Log_Pos: 25127011
Relay_Log_File: as4u2s-relay-bin.006
Relay_Log_Pos: 25129624
Relay_Master_Log_File: as4u-bin.021
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_do_db: gk2006
Replicate_ignore_db:
Last_errno: 0
Last_error:
Skip_counter: 0
Exec_master_log_pos: 25127011
Relay_log_space: 25129624
1 row in set (0.00 sec)
看了一下你的配置应该没有错,你可以先做A->B成功了,再做B->A,最后再做A<->B。
因为A,B现在均既是主,又是从,所以我打了两条状态信息出来,可是刚刚接触这些东西,看不太懂,能不能请你先帮我看看这些信息,里边可能有些问题。
呵呵谢谢了。
信息:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.76
Master_User: back
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysqlbakmysqllog.000001
Read_Master_Log_Pos: 98
Relay_Log_File: liu-relay-bin.000012
Relay_Log_Pos: 242
Relay_Master_Log_File: mysqlbakmysqllog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: backup
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: 98
Relay_Log_Space: 242
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)mysql> show master status\G
*************************** 1. row ***************************
File: mysqlbakmysqllog.000002
Position: 98
Binlog_Do_DB: backup
Binlog_Ignore_DB:
1 row in set (0.00 sec)
Slave_SQL_Running: Yes有上边两句话,应该是没有什么问题吧?
那是不是我的数据库备份的时候出了问题,或者是数据库拷贝的时候有问题啊?
我的操作步骤是不是这样的:
1:将master数据库数据备份到某一路径下:mysqldump -uback -p1234 backup >c:\backup.sql
2: 将backup.sql传到slave上,然后导入slave 上的backup数据库.
是这样吗?
然后我怎么看是否同步了呢?我从master 上向数据库中的一个表里插入3条数据,然后跑到slave数据库里看相同表名下是否有这三条数据。如果有,就是同步了,没有就是没同步。是吗?
Slave_SQL_Running: Yes
有这两句应该是没有什么问题了。
是可以按你说的那样来实现同步和测试,但是可能会有些延迟就是了。
也可以使用命令:load data from master;把要备份的数据从主服务器上直接传到从服务器上。
嗯,我也觉得好像是连上了。但是我在执行load data from master;的时候,又出现这个错误了。
[ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
是我的权限不够吗?
但是我在192.168.1.7上运行下边的这句话的时候,表明另一台已具备所有权限了啊。
mysql> show grants for [email protected];
+-------------------------------------------------------------------------------
------------------------------------------------------------+
| Grants for [email protected]
|
+-------------------------------------------------------------------------------
------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'back'@'192.168.1.76' IDENTIFIED BY PASSWORD '*
6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |
+-------------------------------------------------------------------------------
------------------------------------------------------------+
1 row in set (0.38 sec)是这样的吧?
那哪儿出现问题了呢?急死了。
Id: 15
User: back
Host: YOUR-CC5A5138EA:1127
db: NULL
Command: Binlog Dump
Time: 961
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
4 rows in set (0.00 sec)其中的 db:null是不是没发现配置同步的服务器啊?
但是我明明已经配置上了啊?
帮我看下这条信息吧。谢谢
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.76
Master_User: back
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysqlbakmysqllog.000003
Read_Master_Log_Pos: 98
Relay_Log_File: liu-relay-bin.000004
Relay_Log_Pos: 242
Relay_Master_Log_File: mysqlbakmysqllog.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: backup
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: 98
Relay_Log_Space: 242
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)ERROR:
No query specified最后一行有个error信息。这是什么?
-> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';为什么我按照教程说的执行这句然后提示我执行成功,影响0行,这是为什么啊?我用的ROOT帐号,我也想学一下同步
至于你出现的错误可能是网络的原因或者其他的原因我就不太清楚,不过你自己查看一下数据库目录下的error日志就很清楚了。"host.err"文件上记录了很多信息,包括是否能正常同步,读取了多少数据等。
只是每当我执行load data from master 时,都显示正常。
Query OK, 0 rows affected (0.08 sec)
只是没有变更。而log里却显示下边的信息
这个信息是什么意思啊?
060901 15:59:34 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
060901 15:59:34 [Note] Slave I/O thread killed while reading event
060901 15:59:34 [Note] Slave I/O thread exiting, read up to log 'mysqlbakmysqllog.000002', position 98
060901 15:59:34 [Note] Error reading relay log event: slave SQL thread was killed
060901 15:59:34 [Note] Slave SQL thread initialized, starting replication in log 'mysqlbakmysqllog.000002' at position 98, relay log '.\liu-relay-bin.000001' position: 4
060901 15:59:34 [Note] Slave I/O thread: connected to master '[email protected]:3306', replication started in log 'mysqlbakmysqllog.000002' at position 98
怎么觉得好像它先不能读取,然后又杀掉进程,然后重新生成进程,然后继续等待同步信息但是遇到同步命令时,又不能读取,又杀掉进程
这是怎么一回事儿啊?
我觉得如果把这个问题解决了,那么所有的问题都可以迎刃而解了。还有,权限的问题,我觉得不应该是权限了,理由如下:
我在192.168.1.7上运行下边的这句话
mysql> show grants for [email protected];
+-------------------------------------------------------------------------------
------------------------------------------------------------+
| Grants for [email protected]
|
+-------------------------------------------------------------------------------
------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'back'@'192.168.1.76' IDENTIFIED BY PASSWORD '*
6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |
+-------------------------------------------------------------------------------
------------------------------------------------------------+说明其已具备全部的权限。是这样吧?
(我的同步用户是back,同步db是backup,但是Waiting for master to send event 和Has read all relay log; waiting for the slave I/O thread to update 的用户是system user)
这样对吗?mysql> show processlist \G
*************************** 1. row ***************************
Id: 42
User: system user
Host:
db: NULL
Command: Connect
Time: 3092
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 43
User: system user
Host:
db: NULL
Command: Connect
Time: 3091
State: Has read all relay log; waiting for the slave I/O thread to update
Info: NULL
*************************** 3. row ***************************
Id: 44
User: back;
Host: localhost:2790
db: backup
Command: Query
Time: 0
State: NULL
Info: show processlist
*************************** 4. row ***************************
Id: 49
User: back
Host: YOUR-CC5A5138EA:1629
db: NULL
Command: Binlog Dump
Time: 109
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
4 rows in set (0.00 sec)