mysql主从

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介:

1mysql主从介绍

1 MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后,在A上写数据,另外一台B也会跟着写数据,两者数据实时同步的 
2 MySQL主从是基于binlog的,主上须开启binlog才能进行主从

如下是介绍主从过程: 
1)主将更改操作记录到binlog里
2)从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog(中继日志)里
3)从根据relaylog里面的sql语句按顺序执行
主上有一个log dump线程,用来和从的I/O线程传递binlog
从上有两个线程,其中I/O线程用 来同步主的binlog并生成relaylog,另外一个SQL线程用来把relaylog里面的sql语句执行

mysql主从的应用场景:(1)数据备份-主执行操作,备机-只备份(不执行其它操作)
(2)备机备份-但是还需要在从的服务器上都数据(不能写数据)

2主从的准备工作

主机器的操作:

首先是下安装mysql-解压mysql-给mysql用户-移动mysql到/usr/local/mysql/下(移动之前需要注意这个目录下是否还有mysql这个目录确保这个目录下只有一个mysql目录)-创建mysql数据库-初始化mysql-更改my.cnf-拷贝启动脚本-设置开机启动(基础的就已经完成)
[root@chy01 ~]# ps aux |grep mysql
root       2411  0.0  0.1 115376  1680 ?        S    22:15   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/chy01.pid
mysql      2659  1.8 29.7 1038628 454696 ?      Sl   22:15   0:02 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/chy01.err --pid-file=/data/mysql/chy01.pid --socket=/tmp/mysql.sock
root       2889  0.0  0.0 112664   976 pts/0    R+   22:17   0:00 grep --color=auto mysql
(查看mysql已经启动)
同样在从服务器上也是需要同样的步骤完成基本的Mysql操作之后启动mysql

3配置主服务器

[root@chy01 src]# vim /etc/my.cnf
(更改主的配置文件)
[mysqld]里面增加如下两项
server-id = 11
log_bin=chylinux
其中server-id = 1111指的是主机ip的最后一位的数字,log_bin=chylinux定义log的前缀。)
[root@chy01 src]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!
(重启mysql)
[root@chy01 mysql]# ls
auto.cnf          chy01-bin.000004  chy01.err        db1          mysql               xtrabackup_binlog_pos_innodb
chy01-bin.000001  chy01-bin.000005  chy01.pid        ibdata1      mysql2              xtrabackup_info
chy01-bin.000002  chy01-bin.000006  chylinux.000001  ib_logfile0  performance_schema  zrlog
chy01-bin.000003  chy01-bin.index   chylinux.index   ib_logfile1  test
(查看mysql,
[root@chy01 mysql]# mysql -uroot -paminglinux -e "create database zhucong"
Warning: Using a password on the command line interface can be insecure.
(创建mysql主从库)

mysql> grant replication slave on *.* to 'repl'@'192.168.212.10' identified by 'chylinux';
Query OK, 0 rows affected (0.00 sec)
(创建于从服务器备份时的用户名与密码,ip地址是从服务器的ip地址。)
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
(锁定表,目的是不让此表在写数据,备的服务器需要同步数据库)
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| chylinux.000006 |      331 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
(查看主服务器的status)
[root@chy01 mysql]# mysqldump -uroot -paminglinux zrlog > /tmp/zrlog.sql
Warning: Using a password on the command line interface can be insecure.
[root@chy01 mysql]# mysqldump -uroot -paminglinux db1 > /tmp/db1.sql
Warning: Using a password on the command line interface can be insecure.
(需要在主服务器上备份数据库,然后在从服务器上进行同步)
[root@chy01 mysql]# ls /tmp/*sql
/tmp/db1.sql  /tmp/mysq_all.sql  /tmp/mysql2.sql  /tmp/mysqlbak.sql  /tmp/user.sql  /tmp/zrlog.sql
(查看主服务器上面的备份数据库)

4 配置从服务器

[root@chy ~]# vim /etc/my.cnf
[mysqld]
server-id=10
(在从服务器上增加一个serverid,不需要配置binlog)
[root@chy ~]# /etc/init.d/mariadb restart
Restarting mariadb (via systemctl):                        [  确定  ]
(启动mariadb数据库)
[root@chy ~]# scp 192.168.212.11:/tmp/*.sql /tmp/
The authenticity of host '192.168.212.11 (192.168.212.11)' can't be established.
ECDSA key fingerprint is de:d2:32:86:e0:89:5c:2c:51:68:92:9b:7e:40:52:5c.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.212.11' (ECDSA) to the list of known hosts.
root@192.168.212.11's password: 
db1.sql                                                                              100% 1257     1.2KB/s   00:00    
mysq_all.sql                                                                         100% 1275KB   1.3MB/s   00:00    
mysql2.sql                                                                           100%   30KB  30.1KB/s   00:00    
mysqlbak.sql                                                                         100%  638KB 637.7KB/s   00:00    
user.sql                                                                             100% 6762     6.6KB/s   00:00    
zrlog.sql                                                                            100%   10KB   9.9KB/
(将主服务器/tmp/下的备份数据库拷贝到从服务器上)
MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database mysql2;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database zrlog;
Query OK, 1 row affected (0.00 sec)
(创建3个库)
[root@chy ~]# mysql -uroot mysql2 < /tmp/mysql2.sql
[root@chy ~]# mysql -uroot zrlog < /tmp/zrlog.sql
[root@chy ~]# mysql -uroot db1 < /tmp/db1.sql
(恢复从服务器的三个数据库)
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
MariaDB [(none)]> change master to master_host='192.168.212.11', master_user='repl', master_password='chylinux', master_log_file='chylinux.000001', master_log_pos=474566;
Query OK, 0 rows affected (0.03 sec)
(执行)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.212.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: chylinux.000001
          Read_Master_Log_Pos: 474566
               Relay_Log_File: chy-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: chylinux.000001
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
(这里面只有一个yes,IOrunning 是connecting,这里出现了问题,这里的问题是我之前在主服务器上授权时 grant replication slave on *.* to 'repl'@'192.168.212.10' identified by 'chylinux';这里有个错误就是ip后面少了一个'号当重新授权时就变为了MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.212.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: chylinux.000006
          Read_Master_Log_Pos: 474566
               Relay_Log_File: chy-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: chylinux.000006
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
no 这里看到报了一个错: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 'chylinux.000006' at 474566, the last event read from './chylinux.000006' at 4, the last byte read from './chylinux.000006' at 4.'
[root@chy ~]# tail -n5 /data/mariadb/chy.err 
2017-08-31 23:03:50 139775179691776 [Note] Slave SQL thread initialized, starting replication in log 'chylinux.000006' at position 474566, relay log './chy-relay-bin.000001' position: 4
2017-08-31 23:03:50 139775179441920 [Note] Slave I/O thread: connected to master 'repl@192.168.212.11:3306',replication started in log 'chylinux.000006' at position 474566
2017-08-31 23:03:50 139775179441920 [ERROR] Error reading packet from server: Client requested master to start replication from position > file size; the first event 'chylinux.000006' at 474566, the last event read from './chylinux.000006' at 4, the last byte read from './chylinux.000006' at 4. (server_errno=1236)
2017-08-31 23:03:50 139775179441920 [ERROR] Slave I/O: 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 'chylinux.000006' at 474566, the last event read from './chylinux.000006' at 4, the last byte read from './chylinux.000006' at 4.', Internal MariaDB error code: 1236
2017-08-31 23:03:50 139775179441920 [Note] Slave I/O thread exiting, read up to log 'chylinux.000006', position 474566(查看错误日志发现了Slave I/O: 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 'chylinux.000006' at 474566, the last event read from './chylinux.000006' at 4, the last byte read from './chylinux.000006' at 4.', Internal MariaDB error code: 1236这一段 
这时需要去主服务器上查看它的'./chylinux.000006' at 4pos 具体操作如下[root@chy01 mysql]# mysqlbinlog /data/mysql/chy01-bin.000006 > /tmp/zhucong.txt(这是主服务器上面的操作,将这个文件重定向到zhucong.txt这样方便查看
[root@chy01 mysql]# less /tmp/zhucong.txt
# at 4
#170830 22:15:52 server id 1  end_log_pos 120 CRC32 0xc388e931  Start: binlog v 4, server v 5.6.35-log created 170830 22:15:52 at startup
less查看时可以看到at 后面的pos120
这时需要到从服务器上操作执行MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> change master to master_host='192.168.212.11', master_user='repl', master_password='chylinux', master_log_file='chylinux.000006', master_log_pos=120;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.212.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: chylinux.000006
          Read_Master_Log_Pos: 331
               Relay_Log_File: chy-relay-bin.000002
                Relay_Log_Pos: 629
        Relay_Master_Log_File: chylinux.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
可以查看到没有问题都是yes)
最后需要在主服务器上面恢复写操作:
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
需要注意的是在同步时两边的数据需要一致。
[root@chy ~]# ls /tmp/*.sql
/tmp/db1.sql  /tmp/mysq_all.sql  /tmp/mysql2.sql  /tmp/mysqlbak.sql  /tmp/user.sql  /tmp/zhucong.sql  /tmp/zrlog.sql
(从服务器上的数据库)
[root@chy01 mysql]# ls /tmp/*.sql
/tmp/db1.sql  /tmp/mysq_all.sql  /tmp/mysql2.sql  /tmp/mysqlbak.sql  /tmp/user.sql  /tmp/zhucong.sql  /tmp/zrlog.sql
(主的数据库)

4测试主从同步

# vim /etc/my.cnfdo////# vim /etc/my.cnf//000`id`4`name`400013| Tables_in_zhucong || t1                |1in000| Tables_in_zhucong || t1                |1in000,如需转载请自行联系原作者
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
3月前
|
负载均衡 关系型数据库 MySQL
MySQL主从架构的搭建
MySQL主从架构的搭建
52 3
|
3月前
|
SQL 关系型数据库 MySQL
小白带你学习linux的mysql服务(主从mysql服务和读写分离三十一)
小白带你学习linux的mysql服务(主从mysql服务和读写分离三十一)
54 0
|
2月前
|
SQL 关系型数据库 MySQL
MySQL-主从架构的搭建
MySQL-主从架构的搭建
45 0
|
6月前
|
SQL 关系型数据库 MySQL
MySQL搭建主从备份读写分离(MySQL5.7案例)
MySQL搭建主从备份读写分离(MySQL5.7案例)
96 0
|
4月前
|
SQL 关系型数据库 MySQL
MySQL的主从结构是通过一系列的步骤搭建出来的
MySQL的主从结构是通过一系列的步骤搭建出来的
25 1
|
3月前
|
消息中间件 关系型数据库 MySQL
在kafka connect 同步 mysql 主从数据库
在kafka connect 同步 mysql 主从数据库
27 0
|
3月前
|
关系型数据库 MySQL 数据库
分库分表之基于Shardingjdbc+docker+mysql主从架构实现读写分离(一)
分库分表之基于Shardingjdbc+docker+mysql主从架构实现读写分离(一)
|
3月前
|
关系型数据库 MySQL Java
分库分表之基于Shardingjdbc+docker+mysql主从架构实现读写分离(二)
分库分表之基于Shardingjdbc+docker+mysql主从架构实现读写分离(二)
|
3月前
|
关系型数据库 MySQL 数据库
MySQL:从MySQL看主从架构高可用性实现
MySQL:从MySQL看主从架构高可用性实现
49 0
|
4月前
|
SQL 关系型数据库 MySQL
如何判断mysql主从是否同步
如何判断mysql主从是否同步