CentOS 7 搭建 MySQL8 主从复制,读写分离

             

环境:Mac + CentOS 7.5 + MySQL 8.0

MySQL 主从复制的优点:

1、 如果主服务器出现问题, 可以快速切换到从服务器提供的服务,保证高可用性
2、 可以在从服务器上执行查询操作, 降低主服务器的访问压力
3、 可以在从服务器上执行备份, 以避免备份期间影响主服务器的服务

工作原理:


注意事项:
1、server-id 必须唯一,一般使用 ip 的后三位
2、从库 Slave_IO_Running:NO 可能原因: 帐号无权限操作
3、Can't execute the query because you have a conflicting read lock,解锁下即可 unlock tables;
4、一般只有更新不频繁的数据或者对实时性要求不高的数据可以通过从服务器查询, 实时性要求高的数据仍然需要从主数据库获得
5、修改完主从服务器的配置需要重启 mysql:service mysqld restart


本人用的是虚拟机,需要设置静态ip,参考:mac 电脑 linux 虚拟机固定 IP

请先分别安装 mysql,版本需一致,安装参考:CentOS7 使用 yum 安装 MySQL8.0 装了即可跳过

主机 A: 172.16.7.152 master
从机 B: 172.16.7.153 slave

1、先登录主机 A,在主服务器上,设置一个从数据库的账户,使用 REPLICATION SLAVE(从复制)赋予权限,如:

# 赋予从机权限,有多台从机,就执行多次。mysql8对密码要求比较严格,建议大写+小写+特殊字符+数字 8位以上
mysql>GRANT REPLICATION SLAVE ON *.* TO 'backup'@'172.16.7.153' IDENTIFIED BY 'Mysql12#$';
# 刷新权限
mysql>flush privileges;

2、 打开主机 A 的 my.cnf,输入如下:(修改主数据库的配置文件 my.cnf,开启 BINLOG,并设置 server-id 的值,修改之后必须重启 mysql 服务)

server-id = 152 #主机标示,整数一般ip后三位
log_bin = /var/log/mysql/mysql-bin.log #确保此文件可写,开启bin-log 
read-only =0 #主机,读写都可以 
binlog-do-db =test #需要备份数据,多个写多行 
binlog-ignore-db =mysql #不需要备份的数据库,多个写多行 
#可以通过mysql>show variables like 'log_%'; 验证二进制日志是否已经启动。 

3、现在可以停止主数据的的更新操作,并生成主数据库的备份,我们可以通过 mysqldump 到处数据到从数据库,当然了,你也可以直接用 cp 命令将数据文件复制到从数据库去,注意在导出数据之前先对主数据库进行 READ LOCK,以保证数据的一致性

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.19 sec)
# 然后 mysqldump 导出数据:
mysqldump -h127.0.0.1 -p3306 -uroot -p test > /data/backup/test.sql 

4、得到主服务器当前二进制日志名和偏移量,这个操作的目的是为了在从数据库启动后,从这个点开始进行数据的恢复。

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 1943 | test | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec) 

最好在主数据库备份完毕,恢复写操作。

mysql> unlock tables;
Query OK, 0 rows affected (0.28 sec)

5、将刚才主数据备份的 test.sql 复制到从数据库(navicat、phpmyadmin、命令行都可以),进行导入。

6、修改从数据库的 my.cnf,增加 server-id 参数,指定复制使用的用户,主数据库服务器的 ip,端口以及开始执行复制日志的文件和位置。打开从机 B 的 my.cnf,输入(修改之后必须重启 mysql 服务)

server-id = 153  #主机标示,整数一般ip后三位
log_bin = /var/log/mysql/mysql-bin.log #确保此文件可写,开启bin-log
replicate-do-db =test #只复制某个库,多个写多行
replicate-ignore-db=mysql #不复制某个库,多个写多行
read_only                 #设置数据库只读
# 保存退出重启mysql
service mysqld  restart

7、配置从服务器, 启动 slave 进程,进行 show salve status 验证

# 停止slave同步进程
mysql> stop slave;
# 执行同步语句 安装实际情况写
mysql> change master to 
    master_host='172.16.7.152',
    master_user='backup',
    master_password='Mysql12#$',
    master_log_file='mysql-bin.000002' ,
    master_log_pos=1493;
# 开启slave同步进程
mysql> start slave;
mysql> SHOW SLAVE STATUS\G; 


Slave_IO_Running 和 Slave_SQL_Running 必须都为Yes才表示成功。

首先因为ip写错导致失败。失败的原因一般有以下几点:
1、网络不通
2、防火墙
3、用户 密码 ip pos 等错误

8、测试

152是master,153是slave,主从刚开始都没有表,主创建了user表并且插入了id为1的一条数据,从也有该数据,测试成功。



发表评论 请登录再评论
回到顶部