VMware-MySQL主从

MySQL主从

服务器信息

服务器类型	角色	主机地址	主机名称
虚拟机	master	192.168.40.128	test-1
虚拟机	slave	192.168.40.129	test-2

Master 配置（192.168.40.128）

删除自动生成的配置

/var/lib/mysql/auto.cnf

[root@test-1 ~]# rm -rf  /var/lib/mysql/auto.cnf
[root@test-1 ~]#

主库配置

/etc/my.cnf

[root@test-1 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# 主从配置
# 主库和从库需要不一致，分别配一个唯一的ID编号
server_id = 1    
# 二进制文件存放路径,存放在根目录data文件夹下
log_bin=master-bin    
# 设置需要同步的数据库
binlog_do_db=db_demo
# 忽略系统库同步
binlog_ignore_db=mysql
binlog_ignore_db=information_schema
binlog_ignore_db=performance_schema
[root@test-1 ~]# 

重启MySQL服务

[root@test-1 ~]# systemctl restart mysqld
[root@test-1 ~]# 
[root@test-1 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 二 2025-05-27 16:49:40 CST; 11s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3279 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 3258 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3282 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─3282 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

5月 27 16:49:38 test-1 systemd[1]: Starting MySQL Server...
5月 27 16:49:40 test-1 systemd[1]: Started MySQL Server.
[root@test-1 ~]# 

测试是否正常

Slave 配置（192.168.40.129）

删除自动生成的配置

/var/lib/mysql/auto.cnf

[root@test-2 ~]# rm -rf  /var/lib/mysql/auto.cnf
[root@test-2 ~]#

从库配置

[root@test-2 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# 主从配置
server_id=2
# 二进制文件存放路径,存放在根目录data文件夹下
log_bin=slave-bin    
# 设置需要同步的数据库
replicate_wild_do_table=db_demo.%
#屏蔽系统库同步
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
[root@test-2 ~]# 

重启MySQL服务

[root@test-2 ~]# systemctl restart mysqld
[root@test-2 ~]# 
[root@test-2 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 二 2025-05-27 17:00:52 CST; 7s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3177 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 3155 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3180 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─3180 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

5月 27 17:00:49 test-2 systemd[1]: Starting MySQL Server...
5月 27 17:00:52 test-2 systemd[1]: Started MySQL Server.
[root@test-2 ~]# 

测试是否正常

主从同步配置

查看Master状态

show master status;

主从节点均创建库 db_demo

从节点配置同步相关信息

停止从节点

stop slave;

配置复制主机的信息

change master to 
	master_host='192.168.40.128',
	master_user='root',
	master_password='Root@123456',
	master_log_file='master-bin.000001',
	master_log_pos=154;

启动从节点

start slave;

查看从节点状态

show slave status;

说明

1. Slave_IO_Running 、Slave_SQL_Running 为YES则为正常
2. 若不是 YES，则可查看从节点的数据库日志 /var/log/mysqld.log 进行排查处理

主从同步验证

主库创建表 student

• 数据库： db_demo
• 表：student

查看从库

主库插入数据

从库查看数据