MySQL5.6主从配置和定时备份

Posted on | In | Visitors

MySQL5.6 的 Master-Slave配置

在预装好MySQL5.6版本的两台机器A(192.168.1.123)、B(192.168.1.124)之后，可按照下文的操作进行配置主从备份，关于非主从线程的MySQL同步机制可移步 Windows下面的MYSQL同步（非主从线程同步机制）参考，本篇是基于MySQL内部机制的同步。希望可以帮你解决问题！

由于MySQL5.5版本之后主从之间不能直接将主服务器信息写入从服务器配置文件中，这里需要注意！因此只需要在主配置文件master.cnf中加入必要的一些选项即可，找到[mysqld]段加入如下内容：

1
2
3
4
5
6
7
8
9
10
11
sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
log-bin=mysql-bin #slave会基于此log-bin来做replication
server-id=1 #master的标示
read-only=0
binlog-do-db=mydb #同步数据库，可以复制写多个
binlog-ignore-db=mysql #忽略需要同步的数据库，可以复制写多个
log-slave-updates=1 #这个参数一定要加上，否则不会给更新的记录写入到二进制文件中
innodb_flush_log_at_trx_commit=1
sync_binlog=1
skip-external-locking #加上可以避免因资源锁引起MySQL服务停止
skip-name-resolve #加上可以避免因资源锁引起MySQL服务停止

启动mysql

首先在主服务器A(192.168.1.123)上使用root权限启动MySQL，这里可以使用Daemon守护进程方式进行，

1
/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/master.cnf --basedir=/usr/local/mysql --datadir=/var/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=root --log-error=/var/logs/mysql/master.err --pid-file=/var/mysql.pid --explicit_defaults_for_timestamp &

授权

主服务器A(192.168.1.123)上登录MySQL（root）进行从服务器同步授权

1
/usr/local/mysql/bin/mysql -uroot -h192.168.1.123 -p

授权命令(只是刚搭建时需要执行，后面不再执行，相应属性可以根据实际情况更改) :

1
mysql> grant replication slave on *.* to 'kaifa'@'192.168.1.124' identified by '******';

查看主服务器运行状态

1
2
3
4
5
6
7
mysql> show master stauts;
+------------------+------------+--------------+------------------+-------------------+
| File             | Position   | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+------------+--------------+------------------+------ ------------+
| mysql-bin.000001 |         20 |         mydb |            mysql |                   |
+------------------+------------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

记录File 和 Position 列对应值（从库使用）。

配置从库

从服务器B(192.168.1.124)配置以及启动，在配置文件slave.cnf中加入必要的一些选项即可，找到[mysqld]段加入如下内容

1
2
3
4
5
6
7
8
sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
log_bin=mysql-bin
server-id=2 #slave的标示
replicate-do-db=mydb
binlog-do-db=mydb
binlog-ignore-db=mysql
sync_binlog=1
skip-external-locking

启动从库

使用守护进程启动从MySQL服务

1
/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/slave.cnf --basedir=/usr/local/mysql --datadir=/var/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=root --log-error=/var/logs/mysql/slave.err --pid-file=/var/mysql.pid --explicit_defaults_for_timestamp &

登录从库

从服务器B(192.168.1.124)上登录MYSQL（root）

1
/usr/local/mysql/bin/mysql -uroot -h192.168.1.124 -p

从服务器也可以查看主服务器的状态:

1
mysql> show master status;

主内存数据落盘

停止从服务与主同步,然后再主服务器上MySQL执行 flush logs;

1
mysql> stop slave;

设置同步点

在服务器上执行如下命令，重新配置主服务器信息同步起点，
其中参数说明如下:

  • master_host 主服务器ip
  • master_user 主服务器同步操作用户
  • master_password 主服务器同步操作用户口令
  • master_port 主服务器端口
  • master_log_file 上面主服务器状态中File列的值，如：mysql-bin.000001
  • master_log_pos 上面主服务器状态中Position列的值，如：20
    1
    2
    3
    4
    5
    6
    7
    mysql> change master to
    > master_host='192.168.1.123',
    > master_user='kaifa',
    > master_password='******',
    > master_port=3306,
    > master_log_file='mysql-bin.000001',
    > master_log_pos=20;
重新开启从服务同步;
1
mysql> start slave;
查看从运行状态

(其中Slave_IO_Running和Slave_SQL_Running 正常状态都是Yes)
如果状态非正常可以到下面错误列查看原因并解决，解决完错误信息重新配置，也可能需要重启MySQL服务，停止从同步服务，操作如上6-10步骤;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
mysql> show slave status\G;
*************************** 1\. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.123
Master_User: kaifa
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 20
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 52197
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mydb
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: 20
Relay_Log_Space: 25
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: c4b7b0d0-d87e-11dd-a697-6cf049644ef1
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 125
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
1 row in set (0.00 sec)
ERROR:
No query specified

如果遇到Last_IO_Errno 1236错误可以认真分析并解决问题,必要时可以尝试关闭防火墙IPTABLES(/etc/init.d/iptables stop)和selinux，修改/etc/selinux/config,将SELINUX改为disabled即可永久禁用。

从库定时备份

mysql备份在从服务上/var/mysql_bakup/ 目录下面，从机器B(192.168.1.124)MySQL每三小时定时备份。

  • 使用root权限创建mysql 导出任务脚本/usr/sbin/mysqlbak_shell 并将脚本写入定时任务（权限root）;

  • MYSQL备份目录位于/var/mysql_bakup/;

  • 执行手工导出命令如下：导出文件已gzip压缩;

    1
    /usr/local/mysql/bin/mysqldump -ukaifa -h192.168.1.124 -p******  --default-character-set=utf8 --opt --extended-insert=false --triggers -R --singe-transaction  mydb | gzip > /var/mysql_bakup/mydb_`date +%Y%m%d_%H-%M-%S`.sql.gz。

  • 执行手工导入命令如下：需先解压gzip包;

    1. gzip -d 对应时间段gz备份包;

    2. 再执行导入命令导入这个解压SQL文件（使用二进制模式）;

      1
      /usr/local/mysql/bin/mysql --binary-mode=1 -ukaifa -h192.168.1.124 -p****** mydb > /var/mysql_bakup/解压gz包文件。

  • 按照理论上面备份是每天会备份8个压缩包，后期可以按照周计划删除一周前的压缩包;

  • 定时任务 0 /3 /usr/sbin/mysqlbak_shell（3小时备份一次）。

(The End)

坚持原创技术分享，您的支持将鼓励我继续创作！