mysql主从搭建

---

准备搭建环境，虚拟机CentOS7
查看ip

#linux命令
ip addr

ensxx中的inet为ip

下载yum源

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

安装yum源

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

检查mysql源是否安装成功

 yum repolist enabled | grep "mysql.*-community.*"

使用yum install安装mysql

 yum install -y mysql-community-server

启动mysql

 systemctl start mysqld

查看mysql状态

 systemctl status mysqld

设置开机启动

 systemctl enable mysqld

查看mysql默认密码

 grep /var/log/mysqld.log

登陆mysql

 mysql -uroot -p

修改mysql密码

 mysql>alter user 'root'@'localhost' identified by '新密码(注意密码验证规则)';

修改root为允许远程连接

mysql>use mysql;
mysql>UPDATE user SET Host='%' WHERE User='root';
mysql>flush privileges;

退出mysql

 mysql> exit

关闭防火墙或者允许3306端口开放

systemctl stop firewalld

mysql 主存搭建

准备两台机器
192.168.0.22 主
192.168.0.17 从

两台机器分别创建数据库

CREATE DATABASE `cs` CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `cs2` CHARACTER SET utf8 COLLATE utf8_general_ci;

192.168.0.22 主机器配置

vim /etc/my.cnf

添加以下内容

 server-id=1
 #
 ##log-bin设置此参数表示启用binlog功能，并指定路径名称
 log-bin=/var/lib/mysql/mysql-bin
 sync_binlog=0
 ##设置日志的过期天数
 expire_logs_days=7
 #binlog-do-db：指定需要同步的数据库
 binlog-do-db=cs
 binlog-do-db=cs2
 #binlog-ignore-db:表示同步的时候忽略的数据库
 binlog-ignore-db=information_schema
 binlog-ignore-db=sys
 binlog-ignore-db=mysql
 binlog-ignore-db=performance_schema

赋予从库权限账号，允许用户在主库上读取日志，赋予192.168.0.17和192.168.0.20机器有File权限、REPLICATION SLAVE的权限。

mysql> grant FILE on *.* to 'root'@'192.168.0.17' identified by 'xxxx密码';
mysql> grant replication slave on *.* to 'root'@'192.168.0.17' identified by '密码';
mysql> flush privileges;

*注：以上sql中的用户是指同步的时候从库使用的用户。

执行以上sql,报错[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决方式：（重启mysql失效）

select version(),@@sql_mode;
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

配置从库

修改/etc/my.cnf

vi /etc/my.cnf

添加以下内容

 log-bin=mysql-bin
 #注意：两个从库的server-id不一样，需要唯一。
 server-id=2
 binlog-ignore-db=information_schema
 binlog-ignore-db=sys
 binlog-ignore-db=mysql
 replicate-do-db=cs
 replicate-do-db=cs2
 replicate-ignore-db=mysql
 log-slave-updates
 slave-skip-errors=all
 slave-net-timeout=60
#MySQL复制启动从库出现Slave SQL: If a crash happens的warnings信息则添加以下两行
 master_info_repository=table
 relay_log_info_repository=table

修改后重启mysql

systemctl restart mysqld

进入Slave的mysql控制台

mysql> stop slave;
mysql> change master to master_host='192.168.0.22',master_user='root',master_password='xxxx',master_log_file='mysql-bin.000004', master_log_pos=713;
mysql> start slave;

master_log_file是在Master中show master status显示的File， 而master_log_pos是在Master中show master status显示的Position。可以通过show master status 在主库（192.168.0.22这台机器的mysql）中查询

执行完以上命令，可以通过 show slave status \G 命令来查看从库配置信息
有 Slave_IO_Running: Yes 、 Slave_SQL_Running: Yes 则表示主从同步成功

若出错的话清理掉之前的配置，
stop slave;
reset slave all;
日志文件位置
var/log/mysqld.log
虚拟机克隆需注意修改mysql uuid
文件路径 /var/lib/mysql/auto.cnf
查找文件find -name auto.cnf
查看是否开启sql日志
mysql>show variables like '%general_log%';
开启日志
mysql> set global general_log=on;