Mysql主从同步

一 环境准备

• 操作系统:CentOS Linux release 7.6.1810
• mysql版本:5.7.12
• 机器规划

名称	ip	角色
mysq-01	10.XXX.X.1	master
mysq-02	10.XXX.X.2	salve

二 安装mysql

• 下载

• 查看已有mysql

rpm -qa | grep mysql

• 如果有删除

rpm -e XXX

• centos7 默认安装mariadb 会和mysql冲突 先卸载

yum -y remove mari*

• 安装依赖

yum -y install make gcc-c++ cmake bison-devel ncurses-devel libaio libaio-devel

• 解压

tar xvf mysql-5.7.12-1.el6.x86_64.rpm-bundle.tar

• 安装

rpm -ivh mysql-community-common-5.7.12-1.el7.x86_64.rpm

rpm -ivh mysql-community-libs-5.7.12-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-5.7.12-1.el7.x86_64.rpm

rpm -ivh mysql-community-server-5.7.12-1.el7.x86_64.rpm

• 初始化

mysqld --initialize --user=mysql

• 启动

service mysqld start.

• 查看初始化密码

grep 'password' /var/log/mysqld.log

• 连接修改密码和权限

mysql -u root -p

msql>alter user 'root'@'localhost' identified by 'xxxx';

mysql>use mysql;

msyql>update user set user.Host='%' where user.User='root';

mysql>flush privileges;

mysql>quit

三 主从配置

1. 开启bin_log

• 检测bin_log是否开启

mysql> show variables like '%log_bin%';

如上图所示,如果参数为OFF则表明为开启

• 开启bin_log

• 配置service-id

修改配置文件 /etc/my.cnf

[mysqld]
log-bin=mysql-bin
server-id=1

• 为从库增加角色

mysql> CREATE USER 'slave'@'%' IDENTIFIED BY 'XXXX';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
mysql> FLUSH PRIVILEGES;

• 从库配置service-id
  修改配置文件 /etc/my.cnf

[mysqld]
server-id=1

• 查看主库bin_log信息

mysql> show master status;

binlog信息

• 设置从库 master

mysql> CHANGE MASTER TO MASTER_HOST='10.XXX.X.1',MASTER_USER='slave',MASTER_PASSWORD='!QAZ2wsx',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=741;
mysql> start slave;

• 查看同步状态

mysql> show slave status \G

成功状态

四 测试

mysql> create database test;
mysql> use test
mysql> create table user_test(name varchar(20),age int);
mysql> insert into user_test values ("jim",1);

结果

mysql> show databases;

mysql> use test
mysql> show tables;

mysql> select * from user_test;