MySQL主从配置遇到错误后的解决方式和常用命令

MySQL主从配置遇到错误后的解决方式和常用命令

Master   主库 写
  静态IP   : 192.168.1.138
  默认网关 : 192.168.164.2 
  子网掩码 : 255.255.255.0 
  端口号  8806
  账号 root  密码123456

slave    从库 读
  静态IP   : 192.168.1.139
  默认网关 : 192.168.164.2 
  子网掩码 : 255.255.255.0 
  端口号 8807   
  账号root   密码123456 

通过Navicat Premium 连接  账号都是root 密码 123456

如果要添加新的需要同步的数据库
在MySQL的目录下 修改my.cnf文件  vi /etc/my.cnf  在中间添加字段
binlog-do_db       =   需要同步数据库名
binlog_ignore_db   =   不同步的数据库名

修改配置文件之后需要重启mysql
重启会导致MySQL数据同步不可用
造成原因：因为Slave事务的回滚
解决方法：
mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;
show slave status\G   查看状态

发现问题如果在Master库中进行操作之后发现slave库没有数据
检查slave端mysql主从slave情况
发现
 Slave_IO_Running: Yes
 Slave_SQL_Running: No
就需要进行上面事务回滚的解决方案

将数据库挂起之后 然后启动发现 主从挂了
查看状态io和sql的状态如果有一个是yes
只需要
1.查看网络状态，查看你的虚拟网络是否满足主从的要求(多半是这个 再不然就是防火墙)
2.查看主库的日志文件偏移编号 和偏移位置 然后再slave中查看状态是否对的上
3.如果对不上 就需要重新添加slave的连接信息
  进入到slave的mysql客户端
  slave stop；  停止主从
  change master to master_host='主库ip',master_user='主库给予的连接账号',master_port=端口号,master_password='主库给予的连接账号的密码'
,master_log_file='日子文件偏移的编号', master_log_pos=偏移的位置;  添加主库信息

----------------------------------------------------------------------------------------------------------------------------------
更改了端口号之后需要重新绑定主从
CHANGE MASTER TO MASTER_HOST='192.168.164.128',   //master的ip
　　MASTER_PORT=7781,                             //master的端口
　　MASTER_USER='rep1',                           //主库赋予从库的账号
　　MASTER_PASSWORD='test123456',                 //密码
　　MASTER_LOG_FILE='mysql-bin.000007',           //文件偏移编号(日志文件绑定一次即可)
　　MASTER_LOG_POS=214;                           //文件偏移的位置

----------------------------------------------------------------------------------------------------------------------------------
上面sql执行会报格式错  下面可以正确执行
change master to master_host='192.168.164.128',master_user='rep1',master_password='test123456',master_log_file='mysql-bin.000007', master_log_pos=214;

----------------------------------------------------------------------------------------------------------------------------------
重启服务之后发现slave ip变更了  （发现原因 ：之前执行的sql存储文件把mysql中间的usr表给覆盖掉了 导致数据的变化 然后不可用）
查看主从状态 发现中断；
show slave status\G   查看状态  
----------------------------------------------------------------------------------------------------------------------------------
SHOW MASTER STATUS;   查看Master日志偏移文件的位置     mysql-bin.000007      42231
----------------------------------------------------------------------------------------------------------------------------------
1 mysql>create user repl; //创建新用户
2 //repl用户必须具有REPLICATION SLAVE权限，除此之外没有必要添加不必要的权限，密码为mysql。说明一下192.168.0.%，这个配置是指明repl用户所在服务器，这里%是通配符，表示192.168.0.0-192.168.0.255的Server都可以以repl用户登陆主服务器。当然你也可以指定固定Ip。
3 mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.164.139' IDENTIFIED BY '123456';

FLUSH PRIVILEGES; 刷新
----------------------------------------------------------------------------------------------------------------------------------
vi /etc/sysconfig/network-scripts/ifcfg-eth0      静态ip设置的文件路径
cd /etc/sysconfig/network-scripts/
----------------------------------------------------------------------------------------------------------------------------------
netstat  -anp  |grep   端口号    查看端口号是否被占用   
主要看监控状态为LISTEN表示已经被占用，最后一列显示被服务xxxx占用

----------------------------------------------------------------------------------------------------------------------------------
创建用户用来远程连接
GRANT ALL PRIVILEGES ON *.* TO 'itoffice'@'%' IDENTIFIED BY 'itoffice' WITH GRANT OPTION;

（第一个itoffice表示用户名，%表示所有的电脑都可以连接，也可以设置某个ip地址运行连接，第二个itoffice表示密码）。

 刷新用户权限   flush privileges;命令立即生效

----------------------------------------------------------------------------------------------------------------------------------
 show global variables like 'port';    在mysql 中查看端口号
https://www.cnblogs.com/fnlingnzb-learner/p/5830661.html         解决远程连接的网址
https://www.cnblogs.com/tianlangshu/p/5665290.html               修改mysql端口的网址
----------------------------------------------------------------------------------------------------------------------------------
GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO slave@’192.168.164.139’ IDENTIFIED BY ‘123456’;
建立一个帐户backup，并且只能允许从10.100.0.200这个地址上来登陆，密码是1234。
----------------------------------------------------------------------------------------------------------------------------------
如果你主的端口号 不是3306  就需要在从设置主信息的地方设置一下端口号
----------------------------------------------------------------------------------------------------------------------------------
重新定义密码： sudo passwd root 
 #以下两个的值如果不清楚的话，可以打开你本地的电脑，--》控制面板\所有控制面板项\网络和共享中心\本地连接\详细信息
NETMASK=255.255.255.0 #子网掩码
GATEWAY=192.168.1.1    #默认网关，
----------------------------------------------------------------------------------------------------------------------------------
重启网络可以输入 service network restart 或者 /etc/rc.d/init.d/network restart
----------------------------------------------------------------------------------------------------------------------------------
ContOS7 中的防火墙
启动： systemctl start firewalld
关闭： systemctl stop firewalld
查看状态： systemctl status firewalld 
开机禁用  ： systemctl disable firewalld
开机启用  ： systemctl enable firewalld
----------------------------------------------------------------------------------------------------------------------------------
service mysql restart     重启mysql
----------------------------------------------------------------------------------------------------------------------------------
搭建桥接模式直接连接到物理网络
需要先关闭虚拟机  然后在设定中更改  连接网络的方式 到桥接模式
在虚拟机的全局设置中更爱虚拟网络连接设置 
设置为自动连接   设置桥接模式必须有一个前提就是静态IP ，不然重启或恢复快照的时候都会引起ip地址的变化 
会导致主从不可用
----------------------------------------------------------------------------------------------------------------------------------
数据迁移

----------------------------------------------------------------------------------------------------------------------------------
   #需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
binlog-do-db = day02
binlog-do-db = crane
--------------------------------------------------------------------------------------------------------
mysql-bin.000008        194

change master to master_host='192.168.1.138',master_user='slave',master_port=8806,master_password='123456'
,master_log_file='mysql-bin.000018', master_log_pos=24922;
----------------------------------------------------------------------------------------------------------------------------------
Linux下的MySQL的默认编码格式是latin1  需要在my.cnf文件中的两处添加三行信息更改编码格式
自行百度
----------------------------------------------------------------------------------------------------------------------------------
SHOW EVENTS;查看已有定时事件任务
----------------------------------------------------------------------------------------------------------------------------------
.事件的开启与关闭
开启某事件：ALTER EVENT e_del_t_req_log ON COMPLETION PRESERVE ENABLE; 
关闭某事件：ALTER EVENT e_del_t_req_log ON COMPLETION PRESERVE DISABLE;
----------------------------------------------------------------------------------------------------------------------------------
SHOW FUNCTION STATUS;  MySQL中查看函数的命令
----------------------------------------------------------------------------------------------------------------------------------
启动MySQL先检查网络连接状态再去查看主从复制情况
如果不行执行上上面因为Slave事务回滚引起的操作
----------------------------------------------------------------------------------------------------------------------------------
windows下  输入 “netstat -an”, 即可显示本机所有开放端口
查看IP地址  ipconfig 
Linux 的是  ifconfig

如果你是安装或配置 遇到了坑 请看我另外一篇文章。主要讲坑  --如果有有我没讲解到了你还不知道怎么解决 可以评论出来 。