MySQL主从配置（基于centos7）

安装配置MySQL 5.7

下载MySQL 5.7

下载MySQL安装源

wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

安装MySQL安装源

yum -y localinstall mysql57-community-release-el7-11.noarch.rpm

安装MySQL

yum -y install mysql-community-server

启动MySQL服务

systemctl start mysqld

设置开机启动

systemctl enable mysqld
systemctl daemon-reload

完成之后，会在/var/log/mysqld.log文件中给root生成一个临时默认密码。

vim /var/log/mysqld.log

复制此密码，使用此密码登录root

mysql -u root -p
Enter password :
Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2
Server version: 5.7.25 
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners. 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
mysql> 

修改密码
想要设置成简单密码做如下操作

mysql> set global validate_password_policy=0;

改密码长度

select @@validate_password_length;

修改密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

设置远程登录

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)

退出

exit

防火墙开放3306端口

firewall-cmd --zone=public --add-port=3306/tcp --permanent

firewall-cmd --reload

配置MySQL默认编码为utf8
修改/etc/my.cnf配置文件，在[mysql]下添加编码配置

character_set_server=utf8
init_connect='SET NAMES utf8'

:wq 保存退出
重启MySQL

systemctl restart mysqld

登录root用户查看编码

show variables like '%character%';

最后测试远程连接

主从配置

master(主)配置

修改mysql的配置文件
配置bind-address（配置成master-ip）和server-id（配置成啥都行，注意不能和从id一样）开启bin-log
配置如下：

bind-address=0.0.0.0
server-id=2
log-bin=mysql-bin

创建用于slave机器获取master机器上binlog文件的账号

grant replication slave on *.* to 'root'@'%'identified by '123456';

刷新数据库

flush privileges;

查看master状态，并记录binlog日志文件名以及position

slave(从)配置

修改mysql的配置文件
配置bind-address和server-id（配置成啥都行，注意不能和主id一样）

server-id=3

replicate-do-db = test(指定要复制的库)
replicate-ignore-db = mysql（忽略的库）

change master to master_host='192.168.x.x',master_port=3306,master='root',master_password='123456',master_log_file='mysql-bin.xxxxxx',master_log_pos=xxx;

开启从库

start slave;

验证数据库状态

show slave status \G

当从库的IO线程和SQL线程的状态都是yes时，说明主从配置成功。