主从
Mysql主从又叫Replication、AB复制(不同于PXC)。简单讲就是A与B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，实现数据实时同步
mysql主从是基于binlog，主上需开启binlog才能进行主从
主从过程大概有3个步骤
- 主将更改操作记录到binlog里
- 从将主的binlog事件（sql语句） 同步本机上并记录在relaylog里
- 从根据relaylog里面的sql语句按顺序执行
主从作用
实时灾备，用于故障切换
读写分离，提供查询服务
备份，避免影响业务
主从形式
- 一主一从
- 主主复制
- 一主多从—扩展系统读取的性能，因为读是在从库读取的
- 多主一从—5.7版本开始支持
- 联级复制
MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。
原理：
MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。
- 主库将所有的写操作记录在binlog日志中，并生成log dump线程，将binlog日志传给从库的I/O线程
- 从库生成两个线程，一个是I/O线程，另一个是SQL线程
- I/O线程去请求主库的binlog日志，并将binlog日志中的文件写入relay log（中继日志）中
- SQL线程会读取relay loy中的内容，并解析成具体的操作，来实现主从的操作一致，达到最终数据一致的目的
配置
实现MySQL主从复制需要进行的配置：
主服务器：
- 开启二进制日志
- 配置唯一的server-id
- 获得master二进制日志文件名及位置
- 创建一个用于slave和master通信的用户账号
从服务器：
- 配置唯一的server-id
- 使用master分配的用户账号读取master二进制日志
- 启用slave服务
具体实现过程如下：
主从复制配置步骤：
- 确保从数据库与主数据库里的数据一致
- 在主数据库里创建一个同步账户授权给从数据库使用
- 配合主数据库（修改配置文件）
- 配置从数据库（修改配置文件）
一、准备工作：
- 主从数据库版本最好一致
- 主从数据库内数据保持一致
搭建两台MYSQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作
+ 主数据库：192.168.0.1 /Linux-MySQL
+ 从数据库：192.168.0.2 /Linux-MySQL
二、主数据库master修改：
1.修改mysql配置
找到主数据库的配置文件my.cnf(或者my.ini)，我的在/etc/mysql/my.cnf,在[mysqld]部分插入如下两行：
[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=1 #设置server-id
2.重启mysql，创建用于同步的用户账号
打开mysql会话shell
mysql -hlocalhost -uname -ppassword
创建用户：用户：rel1密码：slavepass
3.授权
主服务器授权从服务器特定账号登录
mysql> CREATE USER 'repl'@'192.168.0.2' IDENTIFIED BY 'slavepass';#创建用户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.2';#分配权限
mysql>flush privileges; #刷新权限
4.查看master状态，记录二进制文件名(mysql-bin.000003)和位置(73)：
mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73 | test | manual,mysql |
+------------------+----------+--------------+------------------+
三、从服务器slave修改：
1.修改mysql配置
同样找到my.cnf配置文件，添加server-id
[mysqld]
server-id=2 #设置server-id，必须唯一
2.重启mysql，打开mysql会话，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)：
复制代码
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.0.1',
-> MASTER_USER='rep1',
-> MASTER_PASSWORD='slavepass',
-> MASTER_LOG_FILE='mysql-bin.000003',
-> MASTER_LOG_POS=73;
3.启动slave同步进程：
mysql>start slave;
4.查看slave状态：
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.1
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 11662
Relay_Log_File: mysqld-relay-bin.000022
Relay_Log_Pos: 11765
Relay_Master_Log_File: mysql-bin.000013
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
...
当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。
接下来就可以进行一些验证了，比如在主master数据库的test数据库的一张表中插入一条数据，在slave的test库的相同数据表中查看是否有新增的数据即可验证主从复制功能是否有效，还可以关闭slave（mysql>stop slave;）,然后再修改master，看slave是否也相应修改（停止slave后，master的修改不会同步到slave），就可以完成主从复制功能的验证了。
还可以用到的其他相关参数：
master开启二进制日志后默认记录所有库所有表的操作，可以通过配置来指定只记录指定的数据库甚至指定的表的操作，具体在mysql配置文件的[mysqld]可添加修改如下选项：
# 不同步哪些数据库
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema
# 只同步哪些数据库，除此之外，其他不同步
binlog-do-db = game
如之前查看master状态时就可以看到只记录了test库，忽略了manual和mysql库。
操作流程汇总
关闭防火墙以SELINUX
[root@icocos ~]# systemctl stop firewalld
[root@icocos ~]# systemctl disable firewalld
[root@icocos ~]# sed -ri 's/(SELINUX=).*/\1disabled/g' /etc/selinux/config
[root@icocos ~]# setenforce 0
安装mysql
安装依赖包
[root@icocos ~]# yum -y install ncurses-devel openssl-devel openssl cmake mariadb-devel
创建用户和组
[root@icocos ~]# groupadd -r -g 306 mysql
[root@icocos ~]# useradd -M -s /sbin/nologin -g 306 -u 306 mysql
下载二进制格式的mysql软件包
[root@icocos ~]# cd /usr/src/
[root@icocos src]#wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
解压软件至/usr/local/
[root@icocos src]# ls
debug kernels mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
[root@icocos src]# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@icocos src]# ls /usr/local/
bin etc games include lib lib64 libexec mysql-5.7.22-linux-glibc2.12-x86_64 sbin share src
[root@icocos src]# cd /usr/local/
[root@icocos local]# ln -sv mysql-5.7.22-linux-glibc2.12-x86_64/ mysql
"mysql" -> "mysql-5.7.22-linux-glibc2.12-x86_64/"
[root@icocos local]# ll
总用量 0
drwxr-xr-x. 2 root root 6 11月 5 2016 bin
drwxr-xr-x. 2 root root 6 11月 5 2016 etc
drwxr-xr-x. 2 root root 6 11月 5 2016 games
drwxr-xr-x. 2 root root 6 11月 5 2016 include
drwxr-xr-x. 2 root root 6 11月 5 2016 lib
drwxr-xr-x. 2 root root 6 11月 5 2016 lib64
drwxr-xr-x. 2 root root 6 11月 5 2016 libexec
lrwxrwxrwx. 1 root root 36 9月 7 22:20 mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 9月 7 22:19 mysql-5.7.22-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 11月 5 2016 sbin
drwxr-xr-x. 5 root root 49 9月 3 23:02 share
drwxr-xr-x. 2 root root 6 11月 5 2016 src
修改目录/usr/locaal/mysql的属主属组
[root@icocos local]# chown -R mysql.mysql /usr/local/mysql
[root@icocos local]# ll /usr/local/mysql -d
lrwxrwxrwx. 1 mysql mysql 36 9月 7 22:20 /usr/local/mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/
添加环境变量
[root@icocos local]# ls /usr/local/mysql
bin COPYING docs include lib man README share support-files
[root@icocos local]# cd
[root@icocos ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@icocos ~]# . /etc/profile.d/mysql.sh
[root@icocos ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
建立数据存放目录
[root@icocos ~]# cd /usr/local/mysql
[root@icocos mysql]# mkdir /opt/data
[root@icocos mysql]# chown -R mysql.mysql /opt/data/
[root@icocos mysql]# ll /opt/
总用量 0
drwxr-xr-x. 2 mysql mysql 6 9月 7 22:25 data
初始化数据库
[root@icocos mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
//这个命令的最后会生成一个临时密码，此处密码是1EbNA-k*BtKo
配置mysql
[root@icocos ~]# ln -sv /usr/local/mysql/include/ /usr/local/include/mysql
"/usr/local/include/mysql" -> "/usr/local/mysql/include/"
[root@icocos ~]# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
[root@icocos ~]# ldconfig -v
生成配置文件
[root@icocos ~]# cat > /etc/my.cnf <<EOF
> [mysqld]
> basedir = /usr/local/mysql
> datadir = /opt/data
> socket = /tmp/mysql.sock
> port = 3306
> pid-file = /opt/data/mysql.pid
> user = mysql
> skip-name-resolve
> EOF
配置服务启动脚本
[root@icocos ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@icocos ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@icocos ~]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld
启动mysql
[root@icocos ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/icocos.err'.
.. SUCCESS!
[root@icocos ~]# ps -ef|grep mysql
root 4897 1 0 22:38 pts/2 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
mysql 5075 4897 6 22:38 pts/2 00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=icocos.err --pid-file=/opt/data/mysql.pid --socket=/tmp/mysql.sock --port=3306
root 5109 4668 0 22:38 pts/2 00:00:00 grep --color=auto mysql
[root@icocos ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
LISTEN 0 80 :::3306 :::*
修改密码
使用临时密码修改
[root@icocos ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit
Bye
mysql主从配置
确保从数据库与主数据库的数据一样先在主数据库创建所需要同步的库和表
[root@icocos ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.22 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. Al
Oracle is a registered trademark of Oracle Corporation and
affiliates. Other names may be trademarks of their respect
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the curr
mysql> create database yan;
Query OK, 1 row affected (0.00 sec)
mysql> create database lisi;
Query OK, 1 row affected (0.00 sec)
mysql> create database wangwu;
Query OK, 1 row affected (0.00 sec)
mysql> use yan;
Database changed
mysql> create table tom (id int not null,name varchar(100)not null ,age tinyint);
Query OK, 0 rows affected (11.83 sec)
mysql> insert tom (id,name,age) values(1,'zhangshan',20),(2,'wangwu',7),(3,'lisi',23);
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from tom;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | zhangshan | 20 |
| 2 | wangwu | 7 |
| 3 | lisi | 23 |
+----+-----------+------+
3 rows in set (0.00 sec)
备份主库
备份主库时需要另开一个终端，给数据库上读锁，避免在备份期间有其他人在写入导致数据同步的不一致
[root@icocos ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.22 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.76 sec)
此锁表的终端必须在备份完成以后才能退出（退出锁表失效）
备份主库并将备份文件传送到从库
[root@icocos ~]# mysqldump -uroot -p123456 --all-databases > /opt/all-20180907.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@icocos ~]# ls /opt/
all-20180907.sql data
[root@icocos ~]# scp /opt/all-20180907.sql root@192.168.0.2:/opt/
The authenticity of host '192.168.0.2 (192.168.0.2)' can't be established.
ECDSA key fingerprint is SHA256:7mLj77SFk7sPkhjpMPfdK3nZ98hOuyP4OKzjXeijSJ0.
ECDSA key fingerprint is MD5:a0:1b:eb:7f:f0:b6:7b:73:97:91:4c:f3:b1:89:d8:ea.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.0.2' (ECDSA) to the list of known hosts.
root@192.168.0.2's password:
all-20180907.sql 100% 784KB 783.3KB/s 00:01
解除主库的锁表状态，直接退出交互式界面即可
mysql> quit
Bye
在从库上恢复主库的备份并查看是否与主库的数据保持一致
[root@icocos ~]# mysql -uroot -p123456 < /opt/all-20180907.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@icocos ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.22 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lisi |
| mysql |
| performance_schema |
| sys |
| wangwu |
| yan |
+--------------------+
7 rows in set (0.18 sec)
mysql> use yan;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from tom;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | zhangshan | 20 |
| 2 | wangwu | 7 |
| 3 | lisi | 23 |
+----+-----------+------+
3 rows in set (0.06 sec)
在主数据库创建一个同步账户授权给从数据使用
[root@icocos ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.22 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create user 'repl'@'192.168.0.2' identified by '123456';
Query OK, 0 rows affected (5.50 sec)
mysql> grant replication slave on *.* to 'repl'@'192.168.0.2';
Query OK, 0 rows affected (0.04 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.09 sec)
配置主数据库编辑配置文件
[root@icocos ~]# vim /etc/my.cnf
[root@icocos ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
//添加以下内容
log-bin=mysql-bin //启用binlog日志
server-id=1 //主数据库服务器唯一标识符 主的必须必从大
log-error=/opt/data/mysql.log
重启mysql服务
[root@icocos ~]# service mysqld restart
Shutting down MySQL..... SUCCESS!
Starting MySQL.Logging to '/opt/data/mysql.log'.
............................... SUCCESS!
[root@icocos ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
LISTEN 0 80 :::3306 :::*
查看主库的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
配置从数据库
编辑配置文件
[root@icocos ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
//添加以下内容：
server-id=2 //设置从库的唯一标识符 从的必须比主小
relay-log=mysql-relay-bin //启用中继日志relay log
error-log=/opt/data/mysql.log
重启从库的mysql服务
[root@icocos ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
[root@icocos ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
LISTEN 0 80 :::3306 :::*
配置并启动主从复制
mysql> change master to
-> master_host='192.168.0.1',
-> master_user='repl',
-> master_password='123456',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.28 sec)
查看从服务器状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes //此处必须是yes
Slave_SQL_Running: Yes //此处必须是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: 154
Relay_Log_Space: 527
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 5abf1791-b2af-11e8-b6ad-000c2980fbb4
Master_Info_File: /opt/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
测试验证在主服务器的yan库的tom表插入数据:
mysql> use yan;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from tom;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | zhangshan | 20 |
| 2 | wangwu | 7 |
| 3 | lisi | 23 |
+----+-----------+------+
3 rows in set (0.09 sec)
mysql> insert tom(id,name,age) value (4,"yyl",18);
Query OK, 1 row affected (0.14 sec)
mysql> select * from tom;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | zhangshan | 20 |
| 2 | wangwu | 7 |
| 3 | lisi | 23 |
| 4 | yyl | 18 |
+----+-----------+------+
4 rows in set (0.00 sec)
在从数据库查看是否数据同步
mysql> use yan;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from tom;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | zhangshan | 20 |
| 2 | wangwu | 7 |
| 3 | lisi | 23 |
| 4 | yyl | 18 |
+----+-----------+------+
4 rows in set (0.00 sec)
