mysql主从复制、读写分离服务(centos)

一、目标：搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，实现主从复制

二、环境：

三、原理：

数据库之所以能进行主从复制，主要是因为二进制文件binlog的存在。多台数据库之间可以通过线程进行通信，从库不断的从主库读取binlog日志并且把内容同步到从库上。


image.png

四、配置步骤：

1. 保证两个数据库中的库和数据是一致的；

（以下为主数据库）

2. 在主数据中创建一个同步账号（可不创建使用现有的），如果仅仅为了主从复制创建账号，只需要授予REPLICATION SLAVE权限。（此账号是主从复制binlog记录使用的，不要与<从数据库>测试账号混淆） 
mysql> GRANT REPLICATION SLAVE ON *.* to 'fei'@'172.18.0.169' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

说明：

备用：
set global validate_password_mixed_case_count=0; 
set global validate_password_length=3; 
set global validate_password_special_char_count=0; 
set global validate_password_policy=0; 
set global validate_password_number_count=3;
SHOW VARIABLES LIKE 'validate_password%'; 
GRANT REPLICATION SLAVE ON *.* to 'fei'@'172.18.0.169' identified by '123';
  • 如果报1819错误表示密码太简单了，先运行set global validate_password_policy=0;设置完这句以后密码就只判断长度了，运行set global validate_password_number_count=3;
  • 查看添加的用户：
    select user,host from mysql.user;
3. 配置主数据库

1）要主数据库，你必须要启用二进制日志（binary logging），并且创建一个唯一的Server ID，这步骤可能要重启MySQL。
2）主服务器发送变更记录到从服务器依赖的是二进制日志，如果没启用二进制日志，复制操作不能实现（主库复制到从库）。
3）复制组中的每台服务器都要配置唯一的Server ID，取值范围是1到(232)−1，你自己决定取值。
4）配置二进制日志和Server ID，你需要关闭MySQL和编辑my.cnf或者my.ini文件，在 [mysqld] 节点下添加配置。
5）下面是启用二进制日志，日志文件名以“master-bin”作为前缀，Server ID配置为1，如下：

[mysqld]
log-bin=mysql-bin # 默认在是/var/lib/mysql下
server-id=1 #主库id，必须唯一
binlog-do-db=test # 要同步的库
binlog-ignore-db=mysql #不给从机同步的库(多个写多行)
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys 
expire_logs_days=7 #自动清理 7 天前的log文件,可根据需要修改
4. 重启mysql
systemctl restart mysqld
5. 查看主服务器状态：
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000003 |     8518 |     test     | mysql            |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

注意：记录好File和Position，后面要用

（以下为从数据库）

6. 配置从数据库：

1）从服务器，同理，要分配一个唯一的Server ID，需要关闭MySQL，修改好my.cnf后再重启，如下：

[mysqld]
server-id = 2 # 集群内唯一
log-bin=salve-bin
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
# read-only=true # 从库只读，但是root依然可以修改，所以需要设置非root账号进行使用

2）在从服务器里配置连接主服务器的信息：
进入mysql:

mysql> stop slave;

mysql> change master to master_host='172.18.0.202', master_port=3306, master_user='fei', master_password='123', master_log_file='master-bin.000003', master_log_pos=8515;

mysql> start slave;

说明：

  • 172.18.0.202是主服务器的id。
  • master_log_file='master-bin.000003'是主服务器的File（你主服务器查出来的是什么就写什么）。
  • master_log_pos=8515是主服务器的Position（你主服务器查出来的是什么就写什么）。
  • 每次重新启动主服务器，master_log_file和master_log_pos都会变。

3）查看状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.18.0.202
                  Master_User: fei
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 8518
               Relay_Log_File: slave-relay-bin.000020
                Relay_Log_Pos: 4020
        Relay_Master_Log_File: master-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

说明：

  • Slave_IO_Running: Yes
  • Slave_SQL_Running: Yes
    都是yes就说明成功了。

4）若 Slave_SQL_Running: no 请重复执行以下内容，直至yes：


mysql>stop slave; 
mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 
mysql>start slave;

5）需要为从库创建一个登录主库的账号

create user 'test'@'localhost' identified by '123';
grant select,insert,update,delete on test.* to 'test'@'localhost' identified by '123';
flush privileges;

五、测试

  • 主库添加信息"fei"
mysql> select * from box;
+----+---------+------+
| id | name    | flag |
+----+---------+------+
| 16 | test222 | A    |
| 17 | test111 | A    |
| 18 | fei     | A    |
+----+---------+------+
3 rows in set (0.00 sec)
  • 从库查看：
mysql> select * from box;
+----+------+------+
| id | name | flag |
+----+------+------+
| 18 | fei  | A    |
+----+------+------+

这边我们也发现，因为主从是通过binlog进行同步的，所以在同步之前的数据没有写入到当前log里面，因此也就没有办法自动进行同步了。

六、一主多从

有了主从复制的操作案例。我们进行一主多从的配置时是非常简单的。只需要按照相同的配置，再添加一台从库服务器即可。
从服务器配置如下：

server-id = 3 # 唯一
read-only=true

mysql> change master to master_host='172.18.0.171', master_port=3306, master_user='tom', master_password='123', master_log_file='master-bin.000003', master_log_pos=8515;

mysql> start slave;

为从库创建一个登录主库的账号

create user 'tom'@'localhost' identified by '123';
grant select,insert,update,delete on test.* to 'tom'@'localhost' identified by '123';
flush privileges;

搞定，按照主从复制的方法进行测试。当主库进行数据添加的时候，多个从库进行了同步的更新

七、读写分离

  • 对于mysql单实例数据库和master库，如果需要设置为只读状态，需要进行如下操作和设置：
mysql -uroot -p
mysql> show global variables like "%read_only%";
mysql> flush tables with read lock;
mysql> set global read_only=1;
mysql> show global variables like "%read_only%";
  • 将MySQL从只读设置为读写状态的命令：
mysql> unlock tables;
mysql> set global read_only=0;
  • 对于需要保证master-slave主从同步的salve库，如果要设置为只读状态，需要执行的命令为：
mysql> set global read_only=1;
  • 将salve库从只读状态变为读写状态，需要执行的命令是：
mysql> set global read_only=0;

也可以通过配置文件：

read-only=true
  • 对于数据库读写状态，主要靠 read_only=1全局参数来设定；默认情况下，数据库是用于读写操作的，所以read_only参数也是0或faluse状态，这时候不论是本地用户还是远程访问数据库的用户，都可以进行读写操作；如需设置为只读状态，将该read_only参数设置为1或TRUE状态，但设置read_only=1状态有两个需要注意的地方：
  1. read_only=1只读模式，不会影响slave同步复制的功能，所以在MySQL slave库中设定了read_only=1后，通过show slave status\G命令查看salve状态，可以看到salve仍然会读取master上的日志，并且在slave库中应用日志，保证主从数据库同步一致；
  2. read_only=1只读模式，可以限定普通用户进行数据修改的操作，但不会限定具有super权限的用户（root）的数据修改操作；在MySQL中设置read_only=1后，普通的应用用户进行insert、update、delete等会产生数据变化的DML操作时，都会报出数据库处于只读模式不能发生数据变化的错误，但具有super权限的用户，例如在本地或远程通过root用户登录到数据库，还是可以进行数据变化的DML操作；

  • 为了确保所有用户，包括具有super权限的用户也不能进行读写操作，就需要执行给所有的表加读锁的命令 flush tables with read lock;这样使用具有super权限的用户登录数据库，想要发生数据变化的操作时，也会提示表被锁定不能修改的报错。

  • 这样通过 设置set global read_only=1;和flush tables with read lock;两条命令，就可以确保数据库处于只读模式，不会发生任何数据改变，在MySQL进行数据库迁移时，限定master主库不能有任何数据变化，就可以通过这种方式来设定。

  • 但同时由于加表锁的命令对数据库表限定非常严格，如果再slave从库上执行这个命令后，slave库可以从master读取binlog日志，但不能够应用日志，slave库不能发生数据改变，当然也不能够实现主从同步了，这时如果使用 unlock tables;解除全局的表读锁，slave就会应用从master读取到的binlog日志，继续保证主从库数据库一致同步。

  • 为了保证主从同步可以一直进行，在slave库上要保证具有super权限的root等用户只能在本地登录，不会发生数据变化，其他远程连接的应用用户只按需分配为select,insert,update,delete等权限，保证没有super权限，则只需要将salve设定read_only=1模式，即可保证主从同步，又可以实现从库只读。

  • 当然设定了read_only=1后，所有的select查询操作都是可以正常进行的。

八、互为主从

互为主从时，需要注意id步长的问题，每一台只生成一个固定步长的id。这边因为是两台机器，所以步长为2。
A数据库只生产ID为 1 3 5 7 9 。。。 的数据。
B数据库只生产ID为 2 4 6 8 10 。。。。

auto-increment-increment = 2 
auto-increment-offset = 100（另外一台配置单数101）

刪掉所有日志，reset slave;
再执行flush logs;
可以保证你不会因为binlog的原因导致出错。
此时开启start slave;我们发现


image.png

两台mysql服务器按照自己预先设置的ID步长进行数据添加，ID不冲突，并且互为主从，互相复制。

九、多主多从结构

多主多从结构，其实就是在双主结构上把从库也加入进来。唯一需要注意的：从库只会读取一台主库上的binlog，而每台主库的binlog都是“残缺”的，因此需要使用

log-slave-updates=on

来促使多台主库之间更新互相之间的binlog，重启服务即可。

十、测试与思考

  1. 如果从库数据不同步，会出现什么情况？
    答：如果从库人为加入一条数据，那么同步就失效了，因此主从架构里从库一定不要写入数据。
  2. 如果同步之前，主库就有数据会怎么样？
    答：不会自动同步，只会同步binlog里面的数据。所以还是停掉主库（防止数据写入），先进行备份，再进行主从架构配置。
  3. 从库怎么设置智能只读？
    答：read-only=true可以让从库只读，但是这边依然无法限制root权限的人进行修改和写操作，一般做法是创建一个普通权限的用户，登录的时候用普通权限，这样就只能只读了。但是如果用root依然是可以修改的。
  4. 如果主库重启会怎么样？
    答：binlog的名称发生了改变。主库重启，从库会自动跟上binlog的位置。
  5. 如果从库重启会怎么样？
    答：从库会自动跟上binlog的位置。
  6. 长时间关机会倒是BInlog更新对不上。（重点）
    答：需要到日志目录下（正常是/var/lib/mysql）将所有binlog删除。删除完回到Mysql命令行执行flush logs;刷新日志。必要时reset slave;重置一些从库信息。然后再继续配置主从架构。
reset slave;
flush logs;

—————————————————————————————

问题一：如果配置都没问题，但就是看不到库和表，这里必须自己手动建立一样的库和表，之后就可以看到数据同步了。
问题二：当使用 grant 权限列表 on 数据库 to ‘用户名’@’访问主机’ identified by ‘密码’; 时会出现”……near ‘identified by ‘密码” at line 1”这个错误
  • 因为新版的的mysql版本已经将创建账户和赋予权限的方式分开了
  • 解决办法:创建账户:create user ‘用户名’@’访问主机’ identified by ‘密码’;赋予权限:grant 权限列表 on 数据库 to ‘用户名’@’访问主机’ with grant option; 
create user 'win'@'localhost' identified by '123';
grant select,insert,update,delete on test.* to 'win'@'localhost' with grant option;
问题三：Mysql主从同步Slave_IO_Running：Connecting ; Slave_SQL_Running：Yes的情况故障排除

参考：https://blog.csdn.net/csdn317797805/article/details/100932662
参考：https://blog.csdn.net/zleiw/article/details/78243316

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 205,132评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 87,802评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,566评论 0赞 338
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,858评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,867评论 5赞 368
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,695评论 1赞 282
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,064评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,705评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 42,915评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,677评论 2赞 323
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,796评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,432评论 4赞 322
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,041评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,992评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,223评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,185评论 2赞 352
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,535评论 2赞 343