详记windows下MySQL主从复制的实现

作者: 温新

分类: 【MySql】

阅读: 4618

时间: 2020-02-15 11:48:05

序言

开始之前先废话一下。
我使用虚拟机来实验的原因:为了避免在操作过程中无意损坏了本地数据库。
为什么不使用Linux系统:
1)一直没有在windows下手动安装数据库,都是实用集成环境,想动手试一试;
2)这一次是在已有一个数据库的前提下再装一个,更想尝试一下;
3)说不定哪天就碰到了Windows服务器;
4)Linux系统的主从复制会实现的,到时候也详细记录下来;
5) 问题总结见文章末尾。

数据库推荐去mysql官方下载

一、环境说明

1.1、环境说明

虚拟机:VMware15.1
VMware虚拟系统:win10

【主库、从库】
MySQL版本:MySQL5.7.26
IP地址:127.0.0.1

主库端口:3306
从库端口:3307

1.2、情况说明

1)主库
使用phpstudy_v8集成环境中的MySql5.7.26作为主库。

说明:由于集成环境安装时间较早,没必要再去折腾重新安装2个。

2)从库
使用解压方式完完全全新手式安装。其中下载过程省去。

二、数据库安装

说明:两个数据库安装位置可以一样也可以不一样,全看个人。

2.1、主库安装

phpstudyv8集成环境。
mysql使用版本:MySql5.7.26
位置:C:\phpstudy
pro\Extensions\MySQL5.7.26
我之前安装好了,就不再重复安装。

2.2、从库安装(mysql5.7.26)

步骤一:官网下载mysql5.7.26 windows 64位版本(mysql-5.7.26-winx64.zip)

步骤二:移动至任意盘,解压并重命名为有语意的名称。
如:我放在了C盘根目录,名称为mysql-slave
位置:C:\mysql-slave

步骤三:配置my.ini文件
1)mysql-slave根目录下创建my.ini文件并进行配置:
C:\mysql-slave\my.ini

2)配置my.ini

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4 
[mysqld]
#设置3306端口
port = 3307 
# 设置mysql的安装目录
basedir=C:\mysql-slave
# 设置mysql数据库的数据的存放目录
datadir=C:\mysql-slave/data3307
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

PS:此处是配置从库,端口很重要,设置为3307!3307!3307!

步骤四:安装mysql

1) 以管理员身份打开CMD窗口
PS:不使用管理员身份打开是无法安装的!

2)执行安装命令

C:\mysql-slave\bin>C:\mysql-slave\bin\mysqld.exe install my3307 --defaults-file="C:\mysql-slave\my.ini"

PS:出现Service successfully installed则安装成功。若没有看到这个成功的提示(包括执行命令后没有任何回应)都是安装失败。安装失败,就去检查my.ini配置文件,看配置文件是否出错(9成是my.ini配置问题)。

3)初始化mysql

C:\mysql-slave\bin>C:\mysql-slave\bin\mysqld --initialize --console

#初始化回应,并返回初始密码
2020-02-15T05:50:01.220199Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-02-15T05:50:01.356151Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-02-15T05:50:01.386174Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-02-15T05:50:01.454403Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 01b4e123-4fb7-11ea-b9eb-000c292c8803.
2020-02-15T05:50:01.456685Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-02-15T05:50:01.462937Z 1 [Note] A temporary password is generated for root@localhost: K+<_jG4SK:Fo

PS:--console显示情况。可以看到我这里出现了好几个Warining,这不影响安装,继续往下走。

4)查看注册表中mysql路经是否正确(可省略)

\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\my3307中的ImagePath
值:
C:\mysql-slave\bin\mysqld.exe --defaults-file=C:\mysql-slave\my.ini my3307

5)启动mysql

net start my3307

6)登录mysql

C:\mysql-slave\bin>C:\mysql-slave\bin\mysql -uroot -P3307 -p

PS:密码是 第 3) 小步中的 root@localhost: K+<_jG4SK:Fo

7)修改root密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; 

# 刷新权限
flush privileges;

PS:生产环境中一定不要使用123456作为密码!

至此,mysql5.7.26安装完成。

三、主从配置

3.1、主库配置

1)主库配置my.ini

在[mysqld]下增加如下配置

# 服务器标识ID
server-id=1
#二进制日志文件格式
log-bin=mysql-bin
#binlog日志格式
binlog_format=mixed

2)重启mysql

由于是phpstudy集成环境,点击重启按钮即可。

3.2、从库配置

1)从库配置my.ini

在[mysqld]在增加如下配置

server-id=2
log-bin=mysql-bin
# 从库二进制日志
relay-log=mysql-relay
binlog_format=mixed

2)重启my3307

net stop my3307
net start my3307

PS:主库、从库中的server-id要有一定的含义,不要稀里糊涂的取1,2,3,4之类的。

PS:到这里关于主从配置中my.ini的配置就已经完成了。

3.2、授权操作

1)主库授权 步骤一:查看主库是否具备充当master的条件

show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

步骤二:创建授权用户(从库连接主库时用)

grant replication slave on *.* to 'my3307'@'%' identified by '123456'; 

flush privileges;

PS:主库操作完成

2)从库授权

步骤一:使用主库分配的授权用户连接主库

change master to
master_host='127.0.0.1',
master_user='my3307',
master_password='123456',
master_log_file='mysql-bin.000002',
master_log_pos=154;

PS:注意:masterlogfile配置一定要与主库中的 show master status时的file一致,masterlogposg与position中显示的位置标识一致

步骤二:查看从库连接状态(是否成功状态信息)

show slave status

PS:由于信息太长,放到了步骤三后面

步骤三:启动slave

start slave;

3)验证主从复制 主库登录mysql,创建ziruchu表, 从库登录mysql,查看ziruchu表是否被复制了(到了这里,一般都是成功的)。

至此,一些逐安装、配置下来,若无报错,则说明主从配置已经成功。

步骤二状态详细信息

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: my3307
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 591
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 591
              Relay_Log_Space: 523
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: ae0a8ec4-6fc1-11e9-821a-4ccc6a4d7344
             Master_Info_File: C:\mysql-slave\data3307\master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

可以看到,SlaveIORunning,SlaveSQLRunning都是YES,说明配置成功。若配置不成功,LastIOErrno,LastIOError,LastSQLErrno,LastSQLError会有错误信息出现:

PS:到这里主从配置就完成了。第五节讲的是中间出错如何去解决。

四、扩展,只读用户(从库)

步骤一:创建只读用户

create user 'slave_onlyready'@'%' identified by '123456';

修改密码

update user setauthentication_string=PASSWORD(‘123456′) where user=’slave_onlyready’;

步骤二:授权只读

grant select on *.* to 'slave_onlyready'@'%' identified by '123456';

flush privileges;

五、启动授权报错

当从库start slave授权启动失败时,先停止slave(stop slave),然后清除授权(reset slave)

#停止slave
stop slave;

#清除授权.reset slave清除授权时保留授权信息
reset slave;

#问题解决后再次开启授权
start slave;

PS:reset slave all也可以清除授权,但是此方式会清除授权信息。

六、总结

mysql主从复制的实现不难,但仍旧折腾了一些时间。主要的折腾是在安装mysql上。 第一次安装,与已经安装好的mysql端口冲突了。修改从库的配置文件,出现了许多错误。安装好后,启动my3307,出现已经启动...启动失败,没有报错,这让人预报,启动成功...启动失败,又不报错,找不到原因就重装吧,再次安装时加上console参数,查看信息,但是看到了不少的信息,然后修改my.ini配置文件,最后启动成功了。

PS:无法保证安装一定不会出错的时间,建议该省的一定不能省。

我是夕阳何处寻,期待和优秀的你一起同行!

夕阳何处寻

2020年2月15日

请登录后再评论