MySQL主从复制的搭建（mysql5.7.26）

(1) 2个或以上的数据库实例
(2) 主库需要开启二进制日志 
(3) server_id要不同,区分不同的节点
(4) 主库需要建立专用的复制用户 (replication slave)
(5) 从库应该通过备份主库,恢复的方法进行"补课"
(6) 人为告诉从库一些复制信息(ip port user pass,二进制日志起点)
(7) 从库应该开启专门的复制线程

1.服务器的准备

也可以是多实例

1920.168.80.90	主库
192.168.80.91	从库

2. 主库开启二进制文件

主库

[mysqld]
user=mysql
basedir=/opt/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
log_bin=/data/binlog/mysql-bin
gtid-mode=on
enforce-gtid-consistency=true
port=3306
[mysql]
socket=/tmp/mysql.sock

从库

[mysqld]
user=mysql
basedir=/opt/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=7
log_bin=/data/binlog/mysql-bin
gtid-mode=on
enforce-gtid-consistency=true
port=3307
[mysql]
socket=/tmp/mysql.sock

数据库的初始化

mysql --initialize-insecure --user=mysql --datadir=/data/mydal/data  --basedir=/opt/mysql

启动配置文件

cat >/etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/opt/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF

启动：

systemctl start mysqld

3. 主库需要建立专用的复制用户

mysql -uroot -p123456 -e "grant replication slave on *.* to repl@'192.168.80.%' identified by '123'"

4. “补课”

主库

mysqldump -uroot -p123456 -A --master-data=2 --single-transaction -R -E --triggers >/tmp/full.sql

scp 到从库

从库

mysql 
mysql> set sql_log_bin=0;
mysql> source /tmp/full.sql

5. 告诉从库信息

head -40 /tmp/full.sql

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=778;

help change master to
[root@db01 ~]# mysql
CHANGE MASTER TO 
MASTER_HOST='192.168.80.90',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000009',
MASTER_LOG_POS=778,
MASTER_CONNECT_RETRY=10;

6. 从库开启复制线程(IO,SQL)

start slave;

7. 检查主从复制状态

mysql 
show slave status \G

错误：

Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'repl@192.168.80.90:3307' - retry-time: 10  retries: 4

解决：

stop slave;
reset slave all;

CHANGE MASTER TO 
MASTER_HOST='192.168.80.90',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000009',
MASTER_LOG_POS=778,
MASTER_CONNECT_RETRY=10;

成功：

show slave status \G
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

8. 验证

主库

create database zhucong;

从库：

show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| gtid               |
| moni               |
| mysql              |
| performance_schema |
| sys                |
| yq                 |
| zhucong            |
+--------------------+
8 rows in set (0.00 sec)