MySql数据库主从的配置

针对5.7以上版本，不限操作系统

一、检查修改MySQL数据库的配置文件

1、Windows

MySQL安装路径下的 my.ini 文件

2、Linux

etc路径下的 my.cnf 文件

配置文件参考：（注意修改： server-id binlog-do-db）

PS：修改配置文件前手动在安装路径下新建 logs 文件夹，在其下分别创建两个名为 binlog errlog 的文件夹

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[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

# These are commonly set, remove the # and set as required.
# 配置MYSQL安装目录
basedir = /usr/local/mysql-5.7.27
# 配置MYSQL存储数据DATA目录
datadir = /usr/local/mysql-5.7.27/data
# 配置端口
port = 3306
# 配置允许最大连接数
#max_connections = 100
# 配置允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors = 10
# 配置mysql在关闭一个非交互的连接之前所要等待的秒数，其取值范围为1-2147483(Windows)，1-31536000(linux)，默认值28800。
#wait_timeout = 28800
# 配置mysql在关闭一个交互的连接之前所要等待的秒数(交互连接如mysql gui tool中的连接)，其取值范围随wait_timeout变动，默认值28800。
#interactive_timeout = 28800
# 添加默认数据引擎项
default-storage-engine = InnoDB
# 添加字符集参数
character-set-server = utf8
# 添加不区分表/字段大小写项
lower_case_table_names = 1

# 服务器标志号，注意在配置文件中不能出现多个这样的标识，如果出现多个的话mysql以第一个为准，一组主从中此标识号不能重复。
server-id = 1
# 开启bin-log，并指定文件目录和文件名前缀。
log-bin = /usr/local/mysql-5.7.27/logs/binlog/bin-log
# 错误日志存放路径
log-error = /usr/local/mysql-5.7.27/logs/errlog/master-error.log
# 每个bin-log最大大小，当此大小等于500M时会自动生成一个新的日志文件。一条记录不会写在2个日志文件中，所以有时日志文件会超过此大小。
max_binlog_size = 500M
# 日志缓存大小
binlog_cache_size = 128K
# 需要同步的数据库名字，如果是多个，就以此格式在写一行即可。
#binlog-do-db = database_test
# 不需要同步的数据库名字，如果是多个，就以此格式在写一行即可。（binlog-do-db,binlog-ignore-db 为互斥关系，只需设置其中一项即可）
binlog-ignore-db = mysql,information_schema,performance_schema,sys
# 当Slave从Master数据库读取日志时更新新写入日志中，如果只启动log-bin 而没有启动log-slave-updates则Slave（从）只记录针对自己数据库操作的更新。
log-slave-updates
# 设置bin-log日志文件格式为：MIXED，可以防止主键重复。
binlog_format = "MIXED"
# 解除bin-log限制存储函数的创建、修改、调用
#log_bin_trust_function_creators = 1

sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# 绕过所有的权限验证
#skip_grant_tables

[mysql]
# 添加默认字符集参数
default-character-set = utf8
# 修改MYSQL端口号，默认为3306，建议不要用默认的，请配置为其他端口号，例如：3369、6033等
port = 3306

[client]
# 添加默认字符集参数
default-character-set = utf8
# 修改MYSQL端口号，默认为3306，建议不要用默认的，请配置为其他端口号，例如：3369、6033等
port = 3306

# 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

二、主数据库（Master）创建从数据库（Slave）使用的用户

1、创建用户并限制使用此用户的IP

CREATE USER '用户名'@'IP地址' IDENTIFIED BY '密码';

CREATE USER '101033136'@'10.10.33.136' IDENTIFIED BY '101033136'; CREATE USER '101033135'@'10.10.33.135' IDENTIFIED BY '101033135';

2、授权用户对数据库的操作(如果仅仅为了主从复制创建账号，只需要授予 REPLICATION SLAVE权限,绕过本步骤直接进行第3步)

GRANT INSERT,DELETE,UPDATE,SELECT ON 数据库名称.* TO '用户名'@'IP地址' IDENTIFIED BY '密码';

授权用户创建数据库的操作

GRANT CREATE ON *.* TO '用户名'@'IP地址' IDENTIFIED BY '密码';

GRANT INSERT,DELETE,UPDATE,SELECT ON anbao.* TO '101033136'@'10.10.33.136' IDENTIFIED BY '101033136';

3、授权 REPLICATION SLAV

GRANT REPLICATION SLAVE ON *.* TO '用户名'@'IP地址' IDENTIFIED BY '密码';

GRANT REPLICATION SLAVE ON . TO '101033136'@'10.10.33.136' IDENTIFIED BY '101033136'; GRANT REPLICATION SLAVE ON . TO '101033135'@'10.10.33.135' IDENTIFIED BY '101033135';

4、刷新权限

FLUSH PRIVILEGES;

5、查看授权

SHOW GRANTS FOR '用户名'@'IP地址';

SHOW GRANTS FOR '101033136'@'10.10.33.136'; SHOW GRANTS FOR '101033135'@'10.10.33.135';

三、创建数据库主从

查看数据库的 log_bin 选项（方便查找 log_bin 的存放路径）

SHOW VARIABLES LIKE '%log_bin%';

1、查看主数据库（Master）的 MASTER 状态，注意记录结果内 File Position 的字段值供给第2步使用

SHOW MASTER STATUS;

2、在从数据库（Slave）执行

#从库连接到主库
CHANGE MASTER TO 
#主机的IP
MASTER_HOST = '主数据库（Master）的IP',
#之前创建的那个使用名
MASTER_USER = '用户名',
#密码
MASTER_PASSWORD = '密码',
#这是主库中 SHOW MASTER STATUS; File下的那个值
MASTER_LOG_FILE = 'bin-log.***',
#这是主库中 SHOW MASTER STATUS; Position下的那个值
MASTER_LOG_POS = *****;

3、从数据库（Slave）开启 SLAVE 同步进程

START SLAVE;

4、查看从数据库（Slave）的 SLAVE 状态

SHOW SLAVE STATUS;

5、从数据库（Slave）关闭 SLAVE 同步进程（慎用！！ 主要用来关闭 SLAVE 同步进程）

STOP SLAVE;

四、主从问题处理

1、主主搭建

就是互为主从！！！！

2、查看从数据库（Slave）的 SLAVE 状态是 Slave_IO_Running 状态为 Connecting

①从数据库（Slave）关闭 SLAVE 同步进程

STOP SLAVE;

②立即检查主数据库（Master）的 master-error.log

位置在数据库安装路径下 `/logs/errlog/master-error.log`

③检查 主数据库（Master）创建从数据库（Slave）使用的用户 的第3步是否操作正确

④检查 创建数据库主从 的第2步是否操作正确

3、查看从数据库（Slave）的 SLAVE 状态是 Slave_IO_Running 状态为 NO

①从数据库（Slave）关闭 SLAVE 同步进程

STOP SLAVE;

②查看主数据库（Master）的 MASTER 状态，注意记录结果内 File Position 的字段值供给第3步使用

SHOW MASTER STATUS;

③在从数据库（Slave）执行

#从库连接到主库
CHANGE MASTER TO 
#主机的IP
MASTER_HOST = '主数据库（Master）的IP',
#之前创建的那个使用名
MASTER_USER = '用户名',
#密码
MASTER_PASSWORD = '密码',
#这是主库中 SHOW MASTER STATUS; File下的那个值
MASTER_LOG_FILE = 'bin-log.***',
#这是主库中 SHOW MASTER STATUS; Position下的那个值
MASTER_LOG_POS = *****;

④从数据库（Slave）开启 SLAVE 同步进程

START SLAVE;

⑤查看从数据库（Slave）的 SLAVE 状态

SHOW SLAVE STATUS;