mysql服务器的主从配置,本来是一件很简单的事情,无奈不是从零开始,总是在别人已经安装好的mysql服务器之上 ,这就会牵扯到,mysql的版本,启动文件,等一些问题。

主服务器:192.168.1.105

从服务器:192.168.1.106

一、授权

主服务器:

Mysql> mysql –uroot –p    进入数据库
GRANT ALL PRIVILEGES ON *.* TO 'wisp'@'192.168.1.105' IDENTIFIED BY 'wisp'; //授予从服务器全部权限
Mysql> flush privileges;     刷新权限配置

赋予从机权限,有多台丛机,就执行多次

二、创建快照

主服务器:

Mysql> flush tables with read lock;      锁表(别ctrl+c)

新开一个ssh到主服务器

Shell>  tar –cvf mysql-snapshot.tar  ./*   在锁表的情况下打包主服务器上的数据文件,并复制过去从服务器

从服务器:

Shell> tar xvf mysql-snapshot.tar

主服务器:

在锁表还没退出的情况下:

Mysql> show master status;      查看主服务器上的二进制日志名和偏移量(以后有用)
Mysql> unlock tables;           解锁

三、配置文件

主服务器

在/etc/my.cnf中选择加入以下:

server-id = 1  #主机标示,整数 
log-bin=mysql-bin  #确保此文件可写
read-only       =0  #主机,读写都可以
binlog-do-db=wow  
binlog-ignore-db=mysql  

#binlog-do-db=需要备份的数据库名,可写多行  
#binlog-ignore-db=不需要备份的数据库名,可写多行

其中最重要的是server-id和log-bin
service mysql restart 重启服务器

验证配置

登录mysql> show master status; 应看到如下行

+-------------------+----------+--------------+------------------+  
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
+-------------------+----------+--------------+------------------+  
| master-bin.000001 |      468 | wow          | mysql            |  
+-------------------+----------+--------------+------------------+  

从服务器:

mysqladmin -u root shutdown -p       先停止从服务器

在/etc/my.cnf中选择加入以下几项:

server-id=2  
log-bin=mysql-bin  
master-host=172.16.0.10  
master-user=allen  
master-password=1234  
master-port=3306  
replicate-do-db=wow  
master-connect-retry=60  
# replicate-do-db=wow  需要备份的数据库名  
# replicate-ignore-db=mysql 忽略的数据库(如果需要的话)  
# master-connect-retry=60 如果从服务器发现主服务器断掉,重新连接的时间差(秒) 

需要别注意server-id务必修改为与主服务器不同的数值
必要时删除从服务器上/var/lib/mysql/master.info和relay-bin文件,否则无论my.cnf如何修改都不生效
service mysql start 启动从服务器

此时一般Slave_SQL_Running线程都会因为各种各样的原因跑不起来,所以:

设置参数:

mysql> CHANGE MASTER TO                         
             MASTER_HOST='192.168.0.105',          
             MASTER_USER='wisp',          
             MASTER_PASSWORD='wisp',        
             MASTER_LOG_FILE='mysql-bin.000045',  (用show master status查看出来)    
             MASTER_LOG_POS=947;   (用show master status查看出来) 

启动从服务器线程:mysql> START SLAVE;

验证配置

登录

mysql> show slave status\G;

确保如下关键行是Yes:

Slave_IO_State: Waiting for master to send event  
.....  
Slave_IO_Running: Yes  
Slave_SQL_Running: Yes  

如果一切顺利的话至此已经全部搞掂 *_*

四.其他事项及命令参考

++如果你想使用复制数据文件的方式来备份数据库

只要在从服务器上的mysql命令行先键入slave stop;然后复制数据库文件,复制好了,再在mysql命令行键入slave start;启动从服务器,这样就即备份了数据有保证了数据完整性,而且整个过程中主服务器的mysql无需停止。

可以在主机A中,做一些INSERT, UPDATE, DELETE 操作,看看主机B中,是否已经被修改

主服务器上的相关命令

show master status 
show slave hosts 
show logs 
show binlog events 
purge logs to ‘log_name’ 
purge logs before ‘date’ 
reset master(老版本flush master) 
set sql_log_bin= 

从服务器上的相关命令

slave start 
slave stop 
slave stop IO_THREAD //此线程把master段的日志写到本地 
slave start IO_THREAD 
slave stop SQL_THREAD //此线程把写到本地的日志应用于数据库 
slave start SQL_THREAD 
reset slave 
SET GLOBAL SQL_SLAVE_SKIP_COUNTER 
load data from master 
show slave status(SUPER,REPLICATION CLIENT) 
CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //动态改变master信息 
PURGE MASTER [before 'date'] 删除master端已同步过的日志 

同步出错时,如果被同步语句确定在从库上可以被忽略

slave stop; 
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n; 
slave start;

# 看show slave status \G中的Seconds_Behind_Master: 0为正常

五.Slave_SQL_Running:No错误的解决

检查数据库发现一台MySQL Slave未和主机同步,查看Slave状态:

mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1062
....
Seconds_Behind_Master:NULL

原因:

程序可能在slave上进行了写操作
也可能是slave机器重起后,事务回滚造成的.
解决办法I:

1.首先停掉Slave服务:slave stop

2.到主服务器上查看主机状态:
记录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='master_ip',
> master_user='user', 
> master_password='pwd', 
> master_port=3307, 
> master_log_file='mysql-bin.000020', 
> master_log_pos=135617781;
1 row in set (0.00 sec)
mysql> slave start;
1 row in set (0.00 sec)

再次查看slave状态发现:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Seconds_Behind_Master: 0

解决办法II:

mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;

使用体会:

方法一是强制性从某一个点开始同步,会有部分没有同步的数据丢失,后续主服务器上删除记录同步也会有一些错误信息,不会影响使用.

方法二不一定会有效果.

六.参考

http://www.ningoo.net/html/2007/mysql_replication_configuration.html

http://leftleg.hzpub.com/post/645/

http://blog.zhangjianfeng.com/article/705

http://koda.iteye.com/blog/462312

http://www.jb51.net/article/25656.htm

http://lihuipeng007.blog.163.com/blog/static/12108438820107725132468/

http://storysky.blog.51cto.com/628458/259280