mysql主从同步部署实战

1、在线下载安装MySQL官方的 Yum Repository

--注意：主从都要安装
wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql-community-server

2、安装后启动、修改密码

1、systemctl start  mysqld.service
--获取初始密码 如 “wbXqrf1?i”
2、grep "password" /var/log/mysqld.log  

3、mysql登录语句：

1、mysql -h 127.0.0.1 -u root -pwbXqrf1?i
--修改原始密码
2、ALTER USER 'root'@'localhost' IDENTIFIED BY 'XinMiMa';

4、同步配置流程

##【主、从】都要配置
```执行 vim /etc/my.cnf   增加参数```
log-bin=mysql-bin 
server-id=16   #习惯以ip 末尾数字  10.112.1.16（主）10.112.1.26（从）

#主机执行
#1）创建 用户   
GRANT ALL PRIVILEGES ON *.* TO 'tradeuser'@'%' IDENTIFIED BY 'XinMiMa';
flush privileges; 

#2）获取binlog位置点   主
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1188 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

#3）从
stop slave;
CHANGE MASTER TO
MASTER_HOST='10.112.1.16',
MASTER_USER='tradeuser',
MASTER_PASSWORD='XinMiMa',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1188;
start slave; 

#4- 从节点（10.112.1.26）状态查看  关键是两个 “yes”
mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

5、验证 流程

主库 创建 一个库 、一个表、插入数据后，再看从库是否有同步
测试脚本sql

#创建数据库
CREATE database clickhouse;
#创建表
CREATE TABLE `ab_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
#主节点插入表
insert into ab_role(name) value('zhangsan');
insert into ab_role(name) value('lisi');
#查看从节点结果
mysql> select * from ab_role;
+----+----------+
| id | name     |
+----+----------+
|  7 | lisi     |
|  6 | zhangsan |
+----+----------+
2 rows in set (0.00 sec)