主从数据库

安装包安装主从

主库可以读写，从库只能读,从库可以有多个，但是server_id不能一样,主从版本最好一样，或者从库版本高于主库版本

1.创建一个同步账号 （也可以用默认的root）

create user '用户名'@'%' identified by '密码';

授予REPLICATION SLAVE权限：

GRANT REPLICATION SLAVE ON *.* TO '用户吗'@'%';

 

2.要配置主数据库，必须要启用二进制日志，并且创建一个唯一的Server ID，

打开mysql的配置文件并编辑（位置/etc/my.cnf），增加如下内容

放在[mysqld]面  放在[mysql_safe]里面没效果

log_bin=master-bin #启动MySQL二进制日志

log_bin_index = master-bin.index

server-id=1  #服务器唯一标识

expire-logs-days=7 #二进制日志的有效期

binlog_ignore_db=mysql #不需要同步的数据库

binlog_ignore_db=information_schema

binlog_ignore_db=performation_schema

binlog_ignore_db=sys

binlog_do_db=mybatis #需要同步的数据库名字

sync_binlog=0

 

Mysql配置参数sync_binlog说明

MySQL提供一个sync_binlog参数来控制数据库的binlog刷到磁盘上去。

默认，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。

如果sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，同样很大的影响MySQL和IO性能。虽然可以通过group commit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

所以很多MySQL DBA设置的sync_binlog并不是最安全的1，而是100或者是0。这样牺牲一定的一致性，可以获得更高的并发和性能。

 

重启mysql服务，查看主服务器状态：

show master status;

mysql> show master status;

+-------------------+----------+--------------+--------------------------------------------+-------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB                           | Executed_Gtid_Set |

+-------------------+----------+--------------+--------------------------------------------+-------------------+

| master-bin.000001 |      120 | hanby        | information_schema,performation_schema,sys |                   |

+-------------------+----------+--------------+--------------------------------------------+-------------------+

1 row in set (0.00 sec)

3.从数据库配置

同样编辑配置文件my.cnf，插入如下内容

server-id = 2

relay-log = slave-relay-bin

relay-log-index = slave-relay-bin.index

指定数据库

replicate-do-db=****(数据库)

============================================

#如果库名相同，使用这个                                            =

#replicate-do-db=kintech_pd                                     =

#如果库名不同，使用这个别名。                                   =

#replicate-rewrite-db = kintech_pd -> kps_common     =

============================================

指定表

replicate-do-table=****.***(数据库.表名)

 

 

 

重启mysql服务，在slave服务器中登陆mysql，连接master主服务器数据库（参数根据实际填写）

change master to master_host='192.168.17.150',master_port=3306,master_user='root',master_log_file='master-bin.000015',master_log_pos=120（上面Position的值）;

 

（重要）这里会报错ERROR 1794 (HY000): Slave is not configuredor failed to initialize properly. You must at least set --server-id to enableeither a master or a slave. Additional error messages can be found in the MySQLerror log.

 

具体原因目前不详，网上查找到的资料:数据库打开这几张表的默认引擎为MyISAM，但是这几张表在建表时的引擎为INNODB

 

解决方法：

 

登录数据库，进入mysql库，执行如下SQL删除5张表

记住，一定要是drop table if exists

drop table if exists innodb_index_stats;

drop table if exists innodb_table_stats;

 

drop table if exists slave_master_info;

drop table if exists slave_relay_log_info;

drop table if exists slave_worker_info;

执行完后，可以用show tables查看一下，看表的数据是否已经比删除之前减少了，如果减少了，说明你成功了！

[root@data02 test] cd/home/data/mysql/data/mysql

[root@data02 mysql]# ll *.ibd

-rw-rw----. 1 mysql mysql 98304 1? 9 02:08 innodb_index_stats.ibd

-rw-rw----. 1 mysql mysql 98304 1? 9 02:08 innodb_table_stats.ibd

-rw-rw----. 1 mysql mysql 98304 1? 9 02:08 slave_master_info.ibd

-rw-rw----. 1 mysql mysql 98304 1? 9 02:08 slave_relay_log_info.ibd

-rw-rw----. 1 mysql mysql 98304 1? 9 02:08 slave_worker_info.ibd

[root@data02 mysql]#

强行删除ibd文件：

[root@data02 mysql]# rm -f *.ibd

重启数据库，登录mysql

find -name mysql_system_tables.sql 找到这个文件

source  路径/mysql_system_tables.sql

show tables;

发现表已经回来了，表数据大概总数量为28个。

重新执行就可以

change master to master_host='192.168.17.139',master_port=3306,master_user='root',master_log_file='master-bin.000003',master_log_pos=239（上面Position的值）;

启动slave

start slave;

测试主从是否配置成功

主从同步的前提必须是两个数据库都存在，本案例中我们需要建好两个名为hanby的数据库

主库创建表，从库也会创建，主库的增删改操作都会自动同步。

 

从库不要执行删除，不然就没效果了。

锁定主数据库，只允许读取不允许写入

mysql> flush tables with read lock;

执行过程

1、主库将变更写入到主库的binlog中

一些MySQL版本并不会开启二进制日志，所以一定要检查是否开启；

如果刚开始没有开启，后面再进行开启的话，需要重启数据库才能生效，而且数据库的重启往往会对业务造成很大的影响；

尽管二进制日志对性能有稍许的影响，所以还是建议大家无论是否使用复制功能，都要开启MySQL二进制日志，因为增量备份也需要二进制日志。

2、从库的IO线程在指定位置读取主库binlog内容存储到本地的中继日志（Relay Log）中

要完成二进制日志的传输过程，MySQL会在从服务器上启动一个工作线程，称为IO线程，这个IO线程会跟主数据库建立一个普通的客户端连接，然后在主服务器上启动一个特殊的二进制转储线程称为binlogdown线程。

从库上的IO线程通过这个二进制转储线程来读取主库上的二进制事件，如果该事件追赶上主库，则会进入sleep状态，直到主库发起信号通知有新事件产生时，才会被唤醒，relay log的格式和binlog格式是完全相同的，

可以使用mysqlbinlog来读取relay log中的内容。

3、从库的SQL线程读取Relay Log日志中的内容，并在从库中重放

SQL线程所执行的事件，我们可以通过配置选项来决定是否要写入到从服务器的二进制日志中。

目前MySQL支持两种复制类型：

基于二进制日志点的复制

基于GTID的复制（MySQL>=5.7推荐使用）

扩展

当只针对某些库的某张表进行同步时，如下，只同步huanqiu库的haha表和huanpc库的heihei表：

replicate-do-db = huanqiu

replicate-wild-do-table = huanqiu.haha       //当只同步几个或少数表时，可以这样设置。注意这要跟上面的库指定配合使用；

replicate-do-db = huanpc

replicate-wild-do-table = huanpc.heihei      //如果同步的库的表比较多时，就不能这样一一指定了，就把这个选项配置去掉，直接根据指定的库进行同步。

关于增删改查，主从数据不一致问题：

#select 语句，暂时没有发现问题

#insert 语句，暂时没有发现问题

#update 语句，暂时没有发现问题

#delete 语句，主库删除多条数据，发现数据不一致

原因：在主库的logbin中的确有执行删除语句，但是在从库的logbin中却没有删除语句

解决：使用 use database 选取当前数据库架构中的需要操作的数据库，然后在执行删除，OK同步成功

查询binlog主从日志的方法

#查看binlog全部文件

mysql>show binary logs;

#查看binlog是否开启NO为开启

mysql> show variables like 'log_bin%';

#详细信息

mysql>  show variables like 'binlog%';

#查看binlog日志

mysql> show binlog events in'mysql-bin.000019';

#或者使用mysqlbinlog，如果报错使用--no-defaults（使用全路径）

[root@localhost ~]# /usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000019　　

手动清理master日志，最好关闭日志，在/etc/my.cnf

#手动刷新日志

mysql> show master status;

#删除全部mysql> reset slave;或 rest master;#删除MySQL-bin.010mysql> PURGE MASTER LOGS TO 'MySQL-bin.010';

 

./configure --prefix=/usr/nginxfdfs --sbin-path=/usr/nginxfdfs/sbin/nginx --conf-path=/data/nginx/conf/nginx.conf --error-log-path=/data/nginx/log/error.log --http-log-path=/data/nginx/log/access.log --pid-path=/data/nginx/log/pid/nginx.pid --lock-path=/data/nginx/log/lock/subsys/nginx --with-http_ssl_module --with-http_realip_module --with-http_addition_module --with-http_sub_module --with-http_dav_module --with-http_flv_module --with-http_gzip_static_module --with-http_stub_status_module --with-http_perl_module --with-ld-opt="-Wl,-E" --with-http_image_filter_module --add-module=/usr/local/src/fastdfs-nginx-module/src/

 

 

 

show master status;

GRANT REPLICATION SLAVE ON *.* TO 'root'@'%';

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'%';

 

CHANGE MASTER TO master_host='192.168.0.12', master_port=3306, master_user='root', master_password='admindev123$%^', master_log_file = 'mysql-bin.000001', master_log_pos = 1020;

start slave;

stop slave;

show slave status;