2台mysql数据库不能同步的问题

sxlfybb 2011-09-27 02:18:31
小弟不才，刚研究mysql同步，碰到问题，请各位大大指教。

2个服务器，单向同步。

主服务器配置：
log-bin=mysqld-bin
server-id=1
binlog-do-db=gzb

slave服务器配置：
log-bin=mysqld-bin
server-id=2
master-host=10.10.10.6 #主服务器IP
master-user=sync
master-password='123456'
master-port=3306
master-connect-retry=5
replicate-do-db=gzb
log-slave-updates


目前的情况是查看日志没有任何报错。但就是不能自动同步，在主服务器里添加数据后，slave服务器没反应。但可以通过load data from master同步。

折腾2天了，请各位老大帮忙！！！！
...全文
138 17 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
sxlfybb 2011-09-28
  • 打赏
  • 举报
 回复
经过luoyoumou兄的指导，终于成功了。配置没错，可能是我个人的原因在从表上进行了其它操作导致了不能同步，再建一个表就没问题了。感谢luoyoumou兄，结贴 。
sxlfybb 2011-09-28
  • 打赏
  • 举报
 回复
master slave:

mysql> show master status\G;
*************************** 1. row ***************************
File: mysqld-bin.000001
Position: 106
Binlog_Do_DB: gzb
Binlog_Ignore_DB:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> select * from gzb.test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 9 | gzb1 | gzb111 |
| 10 | gzb2 | gzb222 |
| 11 | gzb3 | gzb333 |
+----+----------+----------+
3 rows in set (0.00 sec)

slave server:

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.10.6
Master_User: sync
Master_Port: 3306
Connect_Retry: 5
Master_Log_File: mysqld-bin.000001
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000023
Relay_Log_Pos: 252
Relay_Master_Log_File: mysqld-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: gzb
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: 554
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

mysql> select * from gzb.test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 9 | gzb1 | gzb111 |
| 10 | gzb2 | gzb222 |
+----+----------+----------+
2 rows in set (0.00 sec)
jiahehao 2011-09-28
  • 打赏
  • 举报
 回复
单向同步，你似乎还差一步。在master端指定一个用户来同步。

1. 主从关系的同步
master端 192.168.5.10
slave端 192.168.5.4

修改master端的/etc/my.cnf文件
[mysqld]:
log-bin
server-id = 1

进入mysql

创建一个要同步的数据库abc
create database abc;

创建一个用来同步的用户,指定只能在192.168.5.4登录
grant file on *.* to bak@'192.168.5.4 identified by '123456';
grant all privileges on backup.* to bak@’192.168.5.4’ identified by ‘123456’;
flush privileges;

重启mysql

修改slave端的/etc/my.cnf文件
[mysqld]:
server_id = 2
log-bin = mysql-bin
master-host = 192.168.5.10
master-user = bak
master-password = '123456' (如果为空也要打上两个单引号)
master-port = 3306 (根据具体设置的端口号填)
master-connect-retry = 10 连接重试次数
replicate-do-db = abc 要接收的数据库名，如果有多个数据库，每个数据库一行
replicate-ignore-db= mysql 设置不要接收的数据库，每个数据库一行 （一般这条可以不写）

重启mysql
slave start;

配置成功后会在mysql目录下生成master.info,如果要更改slave设置,要先将master.info删除,才会起作用。
然后进入mysql用show slave status 或show master status 查看同步情况
luoyoumou 2011-09-28
  • 打赏
  • 举报
 回复
-- 若还有问题，请加QQ群讨论：172280340
luoyoumou 2011-09-28
  • 打赏
  • 举报
 回复
-- 如果是从库，如果你只同步某一个数据库，则显示应该类似如下：
-- 例如我下面的配置只同步 主库的 groupon 数据库：
mysql-[groupon]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.240
Master_User: repl
Master_Port: 3306
Connect_Retry: 20
Master_Log_File: mysql-bin.003121
Read_Master_Log_Pos: 6029128
Relay_Log_File: localhost-relay-bin.000561
Relay_Log_Pos: 6029273
Relay_Master_Log_File: mysql-bin.003121
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: groupon
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: 6029128
Relay_Log_Space: 6029475
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)
luoyoumou 2011-09-28
  • 打赏
  • 举报
 回复 
-- 用这样查询同步从库的状态，让人更一目了然：

mysql-[groupon]> show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.10.240

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.003121

          Read_Master_Log_Pos: 6029414

               Relay_Log_File: localhost-relay-bin.000102

                Relay_Log_Pos: 251

        Relay_Master_Log_File: mysql-bin.003121

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: 

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

              Relay_Log_Space: 453

              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)
sxlfybb 2011-09-27
  • 打赏
  • 举报
 回复
slave server:

mysql> slave stop;
Query OK, 0 rows affected (0.01 sec)

mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from gzb.test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 9 | gzb1 | gzb111 |
| 10 | gzb2 | gzb222 |
+----+----------+----------+
2 rows in set (0.00 sec)
sxlfybb 2011-09-27
  • 打赏
  • 举报
 回复
master server:

mysql> select @@version;
+------------+
| @@version |
+------------+
| 5.1.52-log |
+------------+
1 row in set (0.00 sec)

mysql> select * from gzb.test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 9 | gzb1 | gzb111 |
| 10 | gzb2 | gzb222 |
| 11 | gzb3 | gzb333 |
+----+----------+----------+
3 rows in set (0.00 sec)


slave server:

mysql> select @@version;
+------------+
| @@version |
+------------+
| 5.1.52-log |
+------------+
1 row in set (0.00 sec)

mysql> select * from gzb.test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 9 | gzb1 | gzb111 |
| 10 | gzb2 | gzb222 |
+----+----------+----------+
2 rows in set (0.00 sec)
WWWWA 2011-09-27
  • 打赏
  • 举报
 回复
没有问题嘛，两个MYSQL版本 一样吧？什么版本？
重新启动SLAVE试试
mysql> slave stop;
mysql> slave start;
sxlfybb 2011-09-27
  • 打赏
  • 举报
 回复
master server:
mysql> show processlist;
+----+------+-------------------+------+-------------+------+----------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-------------------+------+-------------+------+----------------------------------------------------------------+------------------+
| 10 | root | localhost | NULL | Sleep | 41 | | NULL |
| 13 | sync | 10.10.10.12:60436 | NULL | Binlog Dump | 880 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 14 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-------------------+------+-------------+------+----------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> show grants for sync;
+--------------------------------------------------------------------------------------------------------------+
| Grants for sync@% |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'sync'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'sync'@'%' |
| GRANT ALL PRIVILEGES ON `gzb`.* TO 'sync'@'%' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `gzb`.`gzb` TO 'sync'@'%' |
+--------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)



slave server:

mysql> show processlist;
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------+------------------+
| 32 | system user | | NULL | Connect | 15323 | Waiting for master to send event | NULL |
| 33 | system user | | NULL | Connect | 923 | Has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 37 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

mysql> show grants for sync;
\+-----------------------------------------------------------------------------------------------------+
| Grants for sync@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'sync'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `gzb`.* TO 'sync'@'%' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `gzb`.`gzb` TO 'sync'@'%' |
+-----------------------------------------------------------------------------------------------------+
3 rows in set (0.07 sec)

WWWWA 2011-09-27
  • 打赏
  • 举报
 回复
SHOW PROCESSLIST

sync用户的权限是什么
sxlfybb 2011-09-27
  • 打赏
  • 举报
 回复
slave服务器上mysql的日志：
[root@test-gzb ~]# tail -10 /var/log/mysqld.log
110927 8:01:15 [Note] Error reading relay log event: slave SQL thread was killed
110927 8:01:31 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log './mysqld-relay-bin.000001' position: 4
110927 8:01:32 [Note] Slave I/O thread: connected to master 'sync@10.10.10.6:3306',replication started in log 'FIRST' at position 4
110927 8:04:20 [Note] Slave I/O thread killed while reading event
110927 8:04:20 [Note] Slave I/O thread exiting, read up to log 'mysqld-bin.000001', position 106
110927 8:04:20 [Note] Error reading relay log event: slave SQL thread was killed
110927 8:04:20 [Note] Slave SQL thread initialized, starting replication in log 'mysqld-bin.000001' at position 106, relay log './mysqld-relay-bin.000001' position: 4
110927 8:04:20 [Note] Slave I/O thread: connected to master 'sync@10.10.10.6:3306',replication started in log 'mysqld-bin.000001' at position 106


master服务器上mysql的日志：
[root@centostest ~]# tail -10 /var/log/mysqld.log
110927 7:59:48 InnoDB: Starting shutdown...
110927 7:59:50 InnoDB: Shutdown completed; log sequence number 0 44233
110927 7:59:50 [Note] /usr/libexec/mysqld: Shutdown complete

110927 07:59:50 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
110927 08:00:23 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
110927 8:00:23 InnoDB: Started; log sequence number 0 44233
110927 8:00:23 [Note] Event Scheduler: Loaded 0 events
110927 8:00:23 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.52-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
sxlfybb 2011-09-27
  • 打赏
  • 举报
 回复
不好意思，刚才linux服务器上的putty里不能复制，换到windows上才复制出来。状态来晚了。
sxlfybb 2011-09-27
  • 打赏
  • 举报
 回复
mysql> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000001 | 106 | gzb | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


mysql> show slave status;
+----------------------------------+-------------+-------------+-------------+---------------+-------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error |
+----------------------------------+-------------+-------------+-------------+---------------+-------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Waiting for master to send event | 10.10.10.6 | sync | 3306 | 5 | mysqld-bin.000001 | 106 | mysqld-relay-bin.000004 | 252 | mysqld-bin.000001 | Yes | Yes | gzb | | | | | | 0 | | 0 | 106 | 554 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | |
+----------------------------------+-------------+-------------+-------------+---------------+-------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
1 row in set (0.00 sec)

WWWWA 2011-09-27
  • 打赏
  • 举报
 回复
show master status;
show slave status;

TKD03072010 2011-09-27
  • 打赏
  • 举报
 回复
关注....
luoyoumou 2011-09-27
  • 打赏
  • 举报
 回复 
-- 先将从库执行下面命令的结果贴出来：

show slave status\G
两台mysql数据库同步数据同步_如何在两台MySQL数据库间完成同步
泉源：/www.knowsky.com做开辟的光阴要做MySQL的数据库同步，两台安设一样的琐细，都是FreeBSD5.4，安设了Apache 2.0.55和PHP 4.4.0，MySQL的版本是4.1.15，都是而今最新的版本。1. 安设置置两台服务器，分袂安设好...
两台服务器之间mysql数据库怎么做同步_MySQL 数据库同步结构总结
2、应用场景a、可实现读写分离，通过增加从库可以提高数据库的性能，在主库实现增删改等更新操作，在从库实现查询操作，从而提高整体效率；b、提高数据安全——数据复制到从库后，从库中会有一个数据备份，一旦主库...
MySQL数据库主从双向同步
MySQL数据库主从双向同步一 环境二 主从同步1.主服务器配置2.从服务器配置二 双向同步三 后记 一 环境 由于开发需要，需要两台服务器进行负载均衡，两台服务器配置完全相同，均为windows server 2012,且MySQL数据库...
实现两个MySQL数据库之间数据同步的方案
MySQL复制是MySQL提供的一种高可用性和灾备恢复解决方案，它允许将一个MySQL数据库（称为主数据库）的数据复制到另一个MySQL数据库（称为从数据库）。通过配置适当的复制设置，可以实现主数据库的数据更改实时同步到...
mysql数据库 定时同步数据_MySQL数据库的定时同步
本文原创,转载请说明出处需求:要对内网的机器上的MySQL数据库做热备.主机,10.21.11.70.从机,10.21.11.38.每天凌晨0点从70上同步到38上.由于项目本身是点菜系统,如果用C#写程序将38上没有的菜品信息从70复制到38上,...
MySQL

56,803

社区成员

56,752

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章