MYSQL的master/slave数据同步配置

[ 2009-10-21 12:40:26 | Author: zhenhua ]
Font Size: Large | Medium | Small
有三个地址必须调试成功,
1 从库连接主库进行测试: mysql -h192.168.200.80 -urepluser -ptestpassword 此处必须连接成功
2 show master status;必须有相关显示
3 show slave status;
必须为
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

sudo vim /etc/mysql/my.cnf mysql 配置文件
sudo /etc/init.d/mysql restart 重启 mysql

数据库服务器 Master, 主机名 :DB01, IP: 192.168.1.80

数据库服务器 Slave, 主机名 :DB02, IP: 192.168.1.103

先配置 Master 的 mysql 环境 .

修改 mysql 的配置文件 my.cnf 确保有下面的信息 , 没有加上 . 有的话修改成下面这样 . 其他的可以保持默认配置 .

[mysqld]
log-bin = mysql-bin
server-id = 1
binlog-do-db = test // 要同步的表


GRANT REPLICATION SLAVE ON *.* TO repluser@192.168.200.103 IDENTIFIED BY 'testpassword';

从库连接主库进行测试: mysql -h192.168.200.80 -urepluser -ptestpassword
此处测试应该是通过的,否则 mysql 配置有问题

------- 比如 当在另一台机器上登录 mysql 时出现如下错误:
ERROR 2003 (HY000): Can't connect to MySQL server on 'x.x.x.x' (111)
打开 /etc/mysql/my.cnf 文件,找到 bind-address = 127.0.0.1 修改为 bind-address = 0.0.0.0
-----


OK, 简单的 MySQL master 服务器配置到这里 .
重启 mysql 服务 , 登入 , 查看 master[show master status;] 状态 . 如下图 :

可通过 show master status 来监控主服务器的状态,内容如下:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 1164 | | |
+------------------+----------+--------------+------------------+
##### 其中 File 表示日志文件记录, Position 表示日志文件的位置,这个也是数据库执行复制操作的必须标识,后面两字段表示复制的数据库和不复制的数据库名,也可以在配置文件中你进行配置。
此处若不显示,说明配置也有问题。



再配置 Slave 的 mysql 环境 .
同样修改 my.cnf 配置文件 . 确保下面有如下信息 .

[mysqld] -- 最好是放在 [mysqld] 下面
server-id=2
master-host = 192.168.200.80
master-user = repluser
master-password = testpassword
master-port = 3306
master-connect-retry = 120
replicate-do-db = test
保存修改 , 重启 mysql 服务 . 登入数据库 , 可以看到 slave [show slave status;] 状态信息 . 如下图 :

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.200.80
Master_User: repluser
Master_Port: 3306
Connect_Retry: 120
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 1627
Relay_Log_File: mysqld-relay-bin.000009
Relay_Log_Pos: 1764
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
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: 1627
Relay_Log_Space: 1764
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




show slave status\G 如果发现有:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
就说明已经成功了,如果这两个选项不全是 Yes ,那就说明你钱面的某个配置错了,
我做的时候没有把主服务器重启,就出现 Slave_IO_Running: NO 。重启后好了



配置正常 .

简单的 MySQL 主从配置就 OK 了 . 当然这个是测试环境使用 . 生成环境还需要很多地方的优化配置 .



以下是一些备注,可看可不看。

stop slave reset slave start slave

----------- 今天发现 Mysql 不同步, show slave status 查看同步状态如下:
Slave_IO_Running: No
Slave_SQL_Running: Yes
使用 start slave 也不能同步。后来,先 stop slave 再 reset slave 再 start slave 就正常了。


-------------
解决办法 I :
1. 首先停掉 Slave 服务: slave stop
到主服务器上查看主机状态:
记录 File 和 Position 对应的值。


mysql> show master status;
+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000020 | 135617781 | | |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

3. 到 slave 服务器上执行手动同步:

mysql> change master to
> master_host='192.168.200.80',
> master_user='repluser',
> master_password='testpassword',
> master_port=3306,
> master_log_file='mysql-bin.000007',
> master_log_pos=98;






1. 问题:主从服务器表类型的选择

一般的共识是主服务器使用innodb,事务,行锁等功能是myisam所没有的,对修改操作而言,它更高效;从服务器使用myisam,全文检索功能是innodb所没有的,对查询操作而言,它更高效。这样就可以各尽其能。

2. 问题:主从服务器字段类型的选择

字段类型对于分页等操作有很大影响。主服务器一般是innodb,因为不涉及查询,所以可以使用varchar等来存储字符串来节省空间,从服务 器一般是 myisam,因为涉及查询,所以必须在char和varchar之间仔细权衡,没有varchar, text, blob字段的表是静态表,反之是动态表,静态表的检索效率要比动态表好若干倍,一般来说,所有涉及大结果集的查询都应该尽可能保证在静态表上完成,这里 说一个例子:比如说常见的articles表有title(varchar), body(text)等字段,在做文章列表的时候,因为不是静态表,所以查询不会很快,下面开始重构解决方案:把原来的articles表拆分成 subjects表和contents表,title字段设置为一个足够的char类型放在subjects表里,body字段还保持是text类型放到 contents表里,subjects和contents表之间的关系是一对多,这样,顺带着也方便的实现了多页文章的功能,而且更重要的是在查询文章 列表的时候,操作都是在subjects静态表里完成,效率肯定会比前一种方案提升很多。

强调:MyISAM里静态表和动态表的区别对性能影响极大,但我敢说很大一部分使用者并没有注意过这一点!如果你就是其中之一,那么我强烈建议你 再次体会一下前面说的articles分解为subjects/contents的过程,相信你熟悉了以后,下一个应用的速度会有质的提升。

3. 问题:主从服务器读写分离时读操作失败

先重现一下问题:比如说添加一条新数据,添加成功后根据last_insert_id跳转到新添加数据的浏览页面。在此过程中添加新数据的操作是 在主服务器上完成的,浏览新数据的操作实在从服务器上完成的,不过由于主从服务器间SQL同步存在延迟,所以当使用last_insert_id在从服务 器上查询的时候,从服务器很可能还没有还没来得及同步到此记录,所以读操作失败。解决思路也不复杂,在代码里加入一个缓存层(可以使用 memcached),新添加的数据都顺手放到缓存层里一份,新数据的读操作也先查询缓存层,这样就不会再有读操作失败的问题了,当然删除或者更新数据的 时候也要顺带着处理好缓存数据,可以使用观察者模式来搞定。不过这样缓存方案只限于读取单一的记录,对于读取列表的记录的情况,则是无效的。

4. 问题:主从服务器索引是否有必要保持一致

一般都是利用主从服务器完成读写分离,从服务器上进行读操作,主服务器进行写操作,这样的话,主服务器上仅保留主键,外键,唯一索引等必要的索引 即可,以便保持数据合法性,而对于那些原本用于优化SELECT操作的索引,可以全部删除,如此的话主服务器的写操作效率会提升很多。把索引保持在从服务 器上还有一个好处就是实际应用里,我们可能时常要通过ALTER TABLE去调整索引,而ALTER TABLE本身数据剧烈运动,会影响在线业务,为了规避影响,我们可以先停止一台从服务器,然后ALTER TABLE建好索引,然后再把从服务器启动,用这样的方法把所有的从服务器都建好索引,从而平滑的完成了ALTER TABLE操作。
【转自zhengdl126】
Comments Feed Comments Feed: http://www.zhenhua.org/feed.asp?q=comment&id=683
UTF-8 Encoding Trackback URL: http://www.zhenhua.org/trackback.asp?id=683

View Mode: Show All | Comments: 0 | Trackbacks: 1 | Toggle Order | Views: 9148
1
Trackback venuspoor.com
[ venuspoor.com | 2017-01-11 15:16:41 ]
MYSQL的master/slave数据同步配置 - ZhenHua 's blog
1

If you feel this site you find this information helpful, please click on the donation, which is voluntary,Thank you.
Post Comment
Smilies
[arrow] [biggrin] [confused] [cool]
[cry] [eek] [evil] [exclaim]
[frown] [idea] [lol] [mad]
[mrgreen] [neutral] [question] [razz]
[redface] [rolleyes] [sad] [smile]
[surprised] [twisted] [wink]
Enable UBB Codes
Auto Convert URL
Show Smilies
Hidden Comment
Username:   Password:   Register Now?
Security Code * Please Enter the Security Code