LNMP+HAProxy+Keepalived负载均衡（四）- MySQL双机互备及自动备份

• 配置 MySQL 数据库的账户密码（以下命令仅用参考，这里的目的是要有一个可供内网其他服务器访问本机数据库的账户）；

# 停止数据库服务
service mysql stop
# 免验证启动数据库服务
mysqld --user=mysql --skip-grant-tables --skip-networking
# 登录mysql
mysql -u root mysql
# 修改用户密码的几种方式
mysql> UPDATE user SET authentication_string=PASSWORD('dbpwd') where USER='dbadmin';
mysql> UPDATE user SET authentication_string=PASSWORD('dbpwd') where USER='dbbaker';
mysql> UPDATE user SET Password=PASSWORD('dbpwd') where USER='dbadmin';
mysql> alter user user() identified by "dbpwd";
# 使配置生效
mysql> FLUSH PRIVILEGES;
mysql> quit;

# 重启mysql服务，注意，这里的mysql如果找不到服务，可以尝试mysqld，再不行就通过ps -ef | grep mysql找到进程ID(pid)，然后通过kill -9 pid停止服务；
service mysql restart
mysql -udbadmin -p
# Enter password: <输入新密码newpassword>
# 创建用户并配置权限：
mysql> GRANT ALL ON *.* TO 'dbadmin'@'%' IDENTIFIED BY 'dbpwd' WITH GRANT OPTION;
# 移除权限
# REVOKE ALL ON *.* TO 'dbadmin'@'%';
FLUSH PRIVILEGES;
# 全局层级：*.*
# 数据库层级：db_name.*
# 表层级：db_name.tbl_name
# ALL是指分配所有权限，具体权限可以通过下面的查询语句查看

# 查询用户权限
select * from mysql.user \G;
# 删除用户
# delete from mysql.user where Host <> '%';
# 至此，数据库可以通过以上账户进行远程连接了。

• 配置MySQL的双机互备 - 主数据库服务器（192.168.6.200 /etc/my.cnf 的配置请参考 LNMP+HAProxy+Keepalived负载均衡（三）- 配置文件汇总 ）；

# 在主数据库服务器上执行配置：
vim /etc/my.cnf

# 修改配置内容:
server-id=6                           # 数据库集群中唯一
lower_case_table_names=1              # 新增行，数据表不区分大小写
replicate_wild_do_table=sync_db_name.%   # 只同步“sync_db_name”库下的表
log-slave-updates=YES                # 从服务器同步后记录日志

# 保存配置后重启MySQL服务：
service mysql restart
# 进入MySQL命令行，查看主数据库状态信息，其中的“File”和“Position”值后面会用到：
mysql> use sync_db_name;
mysql> show master status;

• 配置MySQL的双机互备 - 备数据库服务器（192.168.6.210）；

# 停止slave：
mysql> stop slave;

# 进入MySQL命令行，配置要同步的主库来源：
mysql> CHANGE MASTER TO MASTER_HOST='192.168.6.200',MASTER_PORT=10002,MASTER_USER='dbadmin',MASTER_PASSWORD='dbpwd',MASTER_LOG_FILE='mysql-bin.000016',MASTER_LOG_POS=1285;
# MASTER_HOST：主数据库的IP；
# MASTER_PORT：主数据库的端口(int)，默认3306；
# MASTER_USER，MASTER_PASSWORD：主数据库账户、密码；
# MASTER_LOG_FILE：主数据库中查询到的“File”值；
# MASTER_LOG_POS：主数据库中查询到的“Position”值；

# 启动slave：
mysql> start slave;

# 检查slave:
mysql> show slave status \G;

# 命令行汇总：
show master status;
stop slave;

Slave_IO_Running、Slave_SQL_Running 均显示为 Yes 说明主备同步服务正常运行，如下图：

• 同步数据库的初始状态；

  在启动同步服务前，还需要手动同步下数据库的初始状态，之后可以通过修改其中主数据库中的表来查看从数据库是否同步变更；

# 锁定要同步的数据库（192.168.6.200）：
msyql> use sync_db_name;
msyql> flush tables with read lock;
# 将要同步的数据库导出脚本：
mysqldump -udbadmin -pdbpwd sync_db_name >/home/backup/sync_db_name.sql
# 解锁前面锁定的数据库：
msyql> unlock tables;

# 将上方备份的 sql 拷贝到从数据库服务器（192.168.6.210），然后创建同名数据库，恢复数据：
msyql> create database sync_db_name;
msyql> use sync_db_name;
msyql> source /home/backup/sync_db_name.sql;

• 做双机互备或多机循环互备；
  然后将主从数据库服务器反转（即将192.168.6.200和192.168.6.210的主备身份调换），然后再配置一次同步即可。即192.168.6.200变更会同步到192.168.6.210，反之亦然。简单讲就是：
  双机互备：A主B从 + A从B主；
  多机循环互备：A主B从 + B主C从 + C主N从 + N主A从（建议不要过多，尤其是数据库数据量大，且变更频繁的情况下，同步毕竟也是有延迟的）；

• 数据库的自动备份（前三步前面的文章都有提到）；

  • 安装计划工具；

  yum -y install crontabs

  • 编辑MySQL的配置文件；

  vim /etc/my.cnf
  
  # 添加配置文件内容：
  [mysqldump]
  # 用于备份数据库
  user=dbbaker
  password=dbpwd

  • 重启数据库服务；

  service mysql restart & service mysql status

  • 准备备份脚本；

  mkdir -p /home/bakup/lgd_system
  
  # 编辑bakdb.sh的内容：
  echo 'mysqldump sync_db_name | gzip > /home/backup/lgd_system/sync_db_name_$(date +%Y%m%d_%H%M%S).sql.gz' > /home/bakup/bakdb.sh
  chmod +x /home/bakcup/bakdb.sh
  
  # 解压缩指定的备份文件：
  gunzip sync_db_name_*.gz

  • 添加备份计划；

# 方式一：
crontab -e
# 方式二：
vim /etc/crontab
  
# 编辑计划：
# Example of job definition:
# .---------------- minute (0 - 59) *表示每分钟
# |  .------------- hour (0 - 23) *表示每小时
# |  |  .---------- day of month (1 - 31) *表示每天
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ... *表示每月
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat *表示每天
# |  |  |  |  |