主主复制失败 , 只能单向复制。反过来不可以。
在'192.168.1.1'机器上 show slave status \G
发现Slave_IO_Running:No ------------------这是不成功的愿意
Slave_SQL_Running: Yes
在'192.168.1.2'机器上 show slave status \G
Slave_IO_Running: Yes
slave_SQL_Running: Yes
所以这台机器能够复制来前面的机器的数据库。
配置基本如下
进行mysql 双向同步配置
a、 先修改原slave 服务器配置
1)配置原slave服务器/etc/my.cnf文件(红字为添加内容)
server-id = 2 从服务器ID号,不要和主ID相同
master-host = 192.168.1.1 指定主服务器IP地址
master-user = repl 制定在主服务器上可以进行同步的用户名
master-password = 123 密码
master-port = 3306 同步所用的端口
master-connect-retry=60 断点重新连接时间
replicate-ignore-db=mysql 屏蔽对mysql库的同步
replicate-do-db=db1 同步数据库名称
log-bin=/var/log/mysql/updatelog 设定生成log文件名
binlog-do-db=db1 设置同步数据库名
binlog-ignore-db=mysql 避免同步mysql用户配置,以免不必要的麻烦 2)重新启动mysql服务,创建一个同步专用账号
输入密码 ,就进入到mysql命令行中了,一般刚装好的没有密码。
[root@node1 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.22
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT,RELOAD,SUPER ON *.* TO 'replication'@'192.168.1.2' IDENTIFIED BY '123456';
#给与从服务器用户replication的同步权限
mysql> Grant ALL PRIVILEGES ON *.* TO li@'%' IDENTIFIED BY '123456';
#如果需要的话添加管理用户,通过mysql的客户端来测试同步的情况
mysql>Flush privileges;
#刷新权限,使设置生效b、 修改原master主服务器的my.cnf,添加如下内容(红色为添加部分)
log-bin=mysql-bin 启动二进制日志系统
binlog-do-db=db1 二进制需要同步的数据库名
server-id = 1 本机数据库ID 标示为主
log-bin=/var/log/mysql/updatelog #设定生成log文件名
binlog-ignore-db=mysql # 避免同步mysql用户配置,以免不必要的麻烦
master-host = 192.168.1.2 设置从原slave数据库同步更新
master-user = repl 更新用户
master-password = 123 密码
master-port = 3306 端口
replicate-do-db=test 需要更新的库
启动mysql服务
[root@node1 ~]#service mysqld restart
在node2服务器执行MySQL命令符下:
mysql> show master status;
看看有无作为主服务器的信息
+------------------+----------+-------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+-------------------+------------------+
| updatelog.000028 | 313361 |test | mysql |
+------------------+----------+-------------------+------------------+
在node1服务器执行MySQL命令下:
[root@node1 ~]#mysql #进入mysql命令行
mysql> slave stop; #先停止slave服务
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.2',MASTER_USER='replication',MASTER_PASSWORD='123456',MASTER_PORT=3306MASTER_LOG_FILE='updatelog.000028',MASTER_LOG_POS=313361;#根据上面主服务器的show master status的结果,进行从服务器的二进制数据库记录回归,达到同步的效果
mysql> slave start; #启动从服务器同步服务
c、 测试
1)在node1服务器上进入mysql命令行
[root@node1 ~]#mysql
mysql>SHOW SLAVE STATUS\
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
此处Slave_IO_Running ,Slave_SQL_Running 都应该是yes,表示从库的I/O,Slave_SQL线程都正确开启.
表明数据库正在同步。
2)在node2服务器上进入mysql命令行,用 show slave status;查看
[root@node1 ~]#mysql
mysql>SHOW SLAVE STATUS\
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
此处Slave_IO_Running ,Slave_SQL_Running 都应该是yes,表示从库的I/O,Slave_SQL线程都正确开启.表明数据库正在同步。
3)这里我找到了一个mysql的客户端。利用在mysql上建立的管理用户登陆数据库,可以直接在表中写入值,去另一个数据库上看能不能刷新出来,在那里数据库上写入的数据。下面可以下载.
在'192.168.1.1'机器上 show slave status \G
发现Slave_IO_Running:No ------------------这是不成功的愿意
Slave_SQL_Running: Yes
在'192.168.1.2'机器上 show slave status \G
Slave_IO_Running: Yes
slave_SQL_Running: Yes
所以这台机器能够复制来前面的机器的数据库。
配置基本如下
进行mysql 双向同步配置
a、 先修改原slave 服务器配置
1)配置原slave服务器/etc/my.cnf文件(红字为添加内容)
server-id = 2 从服务器ID号,不要和主ID相同
master-host = 192.168.1.1 指定主服务器IP地址
master-user = repl 制定在主服务器上可以进行同步的用户名
master-password = 123 密码
master-port = 3306 同步所用的端口
master-connect-retry=60 断点重新连接时间
replicate-ignore-db=mysql 屏蔽对mysql库的同步
replicate-do-db=db1 同步数据库名称
log-bin=/var/log/mysql/updatelog 设定生成log文件名
binlog-do-db=db1 设置同步数据库名
binlog-ignore-db=mysql 避免同步mysql用户配置,以免不必要的麻烦 2)重新启动mysql服务,创建一个同步专用账号
输入密码 ,就进入到mysql命令行中了,一般刚装好的没有密码。
[root@node1 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.22
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT,RELOAD,SUPER ON *.* TO 'replication'@'192.168.1.2' IDENTIFIED BY '123456';
#给与从服务器用户replication的同步权限
mysql> Grant ALL PRIVILEGES ON *.* TO li@'%' IDENTIFIED BY '123456';
#如果需要的话添加管理用户,通过mysql的客户端来测试同步的情况
mysql>Flush privileges;
#刷新权限,使设置生效b、 修改原master主服务器的my.cnf,添加如下内容(红色为添加部分)
log-bin=mysql-bin 启动二进制日志系统
binlog-do-db=db1 二进制需要同步的数据库名
server-id = 1 本机数据库ID 标示为主
log-bin=/var/log/mysql/updatelog #设定生成log文件名
binlog-ignore-db=mysql # 避免同步mysql用户配置,以免不必要的麻烦
master-host = 192.168.1.2 设置从原slave数据库同步更新
master-user = repl 更新用户
master-password = 123 密码
master-port = 3306 端口
replicate-do-db=test 需要更新的库
启动mysql服务
[root@node1 ~]#service mysqld restart
在node2服务器执行MySQL命令符下:
mysql> show master status;
看看有无作为主服务器的信息
+------------------+----------+-------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+-------------------+------------------+
| updatelog.000028 | 313361 |test | mysql |
+------------------+----------+-------------------+------------------+
在node1服务器执行MySQL命令下:
[root@node1 ~]#mysql #进入mysql命令行
mysql> slave stop; #先停止slave服务
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.2',MASTER_USER='replication',MASTER_PASSWORD='123456',MASTER_PORT=3306MASTER_LOG_FILE='updatelog.000028',MASTER_LOG_POS=313361;#根据上面主服务器的show master status的结果,进行从服务器的二进制数据库记录回归,达到同步的效果
mysql> slave start; #启动从服务器同步服务
c、 测试
1)在node1服务器上进入mysql命令行
[root@node1 ~]#mysql
mysql>SHOW SLAVE STATUS\
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
此处Slave_IO_Running ,Slave_SQL_Running 都应该是yes,表示从库的I/O,Slave_SQL线程都正确开启.
表明数据库正在同步。
2)在node2服务器上进入mysql命令行,用 show slave status;查看
[root@node1 ~]#mysql
mysql>SHOW SLAVE STATUS\
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
此处Slave_IO_Running ,Slave_SQL_Running 都应该是yes,表示从库的I/O,Slave_SQL线程都正确开启.表明数据库正在同步。
3)这里我找到了一个mysql的客户端。利用在mysql上建立的管理用户登陆数据库,可以直接在表中写入值,去另一个数据库上看能不能刷新出来,在那里数据库上写入的数据。下面可以下载.
如 a(test) b(test)
双向同步同一个表在'192.168.1.1'机器上 show slave status \G
发现Slave_IO_Running:No ------------------这是不成功的愿意
Slave_SQL_Running: Yes 现在已经检查不是用户问题和 文件权限问题
不知道如何解决这个问题
start slave io_thread;看看有什么效果或错误
Query OK, 0 rows affected (0.00 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected, 1 warnings(0.00 sec)
Query OK, 0 rows affected (0.00 sec)
ysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.4.150
Master_User: back
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: logbin.000003
Read_Master_Log_Pos: 216
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 98
Relay_Master_Log_File: logbin.000003
Slave_IO_Running: No-------------------错误
Slave_SQL_Running: Yes发现有问题 Slave_IO_State: ---按照正常道理 ,应该提示wait for master to send event
090805 16:31:15 [ERROR] Failed to initialize the master info structure
090805 16:31:15 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.45-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
090805 16:38:11 [Note] Slave SQL thread initialized, starting replication in log 'logbin.000003' at position 216, relay log '/var/run/mysqld/mysqld-relay-bin.000002' position: 98
090805 16:38:11 [Note] Slave I/O thread: connected to master '[email protected]:3306', replication started in log 'logbin.000003' at position 216
090805 16:38:11 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file ( server_errno=1236)
090805 16:38:11 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log
090805 16:38:11 [Note] Slave I/O thread exiting, read up to log 'logbin.000003', position 216
090805 16:39:41 [Note] Slave I/O thread: connected to master '[email protected]:3306', replication started in log 'logbin.000003' at position 216
090805 16:39:41 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file ( server_errno=1236)
090805 16:39:41 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log
090805 16:39:41 [Note] Slave I/O thread exiting, read up to log 'logbin.000003', position 216
start slave io_thread; 2台机器都是正常的
090805 16:39:41 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file ( server_errno=1236)
------------------------------------------
这里提示你“'logbin.000003' ”没有喔
在另外一台机上执行:show master status 记录下log和position
然后在这台有错误的机器上重新执行changer mater 看看是否可以(这里面命令中的log名称和position要对应前面命令查到的)
重新启动主机备份机, 结果还是不能解决问题(楼上我已经删除所有的bin.00* -relay-bin.00* master.info relay-log.info) 重启数据库还是一样
090805 17:26:16 [Warning] /usr/libexec/mysqld: ignoring option '--old-passwords' due to invalid value 'bestbrain'090805 17:26:16 InnoDB: Started; log sequence number 1 3087569759
090805 17:26:16 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=/var/run/mysqld/mysqld-relay-bin' to avoid this problem.
090805 17:26:16 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.45-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
090805 17:26:16 [Note] Slave SQL thread initialized, starting replication in log 'logbin.000001' at position 98, relay log '/var/run/mysqld/mysqld-relay-bin.000001' position: 98
090805 17:26:16 [Note] Slave I/O thread: connected to master '[email protected]:3306', replication started in log 'logbin.000001' at position 98
090805 17:26:16 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file ( server_errno=1236)
090805 17:26:16 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log
090805 17:26:16 [Note] Slave I/O thread exiting, read up to log 'logbin.000001', position 98
090805 17:26:30 [Note] Error reading relay log event: slave SQL thread was killed
090805 17:28:05 [Note] Slave SQL thread initialized, starting replication in log 'logbin.000001' at position 98, relay log '/var/run/mysqld/mysqld-relay-bin.000001' position: 4
090805 17:28:05 [Note] Slave I/O thread: connected to master '[email protected]:3306', replication started in log 'logbin.000001' at position 98
090805 17:28:05 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file ( server_errno=1236)
090805 17:28:05 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log
090805 17:28:05 [Note] Slave I/O thread exiting, read up to log 'logbin.000001', position 98
为什么还会报这个错误。
改成log-bin=mysql-bin 即可同时,你b操作里面有2项log-bin,去掉只保留一项然后再试试看看
那个只是网上的文档 但是基本一致。
头痛,还没有解决?
你打开binlog的index,看看里面有没有你错误日志里面所说的'logbin.000001' 记录吧