MYSQL - 主从备份 & 主主备份

*************************************************

【主从备份】
分别在主服务器与从服务器开启数据复制的配置；
1.主服务器操作（master）：
1.1.修改master服务器mysql的配置
sudo vim /etc/mysql/mysql.conf.d/mysql.cnf
......
[mysqld]
......
# 标识数据库的唯一ID，主从的id不能重复
server-id=10;

# 开启bin-log，并指定文件目录和文件名前缀
log-bin = mysql-bin

# 控制将binlog写入磁盘的频率
sync_binlog=1; # 此参数至关重要，不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。
# sync_binlog参数详细说明：
# sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令（刷新binlog_cache中的信息到磁盘），而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
# sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
# 系统默认将sync_binlog设置成0。

# 还有一些其他选用配置参数:
# binlog-do-db=testsql # 指定同步testsql数据库；不写则表明同步除ignore外的所有库。
# binlog-ignore-db=mysql# 指定不同步mysql数据库。
......
# !! !! !! 下面还有一项bind-address，一定要屏蔽，不然slave连不进来
# bind-address = 127.0.0.1

1.2.修改配置后保存退出；重启mysql服务：
sudo service mysql stop
sudo service mysql start

1.3.如果此时master数据库上有以前的配置也需要slave数据库同步的，可以将数据库的操作记录推送到slave，进行同步；
操作命令：
mysql> flush tables with read lock; // 数据库只读锁定命令，防止导出数据库的时候有数据写入。unlock tables命令解除锁定。

在shell终端输入，将对testsql数据库的操作记录导出到/home/lilei/opt/testsql.sql。
[linux@a ~]$ mysqldump -uroot tsetsql -p >/home/lilei/opt/testsql.sql

在shell终端输入，将导出的操作记录推送到slave数据库（192.168.1.157）所在的/home/lilei/opt/目录下。
[linux@a ~]$ rsync -e "ssh -p22" -avpgolr /home/lilei/opt/testsql.sql 192.168.1.157:/home/lilei/opt/

1.4.创建一个用户repl，用于slave节点链接master节点时使用。
创建用户并指定登录IP，'用户名'@'登录IP' 密码为'replabc':
mysql> create user 'repl'@'192.168.1.157' identified with mysql_native_password by 'reqlabc';

赋予'repl'用户[REOLICATION SLAVE]权限，并且限制该用户只能在'192.168.1.157'登录:
mysql> grant REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.157';

刷新授权表信息:
mysql> flush privileges;

/****** 用户操作其他辅助命令 ******/
删除repl用户: mysql> drop user 'repl'@'192.168.1.157';
查看所有用户权限: mysql> show grants;
查看repl用户权限: mysql> show grants for repl@'192.168.1.157';

1.5.获取主节点当前binary log文件名和位置(position)
mysql> show master status;
终端会显示如下表格：

 

 

表中这些参数就是slave数据库需要使用的信息，先记录下来。

2.从服务器操作（slave）：
2.1.修改slave服务器上的mysql配置：
sudo vim /etc/mysql/mysql.conf.d/mysql.cnf
......
# 需要在mysqld这一项下面添加
[mysqld]
......
# 标识数据库的唯一ID，主从的id不能重复
server-id=2;
# 开启binlog日志系统（主从备份时不需要，主主备份时需要）
# log-bin=mysql-bin
......

2.2.修改配置后保存退出；重启mysql服务：
sudo service mysql stop
sudo service mysql start

2.3.登录MySQL，如果需要导入master之前的操作日志，需要此步操作；
先创建master日志操作的数据库testsql（使用utf8编码）：
mysql> create database testsql character set utf8 collate utf8_general_ci;
选中该数据库:
mysql> use testsql;
读取操作日志:
mysql> source /home/lilei/opt/testsql.sql;
操作成功之后，slave上的testsql与master上的testsql就已经达成一致了，现在还需要把slave连接到master，之后的操作就会一直持续同步了。

2.3.然后配置连接到master的配置
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.157', // master的IP地址
mysql> MASTER_USER='repl',
mysql> MASTER_PASSWORD='replabc',
mysql> MASTER_LOG_FILE='mysql-bin.000006',
mysql> MASTER_LOG_POS=1735;

// 配置完可以使用命令查看配置：mysql> show slave status \G;

2.4.开启主从同步
mysql> start slave;
// 再次查看配置
mysql> show slave status \G;
当Slave_IO_Runing项与Slave_SQL_Runing项均为Yes,即表示开启同步成功；
可在master上进行增删操作后，查看slave，数据库内容始终与master保持一致。
/************************************************************************************************/
/* 若进行上述操作后，发现Slave_IO_Runing为No，可以查看mysql的错误日志来分析问题 */
/* 日志存放路径为：/var/log/mysql/error.log */
/* 常见错误可能有： */
/* 1> 当slave的虚拟机是 用安装好mysql的虚拟机镜像出来的，此时两个mysql的UUID会重复， */
/* 导致主从备份开启失败，错误码：1593。（uuid路径: /var/lib/mysql/auto.cnf） */
/* 2> 主机与从机网络连接不通，可使用shell的ping命令检测网络。 */
/* 3> 从机的IP地址与master指定用户登录的IP地址不一致,错误码：1045。 */
/* （上述用例中，从机需要在IP地址为'192.168.1.157'上连接master） */
/* 4> 当master的binlog文件不存在，或者MASTER_LOG_FILE='xxx'的文件名填写错误。 */
/* 错误码：1236。 */
/************************************************************************************************/

【主主备份】
在上述的主从备份环境中，对主机的读写操作都能同步到从机；但是如果对从机进行写操作，主机就无法感知，导致两侧数据不一致（从机只能读取）。
为了解决这种问题，我们可以采用主主备份，即两侧都相互监测，修改对两侧都生效。
+---------------------------------------------------------------------------------------------------------------------------------------+
温馨提示：
在做主主同步前，提醒下需要特别注意的一个问题：
主主复制和主从复制有一些区别，因为多主中都可以对服务器有写权限，所以设计到自增长重复问题，例如：
出现的问题（多主自增长ID重复）
1）首先在A和B两个库上创建test表结构;
2）停掉A，在B上对数据表test(存在自增长属性的ID字段)执行插入操作，返回插入ID为1;
3）然后停掉B，在A上对数据表test(存在自增长属性的ID字段)执行插入操作，返回的插入ID也是1;
4）然后 同时启动A,B，就会出现主键ID重复

解决方法：
只要保证两台服务器上的数据库里插入的自增长数据不同就可以了
如：A插入奇数ID，B插入偶数ID，当然如果服务器多的话，还可以自定义算法，只要不同就可以了
在下面例子中，在两台主主服务器上加入参数，以实现奇偶插入！
记住:在做主主同步时需要设置自增长的两个相关配置，如下：
auto_increment_offset 表示自增长字段从那个数开始，取值范围是1 .. 65535。这个就是序号。如果有n台mysql机器，则从第一台开始分为设1，2...n
auto_increment_increment 表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535。如果有n台mysql机器，这个值就设置为n。

在主主同步配置时，需要将两台服务器的：
auto_increment_increment 增长量都配置为2
auto_increment_offset 分别配置为1和2。这是序号，第一台从1开始，第二台就是2，以此类推.....
这样才可以避免两台服务器同时做更新时自增长字段的值之间发生冲突。（针对的是有自增长属性的字段）
+----------------------------------------------------------------------------------------------------------------------------------------+

[1.主机（192.168.1.156）上的操作]
1.1.修改配置文件
[linux-shell]$ sudo vim /etc/mysql/mysql.conf.d/mysql.cnf
......
[mysqld]
......

server-id=10 # 标识数据库的唯一ID，主从的id不能重复

log-bin = mysql-bin # 开启bin-log，并指定文件目录和文件名前缀

sync_binlog=1 # 数据库操作1次即写入磁盘；此参数不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。

auto-increment-offset = 1 # 设置本地数据表中自增字段默认是以 1 开始的

auto-increment-increment = 2 # 设置本地数据表中自增字段的步长为 2

......
# !! !! !! 下面还有一项bind-address，一定要屏蔽，不然slave连不进来
# bind-address = 127.0.0.1

1.2.保存修改的配置文件后，重启mysql服务
[linux-shell]$ sudo service mysql stop
[linux-shell]$ sudo service mysql start

1.3.（当主机上有独有数据时操作此步）登录mysql，导出本地数据库的操作日志，传输到备用机器上，复现操作
导出配置前，先锁定数据库，不能写入，以保证在导出的过程中不能有新的更改，导出遗漏；
mysql> flush tables with read lock;

在shell终端输入，将对testdb数据库的操作记录导出到/home/lilei/opt/testdb.sql。
[linux-shell]$ mysqldump -uroot testdb -p >/home/lilei/opt/testdb.sql

在shell终端输入，将导出的操作记录推送到备用机器（192.168.1.157）所在的/home/lilei/opt/目录下。
[linux-shell]$ rsync -e "ssh -p22" -avpgolr /home/lilei/opt/testdb.sql 192.168.1.157:/home/lilei/opt/

### 备用机拿到testdb.sql文件后，即可还原对testdb数据库的所有操作
备用机登录mysql，然后创建需要同步的数据库：
mysql> create database testdb character set utf8 collate utf8_general_ci;

选中需要同步的数据库：
mysql> use testdb;

使用主机传输过来的操作记录进行数据库还原：
mysql> source /home/lilei/opt/testdb.sql;

1.4. 创建一个用于同步的用户
创建用户并指定来源IP:
mysql> create user 'repl'@'192.168.1.157' identified with mysql_native_password by 'reqlabc';

赋予'repl'用户[REOLICATION SLAVE]权限，并且限制该用户只能在'192.168.1.157'登录:
mysql> grant REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.157';

刷新授权表信息:
mysql> flush privileges;

1.5.获取主节点当前binary log文件名和位置(position)
mysql> show master status;
终端会显示如下表格：

 

 

 表中这些参数就是备用机器数据库需要使用的信息，先记录下来。

[2.备用机器（192.18.1.157）上的操作]
2.1.修改配置文件（与主机配置类似）
[linux-shell]$ sudo vim /etc/mysql/mysql.conf.d/mysql.cnf
......
[mysqld]
......

server-id=2 # 标识数据库的唯一ID，主从的id不能重复

log-bin = mysql-bin # 开启bin-log，并指定文件目录和文件名前缀

sync_binlog=1 # 数据库操作1次即写入磁盘；此参数不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。

auto-increment-offset = 2 # 设置本地数据表中自增字段默认是以 1 开始的（与主机区分开）

auto-increment-increment = 2 # 设置本地数据表中自增字段的步长为 2

......
# !! !! !! 下面还有一项bind-address，一定要屏蔽，不然主机数据库连不进来
# bind-address = 127.0.0.1

2.2.保存修改的配置文件后，重启mysql服务
[linux-shell]$ sudo service mysql stop
[linux-shell]$ sudo service mysql start

2.3.创建一个用于同步的用户
创建用户并指定来源IP:
mysql> create user 'repl'@'192.168.1.156' identified with mysql_native_password by 'reqlabc';

赋予'repl'用户[REOLICATION SLAVE]权限，并且限制该用户只能在'192.168.1.156'登录:
mysql> grant REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.156';

刷新授权表信息:
mysql> flush privileges;

2.4.设置好与主机同步的操作
先关闭slave服务:
mysql> stop slave;
配置主机信息：
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.1.156',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='replabc',
-> MASTER_LOG_FILE='msyql-bin.000009',
-> MASTER_LOG_POS=1130;
启动slave服务：
mysql> start slave;
查看配置：
mysql> show slave status \G;
当Slave_IO_Runing项与Slave_SQL_Runing项均为Yes,即表示开启同步成功；

2.5.获取主节点当前binary log文件名和位置(position),并配置主机连接到备用机
获取备用机的binlog文件名和位置
mysql> show master status;
终端会显示如下表格：

 

 

 表中这些参数就是主机数据库需要使用的信息，先记录下来。

### 然后在 主机上配置 这些参数，来连接到备用机器；
先关闭slave服务:
　　mysql> stop slave;
配置备用机信息：
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.1.157',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='replabc',
-> MASTER_LOG_FILE='msyql-bin.000005',
-> MASTER_LOG_POS=154;
启动slave服务：
　　mysql> start slave;
查看配置：
　　mysql> show slave status \G;
　　当Slave_IO_Runing项与Slave_SQL_Runing项均为Yes,即表示开启同步成功；