Mysql主从复制

本身这个不应该讲解的,但是我准备讲解分库分表,那这个问题就需要讲解了

主从配置需要注意的点

(1)主从服务器操作系统版本和位数一致;

(2) Master 和 Slave 数据库的版本要一致;

(3) Master 和 Slave 数据库中的数据要一致;

(4) Master 开启二进制日志,Master 和 Slave 的 server_id 在局域网内必须唯一;

注意:两台数据库服务器的的 selinux 都要 disable(永久关闭 selinux,请修改/etc/selinux/config , 将 SELINUX 改为 disabled)

修改主库配置/etc/my.cnf

# 在 [mysqld] 中增加以下配置项 
# 设置 server_id,一般设置为 IP 
server_id=192.168.168.10
# 复制过滤:需要备份的数据库,输出 binlog
#binlog-do-db=sulwan
# 复制过滤:不需要备份的数据库,不输出(mysql 库一般不同步) 
binlog-ignore-db=mysql 
# 开启二进制日志功能,可以随便取,最好有含义 
log-bin=sulwan-mysql-bin 
## 为每个 session 分配的内存,在事务过程中用来存储二进制日志的缓存 
binlog_cache_size=1M 
## 主从复制的格式(mixed,statement,row,默认格式是 statement)
binlog_format=mixed
# 二进制日志自动删除/过期的天数。默认值为 0,表示不自动删除。 
expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误,避免 slave 端复制中断。
# 如:1062 错误是指一些主键重复,1032 错误是因为主从数据库数据不一致
slave_skip_errors=1062

复制过滤可以让你只复制服务器中的一部分数据,有两种复制过滤: (1) 在 Master 上过滤二进制日志中的事件; (2) 在 Slave 上过滤中继日志中的事件.

MySQL 对于二进制日志 (binlog)的复制类型

(1) 基于语句的复制:在 Master 上执行的 SQL 语句,在 Slave 上执行同样的语句。MySQL 默 认采用基于语句的复制,效率比较高。一旦发现没法精确复制时,会自动选着基于行的复制。 (2) 基于行的复制:把改变的内容复制到 Slave,而不是把命令在 Slave 上执行一遍。从MySQL5.0 开始支持。 (3) 混合类型的复制:默认采用基于语句的复制,一旦发现基于语句的无法精确的复制时,就会采用基于行的复制。

创建同步用户:

MySQL [(none)]> grant replication slave, replication client on *.* to 'repl'@'192.168.168.11' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

MySQL [(none)]> flush privileges;
Query OK, 0 rows affected (0.02 sec)

MySQL [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000040 |      622 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
备注:记录Position值,从库时需要使用

修改主库配置/etc/my.cnf

# 在 [mysqld] 中增加以下配置项 
# 设置 server_id,一般设置为 IP 
server_id=11
# 复制过滤:需要备份的数据库,输出 binlog
#binlog-do-db=sulwan
# 复制过滤:不需要备份的数据库,不输出(mysql 库一般不同步) 
binlog-ignore-db=mysql 
# 开启二进制日志功能,可以随便取,最好有含义 
log-bin=sulwan-mysql-slave-bin 
## 为每个 session 分配的内存,在事务过程中用来存储二进制日志的缓存 
binlog_cache_size=1M 
## 主从复制的格式(mixed,statement,row,默认格式是 statement)
binlog_format=mixed
# 二进制日志自动删除/过期的天数。默认值为 0,表示不自动删除。 
expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误,避免 slave 端复制中断。
# 如:1062 错误是指一些主键重复,1032 错误是因为主从数据库数据不一致
slave_skip_errors=1062
## relay_log 配置中继日志 (这个是比主库配置多的)
relay_log=sulwan-mysql-relay-bin 
## log_slave_updates 表示 slave 将复制事件写进自己的二进制日志 
log_slave_updates=1
## 防止改变数据(除了特殊的线程)
read_only=1
如果Slave为其它的Slave的Master时,必须设置bin_log,在这里,我开启了二进制日志,而且显式的命名(默认名称为hostname),但是如果hostname改变则会出现问题。)
relay_log配置中继日志,log_slave_updates表示slave将复制事件 写进自己的二进制日志.当设置log_slave_updates时,你可以让slave扮演其它slave的master.此时,slave把sql线程执行的事件写进自己的二进制日志(binary log)然后,它的slave可以获取这些事件并执行它。

配置用户参数

change master to master_host='192.168.168.10',master_user='repl',master_password='123456',master_port=3306,
master_log_file='mysql-bin.000042',master_log_pos=622,master_connect_retry=30;

上面执行的命令的解释: master_host=‘192.168.168.10’ ##Master的IP地址 master_user=‘repl’ ##用于同步数据的用户(在Master中授权的用户) master_password=‘123456’ ##同步数据用户的密码 master_port=3306 ##master数据库服务的端口 master_log_file=‘mysql-bin.000042’ ##指定Slave从哪个日志文件开始读取复制文件(可在Master上使用show master status查看到日志文件名) master_log_pos=622 ##从哪个POSITION号开始读 master_connect_retry=30 #当重新建立主从连接时,如果连接建立失败,间隔多久后重试,单位为秒,默认设置为60秒,同步延迟调优参数。

##查看主从同步状态 show slave status\G;

启动同步

mysql> start slave;

查看启动状态

mysql> show slave status\G;

主从常用命令

start slave; // 启动从IO进程
stop slave; // 关闭从IO进程
show master status\G;
show slave status\G
show variables like 'server_id';

常规错误

1、从库上写的主库信息一定要和主库一致 2、mysql数据库安装文件auto.cnf中的server-uuid两个库一定不能一样。