mysql设置主从

最新推荐文章于 2025-07-09 18:42:23 发布

原创最新推荐文章于 2025-07-09 18:42:23 发布 · 657 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

本文配置主从使用的操作系统是ubuntu16.04，数据库版本是mysql5.7。

准备好两台安装有mysql的机器

主数据库配置

每个从数据库会使用一个MySQL账号来连接主数据库，所以我们要在主数据库里创建一个账号，并且该账号要授予 REPLICATION SLAVE 权限

创建一个同步账号

create user 'repl'@'%' identified by 'repl_Pass1';

授予REPLICATION SLAVE权限：

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

要配置主数据库，必须要启用二进制日志，并且创建一个唯一的Server ID，打开mysql的配置文件并编辑（位置/etc/my.cnf），增加如下内容

log_bin=master-bin
log_bin_index = master-bin.index
server-id=4
expire-logs-days=7
binlog_ignore_db=mysql
binlog_ignore_db=information_schema
binlog_ignore_db=performation_schema
binlog_ignore_db=sys
binlog_do_db=mybatis

log_bin=master-bin 启动MySQL二进制日志
log_bin_index = master-bin.index
server-id=4  服务器唯一标识
expire-logs-days=7 二进制日志的有效期
binlog_ignore_db=mysql 不需要同步的数据库
binlog_ignore_db=information_schema
binlog_ignore_db=performation_schema
binlog_ignore_db=sys
binlog_do_db=mybatis 需要同步的数据库名字

重启mysql服务，查看主服务器状态：

show master status;

注意将方框里的两个值记录下来，后面在配置从数据库的时候用到。

从数据库配置

同样编辑配置文件my.cnf，插入如下内容

server-id = 2
relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index

重启mysql服务，在slave服务器中登陆mysql，连接master主服务器数据库（参数根据实际填写）

change master to master_host='主机地址', master_port=3306, master_user='用户名', master_password='密码', master_log_file='master-bin.000001', master_log_pos=2237；

启动slave

start slave;

停止slave

stop slave;

查看从数据库状态是否同步

show slave status \G;

这个能同步的前提是的开放mysql的远程权限

bind-address=0.0.0.0

如果不想所有人都能访问你mysql

GRANT ALL PRIVILEGES ON *.* TO 'myusername'@'192.168.1.%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

flush privileges;

192.168.1.% 指允许该网段的全部机器

下面是排错的信息我没用到留着记录下也许以后会用到

排错

Slave_IO_Running: NO

这是一个很常见的错误（我也曾对这个错误咬牙切齿），总结起来就三个原因：

主服务器的网络不通，或者主服务器的防火墙拒绝了外部连接3306端口
在配置从服务器时，输错了ip地址和密码，或者主服务器在创建用户时写错了用户名和密码
在配置从服务器时，输错了主服务器的二进制日志信息

排错过程：（主服务器ip：192.168.1.139，从服务器ip：192.168.1.204）

第0步就是检查错误日志，如果不能快速排错，可以按我的步骤试试：

1．首先在从服务器上执行ping程序，确定能ping通主服务器

在从服务器上执行mysq的远程连接

[root@slave204 log]# mysql -urep -p -h 192.168.1.139 -P3306

如果显示ERROR 1045 (28000): Access denied for user 'test'@'192.168.1.204' (using password: YES)则跳转到第3

2．登陆主服务器的mysql，查看所有的用户

mysql > select user,host from mysql.user;

上图就是我的错误根源，可以看到用户名完全写错了，先删除错误的用户：

mysql > drop user “rep@192.168.1.%”@”%”;

再重新创建用户：

mysql > grant replication slave on *.* to ‘rep’@‘192.168.1.%’ identified by ‘123456’;

mysql > flush privileges;

3．假如用户名没有错，那么如何排除是否是输入的密码错误呢？

额，我也想知道方法。最好就是多输入几遍，或者重新创建用户名和密码来测试。问题还没有解决，转到4

4．在你的防火墙中添加3306端口

[root@localhost mysql]# firewall-cmd --zone=public --add-port=3306/tcp --permanent

[root@localhost mysql]# firewall-cmd --reload

再关闭selinux

[root@slave204 log]# vi /etc/sysconfig/selinux

把SELINUX=enforcing改为SELINUX=disabled

[root@slave204 log]# source /etc/sysconfig/selinux

登录主服务器，查看服务器状态

mysql > show master status \G

然后重新配置一次从服务器，在配置之前首先关闭主从同步进程

mysql > stop slave;

Slave_SQL_Running: No mysql同步故障解决

今天检查数据库发现一台MySQL Slave未和主机同步，查看Slave状态：
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1062
....
Seconds_Behind_Master:NULL
原因：
1.程序可能在slave上进行了写操作
2.也可能是slave机器重起后，事务回滚造成的.

解决办法I：
1.首先停掉Slave服务：slave stop

3.到slave服务器上执行手动同步：
mysql> change master to
> master_host='master_ip',
> master_user='user',
> master_password='pwd',
> master_port=3307,
> master_log_file='mysql-bin.000020',
> master_log_pos=135617781;
1 row in set (0.00 sec)
mysql> slave start;
1 row in set (0.00 sec)
再次查看slave状态发现：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Seconds_Behind_Master: 0

解决办法II：
mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;

自己的使用体会:方法一是强制性从某一个点开始同步,会有部分没有同步的数据丢失,后续主服务器上删除记录同步也会有一些错误信息,不会影响使用.方法二不一定会有效果.

=======================================================================================]

1,主从不能同步:

show slave status;报错:Error xxx dosn't exist
且show slave status\G:
Slave_SQL_Running: NO
Seconds_Behind_Master: NULL

解决方法:
stop slave;
set global sql_slave_skip_counter =1 ;
start slave;

之后Slave会和Master去同步主要看:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master是否为0，0就是已经同步了

2,还需要做的一些优化与监视:
show full processlist; //查看mysql当前同步线程号
skip-name-resolve //跳过dns名称查询，有助于加快连接及同步的速度
max_connections=1000 //增大Mysql的连接数目，(默认100)
max_connect_errors=100 //增大Mysql的错误连接数目,(默认10)

查看日志一些命令
1, show master status\G;
           在这里主要是看log-bin的文件是否相同。
    show slave status\G;
    在这里主要是看:
                   Slave_IO_Running=Yes
                   Slave_SQL_Running=Yes
   如果都是Yes,则说明配置成功.

2,在master上输入show processlist\G;
     mysql> SHOW PROCESSLIST\G
     *************************** 1. row ***************************
       Id: 2
       User: root
       Host: localhost:32931
       db: NULL
       Command: Binlog Dump
       Time: 94
       State: Has sent all binlog to slave; waiting for binlog to
         be updated
       Info: NULL

如果出现Command: Binlog Dump,则说明配置成功.

stop slave    #停止同步
start slave    #开始同步，从日志终止的位置开始更新。
SET SQL_LOG_BIN=0|1 #主机端运行，需要super权限，用来开停日志，随意开停，会造成主机从机数据不一致，造成错误
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n # 客户端运行，用来跳过几个事件，只有当同步进程出现错误而停止的时候才可以执行。
RESET MASTER #主机端运行,清除所有的日志，这条命令就是原来的FLUSH MASTER
RESET SLAVE   #从机运行，清除日志同步位置标志，并重新生成master.info
虽然重新生成了master.info,但是并不起用，最好，将从机的mysql进程重启一下，
LOAD TABLE tblname FROM MASTER #从机运行，从主机端重读指定的表的数据，每次只能读取一个，受timeout时间限制，需要调整timeout时间。执行这个命令需要同步账号有 reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值
LOAD DATA FROM MASTER #从机执行，从主机端重新读入所有的数据。执行这个命令需要同步账号有reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值
CHANGE MASTER TO master_def_list #在线改变一些主机设置，多个用逗号间隔,比如
CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret'
MASTER_POS_WAIT() #从机运行
SHOW MASTER STATUS #主机运行，看日志导出信息
SHOW SLAVE HOSTS #主机运行，看连入的从机的情况。
SHOW SLAVE STATUS (slave)
SHOW MASTER LOGS (master)
SHOW BINLOG EVENTS [ IN 'logname' ] [ FROM pos ] [ LIMIT [offset,] rows ]
PURGE [MASTER] LOGS TO 'logname' ; PURGE [MASTER] LOGS BEFORE 'date'