mysql设计与优化(二)——配置主从

mysql设计与优化(二)——配置主从

标签: 开发 数据库

背景描述:

老板让部署主从。

1 操作

1.1 安装mysql-5.7.26-1.el7.x86_64

> cd /usr/local/src/
> wget http://dev.mysql.com/get/mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar
> tar -vxf mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar
> rpm -e --nodeps mariadb-libs
> rpm -ivh mysql-community-common-5.7.26-1.el7.x86_64.rpm
> rpm -ivh mysql-community-libs-5.7.26-1.el7.x86_64.rpm
> rpm -ivh mysql-community-client-5.7.26-1.el7.x86_64.rpm
> rpm -ivh mysql-community-server-5.7.26-1.el7.x86_64.rpm

1.2 配置文件

  • 主机配置 vim /etc/my.cnf
#需添加的配置
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
server-id=1
innodb_flush_log_at_trx_commit=2
sync_binlog=1
log-bin=mysql-bin-1
  • 从机配置 vim /etc/my.cnf
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
server-id=2
innodb_flush_log_at_trx_commit=2 
sync_binlog=1 
log-bin=mysql-bin-2

1.3 主从配置

进入主机mysq;

GRANT REPLICATION SLAVE ON *.* to 'repl'@'168.160.19.35' identified by 'SEC90opl;./';
show master status;

+--------------------+----------+--------------+------------------+-------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| mysql-bin-1.000001 |      449 |              |                  |                   |
+--------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

把file列和Position列记录下来,一会配置slave要用到

从机配置

change master to master_host='168.160.17.24',master_user='repl' ,master_password='SEC90opl;./', master_log_file='mysql-bin-1.000001' ,master_log_pos=449;
start slave;
show slave status;

1.4 设置用户,授权

> systemctl start mysqld.service
#查看默认密码
mysql>> grep "password" /var/log/mysqld.log
mysql>> CREATE USER 'ecApp'@'168.160.19.%' IDENTIFIED BY 'SEC90opl;./';
mysql>> grant ALL PRIVILEGES on ecdb.* to ecApp@'168.160.19.%' identified by 'SEC90opl;./';
mysql>> flush privileges; 

1.5 开通端口

> firewall-cmd --zone=public --add-port=3306/tcp --permanent
> firewall-cmd --reload
> netstat -tunlp
#关闭防火墙
> systemctl stop firewalld.service

这样部署可以实现读写分离,从机备份主机的数据,提高安全性。

但是只能主机写,从机只能查询,而且还需要调整代码。不算最好的解决方案。

posted @ 2020-07-26 16:50  海森堡不如我侧的准  阅读(135)  评论(0编辑  收藏  举报