mysql 后搭建从库_【Mysql】Mysql主从库搭建过程（爬完坑后整理所得）

Mysql主从数据库搭建流程

新手开始学习mysql主从库，遇到一些问题，总结后写出以下流程

下面以5.7.23版本为例介绍

第一步：去官网下载5.7.23版本的免安装压缩包形式的mysql文件，贴上网址吧：https://dev.mysql.com/downloads/mysql/5.7.html#downloads

第二步：将下载好解压后的mysql文件放到D盘任何一非中文目录下(不是必须，避免出问题)，并在mysql文件后面标注序号1和序号2

第三步：在mysql文件的根目录下新建一个my.ini文件，将文件拷贝进去

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/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.

# basedir = .....

# datadir = .....

# port = .....

# server_id = .....

# 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

# 以下内容手动添加

[client]

port=3307

default-character-set=utf8

[mysqld]

#主库配置

#skip_grant_tables

server_id=1

log_bin=master-bin

log_bin-index=master-bin.index

#端口

port=3306

character_set_server=utf8

#解压目录

basedir = D:\Software\Mysql\mysql-5.7.23-winx64-1

#解压目录下data目录

datadir = D:\Software\Mysql\mysql-5.7.23-winx64-1\data

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[WinMySQLAdmin]

D:\Software\Mysql\mysql-5.7.23-winx64-1\bin\mysqld.exe

主库的my.ini文件

第四步：管理员方式打开CMD，进入mysql的bin目录下，执行以下语句，红色的路径记得改成自己的噢

mysqld --install slave --defaults-file="D:\software\Mysql\mysql-5.7.23-winx64-2\my.ini"

第五步：执行mysqld --initialize-insecure --user=root ,这句命令是为了初始化数据库data

第六步：执行命令 net start mysql启动服务

到此，mysql数据库配置成功

接下来，开始修改mysql的权限和修改root的密码(前提是服务必须要打开噢)

第一步：输入命令myslq -h localhost -P 3306 -u root 进入mysql数据库(当时为了解决这个问题浪费了很多时间，还是因为对mysql不熟的原因)

后面依次输入以下命令即可

mysql>use mysql;

mysql>update user set host='%' where user='root';

mysql>flush privileges;

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

mysql>ALTER USER 'root'@'%' IDENTIFIED BY '123456';

mysql>exit;

到这里，你的mysql主库已经配置成功了，可以下载一个SQLyog等工具访问你的数据库吧

从库的安装流程参考主库安装流程，只是my.ini文件稍有不同，下面是从库的my.ini文件

从库安装完成后，至此主从库已经安装完成了

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/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.

# basedir = .....

# datadir = .....

# port = .....

# server_id = .....

# 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

# 以下内容手动添加

[client]

port=3307

default-character-set=utf8

[mysqld]

#从库配置

server_id=2

relay-log-index=slave-relay-bin.index

relay-log=slave-relay-bin

#端口

port=3307

character_set_server=utf8

#解压目录

basedir=G:\Mysql\mysql-5.6.38-winx64

#解压目录下data目录

datadir=G:\Mysql\mysql-5.6.38-winx64\data

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[WinMySQLAdmin]

G:\Mysql\mysql-5.6.38-winx64\bin\mysqld.exe

my.ini

从库的my.ini文件

剩下的配置就不再赘述了，请参考https://www.cnblogs.com/naruto123/p/8138708.html

感谢大神们无私分享经验，

以上分享只是我在使用5.7.23版本搭建主从库时参考了5.6的主从库教程，搭建时出现了问题所以才总结出一篇

参考大神的经验时一定要有自己的思考和总结