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
AI 代码解读
3配置主服务器
[root@chy01 src]# vim /etc/my.cnf
(更改主的配置文件)
[mysqld]里面增加如下两项
server-id = 11
log_bin=chylinux
其中server-id = 11 （11指的是主机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
（查看主服务器上面的备份数据库）
AI 代码解读
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 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 startup
less查看时可以看到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
（主的数据库）
AI 代码解读
4测试主从同步
# vim /etc/my.cnfdo////# vim /etc/my.cnf//000`id`4`name`400013| Tables_in_zhucong || t1 |1in000| Tables_in_zhucong || t1 |1in000，如需转载请自行联系原作者
AI 代码解读
AI 代码解读
