主从
1.主从部署必要条件:
●主库开启binlog日志(设置log-bin参数)
●主从server-id不同
●从库服务器能连通主库
2.主从同步的内部分析介绍
从库生成两个线程,一个I/O线程,一个SQL线程;
i/o线程去请求主库 的binlog,并将得到的binlog日志写到relay log(中继日志) 文件中;
主库会生成一个 log dump 线程,用来给从库 i/o线程传binlog;
具体步骤
1.首先创建进行备份的用户的授权操作:
grant replication slave on *.* to 'slave_account'@'%' identified by '1q2w3E4R.123abc!@#';
2.执行 show master status; 记录下position 和file 值
mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000002 | 1466 | | mysql,information_schema,performance_schema,sys | |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)
3.到从库去配置要复制的master主机ip binlog日志名字File 和日志读取路径Position
mysql>change master to master_host='10.140.0.7',master_user='slave_account',master_password='1q2w3E4R.123abc!@#',master_log_file='mysql-bin.000002',master_log_pos=1466;
4.启动从库复制
mysql>start slave;
5.检查从库的状态
观察从库上的两个线程,一个I/O线程Slave_IO_Running,一个SQL线程Slave_SQL_Running,是否正常运行,都为yes.