1、介绍:
MySQL主从又叫做Replication或AB复制。MySQL主从是基于binlog的,主节点上必须开启binlog才能进行主从。
主从过程大概分三个步骤:
(1)主节点将更改操作记录到binlog里
(2)从节点将主节点的binlog事件(sql语句)同步到从节点本机上并记录在relaylog里中继日志
(3)从节点根据relaylog里面的sql语句按顺序执行
2、主配置:MySQL服务已安装并且已开启的状态下进行
1、基础配置
(1)编辑配置文件:
[root@lzl ~]# vi /etc/my.cnf
(2)在最后添加两行:
server_id = 8
log_bin = master-binlog
(3)重启一下:
[root@lzl ~]# service mysql restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
(4)添加环境变量并备份MySQL库:
[root@lzl ~]# export PATH=$PATH:/usr/local/mysql/bin/
[root@lzl ~]# mysqldump -uroot -ptest mysql > /tmp/mysql.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
(5)创建一个库以保存数据使用:
[root@lzl ~]# mysql -uroot -ptest -e 'create database kei'
mysql: [Warning] Using a password on the command line interface can be insecure.
(6)将MySQL库恢复成新建的库,作为测试数据:
[root@lzl ~]# mysql -uroot -ptest kei < /tmp/mysql.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
2、数据库配置
(1)进入数据库:
[root@lzl ~]# mysql -uroot -p
Enter password:
(2)创建用作同步数据的用户并赋予权限:
mysql> grant replication slave on *.* to 'repl' @192.168.60.9 identified by '000000';
Query OK, 0 rows affected, 1 warning (0.00 sec)
(3)锁住表以保持表内数据不变:
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.03 sec)
(4)查看主机状态:
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| master-binlog.000004 | 1154190 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
3、从配置:MySQL服务已安装并且已开启的状态下进行
1、基础配置
(1)编辑配置文件:
[root@hh ~]# vi /etc/my.cnf
在最后添加两行:
server_id = 9
log_bin = slave-binlog
(2)重启MySQL服务:
[root@hh ~]# service mysql restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
(3)在主节点上将文件复制到从节点上,并在从节点上查看文件大小是否一致:
[root@lzl ~]# scp /tmp/mysql.sql root@192.168.60.9:/tmp/
The authenticity of host '192.168.60.9 (192.168.60.9)' can't be established.
ECDSA key fingerprint is SHA256:yfhFf20ZhHOC8k7Gh0XWXpnCy90l99dVH07pbH+k8zw.
ECDSA key fingerprint is MD5:05:7c:5c:52:88:29:6e:9c:61:42:00:62:92:85:94:55.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.60.9' (ECDSA) to the list of known hosts.
root@192.168.60.9's password:
mysql.sql 100% 1163KB 28.4MB/s 00:00[root@lzl ~]# ls -la /tmp/mysql.sql
-rw-r--r--. 1 root root 1191126 May 18 04:31 /tmp/mysql.sql
[root@hh ~]# ls -la /tmp/mysql.sql
-rw-r--r--. 1 root root 1191126 May 18 04:51 /tmp/mysql.sql
(4)添加环境变量并创建一个跟主节点上一样的库:
[root@hh ~]# export PATH=$PATH:/usr/local/mysql/bin/
[root@hh ~]# mysql -uroot -p000000 -e 'create database kei'
mysql: [Warning] Using a password on the command line interface can be insecure.
(5)将内容到入库:
[root@hh ~]# mysql -uroot -p000000 kei < /tmp/mysql.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
2、数据库配置
[root@hh ~]# mysql -uroot -p
Enter password:mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> change master to master_host='192.168.60.8',masterr_user='repl',master_password='000000',master_log_file='maaster-binlog.000004',master_log_pos=1154190;
Query OK, 0 rows affected, 2 warnings (0.03 sec)mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
在主节点上将表解锁:
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
在从节点上进行验证:(防火墙关闭)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.60.8
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-binlog.000004
Read_Master_Log_Pos: 1154190
Relay_Log_File: hh-relay-bin.000002
Relay_Log_Pos: 324
Relay_Master_Log_File: master-binlog.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
若出现Yes,如上图状态,即表示主从配置正常