mysql 主从配置

修改配置文件

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

# 不区分字母大小写
lower_case_table_names=1
# 开启二进制日志	   
log-bin=mysql-bin
# 设置server-id , 从节点指定一个其他的id即可 			
server-id=10 				  

# 不同步哪些数据库
binlog-ignore-db = mysql 
binlog-ignore-db = test 
binlog-ignore-db = information_schema

重启 mysql 服务

# 关闭mysql服务 
systemctl stop mysqld.service 

# 查看mysql服务 		 
systemctl status mysqld.service	

# 启动mysql服务 	
systemctl start mysqld.service

# 重启mysql服务 		
systemctl restart mysqld.service 	  

主从配置

# 登录到 mysql 的主节点
mysql -uroot -p

# 查看当前节点作为主节点的时候的信息
show master status;

# 登录到从数据库
mysql -uroot -p

# 先关闭该节点作为从节点时的服务
stop slave;

# 配置
change master to master_host='192.168.37.131', master_user='root', master_password='root', master_log_file='mysql-bin.000003', master_log_pos=383604;

# 开启从节点时的服务
start slave;

# 查看从节点状态 Slave_IO_Running Slave_SQL_Running
show slave status\G

主从测试

# 在主节点上执行如下的 sql 脚本，观察从节点的数据变化

# 创建数据库 
create database spring_db;

# 创建表
CREATE TABLE `t_user` ( 
    `user_id` varchar(40) NOT NULL, 
    `email` varchar(60) DEFAULT NULL, 
    `user_name` varchar(50) DEFAULT NULL COMMENT '不能重复,可为中文', 
    `station` varchar(20) DEFAULT NULL, 
    `password` varchar(64) DEFAULT NULL COMMENT 'shiro MD5密码32位', 
    `state` decimal(11,0) DEFAULT NULL COMMENT '1启用0停用', 
    `manager_id` varchar(40) DEFAULT NULL, 
    `gender` char(1) DEFAULT NULL, 
    `telephone` varchar(11) DEFAULT NULL, 
    `birthday` varchar(20) DEFAULT NULL, 
    `degree` int(2) DEFAULT NULL, 
    `salary` decimal(10,0) DEFAULT NULL, 
    `join_date` varchar(20) DEFAULT NULL, 
    `order_no` int(3) DEFAULT NULL, 
    `remark` varchar(255) DEFAULT NULL, 
    `dept_id` varchar(40) DEFAULT NULL, 
    `create_by` varchar(40) DEFAULT NULL COMMENT '登录人编号', 
    `create_dept` varchar(40) DEFAULT NULL COMMENT '登录人所属部门编号', 
    `create_time` datetime DEFAULT NULL, 
    `update_by` varchar(40) DEFAULT NULL, 
    `update_time` datetime DEFAULT NULL,
    PRIMARY KEY (`user_id`), 
    KEY `SYS_C005467` (`dept_id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 插入数据
insert into `t_user`(`user_id`,`email`,`user_name`,`station`,`password`,`state`,`manager_id` ,`gender`,`telephone`,`birthday`,`degree`,`salary`,`join_date`,`order_no`,`remark`,`dept_id`,`create_by`,`create_dept`,`create_time`,`update_by`,`update_time`) 
values ('002108e2-9a10-4510-9683-8d8fd1d374ef','lw@export.com','老王1','员 工','123456',1,NULL,'0','12345678','1989-01-06 00:00:00',1,2000,'2019-02-26 00:00:00',5,'貌美如花','100102',NULL,NULL,'2015-10-18 17:00:53',NULL,'2015-10-18 17:00:53'), 
('735730bf-3ac0-4690-882d-100178d58ff9','zbz@export.com','zbz','秘 书','123456',1,NULL,'1','13800138013','1989-01-06 00:00:00',4,3000,'2016-03-22 00:00:00',4,'貌美如花','100',NULL,NULL,'2017-03-11 11:51:18',NULL,'1970-01-01 08:00:00'), 
('bc30bdba-30c8-4b71-ae69-6099839e3894',NULL,'李青 云',NULL,'123456',1,NULL,'0',NULL,NULL,NULL,NULL,'2020-11-12 00:00:00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), 
('ca592c7c-860a-464c-8f56-ea51b42a046f','baitu@export.com','baitu','员 工','123456',1,NULL,'1','13800138021','1977-11-09 00:00:00',4,5000,'2014-06-06 00:00:00',3,'貌美如花','100101',NULL,NULL,'2018-02-03 12:28:35',NULL,'1970-01-01 08:00:00'), 
('cf0015d3-bcdd-433b-889d-808b53b72640','mz@itheima.com','马总','总经 理','123456',1,NULL,'1','13800138022','1984-08-13 00:00:00',4,500000,'2010-01-05 00:00:00',1,'貌美如花','100',NULL,NULL,'2016-12-06 11:35:25',NULL,'1970-01-01 08:00:00'), 
('e0de22fe-2c50-4216-ad75-ed0494d2dc92','cgx@itheima.com','cgx','部门经 理','123456',1,NULL,'0','13800138023','1981-01-13 00:00:00',1,20000,'2011-09-16 00:00:00',2,'貌美如花','100101',NULL,NULL,'2016-12-06 11:35:25',NULL,'1970-01-01 08:00:00'), 
('ef1f631d-76c8-4837-9ad9-f5f6209e4463','ylj@export.com','ylj','员 工','123456',1,NULL,'1','13800138025','1988-07-26 00:00:00',4,5000,'2013-02-20 00:00:00',6,'貌美如花','100',NULL,NULL,'2016-11-18 11:12:53',NULL,'1970-01-01 08:00:00');