Mysql读写分离配置笔记

in 编程
关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9

环境:linux centOS6.5,mysql5.6.33

1,主服务上的配置
查看mysql是否启动:ps aux |grep mysq
通过命令行登录管理MySQL服务器: mysql -u root –p
然后查看主数据库状态:show master status;

PS:记录下mysql-bin.000003

2,配置从服务器。
修改从服务器的配置文件/etc/my.cnf
将 server-id = 1修改为 server-id = 10,并确保这个ID没有被别的MySQL服务所使用。
重启mysql数据库:service mysqld restart

3,在从服务器的mysql执行如下配置。
mysql> change master to
-> master_host='192.168.195.99',
-> master_user='root',
-> master_password='root',
-> master_log_file='mysql-bin.000003',
-> master_log_pos=256;
Query OK, 0 rows affected, 2 warnings (0.06 sec)

报错:
mysql> start slave
-> ;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

解决办法:
打开my.cnf 加入下面内容
relay_log=/usr/local/db/mysql01/logs/relay_01_002。
然后:
mysql>
change master to
master_host='192.168.195.99',
master_user='root',
master_password='root',
master_log_file='mysql-bin.000003',
master_log_pos=256;
Query OK, 0 rows affected, 2 warnings (0.41 sec)

mysql> start slave;
Query OK, 0 rows affected (0.04 sec)

4,主从同步检测

mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.195.99
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 256
Relay_Log_File: node2-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No
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: 256
Relay_Log_Space: 120
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size; the first event 'mysql-bin.000003' at 256, the last event read from './mysql-bin.000003' at 4, the last byte read from './mysql-bin.000003' at 4.'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: f28711a6-4232-11e7-87dd-000c29135b40
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 170905 18:34:32
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)

其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES,才表明状态正常。

以上检测的结果:Slave_IO_Running:NO
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size; the first event 'mysql-bin.000003' at 256, the last event read from './mysql-bin.000003' at 4, the last byte read from './mysql-bin.000003' at 4.'

说明配置有问题。
解决方法:
查看主服务器的mysql-bin.000003文件。
将mysql-bin.000003文件转成txt查看
mysqlbinlog mysql-bin.000003>test.txt

发现master_log_pos=256;设置的不对,应该是master_log_pos=4;

回到mysql从服务器,修改配置

重启slave,再次主从同步检测

关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9
扫一扫关注公众号添加购物返利助手,领红包
Comments are closed.

推荐使用阿里云服务器

超多优惠券

服务器最低一折,一年不到100!

朕已阅去看看