mysql数据库主从搭建及错误分析

 

一、环境搭建

mysql主库的配置文件：

server-id=1

log-bin=/var/lib/mysql/binlog/mysql-bin        #开启binlog并定义存放路径

binlog-format=mixed                                    #binlog混合模式的复制

expire_logs_days=10                                   #binlog日志超过10天自动删除

max_binlog_size=50m                                 #定义binlog日志的大小

wait_timeout=31536000                               #服务器关闭非交互连接之前等待活动的秒数

interactive_timeout=31536000                     #服务器关闭交互是连接前等待活动的秒数

max_allowed_packet = 2000M                     #mysql接受数据包的大小

max_connections=2000                                #最大连接数

lower_case_table_names=1                         #表名存储磁盘时是小写，比较时不区分大小写

init_connect='SET collation_connection = utf8_unicode_ci'

                                                                      #设置字符集

init_connect='SET NAMES utf8'                   #解决jdbc访问的乱码问题

character-set-server=utf8                             #服务器字符集

collation-server=utf8_unicode_ci                  #排序功能

skip-character-set-client-handshake             #跳过mysql程序启动是的字符参数，使用服务器的字符参数

mysql从库配置文件：

[mysqld]

server-id=2

relay-log=/var/lib/mysql/relaylog/mysql-relay

max_relay_log_size=50m

 

主库操作：

mysql -uroot -p #进入数据库

grant replication client,replication slave on *.* to replName@'192.168.%.%' identified by '1234';

#创建用户，允许ip段的主机以该用户和密码进行连接

select Host,User,password from mysql.user;

#查看数据库用户的用户及权限信息

show master status\G;

#查看主机的状态

 

从库操作：

mysql -uroot -p                       #进入数据库

change master to master_host='192.168.0.212', master_user='replName', master_password='1234',master_log_file= 'mysql-bin.000002', master_log_pos=245;

#定义主库的ip，用户名，密码及主库的binlog及该节点

flush privileges;

start slave;                           #开启从库

show slave status\G;           #查看从库的连接状态

两个YES状态表示成功

 

测试：

在主库中添加数据：

use test

create table test (学号 char(9),姓名 char(10),年龄 smallint) character set = utf8mb4;

insert into test (学号,姓名,年龄) values ( '03' , '张三' ,23);

在从库查看有没有数据同步

 

二、常见问题及分析：

 

问题1

现象：从库上的状态显示connecting。

原因：1、网络不通

           2、从库的账号密码等连接信息不对

           3、主从的数据库结构不同

解决：首先确认网络环境及连接信息是否正确，

           如果主备的数据结构不通，将从库数据删除，将主库数据导入从库，再连接测试。

问题2

现象：在修改数据库配置文件之后，重启服务，结果报错，错误如下

解决：进入配置文件，找到刚才修改的地方，看下附近是否有空格，删除即可。

问题3：

现象：本人在安装结束后，想着修改binlog的存放路径，在配置文件中做了修改，但是重启服务报错，错误如下。

解决：首先查看是否有定义的路径存在，如果存在，检查其权限，我是由于没有权限导致的错误产生。

问题4：

现象：从库也修改了日志路径，增加了文件夹和权限，重启服务发现连接错误，显示两个no。

解决：在从库中执行reset slave; 忘记之前连接的信息及索引，重新启用一个新的relay-log。然后重新连接主库即可。