mysql主从同步

来源：互联网发布：java大数据编辑：程序博客网时间：2024/06/06 02:39

1.主服务器操作：

>flush tables with read lock;

>show master status;

>另起ssh ：mysqldump –uroot –p’mysql2012’ gapi_gtv|gzip>gtv.sql.gz

>unlock tables;

2.从服务器：

>导入gtv.sql到数据库

>stop slave;

>CHANGE MASTER TO MASTER_HOST='121.12.170.237', MASTER_USER='sync_user',MASTER_PASSWORD='mysql2012', MASTER_LOG_FILE='mysql-bin.00031',MASTER_LOG_POS=552572932;

>start slave;

>show slave status;

//注：MASTER_LOG_FILE，MASTER_LOG_POS为show master status显示的file和pos

replicate-do-db=test这两句应该写在从库上

binlog-do-db=test这句要设置在主库上的。

# binary logging format - mixed recommended

binlog_format=mixed

binlog-ignore-db=mysql忽略的数据库

binlog-do-db=gvod 需要备份的数据库名

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id = 1

replicate-do-db=test #需要备份的数据库名
replicate-ignore-db=mysql #忽略的数据库

参数解释下：

server-id 这个是唯一的不能跟从服务器相同。

binlog_format 二进制文件的格式

binlog_ignore-db 忽略的数据库

binlog-do-db 要同步的数据库

设置完了以后重启数据库就可以了。

第三步从数据库：

同样修改/etc/my.cnf 在mysql 版本5.1.7 不支持master-host”类似的参数; 所以这里只要配置server-id=2 就可以了

然后登陆从数据库设置

change master to master_host='192.168.63.133', master_user='suxh', master_password='111111';

slave start;

主服务器上的相关命令：

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//用于MYISM数据库引擎，已过时；
show slave status(SUPER,REPLICATION CLIENT)

CHANGE MASTER TO MASTER_HOST='121.12.170.237', MASTER_USER='sync_user', MASTER_PASSWORD='mysql2012',MASTER_LOG_FILE='mysql-bin.00031', MASTER_LOG_POS=552572932;
PURGE MASTER [before 'date'] 删除master端已同步过的日志

大家都知道，MySQL的复制（Replication），实际上就是通过将Master端的Binlog通过利用IO线程通过网络复制到 Slave端，然后再通过SQL线程解析Binlog中的日志再应用到数据库中来实现的。所以，Binlog量的大小对IO线程以及Msater和 Slave端之间的网络都会产生直接的影响。

MySQL中Binlog的产生量是没办法改变的，只要我们的Query改变了数据库中的数据，那么就必须将该Query所对应的Event记录到Binlog中。那我们是不是就没有办法优化复制了呢？当然不是，在MySQL复制环境中，实际上是是有8个参数可以让我们控制需要复制或者需要忽略而不进行复制的DB或者Table的，分别为：

主数据库：

Binlog_Do_DB：设定哪些数据库（Schema）需要记录Binlog；

Binlog_Ignore_DB：设定哪些数据库（Schema）不要记录Binlog；

从数据库：

Replicate_Do_DB：设定需要复制的数据库（Schema），多个DB用逗号（“,”）分隔；

Replicate_Ignore_DB：设定可以忽略的数据库（Schema）；

Replicate_Do_Table：设定需要复制的Table；

Replicate_Ignore_Table：设定可以忽略的Table；

Replicate_Wild_Do_Table：功能同Replicate_Do_Table，但可以带通配符来进行设置；

Replicate_Wild_Ignore_Table：功能同Replicate_Ignore_Table，可带通配符设置；

通过上面这八个参数，我们就可以非常方便按照实际需求，控制从Master端到Slave端的Binlog量尽可能的少，从而减小Master端到 Slave端的网络流量，减少IO线程的IO量，还能减少SQL线程的解析与应用SQL的数量，最终达到改善Slave上的数据延时问题。

实际上，上面这八个参数中的前面两个是设置在Master端的，而后面六个参数则是设置在Slave端的。虽然前面两个参数和后面六个参数在功能上并没有非常直接的关系，但是对于优化MySQL的Replication来说都可以启到相似的功能。当然也有一定的区别，其主要区别如下：

如果在Master端设置前面两个参数，不仅仅会让Master端的Binlog记录所带来的IO量减少，还会让Master端的IO线程就可以减少Binlog的读取量，传递给Slave端的IO线程的Binlog量自然就会较少。这样做的好处是可以减少网络IO，减少Slave端IO线程的IO 量，减少Slave端的SQL线程的工作量，从而最大幅度的优化复制性能。当然，在Master端设置也存在一定的弊端，因为MySQL的判断是否需要复制某个Event不是根据产生该Event的Query所更改的数据

基本配置就好了。这是我的简要笔记。

参考文档：
【1】http://icooke.blog.51cto.com/4123148/764796
【2】http://www.feiyan.info/33.html

0 0