-
准备两台mysql服务器环境
参考
https://blog.csdn.net/qq_42303467/article/details/122137696
-
配置主库 ip 192.168.95.130
修改/etc/下的my.cnf文件
vim /etc/my.cnf
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=mysql-bin #开启binlog功能
server-id=1 #与从机的id不可相同
sync-binlog=1 #每次执行写入操作与磁盘同步
binlog-ignore-db=performance_schema #指定不同步的从库的系统数据库
binlog-ignore-db=information_schema
binlog-ignore-db=sys
#binlog-do-db=mysql #指定只同步的从库的数据库(可以是任意的) 不指定默认全部
#
# 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
-
重启主库mysql
systemctl restart mysqld
-
登录主库
mysql -uroot -p
-
配置主库给从库增加同步授权
mysql>
grant replication slave on *.* to 'root'@'%' identified by 'root';
mysql>
grant all privileges on *.* to 'root'@'%' identified by 'root';
mysql>
flush privileges;
-
查看 主库状态
mysql>
show master status;
+------------------+----------+--------------+-------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------+-------------------+
| mysql-bin.000001 | 869 | | performance_schema,information_schema,sys | |
+------------------+----------+--------------+-------------------------------------------+-------------------+
1 row in set (0.00 sec)
-
配置从库 ip192.168.95.132
vim /etc/my.cnf
从库的binlog可以不需要开启
配置如下
# 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
server-id=2 #设置serverid
relay_log=mysql