【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的主从库教程，搭建时出现了问题所以才总结出一篇

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