一、环境准备
机器IP 机器说明
192.168.148.128 用作master
192.168.148.129 用作slave
注意：
1.关闭防火墙和selinux；
#关闭防火墙
systemctl stop firewalld
#开机禁用防火墙
systemctl disable firewalld
#临时关闭selinux
setenforce 0
二、修改master配置/etc/my.cnf
[mysqld]
#数据目录定义
datadir=/var/lib/mysql
#socket文件定义
socket=/var/lib/mysql/mysql.sock
#错误日志定义
log-error=/var/log/mysqld.log
#进程文件定义
pid-file=/var/run/mysqld/mysqld.pid
#跳过密码验证(非必选)
#skip-grant-tables
#开启binlog二进制日志
log-bin=mysql-bin
#中继日志
relay_log=mysql-relay-bin
#设置server_id,具有唯一性
server-id=1
#跳dns过解析，是客户端连接服务端接速度加快
skip-name-resolve
#每次写入binlog的操作都写入到磁盘
sync_binlog=1
#最大连接数
max_connections=200
#二进制日志自动删除/过期的天数。默认值为0，nlog的操作都写入到磁盘
sync_binlog=1
#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除
expire_logs_days=7
三、修改slave配置/etc/my.cnf
[mysqld]
#数据目录定义
datadir=/var/lib/mysql
#socket文件定义
socket=/var/lib/mysql/mysql.sock
#错误日志定义
log-error=/var/log/mysqld.log
#进程文件定义
pid-file=/var/run/mysqld/mysqld.pid
#跳过密码验证
#skip-grant-tables
#开启binlog二进制日志
log-bin=mysql-bin
#中继日志
relay_log=mysql-relay-bin
#设置server_id,具有唯一性
server-id=2
#跳dns过解析，是客户端连接服务端接速度加快
skip-name-resolve
#每次写入binlog的操作都写入到磁盘
sync_binlog=1
#最大连接数
max_connections=200
#二进制日志自动删除/过期的天数。默认值为0，nlog的操作都写入到磁盘
sync_binlog=1
#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除
expire_logs_days=7
四、分别重启两台mysql
#systemctl restart mysqld
1
五、在master新增复制用户并授权
新增复制用户slave并授权
在从数据库新增slave用户
#新增用户 slave 允许从ip为192.168.148.129的机器,通过密码Zdkx#2021来访问
mysql> grant replication slave,replication client on *.* to 'slave'@'192.168.148.129' identified by 'Zdkx#2021';
#刷新使其生效;
mysql> FLUSH PRIVILEGES;
查看master状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 记住文件名 mysql-bin.000007和 Position 154;
后面在从库链接主库时需要
六、在slave上添加主库信息
添加主库信息
# 先停止以下,避免会出错;
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
# 设置master服务器的一些参数 参数说明看下面的解释
mysql> change master to master_host='192.168.148.128', master_user='slave', master_password='pwd.slave', master_port=3306, master_log_file='mysql-bin.000007', master_log_pos=154, master_connect_retry=30;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
#说明
master_host='192.168.148.128' # Master的IP地址
master_user='slave' # 用于同步数据的用户（在Master中授权的用户）
master_password='Zdkx#2021' # 同步数据用户的密码
master_port=3306 # Master数据库服务的端口
master_log_file='mysql-bin.000007' #指定Slave从哪个日志文件开始读复制数据（可在Master上使用show master status查看到日志文件名）
master_log_pos=154 # 从哪个POSITION号开始读
master_connect_retry=30 #当重新建立主从连接时，如果连接建立失败，间隔多久后重试。单位为秒，默认设置为60秒，同步延迟调优参数。
#开启从库
mysql> start slave;
如果启动失败，可以reset slave 之后再 start slave
Query OK, 0 rows affected (0.00 sec)
# 查看slave status
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.148.128
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000005
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes (说明成功)
Slave_SQL_Running: Yes （说明成功）
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 740
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 12afccf6-a283-11eb-9762-000c294fef43
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
七、测试MySQL的主从备份
测试就是在主库中进行DDL以及DML语句的执行，看从库是否会跟主库一样发生变化
在master创建数据
mysql> create database test01;
mysql> use test01;
mysql> CREATE TABLE `test01` (
-> `id` int(255) NOT NULL AUTO_INCREMENT,
-> `username` varchar(255) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
mysql> insert into test01 values(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)
mysql> select * from test01;
+----+----------+
| id | username |
+----+----------+
| 1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)
在slave上查看数据是否同步
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| syh |
| sys |
| test01 |
+--------------------+
6 rows in set (0.00 sec)
mysql> use test01;
#有数据，说明同步成功
mysql> select * from test01;
±—±---------+
| id | username |
±—±---------+
| 1 | zhangsan |
±—±---------+
————————————————
脚本整理
主库 ：create user 'slave'@'192.168.56.46' identified by '123456';
授权访问： grant replication slave on *.* to 'slave'@'192.168.56.46' identified by '123456';
备库上修改master:change master to master_host='192.168.57.98', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000002', master_log_pos=154, master_connect_retry=30;
开启从库：start slave
重置 reset slave
停止：stop slave
显示主库链接状态：show master status
Slave_IO_Running: Yes (说明成功)
Slave_SQL_Running: Yes （说明成功）
主库上修改东西：CREATE TABLE `test01` (`id` int(255) NOT NULL AUTO_INCREMENT,`username` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
