mysql 主从同步实验细解

423212 · 发表于 2017-9-7 10:43:31

一.实验环境

实验环境 192.168.9.108 为master
192.168.9.109 为slave
数据库版本：version 5.1.73
安装方式：采用的yum 安装源为163的源
系统版本：centos 6.5
1.查看系统版本
[iyunv@xuegod100 ~]# cat /etc/issue
CentOS release 6.5 (Final)
Kernel \r on an \m

二.实验准备
1.安装mysql服务
[iyunv@xuegod100 opt]# ansible -i hosts tomcatserver -m yum -a "name=mysql-server  state=present"
注意：这里我之前装过ansible ，这里统一安装的。
   name这里需要写mysql-server
2.启动mysql 服务
[iyunv@xuegod100 opt]# ansible -i hosts tomcatserver -m service  -a 'name=mysqld state=started'
3.为MySQL数据库创建密码
mysql> set password for root@localhost = password('123456');
4.配置主数据库，创建需要同步的数据库
[iyunv@xuegod100 opt]# mysql -uroot -p123456
mysql> create database HA;
mysql> use HA;
mysql> create table T1(id int,name varchar(20));

三.配置主服务器
1.配置my.cnf文件
vim /etc/my.cnf
[iyunv@xuegod100 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql  #数据的存放点
socket=/var/lib/mysql/mysql.sock
user=mysql #用户
log-bin=mysql-bin-master # 启动二进制日志
server-id =1 #本机数据库ID标识
binlog-do-db=HA  #可以被从服务器复制的库。二进制需要同步的数据库名称。
binlog-ignore-db=mysql #不可以被从服务器复制的库
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld-bin-master.log
pid-file=/var/run/mysqld/mysqld-bin-master.pid
2.重启数据库
修改完数据库要重启
service mysqld restart
3.授权从用户可以登陆
mysql> grant replication slave on *.* to slave@192.168.1.64 identified by "123456";
4.查看状态信息
mysql> show master status;
+-------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB             | Binlog_Ignore_DB |
+-------------------------+----------+--------------+------------------+
| mysql-bin-master.000001 |    106 | HA                | mysql          |
+-------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
查看二进制日志（这个是在配置文件里面配置过的）
[iyunv@xuegod100 ~]# ls /var/lib/mysql/
HA  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql-bin-master.000001  mysql-bin-master.index  mysql.sock  test
mysql> show binlog events \G
*************************** 1. row ***************************
Log_name: mysql-bin-master.000001
      Pos: 4
Event_type: Format_desc
  Server_id: 1
End_log_pos: 106
   Info: Server ver: 5.1.73-log, Binlog ver: 4
1 row in set (0.00 sec)
5.到处数据库到从的服务器上保证同步的数据库一致
[iyunv@xuegod100 ~]# mysqldump -uroot -p123456 HA > HA.sql
[iyunv@xuegod100 ~]# scp HA.sql  root@192.168.9.109:/root/
HA.sql

四.配置从服务器
1.测试链接到主服务器是否成功
[iyunv@xuegod101 ~]# mysql -uslave -p123456 -h 192.168.9.108
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.1.73-log Source distribution
mysql> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| test             |
+--------------------+
2 rows in set (0.00 sec)
这里看不到HA 的数据库，只有复制权限
2.导入数据库，和主数据库服务器保持一致
mysql> create database HA;
mysql> mysql -uroot -p123456 HA<HA.sql
3.修改从服务器的配置文件
[iyunv@xuegod101 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=2#从服务器ID号，不要和主ID相同，如果设置多个从服务器，每个从服务器必须有一个唯一的server-id值，必须与主服务器的以及其它从服务器的不相同。可以认为server-id值类似于IP地址：这些ID值能唯一识别复制服务器群集中的每个服务器实
master-host=192.168.9.108 #指定主服务器IP 地址
master-user=slave #指定住服务器上可以进行同步的用户名称
master-password=123456 # 指定密码
master-port=3306
master-connect-retry=60 #断点重新链接时间
[mysqld_safe]
log-error=/var/log/mysqld-slave.log
pid-file=/var/run/mysqld/mysqld-slave.pid
4.重启数据库
service mysqld restart
注意：这里要重启如果直接启动从服务会报错如下
mysql> start slave;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
解决方案：
mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_host='192.168.1.63',master_user='slave',master_password='123456';
mysql> start slave; 启动从
mysql> show slave status\G  查看状态
*************************** 1. row ***************************
            Slave_IO_State: Waiting for master to send event
               Master_Host: 192.168.9.108
               Master_User: slave
               Master_Port: 3306
            Connect_Retry: 60
            Master_Log_File: mysql-bin-master.000001
      Read_Master_Log_Pos: 106
            Relay_Log_File: mysqld-slave-relay-bin.000002
            Relay_Log_Pos: 258
      Relay_Master_Log_File: mysql-bin-master.000001
         Slave_IO_Running: Yes
         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: 106
            Relay_Log_Space: 420
            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: 0
Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 0
            Last_IO_Error:
            Last_SQL_Errno: 0
            Last_SQL_Error:
1 row in set (0.00 sec)
Slave_IO_Running ：一个负责与主机的io通信
Slave_SQL_Running：负责自己的slave mysql进程

五.主服务器上查看状态
mysql> show processlist \G
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 10
Current database: HA
*************************** 1. row ***************************
   Id: 6
User: root
Host: localhost
   db: NULL
Command: Sleep
Time: 1480
  State:
Info: NULL
*************************** 2. row ***************************
   Id: 9
User: slave
Host: 192.168.9.109:33320
   db: NULL
Command: Binlog Dump
Time: 51
  State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 3. row ***************************
   Id: 10
User: root
Host: localhost
   db: HA
Command: Query
Time: 0
  State: NULL
Info: show processlist
1.主数据库插入数据进行测试
mysql> insert into T1 values(1,'天河');
2.从数据库进行查看
mysql> select * from HA.T1;
+------+--------+
| id | name |
+------+--------+
| 1 | 天河 |
+------+--------+
1 row in set (0.00 sec

六.排错
如果遇到主从不同步，看一下主从bin-log的位置，然后再同步。
mysql> show master status;
+-------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------------+----------+--------------+------------------+
| mysql-bin-master.000001 |    200 | HA          | mysql          |
+-------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
从服务器执行MySQL命令下：
mysql> slave stop；          #先停止slave服务
mysql> change master to master_log_file='mysqllog.000004' ,master_log_pos=106;
#根据上面主服务器的show master status的结果，进行从服务器的二进制数据库记录回归，达到同步的效果
mysql>slave start;                   #启动从服务器同步服务
mysql> show slave status\G;       #用show slave status\G;看一下从服务器的同步情况
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果都是yes，那代表已经在同步
重启从服务器，再查看状态：
停止从服务器slave stop;
开启从服务器slave start;
排错思路：
1、二进制日志没有开启
2、IPTABLES 没有放开端口
3、对应的主机 IP地址写错了
SQL线程出错
1、主从服务器数据库结构不统一
出错后，数据少，可以手动解决创建插入，再更新slave状态。
注：如果主上误删除了。那么从上也就误删除了。  #因此主上要定期做mysqldump备份。

七.完成
主从同步到这里就完成了这里可以采用脚本来进行监控，监控 slave的两个yes（Slave_IO及Slave_SQL进程），如发现只有一个或零个yes，就表明主从有问题了，发短信警报吧。

1
2
3
4
5
6
7
8
9
10
11
12
13

[iyunv@xuegod101 ~]# cat mysql_master.sh
#!/bin/bash
port=`netstat -anl|grep 3306 |sed -n '1p' |awk '{print $4}'|awk -F: '{ print $2}'`
array=($(mysql -uroot -p123456 -e "show slave status\G"|grep "Running" |awk '{print $2}'))
if [ "$port" == "3306" ]
then
  if [ "${array[0]}" == "Yes" ] || [ "${array[1]}" == "Yes" ]
then
   echo "slave is OK"
else
   echo "slave is error"
  fi
fi

账号		自动登录	找回密码
密码			立即注册

大疆运维招人啦，

C++ :try 语句块和异常处理

C++的多态

Red Hat RHCE 8 (EX294) Cert Guide

Java/C++ 区别：看完这一篇，就够用！

别再用过时库了！这 13 个顶级 C++ 库才是

c++ size_t 和 int 的区别

[经验分享] mysql 主从同步实验细解

扫码加入运维网微信交流群