本篇是在Linux部署完MySql后，再部署主从服务器(从服务器可以是部署Window机器上的)的记录。MySql 搭建传送门

主从服务器配置

Tip

shell> 代表Linux命令
mysql> 代表MySql命令

一.配置主MySql

1.修改shell> vi /etc/my.cnf

server-id=1 #服务器唯一标识
log_bin=master-bin #启动MySQL二进制日志，即数据同步语句，从数据库会一条一条的执行这些语句
log_bin_index=master-bin.index 
expire_logs_days=7 #自动清理 7 天前的log文件,可根据需要修改

#下面是指定记录二进制日志的数据库，即需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可。
#binlog_do_db=DB1
#binlog_do_db=DB2
#binlog_do_db=DB3
#binlog_ignore_db 指定不记录二进制日志的数据库，即不需要复制的数据库名，如果有多个数据库，重复设置这个选项即可。
#其中需要注意的是，binlog_do_db和binlog_ignore_db为互斥选项，一般只需要一个即可。

2.重启

shell> /etc/init.d/mysqld restart

3.创建slave用到的账号

#192.168.17.%通配符，表示0-255的IP都可访问主服务器，正式环境请配置指定从服务器IP。若将 192.168.17.% 改为 %，则任何ip均可作为其从数据库来访问主服务器
mysql> create user masterbackup@'IP地址设置' identified by '密码设置';
mysql> grant replication slave  on *.*  to masterbackup@'IP自己配置';
mysql> flush privileges;

示例：
mysql> create user masterbackup@'%' identified by 'password';
mysql> grant replication slave  on *.*  to masterbackup@'%';
mysql> flush privileges;

4.创建项目需要用到的账号

mysql> create user root@'IP自己配置|%|192.168.0.%|192.168.0.2' identified by '密码自己配置';
mysql> grant all privileges  on *.*  to root@'IP自己配置';
mysql> flush privileges;

示例：
mysql> create user root@'%' identified by 'password';
mysql> grant all privileges  on *.*  to root@'%';
mysql> flush privileges;

5.记录主服务器的状态

mysql> show master status;

+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |  3499048 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+

记录上面的数据。后面配置从MySql的时候需要用到

二.配置从MySql

1.修改 /etc/my.cnf（windows中是C:/ProgramData/MySQL/MySQL Server 5.7/my.ini）

server-id=2 服务器唯一标识，如果有多个从服务器，每个服务器的server-id不能重复，跟IP一样是唯一标识，如果你没设置server-id或者设置为0，则从服务器不会连接到主服务器。
relay-log=slave-relay-bin #启动MySQL二进制日志，可以用来做数据备份和崩溃恢复，或主服务器挂掉了，将此从服务器作为其他从服务器的主服务器。
relay-log-index=slave-relay-bin.index
#下面指定同步的数据库，如果复制多个数据库，重复设置这个选项即可。若在master端不指定binlog-do-db，则在slave端可用replication-do-db来过滤。
#replicate-do-db=DB1
#replicate-do-db=DB2 
#replicate-ignore-db 不需要同步的数据库，如果有多个数据库，重复设置这个选项即可。
#其中需要注意的是，replicate-do-db和replicate-ignore-db为互斥选项，一般只需要一个即可

2.创建跟服务器同样的项目账号

mysql> create user root@'IP自己配置|%|192.168.0.%|192.168.0.2' identified by '密码自己配置';
mysql> grant all privileges  on *.*  to root@'IP自己配置';
mysql> flush privileges;

3.指定主服务器地址端口用户密码

mysql> stop slave; #停止slave数据同步（若有需要）
#指定主服务器
mysql> change master to master_host='服务器IP',master_port=3306,master_user='masterbackup',master_password='自己设置的密码',master_log_file='master-bin.000001',master_log_pos=3499048;
#启动slave服务
mysql> start slave;


#查看服务状态
mysql> show slave status\G

下面两个值都是Yes的时候表示启动正常，如果其中有个不为Yes可搜索网上的解决方案
Slave_IO_Running: Yes
Slave_SQL_Running: Yes


关注下面的Err提示，可以更快的解决遇到的问题
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

三.备份&还原数据库

1.备份

shell> mysqldump --no-defaults --help
-B：指定数据库
-F：刷新日志
-R：备份存储过程函数
-E: 备份事件

#如果报错 mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect
#ln -s /opt/soft/mysql/mysql.sock /tmp/mysql.sock #自己Mysql的安装位置创建软连接，然后再执行
shell> mkdir -p /home/bak
shell> mysqldump --no-defaults -uroot -p密码 -B -F -R -E --master-data DB1 DB2 DB3 | gzip > /home/bak/bak.gz

设定crontab，每天4点30分备份一次数据库
30  4   *   *   *   /opt/soft/mysql/bin/mysqldump --no-defaults -uroot -p密码 -B -F -R -E --master-data DB1 DB2 DB3 | gzip > /home/bak/bak.gz

2.还原

如果哪一天数据库数据不小心删除了，，首先得先定位drop的时间

#查看当前binlog文件状态
mysql> show master status; 
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000011 |  9499048 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
记录上面的 master-bin.000011 待用

shell> mysqlbinlog --no-defaults --help
#查看MySql binlog
#--database=DB 指定数据库
#--base64-output=decode-rows -v  解密成人类可读语言
#--start-datetime='2020-04-01 19:00:00' 指定起始时间
#--stop-datetime='2020-04-01 19:52:00' 指定结束时间

#假设我们的误操作是今天11点38分之前，那么我们调取那段时间的记录
#这里的/data/mysql/master-bin.000011 就是上面的master-bin.000011，目录的话都是my.cnf里面配置的
shell> mysqlbinlog --no-defaults --database=DB1 --base64-output=decode-rows -v --start-datetime='2020-04-02 11:35:00' --stop-datetime='2020-04-02 11:38:00' /data/mysql/master-bin.000011 | less

找到下面的这个 drop schema DB1

#200402 11:37:20 server id 1  end_log_pos 708266 CRC32 0xa76a23a2       Query   thread_id=4327  exec_time=1     error_code=0
SET TIMESTAMP=1585798640/*!*/;
SET @@session.sql_mode=1075839008/*!*/;
/* ApplicationName=DataGrip 2019.1.2 */ drop schema DB1
/*!*/;

上面显示了时间是 00402 11:37:20,这是我们的结束时间

继续寻找起始时间,我们需要查看 bak文件

#解压缩文件
shell> gzip -d /home/bak/bak.gz #这个是我们的日常备份文件
shell> less bak

定位到最后，发现它有这样有一句话 -- Dump completed on 2020-04-02 10:59:41

这个时间就是我们的起始时间

---

下面，我们开始恢复数据

#将当前备份的数据库文件导入到数据库中
shell> mysql -uroot -p密码 < bak   #还原所有数据库数据至备份点

shell> mysqlbinlog --no-defaults --database=DB1 --base64-output=decode-rows -v --start-datetime='2020-04-02 10:59:41' --stop-datetime='2020-04-02 11:37:20' /data/mysql/master-bin.000011 > new1.sql
shell> mysqlbinlog --no-defaults --database=DB1 --start-datetime='2020-04-02 10:59:41' --stop-datetime='2020-04-02 11:37:20' /data/mysql/master-bin.000011 > new2.sql
 
shell> mysql -uroot -p密码 < bak
shell> mysql -uroot -p密码 < new2.sql  #这里可能出错，对比new1.sql，查找错误的原因，多半是自增主键冲突。建议就是要么停止写入的服务器，要么修改表结构