本文共 10373 字，大约阅读时间需要 34 分钟。
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）备机备份-但是还需要在从的服务器上都数据（不能写数据）
主机器的操作：
首先是下安装mysql-解压mysql-给mysql用户-移动mysql到/usr/local/mysql/下（移动之前需要注意这个目录下是否还有mysql这个目录确保这个目录下只有一个mysql目录）-创建mysql数据库-初始化mysql-更改my.cnf-拷贝启动脚本-设置开机启动（基础的就已经完成）[root@chy01 ~]# ps aux |grep mysqlroot 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.pidmysql 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.sockroot 2889 0.0 0.0 112664 976 pts/0 R+ 22:17 0:00 grep --color=auto mysql（查看mysql已经启动）同样在从服务器上也是需要同样的步骤完成基本的Mysql操作之后启动mysql
[root@chy01 src]# vim /etc/my.cnf(更改主的配置文件)[mysqld]里面增加如下两项server-id = 11log_bin=chylinux其中server-id = 11 （11指的是主机ip的最后一位的数字，log_bin=chylinux定义log的前缀。）[root@chy01 src]# /etc/init.d/mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!（重启mysql）[root@chy01 mysql]# lsauto.cnf chy01-bin.000004 chy01.err db1 mysql xtrabackup_binlog_pos_innodbchy01-bin.000001 chy01-bin.000005 chy01.pid ibdata1 mysql2 xtrabackup_infochy01-bin.000002 chy01-bin.000006 chylinux.000001 ib_logfile0 performance_schema zrlogchy01-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.sqlWarning: Using a password on the command line interface can be insecure.[root@chy01 mysql]# mysqldump -uroot -paminglinux db1 > /tmp/db1.sqlWarning: 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 restartRestarting 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)? yesWarning: 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: Yesno 这里看到报了一个错：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: 42017-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 4745662017-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: 12362017-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 4的pos 具体操作如下[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 startupless查看时可以看到at 后面的pos 为120这时需要到从服务器上操作执行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（主的数据库）
# vim /etc/my.cnfdo////# vim /etc/my.cnf//000`id`4`name`400013| Tables_in_zhucong || t1 |1in000| Tables_in_zhucong || t1 |1in000，如需转载请自行联系原作者