centos7+mysql5.7主从配置、mycat读写分离

最新推荐文章于 2024-11-12 11:10:09 发布

yarbrough1996

最新推荐文章于 2024-11-12 11:10:09 发布

阅读量445

点赞数

分类专栏： mysql 读写分离文章标签： mysql mycat

本文链接：https://blog.youkuaiyun.com/qq_36696616/article/details/102956128

版权

本篇文章记录我们主从配置及读写分离配置步骤

主服务器： 114.***.182.128

从服务器： 114..182.62 119..218.237

前提：三台服务器mysql5.7已经安装完成

主从配置步骤

1. 主服务器配置

新增主从配置 vi /etc/my.cnf

[mysqld]

#replication

server-id=1
log-bin=mysql-bin.log
read-only = 0 #读写都可以
binlog-do-db = hm_advertise #只复制某个库
binlog-do-db = hm_common
binlog-do-db = hm_num
binlog-do-db = hm_order
binlog-do-db = hm_user
#innodb_flush_log_at_trx_commit=1
#sync_binlog=1

配置完成后，重启主库的mysql
service mysqld restart
再登录mysql
mysql -uroot -ptoot
(ERROR 1045 (28000): Access denied for user ‘tkuser’@’%’ (using password: YES)) 如果有类似错误请切换到root用户
给从库mysql用户赋予slave权限（前提：从库有以下用户且有远程权限）
mysql> grant replication slave on *.* to 'mysql237'@'119.3.***.237' identified by 'mysql237'; Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> grant replication slave on *.* to 'mysql62'@'114.116.***.62' identified by 'mysql62'; Query OK, 0 rows affected, 1 warning (0.01 sec)
查看主库状态

mysql> show master status;
±-----------------±---------±-----------------------------------------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-----------------------------------------------±-----------------±------------------+
| mysql-bin.000026 | 526283 | hm_advertise,hm_common,hm_num,hm_order,hm_user | | |
±-----------------±---------±-----------------------------------------------±-----------------±------------------+
1 row in set (0.00 sec)

2.从服务器配置

vi /etc/my.cnf 把以下内容粘到mysqld下。 server_id我的两个从数据库一个是2，一个是3，其余都一样。

#replication
server-id = 2
log_bin = mysql-bin.log
replicate-do-db = hm_advertise #只复制某个库
replicate-do-db = hm_common
replicate-do-db = hm_num
replicate-do-db = hm_order
replicate-do-db = hm_user
#replicate-ignore-db=mysql #不复制某个库

分别重启mysql

[root@ecs-s6-xlarge-2-win-20191105114342 lib]# service mysql restart
Shutting down MySQL… [ OK ]
Starting MySQL. [ OK ]

登录mysql ,从库设置master

[root@ecs-s6-xlarge-2-win-20191105114342 lib]# mysql -umysql237 -pmysql237

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql> change master to master_host='114.116.***.128', master_user='mysql237' ,master_password='mysql237'' Query OK, 0 rows affected, 2 warnings (0.03 sec)

[root@ecs-s6-xlarge-2-win-20191105116655 lib]# mysql -umysql62 -pmysql62

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>