MySQL-主从复制

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