JAVA全系列 教程
3762个小节阅读:7092.4k
目录
C语言快速入门
JAVA全系列 教程
面向对象的程序设计语言
Python全系列 教程
Python3.x版本,未来主流的版本
人工智能 教程
顺势而为,AI创新未来
大厂算法 教程
算法,程序员自我提升必经之路
C++ 教程
一门通用计算机编程语言
微服务 教程
目前业界流行的框架组合
web前端全系列 教程
通向WEB技术世界的钥匙
大数据全系列 教程
站在云端操控万千数据
AIGC全能工具班
A A
White Night
主从模式是指数据可以从一个mysql服务器节点复制到一个或多个从节点。mysql默认采用异步复制。
在微服务项目中数据库采用一主一从的架构。
服务器硬件配置
服务器IP | 角色 | 配置 |
---|---|---|
192.168.139.137 | master | 2核CPU 4G内存 20G硬盘 |
192.168.139.130 | slave | 2核CPU 4G内存 20G硬盘 |
数据库配置 打开主库/etc/my.cnf文件
xxxxxxxxxx
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
log_bin=mysql_bin_log
skip-name-resolve
character_set_server=utf8
#
# 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
server-id=1
#
# 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
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
修改完数据库配置文件,执行重启命令
xxxxxxxxxx
systemctl restart mysqld
在mysql主库终端执行授权命令
xxxxxxxxxx
grant replication slave on *.* to 'root'@'%' identified by '123456';
xxxxxxxxxx
grant all privileges on *.* to 'root'@'%' identified by '123456';
xxxxxxxxxx
flush privileges;
主从设置
进入主库mysql,查看主库状态
xxxxxxxxxx
show master status;
xxxxxxxxxx
mysql> show master status;
+----------------------+----------+--------------+-------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+-------------------------------------------+-------------------+
| localhost-bin.000006 | 154 | itbaizhan | performance-schema,information-schema,sys | |
+----------------------+----------+--------------+-------------------------------------------+-------------------+
1 row in set (0.00 sec)
进入从库MySQL,执行以下命令
xxxxxxxxxx
change master to master_host='masterIp',master_port=3306,master_user='root',master_password='password',master_log_file='mysql_bin_log.000006',master_log_pos=154;
完成后,查看从库状态
xxxxxxxxxx
mysql> show slave status \G
************************* 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.139.137
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: localhost-bin.000006
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000011
Relay_Log_Pos: 375
Relay_Master_Log_File: localhost-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 756
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 2d1abc2a-a6ad-11ed-b1b6-000c293c3ab2
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
从库等待从主库同步数据,说明从库状态正常,集群搭建完成。