mysql 的主从同步

mysql-server : 5.1.41-3ubuntu12.10

os : Ubuntu 10.04.2 LTS
 
需求:
两台数据库服务器,一台是主服务器(192.168.1.1),另一台是从服务器(192.168.1.2)。
为了保障数据安全及服务稳定,要使从服务器与主服务器的数据库labeyes内容同步。同步方向为单向:主服务器->从服务器。
在MySQL 5.1参考手册中发现MySQL数据库同步主要有两种方法:
方法1.通过数据库文件的同步来实现,这种方法在同步时需要暂停掉服务,但会比第二种方法快。
方法2.通过二进制文件的log_bin来实现同步备份,即同步操作数据库的动作,不需要暂停服务,稍慢。
 
过程:
1.设置主服务器
修改/etc/mysql/my.cnf文件,在[mysqld]下面添加
 
#设置绑定IP,以便从服务器登录,默认为127.0.0.1
bind-address            = 192.168.1.1
#设置服务器id,为1到2的32次方–1之间的一个正整数值,不要有相同的。
server-id = 1
#设置要同步的数据库
binlog_do_db = labeyes
#启动log_bin
log_bin = /var/log/mysql/mysql-bin.log
 
2.重启主服务器的mysql服务
sudo service mysql restart
 
3.查看主服务器数据库信息
登陆主服务器mysql查看Master数据库的信息:
mysql -u root -p
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      106 | labeyes      |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
 
查看到log_bin的文件,这里是mysql-bin.000001,位置是106,其实这步可忽略,只是如果要查看的时候用这个命令方便点。
 
4.导出数据库为sql命令文件
第一步是清空所有表和块写入语句,并锁定COMMIT操作。这里不要退出mysql!
第二步是启动另一个终端导出数据。
第三步是查看状态,
第四步是解除第一步的锁状态。
mysql>FLUSH TABLES WITH READ LOCK;
mysqldump --master-data=2 --single-transaction -u root -p -R labeyes >labeyes.sql
mysql>SHOW MASTER STATUS;
mysql>UNLOCK TABLES;
其实导出的sql里面也包含了log_bin文件的信息,这里更准确:
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=106;
 
5.添加同步帐户
添加一个用于同步数据的帐户3gcomet,密码为123456,限制IP为192.168.1.2
mysql> GRANT REPLICATION SLAVE ON *.* TO '3gcomet'@'192.168.1.2' IDENTIFIED BY '123456';
 
6.设置从服务器
修改/etc/mysql/my.cnf文件,在[mysqld]下面添加
server-id=2
 
7.重启从服务器的mysql服务
sudo service mysql restart
 
8.导入数据库
这里必须先建立数据库labeyes再导入
mysql> create database labeyes;
mysql -u root -p -D labeyes < labeyes.sql
 
9.设置从服务器mysql同步
如果之前有启动的,先停止
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='3gcomet', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;
mysql> start slave;
这里设置好后会在从服务器的数据库目录/var/lib/mysql中有master.info和relay-log.info这两个文件,其中master.info记录着上面设置的信息,包括密码!要注意安全!
 
10.确认从服务器在同步
查看一下slave数据库的状态,这里用\G表示以列的方式查看:
mysql> show slave status\G;
主要留意Slave_IO_Running 和 Slave_SQL_Running 是否都为Yes。如果是就没问题,便可测试同步备份是否成功了!
如果有为No的情况,那么参考http://dev.mysql.com/doc/refman/5.1/zh/replication.html#replication-problems
 
11.检查进程状态
在主服务器上,SHOW PROCESSLIST的输出看上去应为:
mysql> SHOW PROCESSLIST\G;
*************************** 1. row ***************************
     Id: 2275
   User: repl
   Host: 192.168.1.2:49245
     db: NULL
Command: Binlog Dump
   Time: 1178
  State: Has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
 
线程2275是一个连接从服务器的复制线程。该信息表示所有主要更新已经被发送到从服务器,主服务器正等待更多的更新出现。
 
在从服务器上,SHOW PROCESSLIST的输出看上去应为:
 
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 42
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 1306
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 43
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 1306
  State: Has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
 
该信息表示线程42是同主服务器通信的I/O线程,线程43是处理保存在中继日志中的更新的SQL线程。SHOW PROCESSLIST运行时,两个线程均空闲,等待其它更新。
请注意Time列的值可以显示从服务器比主服务器滞后多长时间。
 

附相关问题:

1.删除从服务器配置
只需要删除 /var/lib/mysql/master.info 后重启mysql。

2.出现Slave_SQL_Running: No的情况
可能是两种情况:1.程序可能在slave上进行了写操作。2.也可能是slave机器重起后,事务回滚造成的。
2.1重做同步一次,以免数据丢失。
2.2 可以尝试停掉slave,set GLOBAL SQL_SLAVE_SKIP_COUNTER=N;再开一下slave就可以了,这个全局变量赋值为N的意思是:这个指令跳过N个来自master的事件。这对恢复因为指令导致复制停止的是非常有效的。这个指令仅用于slave线程没有运行的情况,否则会产生错误。一般可以把N设置为1来进行调试,看看Slave_SQL_Running: No是否还出现。

3.执行语句时提示 Statement may not be safe to log in statement format.
方法1:把主服务器的 binlog_format 改成 mixed 方式。
方法2:把语句中的 limit 1去掉。

4.类型转换的1677错误
Column 2 of table 'nicsync.C$_0ecard' cannot be converted from type 'varchar(75)' to type 'varchar(25)'出现类型转换,需要进行转换设定。默认是不支持主从字段类型不一致。
默认为'',即不支持主从字段类型不一致,
其它3种类型为:
all_lossy 支持有损转换,如int–>tinyint
all_non_lossy 支持无损转换,如char(20)–>varchar(25)
all_lossy,all_non_lossy 支持所有转换

SHOW GLOBAL VARIABLES LIKE 'slave_type_conversions';

STOP SLAVE;
SET GLOBAL slave_type_conversions=ALL_NON_LOSSY;
START SLAVE;

或者在主服务器上配置

[mysqld]
init_connect = 'set global slave_type_conversions=ALL_NON_LOSSY'

# 下面这行不生效
# slave_type_conversions = "ALL_NON_LOSSY"

常用的配置项有:

init_connect = 'SET collation_connection = utf8_general_ci'
init_connect = 'SET NAMES utf8'
character-set-server = utf8
collation-server = utf8_general_ci
skip-character-set-client-handshake
slave_type_conversions = "ALL_NON_LOSSY"

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据