1.什么是复制?
复制:是将一台MySQL实例(master),发生的DML、DDL等修改操作记录到binlog中,源源不断传输副本库,副本库应用日志,达到一个和主库数据接近一致的状态。
应用场景
a. 备份。
b. 高可用。
c. 读写分离
d. 分布式架构
2.主从复制前提(搭建过程)
2.1 2台以上数据库实例,server_id ,server_uuid
[root@db01 ~]# systemctl start mysqld3307
[root@db01 ~]# systemctl start mysqld3308
[root@db01 ~]# systemctl start mysqld3309
[root@db01 ~]# netstat -tulnp
[root@db01 ~]# mysql -S /tmp/mysql3307.sock -e "select @@server_id;select @@server_uuid"
[root@db01 ~]# mysql -S /tmp/mysql3308.sock -e "select @@server_id;select @@server_uuid"
[root@db01 ~]# mysql -S /tmp/mysql3309.sock -e "select @@server_id;select @@server_uuid"
2.2 主库开启binlog
[root@db01 ~]# cat /etc/my.cnf
log_bin=/data/binlog/mysql-bin
binlog_format=row
[root@db01 ~]# mysql -S /tmp/mysql3307.sock -e "select @@log_bin;"
+-----------+
| @@log_bin |
+-----------+
| 1 |
+-----------+
2.3 主库开启专门的复制用户
[root@db01 ~]# mysql -S /tmp/mysql3307.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123'"
2.4 “补课”: 备份主库恢复至从库
[root@db01 ~]# mysqldump -S /tmp/mysql3307.sock -A --master-data=2 >/tmp/full.sql
[root@db01 ~]# mysql -S /tmp/mysql3308.sock -e "source /tmp/full.sql"
[root@db01 ~]# mysql -S /tmp/mysql3309.sock -e "source /tmp/full.sql"
2.5 开启主从
2.5.1 help 查看开启格式
help change master to;
[root@db01 ~]# grep '\-- \CHANGE' /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=444;
[root@db01 ~]# mysql -S /tmp/mysql3308.sock
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000008',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;
2.5.2 开启复制线程
start slave;
2.5.3 检测状态
[root@db01 ~]# mysql -S /tmp/mysql3308.sock -e "show slave status\G"|grep "Running:"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db01 ~]# mysql -S /tmp/mysql3309.sock -e "show slave status\G"|grep "Running:"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes