mysql数据库同步方法

1、主库创建/etc/my.cnf,修改
里边的键值增加
server-id=1
log-bin=logbin

2、主库增加用户,用于从库读取主库日志。
mysql> grant replication slave,reload,super on *.* to 'slave'@'10.255.254.109' identified by "123456";
mysql> flush privileges;

3、从库连接主库进行测试。mysql -u slave -p123456 -h 192.168.0.1
如果连接成功说明主库配置成功

4、停从库,修改从库/etc/my.cnf,增加选项:
server-id=2
master_host=10.255.254.129
master_user=slave
master_password=123456
relay_log=/var/lib/mysql/mysql-relay-bin
relay_log_index=/var/lib/mysql/mysql-relay-bin.index

5、启动从库,进行主从库数据同步
/opt/mysql/share/mysql/mysql start
/opt/mysql/bin/mysql -u root -p 
mysql>load data from master;
说明:这一步也可以用数据库倒入或者直接目录考过来。


6、进行测试:
①主库查看当前存在的库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              |  
| test               | 
+--------------------+
3 rows in set (0.01 sec)
②从库查看当前存在库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              |  
| test               | 
+--------------------+
3 rows in set (0.01 sec)
说明两者中的数据保持了一致性
③主库创建表,
mysql> create database xxx;
Query OK, 1 row affected (0.00 sec)
打开从库,察看:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              |  
| test               |
| xxx                |       | 
+--------------------+
4 rows in set (0.01 sec)

说明主从数据库创建成功。
7、主从数据库相关命令:
slave stop; slave start ; 开始停止从数据库。
show slave status\G; 显示从库状态信息
show master status\G;显示主库状态信息
purge master logs to ’binlog.000004’; 此命令非常小心,删除主数据库没用的二进制日志文件。如果误删除,那么从库就没有办法自动更新了。
change master;从服务器上修改参数使用

另外,如果你当前操作的从库以前曾经与其他服务器建立过主从关系,你可能会发现即使你在my.cnf文件中更改了主服务器的位置,但是MSQL仍然
在试图连接就旧的主服务器的现象。发生这种问题的时候,我们可以通过清除master.info这个缓存文件或者在mysql中通过命令来进行设置。
方式如下:
a、删除master.info方法
这个文件位于数据文件存放目录里。默认是在/var/lib/mysql中的。你可以直接
将其删除,然后重新启动服务器。

b、mysql命令方法
如果你不方便重新启动服务器的话,那么就只能使用mysql命令来帮助你做到。
首先登录到主服务器上,查看当前服务器状态:
mysql> show master status\G;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73 | test | manual,mysql |
+---------------+----------+--------------+------------------+
记录下File和Position的值。然后登录从服务器,进行如下操作:
mysql> slave stop;
mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name', //主服务器的IP地址
-> MASTER_USER='replication_user_name', //同步数据库的用户
-> MASTER_PASSWORD='replication_password', //同步数据库的密码
-> MASTER_LOG_FILE='recorded_log_file_name', //主服务器二进制日志的文件名(前面要求记
录的参数)
-> MASTER_LOG_POS=recorded_log_position; //日志文件的开始位置(前面要求记录的参数)
mysql> slave start;

AB复制注意事项:
1.第一次启动slave库以后  show slave status \G;  
检查  两个线程  I/O   SQL 是否是YES状态

2.测试
添加或删除数据 (主服务器)   查看辅助的是否同步


如果不同步
    1)查看辅助的sql日志    tailf /var/log/mysqld.log
     140304 11:59:18 [Note] Slave I/O thread: connected to master '[email protected]:3306',  replication 
     started in log 'binlog.000011' at position 294

       #从服务器是否可以顺利到达主服务器
            如果发现你的用户名密码有错误  /etc/my.cnf   /var/lib/mysql/master.info

 2)查看sql线程是否报错
           140304 11:59:08 [Note] Slave SQL thread initialized, starting replication in log 'binlog.000011' at
            position 294, relay log '/var/lib/mysql/mysql-relay-bin.000001' position: 4

           手动去执行同步
                1)mysql> slave stop;
                2)mysql> CHANGE MASTER TO MASTER_HOST='192.168.18.254',MASTER_USER='slave',MASTER_PASSWORD='123456',
                MASTER_LOG_FILE='binlog.000011';

                3)  mysql > slave start;
如果出现开始同步  且AB同步后数据不一致

备份主库   去  手动同步到一致状态   再去执行手动同步中的1 ,2, 3步


需要注意的问题
MySQL Replication 大家都非常熟悉了,我也不会写怎么搭建以及复制的原理,网上相关文章非常多,大家可以自己去搜寻。我在这里就是想
总结一下mysql主从复制需要注意的地方。有人说主从复制很简单嘛,就是master,slave的server_id不一样就搞定。确实,简单的来说就是
这么简单。但是真正在生产环境我们需要注意的太多了。首先说说主库宕机或者从库宕机后复制中断的问题。

虽然很多知识点或许我博客其他文章中都有提到过,或者重复了,但是我还是想总结一下。

主库意外宕机

如果没有设置主库的sync_binlog选项,就可能在奔溃前没有将最后的几个二进制日志事件刷新到磁盘中。备库I/O线程因此也可一直处于读
不到尚未写入磁盘的事件的状态中。当主库从新启动时,备库将重连到主库并再次尝试去读该事件,但主库会告诉备库没有这个二进制日志偏
移量。解决这个问题的方法是指定备库从下一个二进制日志的开头读日志。但是一些事件将永久丢失。可以使用前面文章提到的工具来检查主
从数据一致以及修复pt-table-checksum。即使开启了sync_binlog,myisam表的数据仍然可能在奔溃的时候损坏。对于innodb表,如果
innodb_flush_log_at_trx_commit没有设置为1,也可能丢失数据,但是数据不会损坏。

因此主库的参数建议开启

sync_binlog=1
innodb-flush-log-at-trx-commit=1
MySQL 5.6版本之前存在一个bug,即当启用上述两个参数时,会使得InnoDB存储引擎的group commit失效,从而导致在写密集的环境中性
能的急剧下降。group commit是什么?这是一个知识点,那为什么sync_binlog=1,innodb-flush-log-at-trx-commit=1

会导致组提交失败?这又是一个知识点,大家可以查阅相关资料。

因此,我们常常在性能和数据一致性中做了妥协,通常将参数innodb-flush-log-at-trx-commit设置为2,而这就导致了master不再是
crash safe的,主从数据可能会不一致。关于innodb_flush_log_at_trx_commit的有效值为0,1,2。我这里简单提一下,因为很多知识
点是有连贯性的,往往提到这个问题而又涉及到另外的问题^_^

0代表当提交事务时,并不将事务的重做日志写入磁盘上的日志文件,而是等待主线程每秒的刷新。当宕机时,丢失1秒的事务。

1和2有点相同,但是不同的地方在于:1表示在执行commit时将重做日志缓冲同步写到磁盘,即伴有fsync的调用。2表示将重做日志异步写
到磁盘,即写到文件系统的缓存中。由操作系统控制刷新。因此不能完全保证在执行commit时肯定会写入重做日志文件,只是有这个动作的发生。

因此为了保证事务的ACID中的持久性,必须将innodb_flush_log_at_trx_commit设置为1,也就是每当有事务提交时,就必须确保事务都
已经写入重做日志文件。那么当数据库因为意外发生宕机时,可以通过重做日志文件恢复,并保证可以恢复已经提交的事务。而将该参数设
置为0或者2,都有可能发生恢复时部分事务的丢失。不同之处在于,设置为2时,当mysql数据库发生宕机而操作系统及服务器并没有发生宕
机时,由于此时未写入磁盘的事务日志保存在文件系统缓存中,当恢复时同样能保证数据不丢失。

对于性能与安全我们都要的情况下,我们肯定会使用RAID,并且开启Write Back功能,而且RAID卡提供电池备份单元(BBU,Battery 
Backup Unit),关于这块的知识,童鞋们可以自行查阅相关资料。

备库意外宕机:

当备库在一次非计划的关闭后重启时,会去读master.info文件以找到上次停止复制的位置。不幸的是,该文件可能并没有同步写到磁盘,
因为该信息是在缓存中,可能并没有刷新到磁盘文件master.info。文件中存储的信息可能是错误的,备库可能会尝试重新执行一些二进制
日志事件,这可能导致主键冲突,就是我们常常看见的1062错误。除非能确定备库在哪里停止(很难),否则唯一的办法就是忽略那些错误。

在从库导致复制中断有两方面的原因,即replication中的SQL thread和IO thread。首先来看SQL thread,其主要完成两个操作:

1.运行relay log中对应的事务信息
2.更新relay-info.log文件

更新relay-info.log文件是为了记录已经执行relay log中的位置,当slave重启后可以根据这个位置继续同步relay log。但是,这里
用户会发现这两个操作不是在一个事务中,一个是数据库操作,一个是文件操作,因此不能达到原子的效果。此外,MySQL数据库默认对于
文件relay-info.log是写入到操作系统缓存,因此在发生宕机时可能导致大量的已更新位置的丢失,从而导致重复执行SQL语句,最终的现
象就是主从数据不一致。MySQL 5.5新增了参数sync_relay_log_info,可以控制每次事务更新relay-info.log后就进行一次fdatasync操作,
这加重了系统负担,而且即使这样也可能存在最后一个事务丢失的情况。

IO thread用于同步master上的二进制日志,但是其在crash时依然会导致数据不一致的情况发生。IO thread将收到的二进制日志写入到
relay log,每个二进制日志由多个log event组成,所以每接受到一个log event就需要更新master-info.log。和relay-info.log一样,
其也是写入操作系统缓存,参数sync_master_info可以控制fdatasync的时间。由于IO thread的更新不能像SQL thread一样进行放到一个事
务进行原子操作,因此其是对数据一致性会产生影响,设想一个log event传送到了relay log中两次的情形。
不过好在从MySQL 5.5版本开始提供了参数relay_log_recovery,当发生crash导致重连master时,其不根据master-info.log的信息进行重
连,而是根据relay-info中执行到master的位置信息重新开始拉master上的日志数据(不过需要确保日志依然存在于master上,否则就。。。)
so,mysql 5.5版本的从库推荐配置参数:

sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1
read_only          #从库只读,但是有super权限的依然可以写入
relay_log_recovery = 1
skip_slave_start   # 默认启动从库就开启了同步,io线程和sql线程都运行了,该参数是需要手动执行start slave方可启动同步
复制过滤选项

常常看见很多同学在主库进行过滤选项设置,当然这也有好处,减少了带宽,但是在主库设置过滤选项是非常危险的操作,因为无论是显示要过滤
的或者要同步的,二进制日志只记录你设置的,其他的是不会记录的。当主库有数据需要用到binlog恢复时,你就准备哭吧。所以通常在备库进行
过滤选项设置。比如忽略某个库,同步所有库,或者同步某一个库,当然这会浪费带宽,但是和安全比起来,这点浪费不算什么。有时候安全与性
能往往需要我们自己平衡。

还有就是跨库更新,如果我们在备库是这样设置的,比如同步yayun这个库

replicate_do_db=yayun
主库记录如下:

复制代码
mysql> select *  from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | yayun |
|  2 | atlas |
|  3 | mysql |
+----+-------+
3 rows in set (0.00 sec)

mysql>
复制代码
备库记录如下:

复制代码
mysql> select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | yayun |
|  2 | atlas |
|  3 | mysql |
+----+-------+
3 rows in set (0.00 sec)

mysql>
复制代码
现在我们在主库插入一条记录

复制代码
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into yayun.t1 (name) values ('good yayun');
Query OK, 1 row affected (0.01 sec)

mysql> select  * from yayun.t1; 
+----+------------+
| id | name       |
+----+------------+
|  1 | yayun      |
|  2 | atlas      |
|  3 | mysql      |
|  5 | good yayun |
+----+------------+
4 rows in set (0.00 sec)

mysql>
复制代码
查看备库:

复制代码
mysql> select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | yayun |
|  2 | atlas |
|  3 | mysql |
+----+-------+
3 rows in set (0.00 sec)

mysql>
复制代码
怎么回事?怎么没有同步?这就是跨库更新带来的问题,比如下面的更新:

use test
insert into yayun.t1 (name) values ('good yayun')
当然你会说哪个2B会这么干啊,呵呵,有时2B还是有的。所以我们还有另外2个过滤复制参数

replicate_wild_do_table
replicate_wild_ignore_table
一个是要同步的表,一个是不同步的表,通常我们可以这样写

replicate_wild_do_table=yayun.%
表示同步yayun库下面的所有表,这样就解决的跨库更新的问题。

复制格式的问题

通常推荐使用ROW格式,为什么使用?看看我前面文章MySQL数据恢复和复制对InnoDB锁机制的影响

不要用Seconds_Behind_Master来衡量MySQL主备的延迟时间

这个后续我会写相关文章解释为什么不要用该参数衡量主备的延迟时间。



总结:

上面所提到的参数都是最大限度保证主从数据一致,以及主库宕机,从库宕机复制不会中断,但是性能会打折扣,所以需要我们自己去衡量,
或者做妥协。

results matching ""

    No results matching ""