mysql主从复制

mysql主从

rpm安装mysql

# 按照以下顺序进行安装，因为它们之间存在依赖关系

common --> libs --> clients --> server

安装完成修改密码

#2.执行如下命令

mysqld_safe --user=mysql --skip-grant-tables --skip-networking &

 

#3.使用root登录mysql数据库

mysql -u root mysql

 

#4.更新root密码

mysql> UPDATE user SET Password=PASSWORD( 'newpassword' ) where USER= 'root' ;

#最新版MySQL请采用如下SQL：

mysql>  UPDATE user SET authentication_string=PASSWORD('newpassword') where USER='root';

#5.刷新权限 

mysql> FLUSH PRIVILEGES;

重启

主库：

修改my.conf

server- id =1   #设置主服务器的ID

innodb_flush_log_at_trx_commit=2   #log日志同步速率，一般选择2

sync_binlog=1   #开启binlog日志同步功能

log-bin=mysql-bin-1   #binlog日志文件名

binlog_format = mixed #默认使用Statement,特许情况使用row，推荐使用mixed混合模式

注意：原来的my.cnf配置文件中默认的有server-id=*和log-bin=mysql-bin字段，需要删除掉

DB1

mysql复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

① STATEMENT模式（SBR）

每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

② ROW模式（RBR）

不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

③ MIXED模式（MBR）

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

为了保证主从数据库的数据一致，需要将主的文件手动导入从库。

mysql> FLUSH TABLES WITH READ LOCK; 锁住表

导出需要同步的库的数据

mysql> unlock tables; 解锁表

配置从服务器同步的账号

mysql>grant replication slave on *.* to user@192.168.1.64 identified by "passwd";

mysql > flush privileges ; #刷新权限

mysql>show master status ; ##查看主库的状态 file ,position这两个值很有用。要放到slave配置中

从库

my.cnf

server-id=2innodb_flush_log_at_trx_commit=2sync_binlog=1log-bin=mysql-bin-2

binlog_format = mixed 

重启

在从库执行

mysql>change master to master_host='host', master_user='user', master_password='密码', master_log_file='mysql-bin.000002', master_log_pos=1167;

#启动从库

mysql> start slave;

DB1

#查看是否发现从库

mysql> show slave hosts;

DB2

#查看主从同步的状态

mysql> show slave status\G; ###Slave_IO_Running,Slave_SQL_Running 都为Yes的时候表示配置成功

提高Mysql主从复制的效率

（1）master 端

master端有2个参数可以控制

Binlog_Do_DB : 设定哪些数据库需要记录Binlog

Binlog_Ignore_DB : 设定哪些数据库不要记录Binlog

这两项很重要，指定必要数据库，忽略不需要复制的数据库，可以减少binlog的大小，提高了I/O效率，加快网络传输。

但这两项也同样比较危险，需要谨慎使用，因为可能会有主从数据不一致和复制出错的风险。

2）slave 端

slave端有6个参数可以控制

Replicate_Do_DB : 设定须要复制的数据库，多个DB用逗号分隔

Replicate_Ignore_DB : 设定可以忽略的数据库

Replicate_Do_Table : 设定须要复制的Table

Replicate_Ignore_Table : 设定可以忽略的Table

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

Replicate_Wild_Ignore_Table : 功能同Replicate_Ig-nore_Table，可带通配符设置

slave端的配置优化效果要明显小于master端的，因为master端日志都写完了，日志也传过来了

但这几个参数可以帮助我们减少日志的应用量，因为设置了过滤，实际写入的sql数量变少了，slave端的复制也就加快了

http://www.ttlsa.com/mysql/mysql-5-7-enhanced-multi-thread-salve/

3) 在MySQL 5.7中，引入了基于组提交的并行复制（Enhanced Multi-threaded Slaves），设置参数slave_parallel_workers>0并且global.slave_parallel_type＝‘LOGICAL_CLOCK’，

即可支持一个schema下，slave_parallel_workers个的worker线程并发执行relay log中主库提交的事务。其核心思想：一个组提交的事务都是可以并行回放（配合binary log group commit）；

slave机器的relay log中 last_committed相同的事务（sequence_num不同）可以并发执行。

其中，变量slave-parallel-type可以有两个值：DATABASE 默认值，基于库的并行复制方式；LOGICAL_CLOCK：基于组提交的并行复制方式

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=16

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

复制出错处理：

    常见：1062（主键冲突），1032（记录不存在）

解决方法：

    手动处理

    跳过复制错误设置：set global sql_slave_skip_counter=1