这篇文章为自己动手后的操作记录,以下是我这次主从配置的过程,请大家多多指教:
环境:
1. 两台windows7笔记本。
2. 安装数据库版本,使用 select version();
看到主库上是:
mysql> select version();
+-------------------------+| version() |+-------------------------+| 5.0.45-community-nt-log |+-------------------------+1 row in set
同样,从库上是:
mysql> select version();
+---------------------+| version() |+---------------------+| 5.0.45-community-nt |+---------------------+1 row in set
配置步骤:
1. 修改主库机器上的my.ini文件:
末尾增加:
#主库的serverId 还不清楚怎么使用。但不能和从库相同
server-id=559#设置bin.log文件地址,但是我本地生成的不是log-bin.log而是log-bin.000001 ,log-bin.000002 等等文件log-bin = D:\log-bin.log#设置一个错误日志,和bin.log一样会在启动的时候更新log-error=D:\log-err.log
2. 在主库上增加从库的用户信息:
grant replication slave on *.* to 'slave_test'@'10.28.164.123' identified by 'slave_test';
3. 修复从库上的my.ini文件:
#从库的serverId
server-id=558#配置主库的ip地址master-host=10.28.162.35#从库连接主库的用户名master-user=slave_test#密码master-password=slave_test#尝试次数master-connect-retry=30#主库的库名replicate-do-db=master_db
配置好后,各自重启mysql服务:
主库上查看状态
mysql> show master status;
+----------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| log-bin.000003 | 13484 | | |
+----------------+----------+--------------+------------------+
1 row in set
+----------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| log-bin.000003 | 13484 | | |
+----------------+----------+--------------+------------------+
1 row in set
从库上查看状态:
+----------------------+--------------+--------------+-------------+---------------+-----------------+---------------------+--------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| 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 |
+----------------------+--------------+--------------+-------------+---------------+-----------------+---------------------+--------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Connecting to master | 10.28.162.35 | slave_test | 3306 | 30 | | 4 | slave-relay-bin.000003 | 98 | | No | Yes | cms | | | | | | 0 | | 0 | 0 | 98 | None | | 0 | No | | | | | | NULL |
+----------------------+--------------+--------------+-------------+---------------+-----------------+---------------------+--------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set
| 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 |
+----------------------+--------------+--------------+-------------+---------------+-----------------+---------------------+--------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Connecting to master | 10.28.162.35 | slave_test | 3306 | 30 | | 4 | slave-relay-bin.000003 | 98 | | No | Yes | cms | | | | | | 0 | | 0 | 0 | 98 | None | | 0 | No | | | | | | NULL |
+----------------------+--------------+--------------+-------------+---------------+-----------------+---------------------+--------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set
注意:
Slave_IO_Running | Slave_SQL_Running
No | Yes
正常情况这里应该是两个Yes,并且Master_Log_File 字段为空的(这里需要配置)
网上说的情况有两种:
1. 从库的用户没有权限。
2. 主库的binlog文件不存在。
我检查后发现没有这两种情况,于是 我重启slave 一次执行:
1. slave stop ;命令
2. change master to Master_Log_File='log-bin.000003',Master_Log_Pos=98; #将日志文件重新设置一次,对应于 show master status 看到的File字段。
3. slave start;
还是不行。于是我重新修改了master的日志文件:
mysql> change master to Master_Log_File='log-bin.000004',Master_Log_Pos=98;
Query OK, 0 rows affected
mysql> show master status;
+----------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| log-bin.000004 | 8774 | | |
+----------------+----------+--------------+------------------+
1 row in set
mysql> show master status;
+----------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| log-bin.000004 | 8774 | | |
+----------------+----------+--------------+------------------+
1 row in set
然后再修改从库的 Master_Log_File:
1. slave stop ;命令
2. change master to Master_Log_File='log-bin.000004',Master_Log_Pos=98; #将日志文件重新设置一次
3. slave start;
再看slave状态:
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 |
+----------------------------------+--------------+-------------+-------------+---------------+-----------------+---------------------+--------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | 10.28.162.29 | root | 3306 | 30 | log-bin.000004 | 8774 | CDYJY-ZHANGSB-relay-bin.000002 | 8909 | log-bin.000004 | Yes | Yes | cms | | | | | | 0 | | 0 | 8774 | 8909 | None | | 0 | No | | | | | | 0 |
+----------------------------------+--------------+-------------+-------------+---------------+-----------------+---------------------+--------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set
全部是Yes,状态正常了。
这个时候,主库插入数据,从库就可以正常同步啦~~~!
值得注意的几个问题:
可以看到 log-err.log后有这么一句话:
130801 15:12:02 [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=D:\Program Files\Database\MySQL\Data\xxxx-relay-bin' to avoid this problem.
解释如下:
master(binlog)->relay log(slave io线程)->db data(salve sql线程)
所以这些日志就放到relay log
然后从服务器慢慢执行master的二进制日志 传递给从服务器执行
从服务器需要一个缓冲吧
其中relay log是放在主库本地的。
这次只是简单的配置了主从同步,对于Mysql的大部分日志文件的用处还不是非常理解。需要在深入学习下。
相关推荐
mysql主从同步配置 mysql主从同步配置 mysql主从同步配置
详细描述MySQL主从同步配置过程
mysql主从同步配置,要实现 MySQL 的 Replication ,首先必须打开 Master 端的Binary log(mysql-bin.xxxxxx)功能,否则无法实现。因为整个复制过程实际上就是Slave从Master端获取该日志然后再在自己身上完全按顺序的...
Linux-Mysql主从同步详解...公司内部教学!!!!!!!
12.1: MySQL主从同步 、 主从同步模式 、 总结和答疑.docx
win系统下的mysql主从同步配置,非常详细,图文并茂.用来教学非常合适.对mysql只是稍微懂一点都可以配置
手把手说明如何在linux下配置MySQL的主从同步,windows下同样适宜,仅与Linux系统中的MySQL配置文件名不同而已
MySQL主从同步配置图文详解(案例).pdf 学习资料 复习资料 教学资源
超详细MySQL主从配置和主主同步总结。。。。。。。。。
mysql主从同步配置详解、一台服务器两个mysql,本地安装两个mysql实现主从同步
详细介绍windows下mysql主从同步备份步骤,简单,明了
希望能帮助读者朋友们解决关于主从同步中的一些疑问。八怪写作风格很是严谨,几乎每篇都是从源码入手去剖析MySQL主从复制相关的知识点。 强烈推荐大家订阅本专栏 业界大咖推荐序 从2016年开始八怪(本名高鹏)经常...
dble配置mysql主从同步
mysql主从同步及canal配置详解.docx
Mysql主从同步备份策略 五月 18th, 2009 at 17:30 - 3,803 views 环境: 主从服务器上的MySQL数据库版本同为5.1.34 主机IP:192.168.0.1 从机IP:192.168.0.2 一. MySQL主服务器配置 1.编辑配置文件/etc/my.cnf # ...
MySQL主从同步与读写分离配置图文详解
Mysql5.1.7以上版本主从同步配置方法
主要介绍了CentOS服务器环境下MySQL主从同步配置方法,较为详细的分析了CentOS服务器环境下MySQL主从同步的配置操作步骤、相关命令、使用方法与注意事项,需要的朋友可以参考下