Mysql主从配置

Mysql主从配置

1.下载Mysql

官网http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/

rpm -ivh http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql57-community-release-el7-10.noarch.rpm

2.yum安装

yum install mysql-community-server -y

3.启动mysql

systemctl start mysqld
systemctl stop mysqld
systemctl status mysqld
#开机自启动
systemctl enable mysqld
#查看开机启动项
systemctl list-unit-files | grep enable

4.查看账号密码

grep "password" /var/log/mysqld.log

4.登录

mysql -uroot -p密码

5.降低密码等级并设置密码

set global validate_password_policy=0;
set password for root@localhost = password('123456');

6.授权并刷新

grant all privileges on *.* to 'root'@'%' identified by '123456';
flush privileges;

7.Master节点配置

vim /etc/my.cnf
[mysqld]
#serveId同一局域网唯一
server-id=100
#开启二进制日志功能
log-bin=mysql-bin
#忽略mysql数据库
binlog-ignore-db=mysql
#为每个session分配内存，在事务中用来储存二进制文件
binlog_cache_size=1M
#主从复制格式(mixed,statement,row,默认statement)
binlog_format=mixed

8.Slave节点配置

vim /etc/my.cnf
[mysqld]
#serveId同一局域网唯一
server-id=102
#开启二进制日志功能
log-bin=mysql-slave-bin
#relay_log配置中继日志
relay_log=edu-mysql-relay-bin
#忽略mysql数据库
binlog-ignore-db=mysql
##如果需要同步函数和储存过程
log_bin_trust_function_creators=true
#为每个session分配内存，在事务中用来储存二进制文件
binlog_cache_size=1M
#主从复制格式(mixed,statement,row,默认statement)
binlog_format=mixed
#跳过主从的错误类型1062主键重复1032主从数据库不一致
slave_skip_errors=1062

9.重启mysql

systemctl restart mysqld

10.授予从服务器权限

grant replication slave, replication client on *.* to 'root'@'182.61.xx.xx' identified by '123456';
grant replication slave, replication client on *.* to 'root'@'129.226.xx.xx' identified by '123456';
#刷新
flush privileges;
#查看是否加入
select user,host from mysql.user;

11.关联服务器

#查看从哪里复制
show master status;
change master to master_host='81.70.xx.xx',master_port=3306,master_user='root',master_password='123456',master_log_file='mysql-bin.000001', master_log_pos=1858;
#启动主从复制
start slave;
show slave status\G;

12.有两个yes代表成功