mysql 主从配置

mysql 主从介绍

MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的MySQL主从是基于binlog的，主上须开启binlog才能进行主从。

主从过程大致有3个步骤

• 1）主将更改操作记录到binlog里

• 2）从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里

• 3）从根据relaylog里面的sql语句按顺序执行 主上有一个log dump线程，用来和从的I/O线程传递binlog 从上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的sql语句落地

它的一般应用场景有

• 数据的备份

• 读写分离

mysql 主从配置

准备：

• master ：mariadb 10.2 | 192.168.127.128

• slave ：mariadb 10.2 | 192.168.127.129

配置master

修改配置文件

vim /etc/my.cnf

server-id = 1     # 与从的不相等
log-bin=mysqlbin  # 二进制文件，名字可自定义
binlog-do-db=db1  # 针对某个数据库修改 用,隔开多个数据库 或者binlog-ignore-db=mysql #指定库不修改

保存退出，重启服务

service mysqld restart

修改数据库

# 配置从机
MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.127.129' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

# 开启读锁，从主上配置结束后要解锁
MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

# 查看master信息
MariaDB [(none)]> show master status;
+-----------------+----------+--------------+------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+
| mysqlbin.000001 |     1678 |              |                  |
+-----------------+----------+--------------+------------------+
1 row in set (0.01 sec)

配置slave

修改配置文件

vim /etc/my.cnf

server-id = 111   # 与主的不相等
log-bin=mysqlbin  # 二进制文件，名字可自定义
binlog-do-db=db1  # 针对某个数据库修改 用,隔开多个数据库 或者binlog-ignore-db=mysql #指定库不修改

保存退出，重启服务

service mysqld restart

修改数据库

# 先停止主从，有些mysql版本为 slave stop;
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.01 sec)

# 关键一步，根据主上'show master status'显示的配置主信息
MariaDB [(none)]> change master to master_host='192.168.127.128',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysqlbin.000001',master_log_pos=1678;
Query OK, 0 rows affected, 3 warnings (0.01 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

# 两个yes表示成功
MariaDB [(none)]> show slave status\G;
             ...
             ...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ...
            ...
            Seconds_Behind_Master: 0  # 为主从延迟的时间
            Last_IO_Errno: 0
            Last_IO_Error:
            Last_SQL_Errno: 0
            Last_SQL_Error:

测试

mysql主从的配置文件

# 主服务器上
binlog-do-db=      # 仅同步指定的库
binlog-ignore-db=  # 忽略指定库
# 从服务器上
replicate_do_db=
replicate_ignore_db=
replicate_do_table=
replicate_ignore_table=
replicate_wild_do_table=   # 如aming.%, 支持通配符% 
replicate_wild_ignore_table=

在主机上操作

MariaDB [(none)]> use test;
Database changed
MariaDB [test]> create table test ( 
    -> ID int(11) not null auto_increment, 
    -> name varchar(32) default null, 
    -> primary key (ID),
    -> ) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> insert into test(ID, name) values(1, "John");
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from test;
+----+------+
| ID | name |
+----+------+
|  1 | John |
+----+------+
1 row in set (0.00 sec)

查看从机

MariaDB [test]> select * from test;
+----+---------+
| ID | name    |
+----+---------+
|  1 | xingyys |
+----+---------+
1 row in set (0.00 sec)

主从上的数据一致了。

当从机上删除数据表或字段，又在主上执行相同的删除操作时，主从同步时从主上会因为同步错误操作主从

出现错误，这时后主从就需要修复了。

主从的简单修复

方法：在从机上

> stop slave;
> start slave;

如果以上的方法不成功，就只能重新作主从了。

一主多从和主主配置

mysql 主从架构的基础上还能作一主多从和主主架构。

一主多从

一主多从就是在主从的基础上作简单的横向添加，在主机上

MariaDB [(none)]> grant replication slave on *.* to 'repl'@'slave_ip' identified by '123456';

然后新的从机上作原来从机一样的操作。

主主

主主复制简单讲就是原来的主从上反过来在作主从配置，两台主机互为从机。

在原来主从的配置上再作主从就可以了。但还有一些不同

原来修改主的配置文件

vim /etc/my.cnf

server-id=1                       #指定master主机的id，不可为0，否则拒绝所有slave连接。
log-bin=mysqlbin                  #指定bin-log文件前缀名称，开启binlog日志
binlog_do_db=database_name        #指定binlog日志是记录的是哪个库
replicate-do-db=database_name       #指定复制哪一个库

# 下面三条不同
auto-increment-increment = 2   # 每次增长2
auto-increment-offset = 1      # 设置自动增长的字段的偏移量，即初始值为1
log-slave-updates=1            # slave执行master的sql后，将sql记录在binlog日志中（默认是不记录的）——实际生产我没开启这条

从的配置文件

vim /etc/my.cnf

server-id=1                       #指定master主机的id，不可为0，否则拒绝所有slave连接。
log-bin=mysqlbin                  #指定bin-log文件前缀名称，开启binlog日志
binlog_do_db=database_name        #指定binlog日志是记录的是哪个库
replicate-do-db=database_name       #指定复制哪一个库

# 下面三条不同
auto-increment-increment = 1   # 每次增长2
auto-increment-offset = 2      # 设置自动增长的字段的偏移量，即初始值为1
log-slave-updates=1            # slave执行master的sql后，将sql记录在binlog日志中（默认是不记录的）——实际生产我没开启这条

配置自动增长的不同防止两台数据库同时插入数据时造成主键增长错误。

其他配置相同。