Windows下MySQL的主从配置
简单的原理理解
mysql主从复制允许将一个数据库(主数据库)的数据同步到另一个或几个数据库(从数据库)。可以实现数据备份或读写分离。
1、主服务器上面的任何修改都会通过自己的 I/O tread(I/O 线程)保存在二进制日志 Binary log 里面。
2、从服务器上面也启动一个 I/O thread,通过配置好的用户名和密码, 连接到主服务器上面请求读取二进制日志,然后把读取到的二进制日志写到本地的一个Realy log(中继日志)里面。
3、从服务器上面同时开启一个 SQL thread 定时检查 Realy log(这个文件也是二进制的),如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。
踩坑环境:
操作系统:Windows
主数据库:MySQL5.7
ip:192.168.50.50
从数据库:MySQL8.0
ip:192.168.50.90
主数据库配置
1、新建一个测试数据库mytestdb。
mysql> create database mytestdb;
Query OK, 1 row affected (0.02 sec)
2、修改配置文件my.ini.
server-id=1 # 默认的id就是1
log-bin=mysql-bin # 开启MySQL的日志
binlog_do_db=mytestdb # 要同步的数据库,如果有多个数据库,可写多行,只需修改数据库名,或者写binlog_ignore_db(不同步的数据库,除此之外都同步,同样多个就写多行)
3、重启mysql服务
4、创建用于同步的账号
终端进入MySQL,依次输入命令:
mysql> grant replication slave on *.* to 'clown'@'192.168.50.90' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
5、查看master状态, 记录二进制文件和位置,发现为空
mysql> show master status;
Empty set (0.00 sec)
6、看一看日志是否成功开启
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
6 rows in set, 1 warning (0.00 sec)
我丢,什么鬼,竟然没有开启,明明一切步骤都没错啊,多番尝试还是不行,这可真让人头大
不行,心态不能炸,好好分析一下,问题出在哪了。
结果是我改的配置文件不对,之前自己改了一份my.ini放在了MySQL的安装目录下,修改了无数遍都不生效,真正应该改的my.ini在这呢!!!
之前一直给的都是错的文件(真菜!!!)
重新修改之后,重启MySQL服务
mysql> show variables like '%log_bin%';
+---------------------------------+------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------------------------+
| log_bin | ON |
| log_bin_basename | C:\ProgramData\MySQL\MySQL Server 5.7\Data\mysql-bin |
| log_bin_index | C:\ProgramData\MySQL\MySQL Server 5.7\Data\mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)
这下终于成功开启了!
查看msater状态,记录二进制文件名和位置,等会从库配置时会用到
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | mytest | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
binlog文件的位置:如果在修改my.ini的log-bin时给的是全路径,那么生成的日志文件就在指定的目录下;如果如步骤2中只给一个名字,那么生成的binlog日志的位置为:C:\ProgramData\MySQL\MySQL Server 5.7\Data
假设给的全路径为:
D:\mysql-binlog\mysql-bin
那么服务重启之后就会在该目录下生成mysql-bin.000001和mysql-bin.index文件
从数据库配置
1、创建一个新的数据库mytestdb
2、修改配置文件my.ini
server-id=2 # 主从库id不重复就行;从库可以不用开启日志,我的从库是8.0,已经默认开启了
3、重启MySQL服务
4、终端进入MySQL,敲入以下命令:
mysql> change master to master_host='192.168.50.50',master_port=3306,master_user='clown',master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=154;
Query OK, 0 rows affected, 1 warning (0.11 sec)
5、启动slave同步进程
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
6、查看slave状态
坑坑坑
执行命令:show slave status\G;
Slave_IO_Running: Yes,Slave_SQL_Running: Yes时说明两个线程已启动,主从复制配置成功。
BUT 我这·······(我他么心态崩了啊)
这两个可是少一个都不行的啊!
不过好在各自都有自己的对应的解决办法:
如果是Slave_SQL_Running:No :
解决办法如下:
mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;
mysql> show slave status\G
如果是slave_io_running:No,就像我的问题一样:
1、查看主服务器
2、在从服务器上查看
发现问题所在:发现Master_Log_File没有对应。原因是在配置从库的时候输错了文件名。(真菜,这也能输错)
解决方法:
mysql> stop slave;
mysql> change master to master_log_file='mysql-bin.000001', master_log_pos=0;;
mysql> start slave;
mysql> show slave status\G
解决之后,再一次查看slave状态:
OK,成功!,但是另一个问题又出来了:
提示找不到表,不知道是不是我从库版本高的原因,没有自动创建表,于是乎手动建了一张表,但是,事情总不是那么顺利的,新的错误又出现了:
查看报错,发现是编码的问题,原来是两个数据库的编码方式不一样,于是修改从库的编码方式和主库一样,再一次测试,在主库添加数据,刷新从库,发现数据同步成功!!!