【无标题】

效果：主写数据，从读数据，master写完数据会同步更新到从数据库，从而达到备份的效果。

master 的配置my.conf 加完配置需要重启服务

[mysqld]添加了：

server-id=1 # 唯一
log-bin=/var/lib/mysql/mysql-bin
expire_logs_days=60
max_binlog_size=50M

 

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]

# log-bin 需要在[mysqld]下
server-id=1
log-bin=/var/lib/mysql/mysql-bin

#
# 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
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql

# 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
[client]
socket=/var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

主库创建从库同步的用户，并授权。

操作命令如下：

创建slave用户

CREATE USER 'slave01'@'%';

设置密码

ALTER USER 'slave01'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

授权

GRANT REPLICATION SLAVE ON . TO 'slave01'@'%';

flush privileges;

查询主节点信息命令：

SHOW MASTER STATUS

从数据库的my.cnf 添加

[mysqld]

# log-bin 需要在[mysqld]下
server-id=2
log-bin=/var/lib/mysql/mysql-bin
# mysql 中继日志
relay-log=mysql-relay

修改完配置文件需要重新启动数据库服务。

从库执行配置主从关系：

指定主库的ip 账号密码 日志文件 从什么时候推送日志文件

CHANGE MASTER TO MASTER_HOST = '192.168.197.132',
MASTER_PORT = 3306,
MASTER_USER = 'slave01',
MASTER_PASSWORD = '123456',
MASTER_LOG_FILE = 'master-bin-log.000001',
MASTER_LOG_POS = 1048;

启动slave同步：

START SLAVE;

查看同步状态：

SHOW SLAVE STATUS \G;

主从复制测试成果

在主库创建testdb数据库，刷新从库即可看到新建的testdb库；

主库testdb创建test1表，从库出现test1表。

主库修改数据，从库跟着改变。

总结：

通过配置主从复制在一定程度上可以减轻服务器的压力，提高应用的体验；并且可以达到读库与写库在数据一致性方面达到最终一致性，写的操作必须操作于主库，读数据则没有限制。