mysql设计与优化（二）——配置主从

mysql设计与优化（二）——配置主从

标签： 开发 数据库

目录

• mysql设计与优化（二）——配置主从
• 1 操作
  • 1.1 安装mysql-5.7.26-1.el7.x86_64
  • 1.2 配置文件
  • 1.3 主从配置
  • 1.4 设置用户，授权
  • 1.5 开通端口

背景描述：

老板让部署主从。

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

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

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