MySQL 主备数据库相关操作

MySQL 主备数据库相关操作

创建集群

mysql -h$MYSQL_IP -u$MYSQL_USER -p"$MYSQL_PASS" -e "
GRANT REPLICATION SLAVE ON *.* to 'repl'@'$slave' IDENTIFIED BY '$MYSQL_PASS';
flush privileges;
flush tables with read lock";

获取 master 节点File($binlog_file) 和 Position($bin_pos) 位置

备份主库

mysqldump -h$MYSQL_IP -u$MYSQL_USER -p"$MYSQL_PASS" --opt --all-databases > /tmp/db_1.sql

导入从库

mysql -h$MYSQL_IP1 -u$MYSQL_USER -p"$MYSQL_PASS" < /tmp/db_1.sql

创建集群：

mysql -h$MYSQL_IP1 -u$MYSQL_USER -p"$MYSQL_PASS" -e "
unlock tables;
CHANGE MASTER TO MASTER_HOST='$MYSQL_IP',
MASTER_USER='repl',
MASTER_PASSWORD='$MYSQL_PASS',
MASTER_LOG_FILE='$binlog_file',
MASTER_LOG_POS=$bin_pos;
start slave;"

命令

查看主备状态

show master status\G;
show slave status\G;

查看主节点binlog日志

show master logs;

检查主从同步

1. master 节点 show master status\G; Position不应该为0
2. master 节点 show processlist; STATE 状态应该为 Master has sent all binlog to slave; waiting for more updates

mysql> SELECT * FROM information_schema.processlist where user = "repl"\G;
*************************** 1. row ***************************
     ID: 22
   USER: repl
   HOST: 172.20.20.21:31919
     DB: NULL
COMMAND: Binlog Dump
   TIME: 1791141
  STATE: Master has sent all binlog to slave; waiting for more updates
   INFO: NULL
1 row in set (0.00 sec)

1. 确认如下二对参数值是否一致，如果不一致，说明主从库已经挂了，需要先同步数据：

mysql > show slave status\G;
              Master_Log_File: Relay_Master_Log_File    # binlog file
          Read_Master_Log_Pos: Exec_Master_Log_Pos      # 同步指针

1. slave 节点 Slave_IO_Running 与 Slave_SQL_Running 状态都要为Yes

mysql> show slave status\G;
*************************** 1. row ***************************
...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

1. slave 节点 show processlist;，存在如下2行：

mysql> show processlist\G;
*************************** 1. row ***************************
     Id: 1
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 50
  State: Slave has read all relay log; waiting for more updates
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 1792327
  State: Waiting for master to send event
   Info: NULL

binlog

MySQL中的binlog日志记录了数据库中数据的变动，便于对数据的基于时间点和基于位置的恢复，但是binlog也会日渐增大，占用很大的磁盘空间，因此，要对binlog使用正确安全的方法清理掉一部分没用的日志。

在删除binlog日志之前，首先对binlog日志备份，以防万一

帮助命令：

mysql> help purge
Name: 'PURGE BINARY LOGS'
Description:
Syntax:
PURGE { BINARY | MASTER } LOGS
    { TO 'log_name' | BEFORE datetime_expr }

The binary log is a set of files that contain information about data
modifications made by the MySQL server. The log consists of a set of
binary log files, plus an index file (see
http://dev.mysql.com/doc/refman/5.7/en/binary-log.html).

The PURGE BINARY LOGS statement deletes all the binary log files listed
in the log index file prior to the specified log file name or date.
BINARY and MASTER are synonyms. Deleted log files also are removed from
the list recorded in the index file, so that the given log file becomes
the first in the list.

This statement has no effect if the server was not started with the
--log-bin option to enable binary logging.

URL: http://dev.mysql.com/doc/refman/5.7/en/purge-binary-logs.html

Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

手动清理 binlog

查看主库和从库正在使用的binlog是哪个文件

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000049
         Position: 763545655
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.20.20.22
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000049
          Read_Master_Log_Pos: 763860016
               Relay_Log_File: gse-2-relay-bin.000142
                Relay_Log_Pos: 763859782
        Relay_Master_Log_File: mysql-bin.000049
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...
             Master_Server_Id: 1
                  Master_UUID: fe29dfe3-d853-11e9-8eda-6c92bf9969ab
             Master_Info_File: ../mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
...
1 row in set (0.01 sec)

• 清理方法一：删除指定日期以前的日志索引中binlog日志文件

purge master logs before '2018-12-01 17:20:00';
PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);  # 删除10天前日志

35 2 * * * mysql -e "PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);" >/dev/null 2>&1

• 清理方法二：删除指定日志文件的日志索引中binlog日志文件

purge master logs to'mysql-bin.000048';

注意：

• 时间和文件名一定不可以写错，尤其是时间中的年和文件名中的序号，以防不小心将正在使用的binlog删除！！！
• 切勿删除正在使用的binlog！！！

自动清理binlog

使用如下方法查询当前binlog的过期时间，若为0表示不过期

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set (0.00 sec)

使用如下方法设置binlog过期时间，设置30表示30天后自动清理之前的过期日志

mysql> set global expire_logs_days = 30;

注意：

• 过期时间设置的要适当，对于主从复制，要看从库的延迟决定过期时间，避免主库binlog还未传到从库便因过期而删除，导致主从不一致！

或通过配置文件配置

vim /etc/my.cnf
expire_logs_days = x  # binlog 自动删除的天数。默认值为0，表示`没有自动删除`

从库异常恢复

同步异常

如果从库的Slave未启动，Slave_IO_Running为NO。可能是主库是的master的信息有变化，查看主库show master status;，记录下File、Position字段，假设为mysql-bin.xxxx、xxxx，在从库执行：

mysql> stop slave;
mysql> change master to master_host='ip', master_user='username', master_password='password', master_log_file='mysql-bin.xxxx', master_log_pos=xxxx;
mysql> start slave;

Last_IO_Errno: 1593

如果从库的Slave_IO_Running为NO，Last_SQL_Error为：

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

在搭建主从复制的过程中，要确保两台机器的 server-id 是唯一的，server-id 的命名规则（服务器 ip 地址的最后一位+本 MySQL 服务的端口号），解决方法：

mysql> stop slave;
mysql> reset slave;
mysql> CHANGE MASTER TO MASTER_HOST='<master-ip>', MASTER_USER='<user>', MASTER_PASSWORD='<password>', master_log_file='mysql-bin.xxxx', master_log_pos=xxxx;
mysql> flush privileges;
mysql> start slave;

Last_SQL_Errno: 1062

如果从库的Slave_IO_Running为NO，Last_SQL_Error为：

Error 'Duplicate entry '60314' for key 'PRIMARY'' on query. ...

可能是master未向slave同步成功，但slave中已经有了记录。造成的冲突可以在从库上执行：

mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1000;
mysql> start slave;

或

mysql -h$MYSQL_IP1 -u$MYSQL_USER -p"$MYSQL_PASS" -e "
stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1000;
start slave;"

SQL_SLAVE_SKIP_COUNTER 指的是数量，可以自行加大。跳过几步。再restart slave就可以了。

总结

• 主键冲突、表已存在等错误代码如1062、1032、1060等，可以在mysql主配置文件指定略过此类异常并继续下条sql同步，这样也可以避免很多主从同步的异常中断

[mysqld]
slave-skip-errors = 1032,1060,1062

其他

修改链接数

mysql> show variables like "%max_connec%";
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
| max_connections    | 1000  |
+--------------------+-------+
2 rows in set (0.00 sec)

mysql> set GLOBAL max_connections = 1200;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "%max_connec%";
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
| max_connections    | 1200  |
+--------------------+-------+
2 rows in set (0.00 sec)