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端的复制也就加快了
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