mysql8 在CentOS7.7下 logbin数据同步

搭建数据库环境 省略

// 测试建库建表
create database `study` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

create table `table_1` (
    `id` int(9) not null auto_increment primary key, 
    `name` varchar(64) default null
)CHARSET=utf8 COMMENT='测试表1';

insert into table_1 (`name`)  values ('aaa');




主库 配置文件vi /etc/my.conf
[mysqld]
server-id=1
log-bin="master-bin"

进入mysql 数据库
show variables like '%bin%';
| binlog_format                                  | ROW  
| log_bin_basename                               | /var/lib/mysql/master-bin       |
| log_bin_index                                  | /var/lib/mysql/master-bin.index |


// 用于给从库读取数据的用户
CREATE USER 'slave1'@'localhost' IDENTIFIED BY 'slave1';
use mysql;
update user set host='%' where user='slave1';
grant replication slave on *.* to 'slave1'@'%';
flush privileges;


create database `t1` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;


show master status; // 下面参数需要
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |     2064 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+


从库 配置文件 vi /etc/my.conf
server-id=2
replicate-do-db=t1
#read_only = 1
#relay_log = /var/log/mariadb/relay.log

// 创建数据库
create database `t1` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;


// 设置主节点信息
change master to master_host='172.16.1.80',master_user='slave1',master_password='slave1',master_log_file='master-bin.000001',master_log_pos=2064;  

// 开启
start slave;