Mysql（Mariadb）数据库主从复制

菜鸟运维笔记 2019-08-20

897

Mysql主从复制的实现原理图大致如下：

一、主从技术介绍

主从的用途

1、实时灾备，用于故障切换

2、可创建读写分离，提供更好的查询服务

3、把备份等操作都放在从服务器上进行，减少对业务的影响

主从存在的问题

1、主库宕机后，数据可能丢失

2、从库只有一个sql Thread，主库写压力大时，复制很可能延时

3、一主多从，从机不宜过多，主服务器需要同时向多台服务器中写入数据，压力会很大

主从的原理

1、从库生成两个线程，一个I/O线程，一个SQL线程；

2、i/o线程去请求主库的binlog，并将得到的binlog日志写到relay log（中继日志）文件中；主库会生成一个 log dump 线程，用来给从库 i/o线程传binlog；

3、SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致；

主从部署必要条件

主服务器：

1、开启数据库二进制日志功能；

2、配置数据库认证唯一服务id；

3、获得主库的二进制日志文件名及位置；

4、在主库上面创建一个用于主库和从库通信的用户账号，安全管理。

从服务器：

1、在从库中配置唯一服务id；

2、使用主库创建分配的用户账号读取主库的二进制日志；

3、启用slave功能，用于主从通信。

二、安装准备工作

主库（master）：192.168.1.100 /CentOS Linux release 7.5.1804 (Core)

从库（ slave ）：192.168.1.200 /CentOS Linux release 7.5.1804 (Core)

需要注意的是主从库的版本、数据最好一致。

数据库的安装工作这里不细说了

‍

三、主数据库master修改

修改数据库配置文件

找到主数据库的配置文件my.cnf，我的在/etc/my.cnf,在[mysqld]部分插入如下两行：

[mysqld]
log-bin=mysql-bin #开启二进制日志 server-id=1 #设置server-id
log-bin="/var/lib/mysql/" #设定生成的log文件名；

重启服务

systemctl restart mariadb.service

创建主从同步账户

创建用户及授权用户 用户：cainiao ,密码：passwd
MariaDB [(none)]> CREATE USER 'cainiao'@'192.168.1.200' IDENTIFIED BY 'password';#创建用户 
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'cainiao'@'192.168.1.200';#分配权限
MariaDB [(none)]>flush privileges;    #刷新权限

查看master状态，记录二进制文件名(mysql-bin.000001)和位置(492)：

MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      492 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

四、从服务器slave修改

找到my.cnf配置文件

[mysqld]server-id=2             #设置server-id，必须唯一
 log-bin="/var/lib/mysql/"      #设定生成的log文件名；

重启服务

systemctl restart mariadb.service


mysql -hlocalhost -uroot -p
MariaDB [(none)]> CHANGE MASTER TO      
    -> MASTER_HOST='192.168.1.100',     
    -> MASTER_USER='cainiao',     
    -> MASTER_PASSWORD='passwd',     
    -> MASTER_LOG_FILE='mysql-bin.000001',     
    -> MASTER_LOG_POS=492;

mysql> select * from mysql.slave_master_info \G

启动slave同步进程

MariaDB [(none)]>start slave;

查看slave状态

mysql> show slave status\G


   *************************** 1. row ***************************


              Slave_IO_State: Waiting for master to send event
              Master_Host: 192.168.1.1  //主服务器地址
              Master_User: cainiao   //授权帐户名，尽量避免使用root
              Master_Port: 3306    //数据库端口，部分版本没有此行
              Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
              Read_Master_Log_Pos: 600     //#同步读取二进制日志的位置，大于等于Exec_Master_Log_Pos
              Relay_Log_File: ddte-relay-bin.000003
              Relay_Log_Pos: 251
              Relay_Master_Log_File: mysql-bin.000004
              Slave_IO_Running: Yes    //此状态必须YES   此项为NO的话表示两个数据库之前没有建立链接
              Slave_SQL_Running: Yes     //此状态必须YES  此项为NO的话表示库之间的数据没有同步 手动同步即可
                    ......

注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。

主服务器，建立数据库，并在这个库中建表插入一条数据

MariaDB [(none)]> use test;
Database changed
MariaDB [test]> create table t1(Name varchar(18));
Query OK, 0 rows affected (0.03 sec)


MariaDB [test]> insert into t1(Name) values('cainiao');
Query OK, 1 row affected (0.01 sec)


MariaDB [test]> select * from t1;
+----------+
|   Name   |
+----------+
|  cainiao |
+----------+
1 row in set (0.00 sec)

在slave上面查看test库是否有数据同步过来

MariaDB [(none)]> use test;
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)


MariaDB [test]> select * from t1;
+----------+
|   Name   |
+----------+
|  cainiao |
+----------+
1 row in set (0.00 sec)

还可以用到的其他相关参数

master开启二进制日志后默认记录所有库所有表的操作，可以通过配置来指定只记录指定的数据库甚至指定的表的操作，具体在mysql配置文件的[mysqld]可添加修改如下选项：

# 不同步哪些数据库  
# vim /etc/my.cnf


binlog-ignore-db = mysql   
binlog-ignore-db = test   
binlog-ignore-db = information_schema




# systemctl restart mariadb.service
   # 只同步哪些数据库，除此之外，其他不同步  binlog-do-db = cainiao


# 日志保留时间
expire_logs_days = 10


# 控制binlog的写入频率。每执行多少次事务写入一次
# 这个参数性能消耗很大，但可减小MySQL崩溃造成的损失
sync_binlog = 5


# 日志格式，建议mixed
# statement 保存SQL语句
# row 保存影响记录数据
# mixed 前面两种的结合
binlog_format = mixed

在slave数据库上面操作，设置重新连接超时时间

# 停止主从同步
mysql> stop slave;


# 连接断开时，重新连接超时时间
mysql> change master to master_connect_retry=50;


# 开启主从同步
mysql> start slave;

五、完成

编写一shell脚本，监控slave的两个yes（Slave_IO及Slave_SQL进程），如发现只有一个或零个yes，就表明主从有问题了，发邮箱警报吧。

mysql

文章转载自菜鸟运维笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Mysql（Mariadb）数据库主从复制

主从的用途

评论